Python如何解决sqlite3.DatabaseError: database disk image is malformed

• SQLite

  SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine.

  SQLite is an embedded SQL database engine. Unlike most other SQL databases, SQLite does not have a seprate server process. SQLite reads and writes directly to ordinary disk files.

  The database file format is cross-platform - you can freely copy a database between 32-bit and 64-bit systems or between big-endian and little-endian architectures.

• sqlite3.DatabaseError: database disk image is malformed

  根据SQLalchemy Error Messages)，DatabaseError Exception raised for errors that are related to the database itself, and not the interface or data being passed. This error is a DBAPI Error and originates from the database driver (DBAPI), not SQLAlchemy itself.

  仔细阅读official documents看到The C language interface to SQLite Version 2有提到：

  The SQLite library is designed to be very easy to use from a C or C++ program.

  The interface to the SQLite library consists of three core functions, one opaque data structure, and some constants used as return values. The core interface is as follows:

  • sqlite_open
  • sqlite_close
  • sqlite_exec

  sqlite_exec执行结果返回错误中的一种可能是SQLITE_CORRUPT，对应的就是the database disk image is malformed.

• SQLITE_CORRUPT

  This value is returned if SQLIte detects that the database it is working on has become corrupted.

  Corruption might occur due to a rogue process writing to the database file or it might happen due to a proviously undetected logic error in of SQLite.

  简单来讲，很大可能是准备读取的这个db文件，正好有另一个程序在向其写入数据。

• Python sqlite3

  我的代码中使用的是python的sqlite3

  The sqlite3 module was written by Gerhard Haring. It provides a SQL interface compliant with the DB-API 2.0 specification descirbed by PEP249(Python Database API Specification v2.0).

• How to corrupt an sqlite database file

  既然问题在于SQLITE_CORRUPT,那corrupt的原因呢？

  SQLite database files are ordinary disk files. That means taht any process can open the file and overwrite it with garbage. There is nothing that the SQLite library can do to defend against this.

• 中文总结

  Python sqlite3底层调用（我猜的）C语言的官方sqlite interface，当某一进程正在对sqlite database写入数据的时候复制此database file造成此db文件corrupt。

  这是数据库API本身的问题，不是Python sqlite3 or sqlalchemy library的问题。

  最好的解决角度是从写入&复制db文件那里，避免这两个动作冲突。

  当面对一个已经corrupted文件，问题就归结为如何从corrupted sqlite database file中恢复数据？

• How to recover a corrupt SQLite3 database

  # 检查结构
  sqlite3 corrupt.db "pragma integrity_check"
  # 恢复数据
  sqlite3 corrupt.db ".recover" | sqlite3 new.db
  # 重新检查结构
  sqlite3 new.db "pragma integrity_check"