Module functions¶

sqlite3.connect(database,timeout=5.0,detect_types=0,isolation_level='DEFERRED',check_same_thread=True,factory=sqlite3.Connection,cached_statements=128,uri=False,*,autocommit=sqlite3.LEGACY_TRANSACTION_CONTROL)¶

Open a connection to an SQLite database.

Parameters:

• database (path-like object) – The path to the database file to be opened.You can pass":memory:" to create anSQLite database existing onlyin memory, and open a connectionto it.

• timeout (float) – How many seconds the connection should wait before raisinganOperationalError when a table is locked.If another connection opens a transaction to modify a table,that table will be locked until the transaction is committed.Default five seconds.

• detect_types (int) – Control whether and how data types notnatively supported by SQLiteare looked up to be converted to Python types,using the converters registered withregister_converter().Set it to any combination (using|, bitwise or) ofPARSE_DECLTYPES andPARSE_COLNAMESto enable this.Column names takes precedence over declared types if both flags are set.By default (0), type detection is disabled.

• isolation_level (str |None) – Control legacy transaction handling behaviour.SeeConnection.isolation_level andTransaction control via the isolation_level attribute for more information.Can be"DEFERRED" (default),"EXCLUSIVE" or"IMMEDIATE";orNone to disable opening transactions implicitly.Has no effect unlessConnection.autocommit is set toLEGACY_TRANSACTION_CONTROL (the default).

• check_same_thread (bool) – IfTrue (default),ProgrammingError will be raisedif the database connection is used by a threadother than the one that created it.IfFalse, the connection may be accessed in multiple threads;write operations may need to be serialized by the userto avoid data corruption.Seethreadsafety for more information.

• factory (Connection) – A custom subclass ofConnection to create the connection with,if not the defaultConnection class.

• cached_statements (int) – The number of statements thatsqlite3should internally cache for this connection, to avoid parsing overhead.By default, 128 statements.

• uri (bool) – If set toTrue,database is interpreted as aURI with a file pathand an optional query string.The scheme partmust be"file:",and the path can be relative or absolute.The query string allows passing parameters to SQLite,enabling variousHow to work with SQLite URIs.

• autocommit (bool) – ControlPEP 249 transaction handling behaviour.SeeConnection.autocommit andTransaction control via the autocommit attribute for more information.autocommit currently defaults toLEGACY_TRANSACTION_CONTROL.The default will change toFalse in a future Python release.

Return type:

Connection

Raises anauditing eventsqlite3.connect with argumentdatabase.

Raises anauditing eventsqlite3.connect/handle with argumentconnection_handle.

Changed in version 3.4:Added theuri parameter.

Changed in version 3.7:database can now also be apath-like object, not only a string.

Changed in version 3.10:Added thesqlite3.connect/handle auditing event.

Changed in version 3.12:Added theautocommit parameter.

Changed in version 3.13:Positional use of the parameterstimeout,detect_types,isolation_level,check_same_thread,factory,cached_statements,anduri is deprecated.They will become keyword-only parameters in Python 3.15.

sqlite3.complete_statement(statement)¶

ReturnTrue if the stringstatement appears to containone or more complete SQL statements.No syntactic verification or parsing of any kind is performed,other than checking that there are no unclosed string literalsand the statement is terminated by a semicolon.

For example:

>>>sqlite3.complete_statement("SELECT foo FROM bar;")True>>>sqlite3.complete_statement("SELECT foo")False

This function may be useful during command-line inputto determine if the entered text seems to form a complete SQL statement,or if additional input is needed before callingexecute().

Seerunsource() inLib/sqlite3/__main__.pyfor real-world use.

sqlite3.enable_callback_tracebacks(flag,/)¶

Enable or disable callback tracebacks.By default you will not get any tracebacks in user-defined functions,aggregates, converters, authorizer callbacks etc. If you want to debug them,you can call this function withflag set toTrue. Afterwards, youwill get tracebacks from callbacks onsys.stderr. UseFalseto disable the feature again.

Note

Errors in user-defined function callbacks are logged as unraisable exceptions.Use anunraisablehookhandler forintrospection of the failed callback.

sqlite3.register_adapter(type,adapter,/)¶

Register anadaptercallable to adapt the Python typetypeinto an SQLite type.The adapter is called with a Python object of typetype as its soleargument, and must return a value of atype that SQLite natively understands.

sqlite3.register_converter(typename,converter,/)¶

Register theconvertercallable to convert SQLite objects of typetypename into a Python object of a specific type.The converter is invoked for all SQLite values of typetypename;it is passed abytes object and should return an object of thedesired Python type.Consult the parameterdetect_types ofconnect() for information regarding how type detection works.

Note:typename and the name of the type in your query are matchedcase-insensitively.

Module constants¶

sqlite3.LEGACY_TRANSACTION_CONTROL¶

Setautocommit to this constant to selectold style (pre-Python 3.12) transaction control behaviour.SeeTransaction control via the isolation_level attribute for more information.

sqlite3.PARSE_DECLTYPES¶

Pass this flag value to thedetect_types parameter ofconnect() to look up a converter function usingthe declared types for each column.The types are declared when the database table is created.sqlite3 will look up a converter function using the first word of thedeclared type as the converter dictionary key.For example:

CREATETABLEtest(iintegerprimarykey,!willlookupaconverternamed"integer"ppoint,!willlookupaconverternamed"point"nnumber(10)!willlookupaconverternamed"number")

This flag may be combined withPARSE_COLNAMES using the|(bitwise or) operator.

Note

Generated fields (for exampleMAX(p)) are returned asstr.UsePARSE_COLNAMES to enforce types for such queries.

sqlite3.PARSE_COLNAMES¶

