Module functions and constants¶

sqlite3.version¶

The version number of this module, as a string. This is not the version ofthe SQLite library.

sqlite3.version_info¶

The version number of this module, as a tuple of integers. This is not theversion of the SQLite library.

sqlite3.sqlite_version¶

The version number of the run-time SQLite library, as a string.

sqlite3.sqlite_version_info¶

The version number of the run-time SQLite library, as a tuple of integers.

sqlite3.PARSE_DECLTYPES¶

This constant is meant to be used with thedetect_types parameter of theconnect() function.

Setting it makes thesqlite3 module parse the declared type for eachcolumn it returns. It will parse out the first word of the declared type,i. e. for “integer primary key”, it will parse out “integer”, or for“number(10)” it will parse out “number”. Then for that column, it will lookinto the converters dictionary and use the converter function registered forthat type there.

sqlite3.PARSE_COLNAMES¶

This constant is meant to be used with thedetect_types parameter of theconnect() function.

Setting this makes the SQLite interface parse the column name for each column itreturns. It will look for a string formed [mytype] in there, and then decidethat ‘mytype’ is the type of the column. It will try to find an entry of‘mytype’ in the converters dictionary and then use the converter function foundthere to return the value. The column name found inCursor.descriptiondoes not include the type, i. e. if you use something like'as"Expirationdate[datetime]"' in your SQL, then we will parse outeverything until the first'[' for the column name and stripthe preceeding space: the column name would simply be “Expiration date”.

sqlite3.connect(database[,timeout,detect_types,isolation_level,check_same_thread,factory,cached_statements,uri])¶

Opens a connection to the SQLite database filedatabase. By default returns aConnection object, unless a customfactory is given.

database is apath-like object giving the pathname (absolute orrelative to the current working directory) of the database file to be opened.You can use":memory:" to open a database connection to a database thatresides in RAM instead of on disk.

When a database is accessed by multiple connections, and one of the processesmodifies the database, the SQLite database is locked until that transaction iscommitted. Thetimeout parameter specifies how long the connection should waitfor the lock to go away until raising an exception. The default for the timeoutparameter is 5.0 (five seconds).

For theisolation_level parameter, please see theisolation_level property ofConnection objects.

SQLite natively supports only the types TEXT, INTEGER, REAL, BLOB and NULL. Ifyou want to use other types you must add support for them yourself. Thedetect_types parameter and the using customconverters registered with themodule-levelregister_converter() function allow you to easily do that.

detect_types defaults to 0 (i. e. off, no type detection), you can set it toany combination ofPARSE_DECLTYPES andPARSE_COLNAMES to turntype detection on. Due to SQLite behaviour, types can’t be detected for generatedfields (for examplemax(data)), even whendetect_types parameter is set. Insuch case, the returned type isstr.

By default,check_same_thread isTrue and only the creating thread mayuse the connection. If setFalse, the returned connection may be sharedacross multiple threads. When using multiple threads with the same connectionwriting operations should be serialized by the user to avoid data corruption.

By default, thesqlite3 module uses itsConnection class for theconnect call. You can, however, subclass theConnection class and makeconnect() use your class instead by providing your class for thefactoryparameter.

Consult the sectionSQLite and Python types of this manual for details.

Thesqlite3 module internally uses a statement cache to avoid SQL parsingoverhead. If you want to explicitly set the number of statements that are cachedfor the connection, you can set thecached_statements parameter. The currentlyimplemented default is to cache 100 statements.

Ifuri is true,database is interpreted as a URI. This allows youto specify options. For example, to open a database in read-only modeyou can use:

db=sqlite3.connect('file:path/to/database?mode=ro',uri=True)

More information about this feature, including a list of recognized options, canbe found in theSQLite URI documentation.

Raises anauditing eventsqlite3.connect with argumentdatabase.

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.

sqlite3.register_converter(typename,callable)¶

Registers a callable to convert a bytestring from the database into a customPython type. The callable will be invoked for all database values that are ofthe typetypename. Confer the parameterdetect_types of theconnect()function for how the type detection works. Note thattypename and the name ofthe type in your query are matched in case-insensitive manner.

sqlite3.register_adapter(type,callable)¶

