sqlite3 — DB-API 2.0 interface for SQLite databases

Source code:Lib/sqlite3/

SQLite is a C library that provides a lightweight disk-based database thatdoesn’t require a separate server process and allows accessing the databaseusing a nonstandard variant of the SQL query language. Some applications can useSQLite for internal data storage. It’s also possible to prototype anapplication using SQLite and then port the code to a larger database such asPostgreSQL or Oracle.

Thesqlite3 module was written by Gerhard Häring. It provides an SQL interfacecompliant with the DB-API 2.0 specification described byPEP 249, andrequires SQLite 3.7.15 or newer.

This document includes four main sections:

  • Tutorial teaches how to use thesqlite3 module.

  • Reference describes the classes and functions this moduledefines.

  • How-to guides details how to handle specific tasks.

  • Explanation provides in-depth background ontransaction control.

See also

https://www.sqlite.org

The SQLite web page; the documentation describes the syntax and theavailable data types for the supported SQL dialect.

https://www.w3schools.com/sql/

Tutorial, reference and examples for learning SQL syntax.

PEP 249 - Database API Specification 2.0

PEP written by Marc-André Lemburg.

Tutorial

In this tutorial, you will create a database of Monty Python moviesusing basicsqlite3 functionality.It assumes a fundamental understanding of database concepts,includingcursors andtransactions.

First, we need to create a new database and opena database connection to allowsqlite3 to work with it.Callsqlite3.connect() to create a connection tothe databasetutorial.db in the current working directory,implicitly creating it if it does not exist:

importsqlite3con=sqlite3.connect("tutorial.db")

The returnedConnection objectconrepresents the connection to the on-disk database.

In order to execute SQL statements and fetch results from SQL queries,we will need to use a database cursor.Callcon.cursor() to create theCursor:

cur=con.cursor()

Now that we’ve got a database connection and a cursor,we can create a database tablemovie with columns for title,release year, and review score.For simplicity, we can just use column names in the table declaration –thanks to theflexible typing feature of SQLite,specifying the data types is optional.Execute theCREATETABLE statementby callingcur.execute(...):

cur.execute("CREATE TABLE movie(title, year, score)")

We can verify that the new table has been created by queryingthesqlite_master table built-in to SQLite,which should now contain an entry for themovie table definition(seeThe Schema Table for details).Execute that query by callingcur.execute(...),assign the result tores,and callres.fetchone() to fetch the resulting row:

>>>res=cur.execute("SELECT name FROM sqlite_master")>>>res.fetchone()('movie',)

We can see that the table has been created,as the query returns atuple containing the table’s name.If we querysqlite_master for a non-existent tablespam,res.fetchone() will returnNone:

>>>res=cur.execute("SELECT name FROM sqlite_master WHERE name='spam'")>>>res.fetchone()isNoneTrue

Now, add two rows of data supplied as SQL literalsby executing anINSERT statement,once again by callingcur.execute(...):

cur.execute("""    INSERT INTO movie VALUES        ('Monty Python and the Holy Grail', 1975, 8.2),        ('And Now for Something Completely Different', 1971, 7.5)""")

TheINSERT statement implicitly opens a transaction,which needs to be committed before changes are saved in the database(seeTransaction control for details).Callcon.commit() on the connection objectto commit the transaction:

con.commit()

We can verify that the data was inserted correctlyby executing aSELECT query.Use the now-familiarcur.execute(...) toassign the result tores,and callres.fetchall() to return all resulting rows:

>>>res=cur.execute("SELECT score FROM movie")>>>res.fetchall()[(8.2,), (7.5,)]

The result is alist of twotuples, one per row,each containing that row’sscore value.

Now, insert three more rows by callingcur.executemany(...):

data=[("Monty Python Live at the Hollywood Bowl",1982,7.9),("Monty Python's The Meaning of Life",1983,7.5),("Monty Python's Life of Brian",1979,8.0),]cur.executemany("INSERT INTO movie VALUES(?, ?, ?)",data)con.commit()# Remember to commit the transaction after executing INSERT.

Notice that? placeholders are used to binddata to the query.Always use placeholders instead ofstring formattingto bind Python values to SQL statements,to avoidSQL injection attacks(seeHow to use placeholders to bind values in SQL queries for more details).

We can verify that the new rows were insertedby executing aSELECT query,this time iterating over the results of the query:

>>>forrowincur.execute("SELECT year, title FROM movie ORDER BY year"):...print(row)(1971, 'And Now for Something Completely Different')(1975, 'Monty Python and the Holy Grail')(1979, "Monty Python's Life of Brian")(1982, 'Monty Python Live at the Hollywood Bowl')(1983, "Monty Python's The Meaning of Life")

