Migrating data from one sqlite database to multiple SQLite databases using SQLObject

Question

Till now our application has been using one SQLite database with SQLObject as the ORM. Obviously at some point we knew we had to face the SQLite concurrency problem and so we did.

We ended up splitting the current database into multiple databases. Meaning each table schema remained the same but we distributed different tables into multiple databases keeping tightly coupled tables together.

Now this works very well in a clean install of the new version of our application but upgrade to the previous versions of our application to this new version needs a special data migration before our application can start working. In this case the database migration is simple moving the tables from this single database into appropriate different databases.

To exemplify, consider this is the older structure:

single_db.db --- A single db

 * A -- Table A
 * B -- Table B
 * C -- Table C
 * D -- Table D
 * E -- Table E
 * F -- Table F

The new structure:

db1.db --- Database 1

 - A -- Table A
 - B -- Table B
 - C -- Table C
 - D -- Table D

db2.db --- Database 2

 - E -- Table E

db3.db --- Database 3

 - F -- Table F

When the upgrade will happen, our application will create the new structure with the above 3 databases and with empty tables in them. Also the older database single_db.db with all the tables and actual data will be there. Now before our application can begin working, it should move the tables or I should say copy the data from a table from the older database to the corresponding table in the corresponding new database.

I will need to write the code for this database migration. I know I can query a table using the older database connection and insert the returned rows to the corresponding table using the newer database connection. One caveat I should mention here is some of these tables can contain large number of rows. That is rows can be till 2 - 2.5 million in 2/3 tables.

So want to ask if I can use any other SLQObject tricks since I am using SQLObject on top of SQLite and also has anyone done this before?

Thanks for your help.

score 1 · Answer 1 · answered Jun 29 '12 at 13:46

I realise you probably solved this by now but for anyone googling I had to do almost exactly the same as the OP, this was the core part of the code that I used (it's modified from something I found, but I can't find it again to credit the original author, apologies!)

def _iterdump(connection, table_name):
    """
    Returns an iterator to dump a database table in SQL text format.
    """

    cu = connection.cursor()

    yield('BEGIN TRANSACTION;')

    # sqlite_master table contains the SQL CREATE statements for the database.
    q = """
       SELECT name, type, sql
        FROM sqlite_master
            WHERE sql NOT NULL AND
            type == 'table' AND
            name == :table_name
        """
    schema_res = cu.execute(q, {'table_name': table_name})
    for table_name, type, sql in schema_res.fetchall():
        if table_name == 'sqlite_sequence':
            yield('DELETE FROM sqlite_sequence;')
        elif table_name == 'sqlite_stat1':
            yield('ANALYZE sqlite_master;')
        elif table_name.startswith('sqlite_'):
            continue
        else:
            yield('%s;' % sql)

        # Build the insert statement for each row of the current table
        res = cu.execute("PRAGMA table_info('%s')" % table_name)
        column_names = [str(table_info[1]) for table_info in res.fetchall()]
        q = "SELECT 'INSERT INTO \"%(tbl_name)s\" VALUES("
        q += ",".join(["'||quote(" + col + ")||'" for col in column_names])
        q += ")' FROM '%(tbl_name)s'"
        query_res = cu.execute(q % {'tbl_name': table_name})
        for row in query_res:
            yield("%s;" % row[0])

If you pass the sqlite connection for the original db and the name of the table in the original db this generator will give back commands that you can pass to execute on the sqlite object for the new db.

When I did this I also did a count of rows first on all the tables and incremented a counter as I executed INSERT lines so I could show progress on the migration.

Migrating data from one sqlite database to multiple SQLite databases using SQLObject

1 Answers1