= Python Sqlite3 =

The '''`sqlite3`''' module is a database interface library (implementing [[https://peps.python.org/pep-0249/|the DB-API as per PEP 249]]) for [[Sqlite]]. 

<<TableOfContents>>

----



== 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|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|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 [[Python/Builtins/Types#Tuple|tuple]] of [[Python/Builtins/Types#Str|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`|| ||

----



== See also ==

[[https://docs.python.org/3/library/sqlite3.html|Python sqlite3 module documentation]]



----
CategoryRicottone