Utilities for duplicating tables and creating a table with the results of a query

[davidleejy]

is there a duplicate table functionality? Otherwise, I'd be happy to submit a PR.

In sqlite3 it would look like:

import sqlite3 as sl

con = sl.connect('prompt-tune.db')

def db_duplicate_table(table_name, table_name_new, con=con):
    # Duplicates table `table_name` to a new table `table_name_new`.
    try:
        cur = con.cursor()
        cur.execute(f"""CREATE TABLE {table_name_new} AS SELECT * FROM {table_name}""")
    except Exception as e:
        print(e)
    finally:
        cur.close()

db_duplicate_table('orig_table', 'new_table')

[simonw]

I like the idea of this as a feature - design could look like this:

db["my_table"].duplicate("new_table")

Then for the CLI tool:

sqlite-utils duplicate data.db my_table new_table

Additional features that might be useful:

• Duplicate across connection aliases - so you can copy a table to another database using the duplicate method or command
• Ability to execute an arbitrary SQL query and save the results to a new table - basically a wrapper around CREATE TABLE t AS SELECT ..., again with alias support for copying to another database

[davidleejy]

I've written the code and test. Would you be able to advise how to compare table columns in a pytest function properly? Experiencing a challenge when comparing columns.

Test:

def test_duplicate(fresh_db):
    table = fresh_db.create_table(
        "table1",
        {
            "text_col": str,
            "float_col": float,
            "int_col": int,
            "bool_col": bool,
            "bytes_col": bytes,
            "datetime_col": datetime.datetime,
        },
    )
    dt = datetime.datetime.now()
    b = bytes('hello world', 'utf-8')
    data = {"text_col": "Cleo", 
            "float_col": 3.14,
            "int_col": -2,
            "bool_col": True,
            "bytes_col": b,
            "datetime_col": str(dt)}
    table1 = fresh_db["table1"]
    row_id = table1.insert(data).last_rowid
    table1.duplicate('table2')
    table2 = fresh_db["table2"]
    assert data == table2.get(row_id)
    assert table1.columns == table2.columns    # FAILS HERE

Result:

Failure is due to column types being named differently -- e.g. 'FLOAT' vs 'REAL', 'INTEGER' vs 'INT'. How should I go about comparing columns while accounting for equivalent types?

Or did I miss out something in my duplication code correctly? Here's how I did it: in db.py, I've added the following code:

class Table(Queryable):
    [...]
    def duplicate(
        self, 
        name_new: str
    ) -> "Table":
        """
        Duplicate this table in this database.

        :param name_new: Name of new table.
        """
        assert self.exists()
        with self.db.conn:
            sql = "CREATE TABLE [{new_table}] AS SELECT * FROM [{table}];".format(
                new_table = name_new,
                table = self.name,
            )
            self.db.execute(sql)
        return self.db[name_new]

[davidleejy]

Learnt that the types in Sqlite-utils differ somewhat from those in Sqlite. I've changed my test to account for this difference and the test has passed successfully. I will submit a PR.

[simonw]

Documentation:

• https://sqlite-utils.datasette.io/en/latest/python-api.html#duplicating-tables
• https://sqlite-utils.datasette.io/en/latest/reference.html#sqlite_utils.db.Table.duplicate