Copying a table wholesale in SQLite

Question

I'm aware of that a very similar question has been asked here. However, I'm unable to implement the solution there given: when I try to run the script beginning ATTACH DATABASE 'other.db' AS other; I get an error saying that it couldn't connect to other.db, despite there being such a file in that directory.

I've got two SQLite databases, database_a.db and database_b.db. I want to copy a table, named Table_to_be_copied, from database_a.db to database_b.db. Table_to_be_copied has no analogue or equivalent in database_b.db and I can't assume anything about said table except for its name; I can't assume how many columns it has, or what they're called.

I want to copy Table_to_be_copied wholesale: name, columns and rows. What's the easiest way of achieving this? (For what it's worth, I'm connecting to the databases via Python scripts.) I know one way of copying the table, but it's not very elegant: it involves writing a script which reads all the data required from database_a.db into memory and then recreating the table from scratch in database_b.db. But is there a better way?

`ATTACH DATABASE 'other.db' AS other;` how about removing the quotations, and using this instead? `ATTACH DATABASE other.db AS other;` — , Jul 07 '20 at 14:20
@ChloroxBleach69 Here's what that gives me: `Result: no such column: combined_data.db` // `At line 1:` // `ATTACH DATABASE combined_data.db AS combined;` — Tom Hosker, Jul 07 '20 at 14:28
@ChloroxBleach69 I'm running the script via the DB Browser for SQLite, if that makes a difference. (I tend to test the waters by running scripts through said browser. For more proper stuff I use Python.) — Tom Hosker, Jul 07 '20 at 14:30
Hmm how about trying c.execute("ATTACH DATABASE ? AS combined", ("other.db",))` — , Jul 07 '20 at 14:41
@ChloroxBleach69 Thanks for the tip. After a bit more weeping and gnashing of teeth, I've managed to get it working. — Tom Hosker, Jul 08 '20 at 09:01

Tom Hosker · Answer 1 · 2020-07-08T10:23:37.530

To the best of my knowledge, the shortest snippet of Python code for copying an SQLite table wholesale is as follows.

Say that you want to copy a table called Platform_Specifics from database_a.db to database_b.db. Then run:

import sqlite3

def create_local_specifics():
    connection = sqlite3.connect("database_b.db")
    cursor = connection.cursor()
    query0 = "DROP TABLE IF EXISTS Platform_Specifics;"
    query1 = ("CREATE TABLE Platform_Specifics\n"+
              "(\n"+
              "    specific TEXT,\n"+
              "    value TEXT,\n"+
              "    PRIMARY KEY(specific)\n"+
              ");")
    cursor.execute(query0)
    cursor.execute(query1)
    connection.commit()
    connection.close()

def transfer_local_specifics():
    connection = sqlite3.connect("database_a.db")
    cursor = connection.cursor()
    query0 = "ATTACH DATABASE 'database_b.db' AS other;"
    query1 = ("INSERT INTO other.Platform_Specifics "+
              "SELECT * FROM Platform_Specifics;")
    query2 = "DETACH other;"
    cursor.execute(query0)
    cursor.execute(query1)
    connection.commit()
    cursor.execute(query2)
    connection.commit()
    connection.close()

def copy_local_specifics():
    create_local_specifics()
    transfer_local_specifics()

copy_local_specifics()

One obvious drawback of the above: you do need to know the CREATE script for the table you wish to copy, which may be cumbersome.

Of course, I'd be eager to hear from anyone who knows of a shorter and/or simpler method.

Copying a table wholesale in SQLite

1 Answers1