Python Sqlite3

The sqlite3 module is a database interface library (implementing the DB-API as per PEP 249) for Sqlite.

Contents

Python Sqlite3

Example

import sqlite3

con = sqlite3.connect("example.db")
cur = con.cursor()

# list tables
for i, n in enumerate(cur.execute("SELECT name FROM sqlite_schema WHERE type='table'")):
    print(i, n[0])

# print SQL definition of the 'example' table
sql = list(cur.execute("SELECT sql FROM sqlite_schema WHERE name='example'"))
print(sql[0][0])

# cleanup
con.close()

Classes

Blob

Method	Meaning
`close()`
`read()`
`seed()`
`tell()`
`write()`

Connection

See the example.

Method	Meaning
`backup()`
`blobopen()`	open a `sqlite3.Blob` handle
`close()`	close the connection
`commit()`	commit all pending transactions
`cursor()`	create a `sqlite3.Cursor` referring to this connection
`create_aggregate()`
`create_collation()`
`create_function()`
`create_window_function()`
`deserialize()`
`enable_load_extension()`
`getlimit()`
`interrupt()`
`iterdump()`
`load_extension()`
`rollback()`	undo all pending transactions
`serialize()`
`setlimit()`
`set_progress_handler()`
`set_trace_callback()`

Attribute	Meaning
`autocommit`
`in_transaction`
`isolation_level`
`row_factory`
`text_factory`
`total_changes`

Shortcut Methods

A sqlite3.Connection object has three methods that seem to overlap with the design of sqlite3.Cursor objects. These are shortcut methods that implicitly create and destroy new sqlite3.Cursor objects on each call. These are not recommended for use.

Method	Example
`execute()`	`execute('SELECT * FROM example;')`
`executemany()`	`executemany('INSERT INTO example VALUES(?, ?);', [(1, 'a',), (2, 'b',)])`
`executescript()`	`executescript('CREATE TABLE dev(id, name'); CREATE TABLE prod(id, name)`

Cursor

See the example.

Method	Meaning
`close()`
`execute()`
`executemany()`
`executescript()`
`fetchall()`
`fetchmany()`
`fetchone()`
`lastrowid()`
`setinputsizes()`
`setoutputsize()`

Attribute	Meaning
`arraysize`
`connection`
`description`
`lastrowid`
`rowcount`
`row_factory`

PrepareProtocol

Row

Access the column names (as a tuple of strings) with the keys() method.

Functions

Function'	Meaning
`complete_statement()`
`connect()`
`enable_callback_tracebacks()`
`register_adapter()`
`register_converter()`

Constants

Name	Meaning
`PARSE_COLNAMES`
`PARSE_DECLTYPES`
`SQLITE_OK`
`SQLITE_DENY`
`SQLITE_IGNORE`
`apilevel`	internal implementation detail of DB-API; always `"2.0"`
`paramstyle`	always "`qmark"`
`sqlite_version`	runtime version of Sqlite as a string
`sqlite_version_info`	runtime version of Sqlite as an integer tuple
`threadsafety`
`version`	module version as a string
`version_info`	module version as an integer tuple

Exceptions

Name	Meaning
`Warning`	not used by the module but provided for higher-level libraries
`Error`	base class of all below exceptions; adds two attributes (`sqlite_errorcode`, `sqlite_errorname`)
`InterfaceError`
`DatabaseError`
`DataError`
`OperationalError`
`IntegrityError`
`InternalError`
`ProgrammingError`
`NotSupportedError`

Python/Sqlite3