Registers a callable to convert the custom Python typetype into one ofSQLite’s supported types. The callablecallable accepts as single parameterthe Python value, and must return a value of the following types: int,float, str or bytes.

sqlite3.complete_statement(sql)¶

ReturnsTrue if the stringsql contains one or more complete SQLstatements terminated by semicolons. It does not verify that the SQL issyntactically correct, only that there are no unclosed string literals and thestatement is terminated by a semicolon.

This can be used to build a shell for SQLite, as in the following example:

# A minimal SQLite shell for experimentsimportsqlite3con=sqlite3.connect(":memory:")con.isolation_level=Nonecur=con.cursor()buffer=""print("Enter your SQL commands to execute in sqlite3.")print("Enter a blank line to exit.")whileTrue:line=input()ifline=="":breakbuffer+=lineifsqlite3.complete_statement(buffer):try:buffer=buffer.strip()cur.execute(buffer)ifbuffer.lstrip().upper().startswith("SELECT"):print(cur.fetchall())exceptsqlite3.Errorase:print("An error occurred:",e.args[0])buffer=""con.close()

sqlite3.enable_callback_tracebacks(flag)¶

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.

Connection Objects¶

classsqlite3.Connection¶

A SQLite database connection has the following attributes and methods:

isolation_level¶

Get or set the current default isolation level.None for autocommit mode orone of “DEFERRED”, “IMMEDIATE” or “EXCLUSIVE”. See sectionControlling Transactions for a more detailed explanation.

in_transaction¶

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

New in version 3.2.

cursor(factory=Cursor)¶

The cursor method accepts a single optional parameterfactory. Ifsupplied, this must be a callable returning an instance ofCursoror its subclasses.

commit()¶

This method commits the current transaction. If you don’t call this method,anything you did since the last call tocommit() is not visible fromother database connections. If you wonder why you don’t see the data you’vewritten to the database, please check you didn’t forget to call this method.

rollback()¶

This method rolls back any changes to the database since the last call tocommit().

close()¶

This closes the database connection. Note that this does not automaticallycallcommit(). If you just close your database connection withoutcallingcommit() first, your changes will be lost!

execute(sql[,parameters])¶

This is a nonstandard shortcut that creates a cursor object by callingthecursor() method, calls the cursor’sexecute() method with theparameters given, and returnsthe cursor.

executemany(sql[,parameters])¶

This is a nonstandard shortcut that creates a cursor object bycalling thecursor() method, calls the cursor’sexecutemany() method with theparameters given, andreturns the cursor.

executescript(sql_script)¶

This is a nonstandard shortcut that creates a cursor object bycalling thecursor() method, calls the cursor’sexecutescript() method with the givensql_script, andreturns the cursor.

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

Creates a user-defined function that you can later use from within SQLstatements under the function namename.num_params is the number ofparameters the function accepts (ifnum_params is -1, the function maytake any number of arguments), andfunc is a Python callable that iscalled as the SQL function. Ifdeterministic is true, the created functionis marked asdeterministic, whichallows SQLite to perform additional optimizations. This flag is supported bySQLite 3.8.3 or higher,NotSupportedError will be raised if usedwith older versions.

The function can return any of the types supported by SQLite: bytes, str, int,float andNone.

Changed in version 3.8:Thedeterministic parameter was added.

Example:

importsqlite3importhashlibdefmd5sum(t):returnhashlib.md5(t).hexdigest()con=sqlite3.connect(":memory:")con.create_function("md5",1,md5sum)cur=con.cursor()cur.execute("select md5(?)",(b"foo",))print(cur.fetchone()[0])con.close()

create_aggregate(name,num_params,aggregate_class)¶

Creates a user-defined aggregate function.

The aggregate class must implement astep method, which accepts the numberof parametersnum_params (ifnum_params is -1, the function may takeany number of arguments), and afinalize method which will return thefinal result of the aggregate.

Thefinalize method can return any of the types supported by SQLite:bytes, str, int, float andNone.

Example:

importsqlite3classMySum: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.cursor()cur.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)¶

Creates a collation with the specifiedname andcallable. The callable willbe passed two string arguments. It should return -1 if the first is orderedlower than the second, 0 if they are ordered equal and 1 if the first is orderedhigher than the second. Note that this controls sorting (ORDER BY in SQL) soyour comparisons don’t affect other SQL operations.

