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.
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)