Python sqlite copy table from one database to another

Question

I'm using python 2.7 with the builtin sqlite3 module on Windows XP. The code looks like the following:

#!/usr/bin/env python2

import sqlite3
import sys

def open_db(nam):
    conn = sqlite3.connect(sys.argv[1])
    # Let rows returned be of dict/tuple type
    conn.row_factory = sqlite3.Row
    print "Openned database %s as %r" % (nam, conn)
    return conn

def copy_table(table, src, dest):
    print "Copying %s %s => %s" % (table, src, dest)
    sc = src.execute('SELECT * FROM %s' % table)
    ins = None
    dc = dest.cursor()
    for row in sc.fetchall():
        if not ins:
            cols = tuple([k for k in row.keys() if k != 'id'])
            ins = 'INSERT OR REPLACE INTO %s %s VALUES (%s)' % (table, cols,
                                                     ','.join(['?'] * len(cols)))
            print 'INSERT stmt = ' + ins
        c = [row[c] for c in cols]
        dc.execute(ins, c)

    dest.commit()

src_conn  = open_db(sys.argv[1])
dest_conn = open_db(sys.argv[2])

copy_table('audit', src_conn, dest_conn)

When I run this with db_copy.py src.db dest.db the source database was doubled. So I set the source file attribute to readonly. I now get:

sqlite3.OperationalError: attempt to write a readonly database

It seems somewhere the source and destination database connections are mixed? I've been debugging this for hours without finding the cause.

Solution

You are ignoring the nam parameter and using sys.argv[1] for all calls to open_db():

def open_db(nam):
    conn = sqlite3.connect(sys.argv[1])

This opens the first named database twice, as both src_conn and dest_conn. Use nam instead:

def open_db(nam):
    conn = sqlite3.connect(nam)