Note that the callable will get its parameters as Python bytestrings, which willnormally be encoded in UTF-8.

The following example shows a custom collation that sorts “the wrong way”:

importsqlite3defcollate_reverse(string1,string2):ifstring1==string2:return0elifstring1<string2:return1else:return-1con=sqlite3.connect(":memory:")con.create_collation("reverse",collate_reverse)cur=con.cursor()cur.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()

To remove a collation, callcreate_collation withNone as callable:

con.create_collation("reverse",None)

interrupt()¶

You can call this method from a different thread to abort any queries that mightbe executing on the connection. The query will then abort and the caller willget an exception.

set_authorizer(authorizer_callback)¶

This routine registers a callback. The callback is invoked for each attempt toaccess a column of a table in the database. The callback should returnSQLITE_OK if access is allowed,SQLITE_DENY if the entire SQLstatement should be aborted with an error andSQLITE_IGNORE if thecolumn should be treated as a NULL value. These constants are available in thesqlite3 module.

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(handler,n)¶

This routine registers a callback. The callback is 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 forhandler.

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

Registerstrace_callback to be called for each SQL statement that isactually executed by the SQLite backend.

The only argument passed to the callback is the statement (as string) thatis being executed. The return value of the callback is ignored. Note thatthe backend does not only run statements passed to theCursor.execute()methods. Other sources include the transaction management of the Pythonmodule and the execution of triggers defined in the current database.

PassingNone astrace_callback will disable the trace callback.

New in version 3.3.

enable_load_extension(enabled)¶

This routine allows/disallows the SQLite engine to load SQLite extensionsfrom shared libraries. SQLite extensions can define new functions,aggregates or whole new virtual table implementations. One well-knownextension is the fulltext-search extension distributed with SQLite.

Loadable extensions are disabled by default. See1.

New in version 3.2.

importsqlite3con=sqlite3.connect(":memory:")# enable extension loadingcon.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)¶

This routine loads a SQLite extension from a shared library. You have toenable extension loading withenable_load_extension() before you canuse this routine.

Loadable extensions are disabled by default. See1.

New in version 3.2.

row_factory¶

You can change this attribute to a callable that accepts the cursor and theoriginal row as a tuple and will return the real result row. This way, you canimplement more advanced ways of returning results, such as returning an objectthat can also access columns by name.

Example:

importsqlite3defdict_factory(cursor,row):d={}foridx,colinenumerate(cursor.description):d[col[0]]=row[idx]returndcon=sqlite3.connect(":memory:")con.row_factory=dict_factorycur=con.cursor()cur.execute("select 1 as a")print(cur.fetchone()["a"])con.close()

If returning a tuple doesn’t suffice and you want name-based access tocolumns, you should consider settingrow_factory to thehighly-optimizedsqlite3.Row type.Row provides bothindex-based and case-insensitive name-based access to columns with almost nomemory overhead. It will probably be better than your own customdictionary-based approach or even a db_row based solution.

text_factory¶

Using this attribute you can control what objects are returned for theTEXTdata type. By default, this attribute is set tostr and thesqlite3 module will return Unicode objects forTEXT. If you want toreturn bytestrings instead, you can set it tobytes.

You can also set it to any other callable that accepts a single bytestringparameter and returns the resulting object.

See the following example code for illustration:

importsqlite3con=sqlite3.connect(":memory:")cur=con.cursor()AUSTRIA="\xd6sterreich"# by default, rows are returned as Unicodecur.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¶

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

iterdump()¶

Returns an iterator to dump the database in an SQL text format. Useful whensaving an in-memory database for later restoration. This function providesthe same capabilities as the.dump command in thesqlite3shell.

Example:

# Convert file existing_db.db to SQL dump file dump.sqlimportsqlite3con=sqlite3.connect('existing_db.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)¶

This method makes a backup of a SQLite database even while it’s being accessedby other clients, or concurrently by the same connection. The copy will bewritten into the mandatory argumenttarget, that must be anotherConnection instance.

By default, or whenpages is either0 or a negative integer, the entiredatabase is copied in a single step; otherwise the method performs a loopcopying up topages pages at a time.

