Python and sqlite3 - importing and exporting databases

Question

I'm trying to write a script to import a database file. I wrote the script to export the file like so:

import sqlite3

con = sqlite3.connect('../sqlite.db')
with open('../dump.sql', 'w') as f:
    for line in con.iterdump():
        f.write('%s\n' % line)

Now I want to be able to import that database. I have tried :

import sqlite3

con = sqlite3.connect('../sqlite.db')
f = open('../dump.sql','r')
str = f.read()
con.execute(str)

but I'm not allowed to execute more than one statement. Is there a way to get it to run an SQL script directly?

Solution 1

sql = f.read() # watch out for built-in `str`
cur.executescript(sql)

Documentation.

Solution 2

Try using

con.executescript(str)

Documentation

Connection.executescript(sql_script)
    This is a nonstandard shortcut that creates an intermediate cursor object
    by calling the cursor method, then calls the cursor’s executescript
    method with the parameters given.

Or create the cursor first

import sqlite3

con = sqlite3.connect('../sqlite.db')
f = open('../dump.sql','r')
str = f.read()
cur = con.cursor()
cur.execute(str)

Comments

[RichardTheKiwi]

@adam - I think you mean to create a cursor first if you copied that from the documentation ("cur.")

[mechanical_meat]

The sqlite3 module allows either usage; take your pick.

[RichardTheKiwi]

@adam - I am aware of that. But "cur" is not defined in the question code, right?

[JPC]

Is there a more efficient way to execute a lot of inserts? I have a lot of them and it's too slow this way

[mechanical_meat]

There are some tips here: sqlite.org/cvstrac/wiki?p=PerformanceTuning The dump should already be wrapped in a transaction for you (to avoid opening and closing database between inserts).

[Enrico]

@JPC try executemany()