CODEWITHSUNDEEP
pythonsqlitepysqlite
Ayman
Ayman

Reputation: 11585

Python sqlite copy table from one database to another

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.

Upvotes: 3

Views: 13084

Answers (1)

Martijn Pieters
Martijn Pieters

Reputation: 1121992

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)

Upvotes: 3

Related Questions