Ifprogress is specified, it must either beNone or a callable object thatwill be executed at each iteration with three integer arguments, respectivelythestatus of the last iteration, theremaining number of pages still to becopied and thetotal number of pages.

Thename argument specifies the database name that will be copied: it must bea string containing either"main", the default, to indicate the maindatabase,"temp" to indicate the temporary database or the name specifiedafter theAS keyword in anATTACHDATABASE statement for an attacheddatabase.

Thesleep argument specifies the number of seconds to sleep by betweensuccessive attempts to backup remaining pages, can be specified either as aninteger or a floating point value.

Example 1, copy an existing database into another:

importsqlite3defprogress(status,remaining,total):print(f'Copied{total-remaining} of{total} pages...')con=sqlite3.connect('existing_db.db')bck=sqlite3.connect('backup.db')withbck:con.backup(bck,pages=1,progress=progress)bck.close()con.close()

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

importsqlite3source=sqlite3.connect('existing_db.db')dest=sqlite3.connect(':memory:')source.backup(dest)

Availability: SQLite 3.6.11 or higher

New in version 3.7.

Cursor Objects¶

classsqlite3.Cursor¶

ACursor instance has the following attributes and methods.

execute(sql[,parameters])¶

Executes an SQL statement. The SQL statement may be parameterized (i. e.placeholders instead of SQL literals). Thesqlite3 module supports twokinds of placeholders: question marks (qmark style) and named placeholders(named style).

Here’s an example of both styles:

importsqlite3con=sqlite3.connect(":memory:")cur=con.cursor()cur.execute("create table people (name_last, age)")who="Yeltsin"age=72# This is the qmark style:cur.execute("insert into people values (?, ?)",(who,age))# And this is the named style:cur.execute("select * from people where name_last=:who and age=:age",{"who":who,"age":age})print(cur.fetchone())con.close()

execute() will only execute a single SQL statement. If you try to executemore than one statement with it, it will raise aWarning. Useexecutescript() if you want to execute multiple SQL statements with onecall.

executemany(sql,seq_of_parameters)¶

Executes an SQL command against all parameter sequences or mappings found inthe sequenceseq_of_parameters. Thesqlite3 module also allowsusing aniterator yielding parameters instead of a sequence.

importsqlite3classIterChars:def__init__(self):self.count=ord('a')def__iter__(self):returnselfdef__next__(self):ifself.count>ord('z'):raiseStopIterationself.count+=1return(chr(self.count-1),)# this is a 1-tuplecon=sqlite3.connect(":memory:")cur=con.cursor()cur.execute("create table characters(c)")theIter=IterChars()cur.executemany("insert into characters(c) values (?)",theIter)cur.execute("select c from characters")print(cur.fetchall())con.close()

Here’s a shorter example using agenerator:

importsqlite3importstringdefchar_generator():forcinstring.ascii_lowercase:yield(c,)con=sqlite3.connect(":memory:")cur=con.cursor()cur.execute("create table characters(c)")cur.executemany("insert into characters(c) values (?)",char_generator())cur.execute("select c from characters")print(cur.fetchall())con.close()

executescript(sql_script)¶

This is a nonstandard convenience method for executing multiple SQL statementsat once. It issues aCOMMIT statement first, then executes the SQL script itgets as a parameter.

sql_script can be an instance ofstr.

Example:

importsqlite3con=sqlite3.connect(":memory:")cur=con.cursor()cur.executescript("""    create table person(        firstname,        lastname,        age    );    create table book(        title,        author,        published    );    insert into book(title, author, published)    values (        'Dirk Gently''s Holistic Detective Agency',        'Douglas Adams',        1987    );    """)con.close()

fetchone()¶

Fetches the next row of a query result set, returning a single sequence,orNone when no more data is available.

fetchmany(size=cursor.arraysize)¶

Fetches the next set of rows of a query result, returning a list. An emptylist is returned when no more rows are available.

The number of rows to fetch per call is specified by thesize parameter.If it is not given, the cursor’s arraysize determines the number of rowsto be fetched. The method should try to fetch as many rows as indicated bythe size parameter. If this is not possible due to the specified number ofrows not being available, fewer rows may be 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()¶

