Dump data from malformed SQLite in Python

问题:

I have a malformed database. When I try to get records from any of two tables, it throws an exception:

DatabaseError: database disk image is malformed

I know that through commandline I can do this:

sqlite3 ".dump" base.db | sqlite3 new.db

Can I do something like this from within Python?

回答1:

As far as i know you cannot do that (alas, i might be mistaken), because the sqlite3 module for python is very limited.

Only workaround i can think of involves calling the os command shell (e.g. terminal, cmd, ...) (more info) via pythons call-command:

Combine it with the info from here to do something like this:

This is done on an windows xp machine: Unfortunately i can't test it on a unix machine right now - hope it will help you:

    from subprocess import check_call

    def sqliterepair():
        check_call(["sqlite3", "C:/sqlite-tools/base.db", ".mode insert", ".output C:/sqlite-tools/dump_all.sql", ".dump", ".exit"])
        check_call(["sqlite3", "C:/sqlite-tools/new.db", ".read C:/sqlite-tools/dump_all.sql", ".exit"])
        return

The first argument is calling the sqlite3.exe. Because it is in my system path variable, i don't need to specify the path or the suffix ".exe". The other arguments are chained into the sqlite3-shell.

Note that the argument ".exit" is required so the sqlite-shell will exit. Otherwise the check_call() will never complete because the outer cmd-shell or terminal will be in suspended.

Of course the dump-file should be removed afterwards...

EDIT: Much shorter solution (credit goes to OP (see comment))

os.system("sqlite3 C:/sqlite-tools/base.db .dump | sqlite3 C:/sqlite-tools/target.db")

Just tested this: it works. Apparently i was wrong in the comments.

回答2:

If I understood properly, what you want is to duplicate an sqlite3 database in python. Here is how I would do it:

# oldDB = path to the corrupted db, 
# newDB = path to the new db
def duplicateDB(oldDB, newDB):

    con = sqlite3.connect(oldDB)
    script = ''.join(con.iterdump())
    con.close()

    con = sqlite3.connect(newDB)
    con.executescript(script)
    con.close()

    print "duplicated %s into %s" % (oldDB,newDB)

In your example, call duplicateDB('base.db', 'new.db'). The iterdump function is equivalent to dump.

Note that if you use Python 3, you will need to change the print statement.