Pass this flag value to thedetect_types parameter ofconnect() to look up a converter function byusing the type name, parsed from the query column name,as the converter dictionary key.The query column name must be wrapped in double quotes (")and the type name must be wrapped in square brackets ([]).

SELECTMAX(p)as"p [point]"FROMtest;!willlookupconverter"point"

This flag may be combined withPARSE_DECLTYPES using the|(bitwise or) operator.

sqlite3.SQLITE_OK¶

sqlite3.SQLITE_DENY¶

sqlite3.SQLITE_IGNORE¶

Flags that should be returned by theauthorizer_callbackcallablepassed toConnection.set_authorizer(), to indicate whether:

• Access is allowed (SQLITE_OK),

• The SQL statement should be aborted with an error (SQLITE_DENY)

• The column should be treated as aNULL value (SQLITE_IGNORE)

sqlite3.apilevel¶

String constant stating the supported DB-API level. Required by the DB-API.Hard-coded to"2.0".

sqlite3.paramstyle¶

String constant stating the type of parameter marker formatting expected bythesqlite3 module. Required by the DB-API. Hard-coded to"qmark".

Note

Thenamed DB-API parameter style is also supported.

sqlite3.sqlite_version¶

Version number of the runtime SQLite library as astring.

sqlite3.sqlite_version_info¶

Version number of the runtime SQLite library as atuple ofintegers.

sqlite3.threadsafety¶

Integer constant required by the DB-API 2.0, stating the level of threadsafety thesqlite3 module supports. This attribute is set based onthe defaultthreading mode theunderlying SQLite library is compiled with. The SQLite threading modes are:

1. Single-thread: In this mode, all mutexes are disabled and SQLite isunsafe to use in more than a single thread at once.

2. Multi-thread: In this mode, SQLite can be safely used by multiplethreads provided that no single database connection is usedsimultaneously in two or more threads.

3. Serialized: In serialized mode, SQLite can be safely used bymultiple threads with no restriction.

The mappings from SQLite threading modes to DB-API 2.0 threadsafety levelsare as follows:

SQLite threadingmode	threadsafety	SQLITE_THREADSAFE	DB-API 2.0 meaning
single-thread	0	0	Threads may not share themodule
multi-thread	1	2	Threads may share the module,but not connections
serialized	3	1	Threads may share the module,connections and cursors

Changed in version 3.11:Setthreadsafety dynamically instead of hard-coding it to1.

sqlite3.version¶

Version number of this module as astring.This is not the version of the SQLite library.

Deprecated since version 3.12, will be removed in version 3.14:This constant used to reflect the version number of thepysqlitepackage, a third-party library which used to upstream changes tosqlite3. Today, it carries no meaning or practical value.

sqlite3.version_info¶

Version number of this module as atuple ofintegers.This is not the version of the SQLite library.

Deprecated since version 3.12, will be removed in version 3.14:This constant used to reflect the version number of thepysqlitepackage, a third-party library which used to upstream changes tosqlite3. Today, it carries no meaning or practical value.

sqlite3.SQLITE_DBCONFIG_DEFENSIVE¶

sqlite3.SQLITE_DBCONFIG_DQS_DDL¶

sqlite3.SQLITE_DBCONFIG_DQS_DML¶

sqlite3.SQLITE_DBCONFIG_ENABLE_FKEY¶

sqlite3.SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER¶

sqlite3.SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION¶

sqlite3.SQLITE_DBCONFIG_ENABLE_QPSG¶

sqlite3.SQLITE_DBCONFIG_ENABLE_TRIGGER¶

sqlite3.SQLITE_DBCONFIG_ENABLE_VIEW¶

sqlite3.SQLITE_DBCONFIG_LEGACY_ALTER_TABLE¶

sqlite3.SQLITE_DBCONFIG_LEGACY_FILE_FORMAT¶

sqlite3.SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE¶

sqlite3.SQLITE_DBCONFIG_RESET_DATABASE¶

sqlite3.SQLITE_DBCONFIG_TRIGGER_EQP¶

sqlite3.SQLITE_DBCONFIG_TRUSTED_SCHEMA¶

sqlite3.SQLITE_DBCONFIG_WRITABLE_SCHEMA¶

These constants are used for theConnection.setconfig()andgetconfig() methods.

The availability of these constants varies depending on the version of SQLitePython was compiled with.

Added in version 3.12.

See also

https://www.sqlite.org/c3ref/c_dbconfig_defensive.html

SQLite docs: Database Connection Configuration Options

Connection objects¶

classsqlite3.Connection¶

Each open SQLite database is represented by aConnection object,which is created usingsqlite3.connect().Their main purpose is creatingCursor objects,andTransaction control.

See also

• How to use connection shortcut methods

• How to use the connection context manager

Changed in version 3.13:AResourceWarning is emitted ifclose() is not called beforeaConnection object is deleted.

An SQLite database connection has the following attributes and methods:

cursor(factory=Cursor)¶

Create and return aCursor object.The cursor method accepts a single optional parameterfactory. Ifsupplied, this must be acallable returningan instance ofCursor or its subclasses.

blobopen(table,column,row,/,*,readonly=False,name='main')¶

Open aBlob handle to an existingBLOB.

Parameters:

• table (str) – The name of the table where the blob is located.

• column (str) – The name of the column where the blob is located.

• row (str) – The name of the row where the blob is located.

• readonly (bool) – Set toTrue if the blob should be opened without writepermissions.Defaults toFalse.

• name (str) – The name of the database where the blob is located.Defaults to"main".

Raises:

OperationalError – When trying to open a blob in aWITHOUTROWID table.

Return type:

Blob

Note

The blob size cannot be changed using theBlob class.Use the SQL functionzeroblob to create a blob with a fixed size.

Added in version 3.11.

commit()¶

Commit any pending transaction to the database.Ifautocommit isTrue, or there is no open transaction,this method does nothing.Ifautocommit isFalse, a new transaction is implicitlyopened if a pending transaction was committed by this method.

rollback()¶

Roll back to the start of any pending transaction.Ifautocommit isTrue, or there is no open transaction,this method does nothing.Ifautocommit isFalse, a new transaction is implicitlyopened if a pending transaction was rolled back by this method.

close()¶

Close the database connection.Ifautocommit isFalse,any pending transaction is implicitly rolled back.Ifautocommit isTrue orLEGACY_TRANSACTION_CONTROL,no implicit transaction control is executed.Make sure tocommit() before closingto avoid losing pending changes.

execute(sql,parameters=(),/)¶

Create a newCursor object and callexecute() on it with the givensql andparameters.Return the new cursor object.

executemany(sql,parameters,/)¶

Create a newCursor object and callexecutemany() on it with the givensql andparameters.Return the new cursor object.

executescript(sql_script,/)¶

Create a newCursor object and callexecutescript() on it with the givensql_script.Return the new cursor object.

create_function(name,narg,func,*,deterministic=False)¶

Create or remove a user-defined SQL function.

Parameters:

• name (str) – The name of the SQL function.

• narg (int) – The number of arguments the SQL function can accept.If-1, it may take any number of arguments.

• func (callback | None) – Acallable that is called when the SQL function is invoked.The callable must returna type natively supported by SQLite.Set toNone to remove an existing SQL function.

• deterministic (bool) – IfTrue, the created SQL function is marked asdeterministic,which allows SQLite to perform additional optimizations.

Changed in version 3.8:Added thedeterministic parameter.

Example:

>>>importhashlib>>>defmd5sum(t):...returnhashlib.md5(t).hexdigest()>>>con=sqlite3.connect(":memory:")>>>con.create_function("md5",1,md5sum)>>>forrowincon.execute("SELECT md5(?)",(b"foo",)):...print(row)('acbd18db4cc2f85cedef654fccc4a4d8',)>>>con.close()

Changed in version 3.13:Passingname,narg, andfunc as keyword arguments is deprecated.These parameters will become positional-only in Python 3.15.

create_aggregate(name,n_arg,aggregate_class)¶

Create or remove a user-defined SQL aggregate function.

Parameters:

• name (str) – The name of the SQL aggregate function.

• n_arg (int) – The number of arguments the SQL aggregate function can accept.If-1, it may take any number of arguments.

• aggregate_class (class | None) –

  A class must implement the following methods:

  • step(): Add a row to the aggregate.

  • finalize(): Return the final result of the aggregate asa type natively supported by SQLite.

  The number of arguments that thestep() method must acceptis controlled byn_arg.

  Set toNone to remove an existing SQL aggregate function.

Example:

classMySum:def__init__(self):self.count=0defstep(self,value):self.count+=valuedeffinalize(self):returnself.countcon=sqlite3.connect(":memory:")con.create_aggregate("mysum",1,MySum)cur=con.execute("CREATE TABLE test(i)")cur.execute("INSERT INTO test(i) VALUES(1)")cur.execute("INSERT INTO test(i) VALUES(2)")cur.execute("SELECT mysum(i) FROM test")print(cur.fetchone()[0])con.close()

Changed in version 3.13:Passingname,n_arg, andaggregate_class as keyword arguments is deprecated.These parameters will become positional-only in Python 3.15.

create_window_function(name,num_params,aggregate_class,/)¶

Create or remove a user-defined aggregate window function.

Parameters:

• name (str) – The name of the SQL aggregate window function to create or remove.

• num_params (int) – The number of arguments the SQL aggregate window function can accept.If-1, it may take any number of arguments.

• aggregate_class (class | None) –

  A class that must implement the following methods:

  • step(): Add a row to the current window.

  • value(): Return the current value of the aggregate.

  • inverse(): Remove a row from the current window.

  • finalize(): Return the final result of the aggregate asa type natively supported by SQLite.

  The number of arguments that thestep() andvalue() methodsmust accept is controlled bynum_params.

  Set toNone to remove an existing SQL aggregate window function.

Raises:

NotSupportedError – If used with a version of SQLite older than 3.25.0,which does not support aggregate window functions.

Added in version 3.11.

Example:

# Example taken from https://www.sqlite.org/windowfunctions.html#udfwinfuncclassWindowSumInt:def__init__(self):self.count=0defstep(self,value):"""Add a row to the current window."""self.count+=valuedefvalue(self):"""Return the current value of the aggregate."""returnself.countdefinverse(self,value):"""Remove a row from the current window."""self.count-=valuedeffinalize(self):"""Return the final value of the aggregate.        Any clean-up actions should be placed here.        """returnself.countcon=sqlite3.connect(":memory:")cur=con.execute("CREATE TABLE test(x, y)")values=[("a",4),("b",5),("c",3),("d",8),("e",1),]cur.executemany("INSERT INTO test VALUES(?, ?)",values)con.create_window_function("sumint",1,WindowSumInt)cur.execute("""    SELECT x, sumint(y) OVER (        ORDER BY x ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING    ) AS sum_y    FROM test ORDER BY x""")print(cur.fetchall())con.close()

create_collation(name,callable,/)¶

Create a collation namedname using the collating functioncallable.callable is passed twostring arguments,and it should return aninteger:

• 1 if the first is ordered higher than the second

• -1 if the first is ordered lower than the second

• 0 if they are ordered equal

The following example shows a reverse sorting collation:

defcollate_reverse(string1,string2):ifstring1==string2:return0elifstring1<string2:return1else:return-1con=sqlite3.connect(":memory:")con.create_collation("reverse",collate_reverse)cur=con.execute("CREATE TABLE test(x)")cur.executemany("INSERT INTO test(x) VALUES(?)",[("a",),("b",)])cur.execute("SELECT x FROM test ORDER BY x COLLATE reverse")forrowincur:print(row)con.close()

Remove a collation function by settingcallable toNone.

Changed in version 3.11:The collation name can contain any Unicode character. Earlier, onlyASCII characters were allowed.

interrupt()¶

Call this method from a different thread to abort any queries that mightbe executing on the connection.Aborted queries will raise anOperationalError.

set_authorizer(authorizer_callback)¶

Registercallableauthorizer_callback to be invokedfor each attempt to access a column of a table in the database.The callback should return one ofSQLITE_OK,SQLITE_DENY, orSQLITE_IGNOREto signal how access to the column should be handledby the underlying SQLite library.

The first argument to the callback signifies what kind of operation is to beauthorized. The second and third argument will be arguments orNonedepending on the first argument. The 4th argument is the name of the database(“main”, “temp”, etc.) if applicable. The 5th argument is the name of theinner-most trigger or view that is responsible for the access attempt orNone if this access attempt is directly from input SQL code.

Please consult the SQLite documentation about the possible values for the firstargument and the meaning of the second and third argument depending on the firstone. All necessary constants are available in thesqlite3 module.

PassingNone asauthorizer_callback will disable the authorizer.

Changed in version 3.11:Added support for disabling the authorizer usingNone.

Changed in version 3.13:Passingauthorizer_callback as a keyword argument is deprecated.The parameter will become positional-only in Python 3.15.

set_progress_handler(progress_handler,n)¶

Registercallableprogress_handler to be invoked for everyninstructions of the SQLite virtual machine. This is useful if you want toget called from SQLite during long-running operations, for example to updatea GUI.

If you want to clear any previously installed progress handler, call themethod withNone forprogress_handler.

Returning a non-zero value from the handler function will terminate thecurrently executing query and cause it to raise aDatabaseErrorexception.

Changed in version 3.13:Passingprogress_handler as a keyword argument is deprecated.The parameter will become positional-only in Python 3.15.

set_trace_callback(trace_callback)¶

Registercallabletrace_callback to be invokedfor each SQL statement that is actually executed by the SQLite backend.

The only argument passed to the callback is the statement (asstr) that is being executed. The return value of the callback isignored. Note that the backend does not only run statements passed to theCursor.execute() methods. Other sources include thetransaction management of thesqlite3 module and the execution of triggers defined in the currentdatabase.

PassingNone astrace_callback will disable the trace callback.

Note

Exceptions raised in the trace callback are not propagated. As adevelopment and debugging aid, useenable_callback_tracebacks() to enable printingtracebacks from exceptions raised in the trace callback.

Added in version 3.3.

Changed in version 3.13:Passingtrace_callback as a keyword argument is deprecated.The parameter will become positional-only in Python 3.15.

enable_load_extension(enabled,/)¶

Enable the SQLite engine to load SQLite extensions from shared librariesifenabled isTrue;else, disallow loading SQLite extensions.SQLite extensions can define new functions,aggregates or whole new virtual table implementations. One well-knownextension is the fulltext-search extension distributed with SQLite.

Note

Thesqlite3 module is not built with loadable extension support bydefault, because some platforms (notably macOS) have SQLitelibraries which are compiled without this feature.To get loadable extension support,you must pass the--enable-loadable-sqlite-extensions optiontoconfigure.

Raises anauditing eventsqlite3.enable_load_extension with argumentsconnection,enabled.

Added in version 3.2.

Changed in version 3.10:Added thesqlite3.enable_load_extension auditing event.

con.enable_load_extension(True)# Load the fulltext search extensioncon.execute("select load_extension('./fts3.so')")# alternatively you can load the extension using an API call:# con.load_extension("./fts3.so")# disable extension loading againcon.enable_load_extension(False)# example from SQLite wikicon.execute("CREATE VIRTUAL TABLE recipe USING fts3(name, ingredients)")con.executescript("""    INSERT INTO recipe (name, ingredients) VALUES('broccoli stew', 'broccoli peppers cheese tomatoes');    INSERT INTO recipe (name, ingredients) VALUES('pumpkin stew', 'pumpkin onions garlic celery');    INSERT INTO recipe (name, ingredients) VALUES('broccoli pie', 'broccoli cheese onions flour');    INSERT INTO recipe (name, ingredients) VALUES('pumpkin pie', 'pumpkin sugar flour butter');    """)forrowincon.execute("SELECT rowid, name, ingredients FROM recipe WHERE name MATCH 'pie'"):print(row)

load_extension(path,/,*,entrypoint=None)¶

Load an SQLite extension from a shared library.Enable extension loading withenable_load_extension() beforecalling this method.

Parameters:

• path (str) – The path to the SQLite extension.

• entrypoint (str |None) – Entry point name.IfNone (the default),SQLite will come up with an entry point name of its own;see the SQLite docsLoading an Extension for details.

Raises anauditing eventsqlite3.load_extension with argumentsconnection,path.

Added in version 3.2.

Changed in version 3.10:Added thesqlite3.load_extension auditing event.

Changed in version 3.12:Added theentrypoint parameter.

iterdump(*,filter=None)¶

Return aniterator to dump the database as SQL source code.Useful when saving an in-memory database for later restoration.Similar to the.dump command in thesqlite3 shell.

Parameters:

filter (str |None) – An optionalLIKE pattern for database objects to dump, e.g.prefix_%.IfNone (the default), all database objects will be included.

Example:

# Convert file example.db to SQL dump file dump.sqlcon=sqlite3.connect('example.db')withopen('dump.sql','w')asf:forlineincon.iterdump():f.write('%s\n'%line)con.close()

See also

How to handle non-UTF-8 text encodings

Changed in version 3.13:Added thefilter parameter.

backup(target,*,pages=-1,progress=None,name='main',sleep=0.250)¶

Create a backup of an SQLite database.

Works even if the database is being accessed by other clientsor concurrently by the same connection.

Parameters:

• target (Connection) – The database connection to save the backup to.

• pages (int) – The number of pages to copy at a time.If equal to or less than0,the entire database is copied in a single step.Defaults to-1.

• progress (callback | None) – If set to acallable,it is invoked with three integer arguments for every backup iteration:thestatus of the last iteration,theremaining number of pages still to be copied,and thetotal number of pages.Defaults toNone.

• name (str) – The name of the database to back up.Either"main" (the default) for the main database,"temp" for the temporary database,or the name of a custom database as attached using theATTACHDATABASE SQL statement.

• sleep (float) – The number of seconds to sleep between successive attemptsto back up remaining pages.

Example 1, copy an existing database into another:

defprogress(status,remaining,total):print(f'Copied{total-remaining} of{total} pages...')src=sqlite3.connect('example.db')dst=sqlite3.connect('backup.db')withdst:src.backup(dst,pages=1,progress=progress)dst.close()src.close()

Example 2, copy an existing database into a transient copy:

src=sqlite3.connect('example.db')dst=sqlite3.connect(':memory:')src.backup(dst)dst.close()src.close()

Added in version 3.7.

See also

How to handle non-UTF-8 text encodings

getlimit(category,/)¶

Get a connection runtime limit.

Parameters:

category (int) – TheSQLite limit category to be queried.

Return type:

int

Raises:

ProgrammingError – Ifcategory is not recognised by the underlying SQLite library.

Example, query the maximum length of an SQL statementforConnectioncon (the default is 1000000000):

>>>con.getlimit(sqlite3.SQLITE_LIMIT_SQL_LENGTH)1000000000

Added in version 3.11.

setlimit(category,limit,/)¶

Set a connection runtime limit.Attempts to increase a limit above its hard upper bound are silentlytruncated to the hard upper bound. Regardless of whether or not the limitwas changed, the prior value of the limit is returned.

Parameters:

• category (int) – TheSQLite limit category to be set.

• limit (int) – The value of the new limit.If negative, the current limit is unchanged.

Return type:

int

Raises:

ProgrammingError – Ifcategory is not recognised by the underlying SQLite library.

Example, limit the number of attached databases to 1forConnectioncon (the default limit is 10):

>>>con.setlimit(sqlite3.SQLITE_LIMIT_ATTACHED,1)10>>>con.getlimit(sqlite3.SQLITE_LIMIT_ATTACHED)1

Added in version 3.11.

getconfig(op,/)¶

Query a boolean connection configuration option.

Parameters:

op (int) – ASQLITE_DBCONFIG code.

Return type:

bool

Added in version 3.12.

setconfig(op,enable=True,/)¶

Set a boolean connection configuration option.

Parameters:

• op (int) – ASQLITE_DBCONFIG code.

• enable (bool) –True if the configuration option should be enabled (default);False if it should be disabled.

Added in version 3.12.

serialize(*,name='main')¶

Serialize a database into abytes object. For anordinary on-disk database file, the serialization is just a copy of thedisk file. For an in-memory database or a “temp” database, theserialization is the same sequence of bytes which would be written todisk if that database were backed up to disk.

Parameters:

name (str) – The database name to be serialized.Defaults to"main".

Return type:

bytes

Note

This method is only available if the underlying SQLite library has theserialize API.

Added in version 3.11.

deserialize(data,/,*,name='main')¶

Deserialize aserialized database into aConnection.This method causes the database connection to disconnect from databasename, and reopenname as an in-memory database based on theserialization contained indata.

Parameters:

• data (bytes) – A serialized database.

• name (str) – The database name to deserialize into.Defaults to"main".

Raises:

• OperationalError – If the database connection is currently involved in a readtransaction or a backup operation.

• DatabaseError – Ifdata does not contain a valid SQLite database.

• OverflowError – Iflen(data) is larger than2**63-1.

Note

This method is only available if the underlying SQLite library has thedeserialize API.

Added in version 3.11.

autocommit¶

This attribute controlsPEP 249-compliant transaction behaviour.autocommit has three allowed values:

• False: SelectPEP 249-compliant transaction behaviour,implying thatsqlite3 ensures a transaction is always open.Usecommit() androllback() to close transactions.

  This is the recommended value ofautocommit.

• True: Use SQLite’sautocommit mode.commit() androllback() have no effect in this mode.

• LEGACY_TRANSACTION_CONTROL:Pre-Python 3.12 (non-PEP 249-compliant) transaction control.Seeisolation_level for more details.

  This is currently the default value ofautocommit.

Changingautocommit toFalse will open a new transaction,and changing it toTrue will commit any pending transaction.

SeeTransaction control via the autocommit attribute for more details.

Note

Theisolation_level attribute has no effect unlessautocommit isLEGACY_TRANSACTION_CONTROL.

Added in version 3.12.

in_transaction¶

This read-only attribute corresponds to the low-level SQLiteautocommit mode.

True if a transaction is active (there are uncommitted changes),False otherwise.

Added in version 3.2.

isolation_level¶

Controls thelegacy transaction handling mode ofsqlite3.If set toNone, transactions are never implicitly opened.If set to one of"DEFERRED","IMMEDIATE", or"EXCLUSIVE",corresponding to the underlyingSQLite transaction behaviour,implicit transaction management is performed.

If not overridden by theisolation_level parameter ofconnect(),the default is"", which is an alias for"DEFERRED".

Note

Usingautocommit to control transaction handling isrecommended over usingisolation_level.isolation_level has no effect unlessautocommit isset toLEGACY_TRANSACTION_CONTROL (the default).

row_factory¶

The initialrow_factoryforCursor objects created from this connection.Assigning to this attribute does not affect therow_factoryof existing cursors belonging to this connection, only new ones.IsNone by default,meaning each row is returned as atuple.

SeeHow to create and use row factories for more details.

text_factory¶

Acallable that accepts abytes parameterand returns a text representation of it.The callable is invoked for SQLite values with theTEXT data type.By default, this attribute is set tostr.

SeeHow to handle non-UTF-8 text encodings for more details.

total_changes¶

Return the total number of database rows that have been modified, inserted, ordeleted since the database connection was opened.

Cursor objects¶

ACursor object represents adatabase cursorwhich is used to execute SQL statements,and manage the context of a fetch operation.Cursors are created usingConnection.cursor(),or by using any of theconnection shortcut methods.

Cursor objects areiterators,meaning that if youexecute() aSELECT query,you can simply iterate over the cursor to fetch the resulting rows:

forrowincur.execute("SELECT t FROM data"):print(row)

classsqlite3.Cursor¶

ACursor instance has the following attributes and methods.

execute(sql,parameters=(),/)¶

Execute a single SQL statement,optionally binding Python values usingplaceholders.

Parameters:

• sql (str) – A single SQL statement.

• parameters (dict |sequence) – Python values to bind to placeholders insql.Adict if named placeholders are used.Asequence if unnamed placeholders are used.SeeHow to use placeholders to bind values in SQL queries.

Raises:

ProgrammingError – Ifsql contains more than one SQL statement.

Ifautocommit isLEGACY_TRANSACTION_CONTROL,isolation_level is notNone,sql is anINSERT,UPDATE,DELETE, orREPLACE statement,and there is no open transaction,a transaction is implicitly opened before executingsql.

Deprecated since version 3.12, will be removed in version 3.14:DeprecationWarning is emitted ifnamed placeholders are usedandparameters is a sequence instead of adict.Starting with Python 3.14,ProgrammingError willbe raised instead.

Useexecutescript() to execute multiple SQL statements.

executemany(sql,parameters,/)¶

For every item inparameters,repeatedly execute theparameterizedDML SQL statementsql.

Uses the same implicit transaction handling asexecute().

Parameters:

• sql (str) – A single SQL DML statement.

• parameters (iterable) – Aniterable of parameters to bind withthe placeholders insql.SeeHow to use placeholders to bind values in SQL queries.

Raises:

ProgrammingError – Ifsql contains more than one SQL statement,or is not a DML statement.

Example:

rows=[("row1",),("row2",),]# cur is an sqlite3.Cursor objectcur.executemany("INSERT INTO data VALUES(?)",rows)

Note

Any resulting rows are discarded,including DML statements withRETURNING clauses.

Deprecated since version 3.12, will be removed in version 3.14:DeprecationWarning is emitted ifnamed placeholders are usedand the items inparameters are sequencesinstead ofdicts.Starting with Python 3.14,ProgrammingError willbe raised instead.

executescript(sql_script,/)¶

Execute the SQL statements insql_script.If theautocommit isLEGACY_TRANSACTION_CONTROLand there is a pending transaction,an implicitCOMMIT statement is executed first.No other implicit transaction control is performed;any transaction control must be added tosql_script.

sql_script must be astring.

Example:

# cur is an sqlite3.Cursor objectcur.executescript("""    BEGIN;    CREATE TABLE person(firstname, lastname, age);    CREATE TABLE book(title, author, published);    CREATE TABLE publisher(name, address);    COMMIT;""")

fetchone()¶

Ifrow_factory isNone,return the next row query result set as atuple.Else, pass it to the row factory and return its result.ReturnNone if no more data is available.

fetchmany(size=cursor.arraysize)¶

Return the next set of rows of a query result as alist.Return an empty list if no more rows are available.

The number of rows to fetch per call is specified by thesize parameter.Ifsize is not given,arraysize determines the number of rowsto be fetched.If fewer thansize rows are available,as many rows as are available are returned.

Note there are performance considerations involved with thesize parameter.For optimal performance, it is usually best to use the arraysize attribute.If thesize parameter is used, then it is best for it to retain the samevalue from onefetchmany() call to the next.

fetchall()¶

Return all (remaining) rows of a query result as alist.Return an empty list if no rows are available.Note that thearraysize attribute can affect the performance ofthis operation.

close()¶

Close the cursor now (rather than whenever__del__ is called).

The cursor will be unusable from this point forward; aProgrammingErrorexception will be raised if any operation is attempted with the cursor.

setinputsizes(sizes,/)¶

Required by the DB-API. Does nothing insqlite3.

setoutputsize(size,column=None,/)¶

Required by the DB-API. Does nothing insqlite3.

arraysize¶

Read/write attribute that controls the number of rows returned byfetchmany().The default value is 1 which means a single row would be fetched per call.

connection¶

Read-only attribute that provides the SQLite databaseConnectionbelonging to the cursor. ACursor object created bycallingcon.cursor() will have aconnection attribute that refers tocon:

>>>con=sqlite3.connect(":memory:")>>>cur=con.cursor()>>>cur.connection==conTrue>>>con.close()

description¶

Read-only attribute that provides the column names of the last query. Toremain compatible with the Python DB API, it returns a 7-tuple for eachcolumn where the last six items of each tuple areNone.

It is set forSELECT statements without any matching rows as well.

lastrowid¶

Read-only attribute that provides the row id of the last inserted row. Itis only updated after successfulINSERT orREPLACE statementsusing theexecute() method. For other statements, afterexecutemany() orexecutescript(), or if the insertion failed,the value oflastrowid is left unchanged. The initial value oflastrowid isNone.

Note

Inserts intoWITHOUTROWID tables are not recorded.

Changed in version 3.6:Added support for theREPLACE statement.

rowcount¶

Read-only attribute that provides the number of modified rows forINSERT,UPDATE,DELETE, andREPLACE statements;is-1 for other statements,includingCTE queries.It is only updated by theexecute() andexecutemany() methods,after the statement has run to completion.This means that any resulting rows must be fetched in order forrowcount to be updated.

row_factory¶

Control how a row fetched from thisCursor is represented.IfNone, a row is represented as atuple.Can be set to the includedsqlite3.Row;or acallable that accepts two arguments,aCursor object and thetuple of row values,and returns a custom object representing an SQLite row.

Defaults to whatConnection.row_factory was set towhen theCursor was created.Assigning to this attribute does not affectConnection.row_factory of the parent connection.

SeeHow to create and use row factories for more details.

Row objects¶

classsqlite3.Row¶

ARow instance serves as a highly optimizedrow_factory forConnection objects.It supports iteration, equality testing,len(),andmapping access by column name and index.

TwoRow objects compare equalif they have identical column names and values.

SeeHow to create and use row factories for more details.

keys()¶

Return alist of column names asstrings.Immediately after a query,it is the first member of each tuple inCursor.description.

Changed in version 3.5:Added support of slicing.

Blob objects¶

classsqlite3.Blob¶

Added in version 3.11.

ABlob instance is afile-like objectthat can read and write data in an SQLiteBLOB.Calllen(blob) to get the size (number of bytes) of the blob.Use indices andslices for direct access to the blob data.

Use theBlob as acontext manager to ensure that the blobhandle is closed after use.

con=sqlite3.connect(":memory:")con.execute("CREATE TABLE test(blob_col blob)")con.execute("INSERT INTO test(blob_col) VALUES(zeroblob(13))")# Write to our blob, using two write operations:withcon.blobopen("test","blob_col",1)asblob:blob.write(b"hello, ")blob.write(b"world.")# Modify the first and last bytes of our blobblob[0]=ord("H")blob[-1]=ord("!")# Read the contents of our blobwithcon.blobopen("test","blob_col",1)asblob:greeting=blob.read()print(greeting)# outputs "b'Hello, world!'"con.close()

close()¶

Close the blob.

The blob will be unusable from this point onward. AnError (or subclass) exception will be raised if anyfurther operation is attempted with the blob.

read(length=-1,/)¶

Readlength bytes of data from the blob at the current offset position.If the end of the blob is reached, the data up toEOF will be returned. Whenlength is notspecified, or is negative,read() will read until the end ofthe blob.

write(data,/)¶

Writedata to the blob at the current offset. This function cannotchange the blob length. Writing beyond the end of the blob will raiseValueError.

tell()¶

Return the current access position of the blob.

seek(offset,origin=os.SEEK_SET,/)¶

Set the current access position of the blob tooffset. Theoriginargument defaults toos.SEEK_SET (absolute blob positioning).Other values fororigin areos.SEEK_CUR (seek relative to thecurrent position) andos.SEEK_END (seek relative to the blob’send).

PrepareProtocol objects¶

classsqlite3.PrepareProtocol¶

The PrepareProtocol type’s single purpose is to act as aPEP 246 styleadaption protocol for objects that canadapt themselves tonative SQLite types.

Exceptions¶

The exception hierarchy is defined by the DB-API 2.0 (PEP 249).

exceptionsqlite3.Warning¶

This exception is not currently raised by thesqlite3 module,but may be raised by applications usingsqlite3,for example if a user-defined function truncates data while inserting.Warning is a subclass ofException.

exceptionsqlite3.Error¶

The base class of the other exceptions in this module.Use this to catch all errors with one singleexcept statement.Error is a subclass ofException.

If the exception originated from within the SQLite library,the following two attributes are added to the exception:

sqlite_errorcode¶

The numeric error code from theSQLite API

Added in version 3.11.

sqlite_errorname¶

The symbolic name of the numeric error codefrom theSQLite API

Added in version 3.11.

exceptionsqlite3.InterfaceError¶

Exception raised for misuse of the low-level SQLite C API.In other words, if this exception is raised, it probably indicates a bug in thesqlite3 module.InterfaceError is a subclass ofError.

exceptionsqlite3.DatabaseError¶

Exception raised for errors that are related to the database.This serves as the base exception for several types of database errors.It is only raised implicitly through the specialised subclasses.DatabaseError is a subclass ofError.

exceptionsqlite3.DataError¶

Exception raised for errors caused by problems with the processed data,like numeric values out of range, and strings which are too long.DataError is a subclass ofDatabaseError.

exceptionsqlite3.OperationalError¶

Exception raised for errors that are related to the database’s operation,and not necessarily under the control of the programmer.For example, the database path is not found,or a transaction could not be processed.OperationalError is a subclass ofDatabaseError.

exceptionsqlite3.IntegrityError¶

Exception raised when the relational integrity of the database is affected,e.g. a foreign key check fails. It is a subclass ofDatabaseError.

exceptionsqlite3.InternalError¶

Exception raised when SQLite encounters an internal error.If this is raised, it may indicate that there is a problem with the runtimeSQLite library.InternalError is a subclass ofDatabaseError.

exceptionsqlite3.ProgrammingError¶

Exception raised forsqlite3 API programming errors,for example supplying the wrong number of bindings to a query,or trying to operate on a closedConnection.ProgrammingError is a subclass ofDatabaseError.

exceptionsqlite3.NotSupportedError¶

Exception raised in case a method or database API is not supported by theunderlying SQLite library. For example, settingdeterministic toTrue increate_function(), if the underlying SQLite librarydoes not support deterministic functions.NotSupportedError is a subclass ofDatabaseError.

SQLite and Python types¶

SQLite natively supports the following types:NULL,INTEGER,REAL,TEXT,BLOB.

The following Python types can thus be sent to SQLite without any problem:

This is how SQLite types are converted to Python types by default:

The type system of thesqlite3 module is extensible in two ways: you canstore additional Python types in an SQLite database viaobject adapters,and you can let thesqlite3 module convert SQLite types toPython types viaconverters.

Default adapters and converters (deprecated)¶

The deprecated default adapters and converters consist of:

• An adapter fordatetime.date objects tostrings inISO 8601 format.

• An adapter fordatetime.datetime objects to strings inISO 8601 format.

• A converter fordeclared “date” types todatetime.date objects.

• A converter for declared “timestamp” types todatetime.datetime objects.Fractional parts will be truncated to 6 digits (microsecond precision).

Command-line interface¶

Thesqlite3 module can be invoked as a script,using the interpreter’s-m switch,in order to provide a simple SQLite shell.The argument signature is as follows:

python-msqlite3[-h][-v][filename][sql]

Type.quit or CTRL-D to exit the shell.

-h,--help¶

Print CLI help.

-v,--version¶

Print underlying SQLite library version.

How to use placeholders to bind values in SQL queries¶

SQL operations usually need to use values from Python variables. However,beware of using Python’s string operations to assemble queries, as theyare vulnerable toSQL injection attacks. For example, an attacker can simplyclose the single quote and injectORTRUE to select all rows:

>>># Never do this -- insecure!>>>symbol=input()' OR TRUE; -->>>sql="SELECT * FROM stocks WHERE symbol = '%s'"%symbol>>>print(sql)SELECT * FROM stocks WHERE symbol = '' OR TRUE; --'>>>cur.execute(sql)

Instead, use the DB-API’s parameter substitution. To insert a variable into aquery string, use a placeholder in the string, and substitute the actual valuesinto the query by providing them as atuple of values to the secondargument of the cursor’sexecute() method.

An SQL statement may use one of two kinds of placeholders:question marks (qmark style) or named placeholders (named style).For the qmark style,parameters must be asequence whose length must match the number of placeholders,or aProgrammingError is raised.For the named style,parameters must bean instance of adict (or a subclass),which must contain keys for all named parameters;any extra items are ignored.Here’s an example of both styles:

con=sqlite3.connect(":memory:")cur=con.execute("CREATE TABLE lang(name, first_appeared)")# This is the named style used with executemany():data=({"name":"C","year":1972},{"name":"Fortran","year":1957},{"name":"Python","year":1991},{"name":"Go","year":2009},)cur.executemany("INSERT INTO lang VALUES(:name, :year)",data)# This is the qmark style used in a SELECT query:params=(1972,)cur.execute("SELECT * FROM lang WHERE first_appeared = ?",params)print(cur.fetchall())con.close()

How to adapt custom Python types to SQLite values¶

SQLite supports only a limited set of data types natively.To store custom Python types in SQLite databases,adapt them to one of thePython types SQLite natively understands.

There are two ways to adapt Python objects to SQLite types:letting your object adapt itself, or using anadapter callable.The latter will take precedence above the former.For a library that exports a custom type,it may make sense to enable that type to adapt itself.As an application developer, it may make more sense to take direct control byregistering custom adapter functions.

classPoint:def__init__(self,x,y):self.x,self.y=x,ydef__conform__(self,protocol):ifprotocolissqlite3.PrepareProtocol:returnf"{self.x};{self.y}"con=sqlite3.connect(":memory:")cur=con.cursor()cur.execute("SELECT ?",(Point(4.0,-3.2),))print(cur.fetchone()[0])con.close()

classPoint:def__init__(self,x,y):self.x,self.y=x,ydefadapt_point(point):returnf"{point.x};{point.y}"sqlite3.register_adapter(Point,adapt_point)con=sqlite3.connect(":memory:")cur=con.cursor()cur.execute("SELECT ?",(Point(1.0,2.5),))print(cur.fetchone()[0])con.close()

How to convert SQLite values to custom Python types¶

Writing an adapter lets you convertfrom custom Python typesto SQLitevalues.To be able to convertfrom SQLite valuesto custom Python types,we useconverters.

Let’s go back to thePoint class. We stored the x and y coordinatesseparated via semicolons as strings in SQLite.

First, we’ll define a converter function that accepts the string as a parameterand constructs aPoint object from it.

defconvert_point(s):x,y=map(float,s.split(b";"))returnPoint(x,y)

We now need to tellsqlite3 when it should convert a given SQLite value.This is done when connecting to a database, using thedetect_types parameterofconnect(). There are three options:

• Implicit: setdetect_types toPARSE_DECLTYPES

• Explicit: setdetect_types toPARSE_COLNAMES

• Both: setdetect_types tosqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES.Column names take precedence over declared types.

The following example illustrates the implicit and explicit approaches:

classPoint:def__init__(self,x,y):self.x,self.y=x,ydef__repr__(self):returnf"Point({self.x},{self.y})"defadapt_point(point):returnf"{point.x};{point.y}"defconvert_point(s):x,y=list(map(float,s.split(b";")))returnPoint(x,y)# Register the adapter and convertersqlite3.register_adapter(Point,adapt_point)sqlite3.register_converter("point",convert_point)# 1) Parse using declared typesp=Point(4.0,-3.2)con=sqlite3.connect(":memory:",detect_types=sqlite3.PARSE_DECLTYPES)cur=con.execute("CREATE TABLE test(p point)")cur.execute("INSERT INTO test(p) VALUES(?)",(p,))cur.execute("SELECT p FROM test")print("with declared types:",cur.fetchone()[0])cur.close()con.close()# 2) Parse using column namescon=sqlite3.connect(":memory:",detect_types=sqlite3.PARSE_COLNAMES)cur=con.execute("CREATE TABLE test(p)")cur.execute("INSERT INTO test(p) VALUES(?)",(p,))cur.execute('SELECT p AS "p [point]" FROM test')print("with column names:",cur.fetchone()[0])cur.close()con.close()

Adapter and converter recipes¶

This section shows recipes for common adapters and converters.

importdatetimeimportsqlite3defadapt_date_iso(val):"""Adapt datetime.date to ISO 8601 date."""returnval.isoformat()defadapt_datetime_iso(val):"""Adapt datetime.datetime to timezone-naive ISO 8601 date."""returnval.isoformat()defadapt_datetime_epoch(val):"""Adapt datetime.datetime to Unix timestamp."""returnint(val.timestamp())sqlite3.register_adapter(datetime.date,adapt_date_iso)sqlite3.register_adapter(datetime.datetime,adapt_datetime_iso)sqlite3.register_adapter(datetime.datetime,adapt_datetime_epoch)defconvert_date(val):"""Convert ISO 8601 date to datetime.date object."""returndatetime.date.fromisoformat(val.decode())defconvert_datetime(val):"""Convert ISO 8601 datetime to datetime.datetime object."""returndatetime.datetime.fromisoformat(val.decode())defconvert_timestamp(val):"""Convert Unix epoch timestamp to datetime.datetime object."""returndatetime.datetime.fromtimestamp(int(val))sqlite3.register_converter("date",convert_date)sqlite3.register_converter("datetime",convert_datetime)sqlite3.register_converter("timestamp",convert_timestamp)

How to use connection shortcut methods¶

Using theexecute(),executemany(), andexecutescript()methods of theConnection class, your code canbe written more concisely because you don’t have to create the (oftensuperfluous)Cursor objects explicitly. Instead, theCursorobjects are created implicitly and these shortcut methods return the cursorobjects. This way, you can execute aSELECT statement and iterate over itdirectly using only a single call on theConnection object.

# Create and fill the table.con=sqlite3.connect(":memory:")con.execute("CREATE TABLE lang(name, first_appeared)")data=[("C++",1985),("Objective-C",1984),]con.executemany("INSERT INTO lang(name, first_appeared) VALUES(?, ?)",data)# Print the table contentsforrowincon.execute("SELECT name, first_appeared FROM lang"):print(row)print("I just deleted",con.execute("DELETE FROM lang").rowcount,"rows")# close() is not a shortcut method and it's not called automatically;# the connection object should be closed manuallycon.close()

How to use the connection context manager¶

AConnection object can be used as a context manager thatautomatically commits or rolls back open transactions when leaving the body ofthe context manager.If the body of thewith statement finishes without exceptions,the transaction is committed.If this commit fails,or if the body of thewith statement raises an uncaught exception,the transaction is rolled back.Ifautocommit isFalse,a new transaction is implicitly opened after committing or rolling back.

If there is no open transaction upon leaving the body of thewith statement,or ifautocommit isTrue,the context manager does nothing.

con=sqlite3.connect(":memory:")con.execute("CREATE TABLE lang(id INTEGER PRIMARY KEY, name VARCHAR UNIQUE)")# Successful, con.commit() is called automatically afterwardswithcon:con.execute("INSERT INTO lang(name) VALUES(?)",("Python",))# con.rollback() is called after the with block finishes with an exception,# the exception is still raised and must be caughttry:withcon:con.execute("INSERT INTO lang(name) VALUES(?)",("Python",))exceptsqlite3.IntegrityError:print("couldn't add Python twice")# Connection object used as context manager only commits or rollbacks transactions,# so the connection object should be closed manuallycon.close()

How to work with SQLite URIs¶

Some useful URI tricks include:

• Open a database in read-only mode:

>>>con=sqlite3.connect("file:tutorial.db?mode=ro",uri=True)>>>con.execute("CREATE TABLE readonly(data)")Traceback (most recent call last):OperationalError:attempt to write a readonly database>>>con.close()

• Do not implicitly create a new database file if it does not already exist;will raiseOperationalError if unable to create a new file:

>>>con=sqlite3.connect("file:nosuchdb.db?mode=rw",uri=True)Traceback (most recent call last):OperationalError:unable to open database file

• Create a shared named in-memory database:

db="file:mem1?mode=memory&cache=shared"con1=sqlite3.connect(db,uri=True)con2=sqlite3.connect(db,uri=True)withcon1:con1.execute("CREATE TABLE shared(data)")con1.execute("INSERT INTO shared VALUES(28)")res=con2.execute("SELECT data FROM shared")assertres.fetchone()==(28,)con1.close()con2.close()

More information about this feature, including a list of parameters,can be found in theSQLite URI documentation.

How to create and use row factories¶

By default,sqlite3 represents each row as atuple.If atuple does not suit your needs,you can use thesqlite3.Row classor a customrow_factory.

Whilerow_factory exists as an attribute both on theCursor and theConnection,it is recommended to setConnection.row_factory,so all cursors created from the connection will use the same row factory.

Row provides indexed and case-insensitive named access to columns,with minimal memory overhead and performance impact over atuple.To useRow as a row factory,assign it to therow_factory attribute:

>>>con=sqlite3.connect(":memory:")>>>con.row_factory=sqlite3.Row

Queries now returnRow objects:

>>>res=con.execute("SELECT 'Earth' AS name, 6378 AS radius")>>>row=res.fetchone()>>>row.keys()['name', 'radius']>>>row[0]# Access by index.'Earth'>>>row["name"]# Access by name.'Earth'>>>row["RADIUS"]# Column names are case-insensitive.6378>>>con.close()

You can create a customrow_factorythat returns each row as adict, with column names mapped to values:

defdict_factory(cursor,row):fields=[column[0]forcolumnincursor.description]return{key:valueforkey,valueinzip(fields,row)}

Using it, queries now return adict instead of atuple:

>>>con=sqlite3.connect(":memory:")>>>con.row_factory=dict_factory>>>forrowincon.execute("SELECT 1 AS a, 2 AS b"):...print(row){'a': 1, 'b': 2}>>>con.close()

The following row factory returns anamed tuple:

fromcollectionsimportnamedtupledefnamedtuple_factory(cursor,row):fields=[column[0]forcolumnincursor.description]cls=namedtuple("Row",fields)returncls._make(row)

namedtuple_factory() can be used as follows:

>>>con=sqlite3.connect(":memory:")>>>con.row_factory=namedtuple_factory>>>cur=con.execute("SELECT 1 AS a, 2 AS b")>>>row=cur.fetchone()>>>rowRow(a=1, b=2)>>>row[0]# Indexed access.1>>>row.b# Attribute access.2>>>con.close()

With some adjustments, the above recipe can be adapted to use adataclass, or any other custom class,instead of anamedtuple.

How to handle non-UTF-8 text encodings¶

By default,sqlite3 usesstr to adapt SQLite valueswith theTEXT data type.This works well for UTF-8 encoded text, but it might fail for other encodingsand invalid UTF-8.You can use a customtext_factory to handle such cases.

Because of SQLite’sflexible typing, it is not uncommon to encounter tablecolumns with theTEXT data type containing non-UTF-8 encodings,or even arbitrary data.To demonstrate, let’s assume we have a database with ISO-8859-2 (Latin-2)encoded text, for example a table of Czech-English dictionary entries.Assuming we now have aConnection instanceconconnected to this database,we can decode the Latin-2 encoded text using thistext_factory:

con.text_factory=lambdadata:str(data,encoding="latin2")

For invalid UTF-8 or arbitrary data in stored inTEXT table columns,you can use the following technique, borrowed from theUnicode HOWTO:

con.text_factory=lambdadata:str(data,errors="surrogateescape")

Transaction control¶

sqlite3 offers multiple methods of controlling whether,when and how database transactions are opened and closed.Transaction control via the autocommit attribute is recommended,whileTransaction control via the isolation_level attributeretains the pre-Python 3.12 behaviour.