在 Python 中从格式错误的 SQLite 转储数据 [英] 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

我可以在 Python 中做这样的事情吗?

Can I do something like this from within Python?

推荐答案

据我所知你不能这样做(唉，我可能弄错了)，因为 python 的 sqlite3 模块非常有限.

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

我能想到的唯一解决方法是调用 os 命令外壳(例如终端、cmd、...)(更多信息) 通过 python 调用-command:

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:

这是在 windows xp 机器上完成的:不幸的是，我现在无法在 unix 机器上测试它 - 希望它会帮助你:

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

第一个参数是调用 sqlite3.exe.因为它在我的系统路径变量中，所以我不需要指定路径或后缀.exe".其他参数链接到 sqlite3-shell 中.

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.

请注意，参数.exit"是必需的，因此 sqlite-shell 将退出.否则 check_call() 将永远不会完成，因为外部 cmd-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...

更短的解决方案(归功于 OP(见评论))

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.

这篇关于在 Python 中从格式错误的 SQLite 转储数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！