sqlite3

macOS上sqlite3等内置REPL使用的是libedit而非readline，这是其交互体验较差的原因

In Python's sqlite3, fetchone() returns the next row on each call and returns None when called after all rows are consumed; fetchall() returns a list of all remaining rows at once, returning an empty list when there is no data.

In Python's sqlite3 module, connect() returns a connection object, which is a live link between the program and the database.

cursor() creates a cursor object, which is responsible for sending SQL and iterating over the results row by row.

execute() can execute only one SQL statement at a time; putting multiple statements into the same execute() triggers a ProgrammingError.

In Python's sqlite3, an insert operation opens a transaction; if the connection is closed without calling commit(), uncommitted changes are discarded, but table creation (DDL) statements take effect immediately and survive.

In Python 3.6 and later versions, the default value of sqlite3's isolation_level is "DEFERRED"; a transaction begins when the first data-modification statement is executed and ends only when commit() or rollback() is explicitly called.

Lazy cursors are a common pitfall of many database drivers: for example, in Python's sqlite3, the cursor returned by `execute()` does not fetch all results immediately, and if `fetchall()` is not called or the cursor is not iterated, the query may not be fully consumed and no exception is raised.

The sqlite3 interface is designed for generality and standardization rather than for the convenience of everyday use, causing developers to repeatedly write boilerplate code such as creating connections, creating cursors, executing SQL, iterating over result tuples, manually mapping to dictionaries or objects, handling commits and rollbacks, and closing connections.

Even when using row_factory to convert results into sqlite3.Row objects, type conversions for bool, dates, JSON fields and the like still need to be written by hand.

Libraries such as sqlite3, psycopg2, and pymysql all implement the DB-API 2.0 specification.

The standard library sqlite3 does not expose any interface for creating custom virtual tables at all, whereas APSW fully maps this mechanism to Python through classes such as VTTable and VTCursor.

For the vast majority of applications, the standard library sqlite3 is a reasonable default choice; APSW is more suitable for building SQLite extensions, database tools, and scenarios that need to extract the full performance of SQLite.