Fetches all (remaining) rows of a query result, returning a list. Note thatthe cursor’s arraysize attribute can affect the performance of this operation.An empty list is returned when no rows are available.

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.

rowcount¶

Although theCursor class of thesqlite3 module implements thisattribute, the database engine’s own support for the determination of “rowsaffected”/”rows selected” is quirky.

Forexecutemany() statements, the number of modifications are summed upintorowcount.

As required by the Python DB API Spec, therowcount attribute “is -1 incase noexecuteXX() has been performed on the cursor or the rowcount of thelast operation is not determinable by the interface”. This includesSELECTstatements because we cannot determine the number of rows a query produceduntil all rows were fetched.

With SQLite versions before 3.6.5,rowcount is set to 0 ifyou make aDELETEFROMtable without any condition.

lastrowid¶

This read-only attribute provides the rowid of the last modified row. It isonly set if you issued anINSERT or aREPLACE statement using theexecute() method. For operations other thanINSERT orREPLACE or whenexecutemany() is called,lastrowid isset toNone.

If theINSERT orREPLACE statement failed to insert the previoussuccessful rowid is returned.

Changed in version 3.6:Added support for theREPLACE statement.

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.

description¶

This read-only attribute 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.

connection¶

This read-only attribute provides the SQLite databaseConnectionused by theCursor object. ACursor object created bycallingcon.cursor() will have aconnection attribute that refers tocon:

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

Row Objects¶

classsqlite3.Row¶

ARow instance serves as a highly optimizedrow_factory forConnection objects.It tries to mimic a tuple in most of its features.

It supports mapping access by column name and index, iteration,representation, equality testing andlen().

If twoRow objects have exactly the same columns and theirmembers are equal, they compare equal.

keys()¶

This method returns a list of column names. Immediately after a query,it is the first member of each tuple inCursor.description.

Changed in version 3.5:Added support of slicing.

Let’s assume we initialize a table as in the example given above:

con=sqlite3.connect(":memory:")cur=con.cursor()cur.execute('''create table stocks(date text, trans text, symbol text, qty real, price real)''')cur.execute("""insert into stocks            values ('2006-01-05','BUY','RHAT',100,35.14)""")con.commit()cur.close()

Now we plugRow in:

>>>con.row_factory=sqlite3.Row>>>cur=con.cursor()>>>cur.execute('select * from stocks')<sqlite3.Cursor object at 0x7f4e7dd8fa80>>>>r=cur.fetchone()>>>type(r)<class 'sqlite3.Row'>>>>tuple(r)('2006-01-05', 'BUY', 'RHAT', 100.0, 35.14)>>>len(r)5>>>r[2]'RHAT'>>>r.keys()['date', 'trans', 'symbol', 'qty', 'price']>>>r['qty']100.0>>>formemberinr:...print(member)...2006-01-05BUYRHAT100.035.14

Exceptions¶

exceptionsqlite3.Warning¶

A subclass ofException.

exceptionsqlite3.Error¶

The base class of the other exceptions in this module. It is a subclassofException.

exceptionsqlite3.DatabaseError¶

Exception raised for errors that are related to the database.

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.ProgrammingError¶

Exception raised for programming errors, e.g. table not found or alreadyexists, syntax error in the SQL statement, wrong number of parametersspecified, etc. It is a subclass ofDatabaseError.

exceptionsqlite3.OperationalError¶

Exception raised for errors that are related to the database’s operationand not necessarily under the control of the programmer, e.g. an unexpecteddisconnect occurs, the data source name is not found, a transaction couldnot be processed, etc. It is a subclass ofDatabaseError.

exceptionsqlite3.NotSupportedError¶

Exception raised in case a method or database API was used which is notsupported by the database, e.g. calling therollback()method on a connection that does not support transaction or hastransactions turned off. It is a subclass ofDatabaseError.

SQLite and Python types¶

classPoint:def__init__(self,x,y):self.x,self.y=x,y

importsqlite3classPoint:def__init__(self,x,y):self.x,self.y=x,ydef__conform__(self,protocol):ifprotocolissqlite3.PrepareProtocol:return"%f;%f"%(self.x,self.y)con=sqlite3.connect(":memory:")cur=con.cursor()p=Point(4.0,-3.2)cur.execute("select ?",(p,))print(cur.fetchone()[0])con.close()