Each row is a two-itemtuple of(year,title),matching the columns selected in the query.

Finally, verify that the database has been written to diskby callingcon.close()to close the existing connection, opening a new one,creating a new cursor, then querying the database:

>>>con.close()>>>new_con=sqlite3.connect("tutorial.db")>>>new_cur=new_con.cursor()>>>res=new_cur.execute("SELECT title, year FROM movie ORDER BY score DESC")>>>title,year=res.fetchone()>>>print(f'The highest scoring Monty Python movie is{title!r}, released in{year}')The highest scoring Monty Python movie is 'Monty Python and the Holy Grail', released in 1975

You’ve now created an SQLite database using thesqlite3 module,inserted data and retrieved values from it in multiple ways.

Reference

Module functions

sqlite3.connect(database,timeout=5.0,detect_types=0,isolation_level='DEFERRED',check_same_thread=True,factory=sqlite3.Connection,cached_statements=100,uri=False)

Open a connection to an SQLite database.

Parameters
Return type

Connection

Raises anauditing eventsqlite3.connect with argumentdatabase.

Raises anauditing eventsqlite3.connect/handle with argumentconnection_handle.

New in version 3.4:Theuri parameter.

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

New in version 3.10:Thesqlite3.connect/handle auditing event.

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().

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, you willget tracebacks from callbacks onsys.stderr. UseFalse todisable the feature again.

sqlite3.register_adapter(type,adapter,/)

Register anadapter callable to adapt the Python typetype into anSQLite 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 theconverter callable 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.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 type name must be wrapped in square brackets ([]).

SELECTpas"p [point]"FROMtest;!willlookupconverter"point"

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

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.

sqlite3.SQLITE_OK
sqlite3.SQLITE_DENY
sqlite3.SQLITE_IGNORE

Flags that should be returned by theauthorizer_callback callablepassed 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, stating the level of thread safetythesqlite3 module supports. Currently hard-coded to1, meaning“Threads may share the module, but not connections.” However, this may notalways be true. You can check the underlying SQLite library’s compile-timethreaded mode using the following query:

importsqlite3con=sqlite3.connect(":memory:")con.execute("""    select * from pragma_compile_options    where compile_options like 'THREADSAFE=%'""").fetchall()

Note that theSQLITE_THREADSAFE levels do not match the DB-API 2.0threadsafety levels.

sqlite3.version

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

sqlite3.version_info

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

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.

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 a callable returning an instance ofCursoror its subclasses.

commit()

Commit any pending transaction to the database.If there is no open transaction, this method is a no-op.

rollback()

Roll back to the start of any pending transaction.If there is no open transaction, this method is a no-op.

close()

Close the database connection.Any pending transaction is not committed implicitly;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) – A callable 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.

Raises

NotSupportedError – Ifdeterministic is used with SQLite versions older than 3.8.3.

New in version 3.8: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',)
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:

    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()
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.

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)

Register callableauthorizer_callback to be invoked for each attempt toaccess a column of a table in the database. The callback should returnone 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.

set_progress_handler(progress_handler,n)

Register callableprogress_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 anOperationalErrorexception.

set_trace_callback(trace_callback)

Register callabletrace_callback to be invoked for each SQL statementthat 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.

New in version 3.3.

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.

New 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)con.close()
load_extension(path,/)

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

Raises anauditing eventsqlite3.load_extension with argumentsconnection,path.

New in version 3.2.

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

iterdump()

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.

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()
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 a callable, it is invoked with three integer arguments forevery 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)

New in version 3.7.

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.

New in version 3.2.

isolation_level

This attribute controls thetransaction handling performed bysqlite3.If set toNone, transactions are never implicitly opened.If set to one of"DEFERRED","IMMEDIATE", or"EXCLUSIVE",corresponding to the underlyingSQLite transaction behaviour,implicittransaction management is performed.

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

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

A callable that accepts abytes parameter and returns a textrepresentation of it.The callable is invoked for SQLite values with theTEXT data type.By default, this attribute is set tostr.If you want to returnbytes instead, settext_factory tobytes.

Example:

con=sqlite3.connect(":memory:")cur=con.cursor()AUSTRIA="Österreich"# by default, rows are returned as strcur.execute("SELECT ?",(AUSTRIA,))row=cur.fetchone()assertrow[0]==AUSTRIA# but we can make sqlite3 always return bytestrings ...con.text_factory=bytescur.execute("SELECT ?",(AUSTRIA,))row=cur.fetchone()asserttype(row[0])isbytes# the bytestrings will be encoded in UTF-8, unless you stored garbage in the# database ...assertrow[0]==AUSTRIA.encode("utf-8")# we can also implement a custom text_factory ...# here we implement one that appends "foo" to all stringscon.text_factory=lambdax:x.decode("utf-8")+"foo"cur.execute("SELECT ?",("bar",))row=cur.fetchone()assertrow[0]=="barfoo"con.close()
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 SQL a single SQL statement,optionally binding Python values usingplaceholders.