importsqlite3classPoint:def__init__(self,x,y):self.x,self.y=x,ydefadapt_point(point):return"%f;%f"%(point.x,point.y)sqlite3.register_adapter(Point,adapt_point)con=sqlite3.connect(":memory:")cur=con.cursor()p=Point(4.0,-3.2)cur.execute("select ?",(p,))print(cur.fetchone()[0])con.close()

importsqlite3importdatetimeimporttimedefadapt_datetime(ts):returntime.mktime(ts.timetuple())sqlite3.register_adapter(datetime.datetime,adapt_datetime)con=sqlite3.connect(":memory:")cur=con.cursor()now=datetime.datetime.now()cur.execute("select ?",(now,))print(cur.fetchone()[0])con.close()

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

importsqlite3classPoint:def__init__(self,x,y):self.x,self.y=x,ydef__repr__(self):return"(%f;%f)"%(self.x,self.y)defadapt_point(point):return("%f;%f"%(point.x,point.y)).encode('ascii')defconvert_point(s):x,y=list(map(float,s.split(b";")))returnPoint(x,y)# Register the adaptersqlite3.register_adapter(Point,adapt_point)# Register the convertersqlite3.register_converter("point",convert_point)p=Point(4.0,-3.2)########################## 1) Using declared typescon=sqlite3.connect(":memory:",detect_types=sqlite3.PARSE_DECLTYPES)cur=con.cursor()cur.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()######################## 1) Using column namescon=sqlite3.connect(":memory:",detect_types=sqlite3.PARSE_COLNAMES)cur=con.cursor()cur.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()

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

Controlling Transactions¶

The underlyingsqlite3 library operates inautocommit mode by default,but the Pythonsqlite3 module by default does not.

autocommit mode means that statements that modify the database take effectimmediately. ABEGIN orSAVEPOINT statement disablesautocommitmode, and aCOMMIT, aROLLBACK, or aRELEASE that ends theoutermost transaction, turnsautocommit mode back on.

The Pythonsqlite3 module by default issues aBEGIN statementimplicitly before a Data Modification Language (DML) statement (i.e.INSERT/UPDATE/DELETE/REPLACE).

You can control which kind ofBEGIN statementssqlite3 implicitlyexecutes via theisolation_level parameter to theconnect()call, or via theisolation_level property of connections.If you specify noisolation_level, a plainBEGIN is used, which isequivalent to specifyingDEFERRED. Other possible values areIMMEDIATEandEXCLUSIVE.

You can disable thesqlite3 module’s implicit transaction management bysettingisolation_level toNone. This will leave the underlyingsqlite3 library operating inautocommit mode. You can then completelycontrol the transaction state by explicitly issuingBEGIN,ROLLBACK,SAVEPOINT, andRELEASE statements in your code.

Usingsqlite3 efficiently¶

importsqlite3persons=[("Hugo","Boss"),("Calvin","Klein")]con=sqlite3.connect(":memory:")# Create the tablecon.execute("create table person(firstname, lastname)")# Fill the tablecon.executemany("insert into person(firstname, lastname) values (?, ?)",persons)# Print the table contentsforrowincon.execute("select firstname, lastname from person"):print(row)print("I just deleted",con.execute("delete from person").rowcount,"rows")# close is not a shortcut method and it's not called automatically,# so the connection object should be closed manuallycon.close()

importsqlite3con=sqlite3.connect(":memory:")con.row_factory=sqlite3.Rowcur=con.cursor()cur.execute("select 'John' as name, 42 as age")forrowincur:assertrow[0]==row["name"]assertrow["name"]==row["nAmE"]assertrow[1]==row["age"]assertrow[1]==row["AgE"]con.close()

importsqlite3con=sqlite3.connect(":memory:")con.execute("create table person (id integer primary key, firstname varchar unique)")# Successful, con.commit() is called automatically afterwardswithcon:con.execute("insert into person(firstname) values (?)",("Joe",))# 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 person(firstname) values (?)",("Joe",))exceptsqlite3.IntegrityError:print("couldn't add Joe twice")# Connection object used as context manager only commits or rollbacks transactions,# so the connection object should be closed manuallycon.close()