Parameters
Raises

Warning – Ifsql contains more than one SQL statement.

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

Useexecutescript() to execute multiple SQL statements.

executemany(sql,parameters,/)

For every item inparameters,repeatedly execute theparameterizedSQL statementsql.

Uses the same implicit transaction handling asexecute().

Parameters
Raises

Example:

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

Execute the SQL statements insql_script.If 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
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.

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.

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 raised bysqlite3 if an SQL query is not astring, or if multiple statements are passed toexecute() orexecutemany().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.

exceptionsqlite3.InterfaceError

This exception is raised bysqlite3 for fetch across rollback,or ifsqlite3 is unable to bind parameters.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 trying to operate on a closedConnection,or trying to execute non-DML statements withexecutemany().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:

Python type

SQLite type

None

NULL

int

INTEGER

float

REAL

str

TEXT

bytes

BLOB

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

SQLite type

Python type

NULL

None

INTEGER

int

REAL

float

TEXT

depends ontext_factory,str by default

BLOB

bytes

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

There are default adapters for the date and datetime types in the datetimemodule. They will be sent as ISO dates/ISO timestamps to SQLite.

The default converters are registered under the name “date” fordatetime.date and under the name “timestamp” fordatetime.datetime.

This way, you can use date/timestamps from Python without any additionalfiddling in most cases. The format of the adapters is also compatible with theexperimental SQLite date/time functions.

The following example demonstrates this.

importsqlite3importdatetimecon=sqlite3.connect(":memory:",detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)cur=con.cursor()cur.execute("create table test(d date, ts timestamp)")today=datetime.date.today()now=datetime.datetime.now()cur.execute("insert into test(d, ts) values (?, ?)",(today,now))cur.execute("select d, ts from test")row=cur.fetchone()print(today,"=>",row[0],type(row[0]))print(now,"=>",row[1],type(row[1]))cur.execute('select current_date as "d [date]", current_timestamp as "ts [timestamp]"')row=cur.fetchone()print("current_date",row[0],type(row[0]))print("current_timestamp",row[1],type(row[1]))con.close()

If a timestamp stored in SQLite has a fractional part longer than 6numbers, its value will be truncated to microsecond precision by thetimestamp converter.

Note

The default “timestamp” converter ignores UTC offsets in the database andalways returns a naivedatetime.datetime object. To preserve UTCoffsets in timestamps, either leave converters disabled, or register anoffset-aware converter withregister_converter().

How-to guides

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 should 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())

Note

PEP 249 numeric placeholders arenot supported.If used, they will be interpreted as named placeholders.

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.

How to write adaptable objects

Suppose we have aPoint class that represents a pair of coordinates,x andy, in a Cartesian coordinate system.The coordinate pair will be stored as a text string in the database,using a semicolon to separate the coordinates.This can be implemented by adding a__conform__(self,protocol)method which returns the adapted value.The object passed toprotocol will be of typePrepareProtocol.

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])

How to register adapter callables

The other possibility is to create a function that converts the Python objectto an SQLite-compatible type.This function can then be registered usingregister_adapter().

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])

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.

Note

Converter functions arealways passed abytes object,no matter the underlying SQLite data type.

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])

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.

If there is no open transaction upon leaving the body of thewith statement,the context manager is a no-op.

Note

The context manager neither implicitly opens a new transactionnor closes the connection.

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

  • 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,)

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

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}

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

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

Explanation

Transaction control

Thesqlite3 module does not adhere to the transaction handling recommendedbyPEP 249.

If the connection attributeisolation_levelis notNone,new transactions are implicitly opened beforeexecute() andexecutemany() executesINSERT,UPDATE,DELETE, orREPLACE statements;for other statements, no implicit transaction handling is performed.Use thecommit() androllback() methodsto respectively commit and roll back pending transactions.You can choose the underlyingSQLite transaction behaviour —that is, whether and what type ofBEGIN statementssqlite3implicitly executes –via theisolation_level attribute.

Ifisolation_level is set toNone,no transactions are implicitly opened at all.This leaves the underlying SQLite library inautocommit mode,but also allows the user to perform their own transaction handlingusing explicit SQL statements.The underlying SQLite library autocommit mode can be queried using thein_transaction attribute.

Theexecutescript() method implicitly commitsany pending transaction before execution of the given SQL script,regardless of the value ofisolation_level.

Changed in version 3.6:sqlite3 used to implicitly commit an open transaction before DDLstatements. This is no longer the case.