conn=sqlite3.connect('/tmp/example')

c=conn.cursor()# Create tablec.execute('''create table stocks(date text, trans text, symbol text, qty real, price real)''')# Insert a row of datac.execute("""insert into stocks          values ('2006-01-05','BUY','RHAT',100,35.14)""")# Save (commit) the changesconn.commit()# We can also close the cursor if we are done with itc.close()

# Never do this -- insecure!symbol='IBM'c.execute("... where symbol = '%s'"%symbol)# Do this insteadt=(symbol,)c.execute('select * from stocks where symbol=?',t)# Larger examplefortin(('2006-03-28','BUY','IBM',1000,45.00),('2006-04-05','BUY','MSOFT',1000,72.00),('2006-04-06','SELL','IBM',500,53.00),):c.execute('insert into stocks values (?,?,?,?,?)',t)

>>>c=conn.cursor()>>>c.execute('select * from stocks order by price')>>>forrowinc:...printrow...(u'2006-01-05', u'BUY', u'RHAT', 100, 35.140000000000001)(u'2006-03-28', u'BUY', u'IBM', 1000, 45.0)(u'2006-04-06', u'SELL', u'IBM', 500, 53.0)(u'2006-04-05', u'BUY', u'MSOFT', 1000, 72.0)>>>

See also

http://www.pysqlite.org

The pysqlite web page.

http://www.sqlite.org

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

PEP 249 - Database API Specification 2.0

PEP written by Marc-André Lemburg.

Module functions and constants¶

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.descriptionis only the first word of the column name, i. e. if you use something like'as"x[datetime]"' in your SQL, then we will parse out everything until thefirst blank for the column name: the column name would simply be “x”.

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

Opens a connection to the SQLite database filedatabase. You can use":memory:" to open a database connection to a database that resides in RAMinstead 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 theConnection.isolation_level property ofConnection objects.

SQLite natively supports only the types TEXT, INTEGER, FLOAT, 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.

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.

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 that the case oftypename andthe name of the type in your query must match!

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, long,float, str (UTF-8 encoded), unicode or buffer.

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=raw_input()ifline=="":breakbuffer+=lineifsqlite3.complete_statement(buffer):try:buffer=buffer.strip()cur.execute(buffer)ifbuffer.lstrip().upper().startswith("SELECT"):printcur.fetchall()exceptsqlite3.Error,e: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, youcan call this function withflag as True. Afterwards, you will get tracebacksfrom callbacks onsys.stderr. UseFalse to disable the featureagain.

Connection Objects¶

AConnection instance has the following attributes and methods:

Connection.isolation_level¶

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

Connection.cursor([cursorClass])¶

The cursor method accepts a single optional parametercursorClass. Ifsupplied, this must be a custom cursor class that extendssqlite3.Cursor.

Connection.commit()¶

This method commits the current transaction. If you don’t call this method,anything you did since the last call to commit() is not visible from 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.

Connection.rollback()¶

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

Connection.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!

Connection.execute(sql[,parameters])¶

This is a nonstandard shortcut that creates an intermediate cursor object bycalling the cursor method, then calls the cursor’sexecute() method withthe parameters given.

Connection.executemany(sql[,parameters])¶

This is a nonstandard shortcut that creates an intermediate cursor object bycalling the cursor method, then calls the cursor’sexecutemany() methodwith the parameters given.

Connection.executescript(sql_script)¶

This is a nonstandard shortcut that creates an intermediate cursor object bycalling the cursor method, then calls the cursor’sexecutescript() methodwith the parameters given.

Connection.create_function(name,num_params,func)¶

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, andfunc is a Python callable that is calledas the SQL function.

The function can return any of the types supported by SQLite: unicode, str, int,long, float, buffer and None.

Example:

importsqlite3importmd5defmd5sum(t):returnmd5.md5(t).hexdigest()con=sqlite3.connect(":memory:")con.create_function("md5",1,md5sum)cur=con.cursor()cur.execute("select md5(?)",("foo",))printcur.fetchone()[0]

Connection.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, and afinalize method which will return thefinal result of the aggregate.

Thefinalize method can return any of the types supported by SQLite:unicode, str, int, long, float, buffer and None.

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")printcur.fetchone()[0]

Connection.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):return-cmp(string1,string2)con=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:printrowcon.close()

To remove a collation, callcreate_collation with None as callable:

con.create_collation("reverse",None)

Connection.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.

Connection.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.

Connection.set_progress_handler(handler,n)¶

New in version 2.6.

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.

Connection.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")printcur.fetchone()["a"]

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.

Connection.text_factory¶

Using this attribute you can control what objects are returned for the TEXT datatype. By default, this attribute is set tounicode and thesqlite3 module will return Unicode objects for TEXT. If you want toreturn bytestrings instead, you can set it tostr.

For efficiency reasons, there’s also a way to return Unicode objects only fornon-ASCII data, and bytestrings otherwise. To activate it, set this attribute tosqlite3.OptimizedUnicode.

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()# Create the tablecon.execute("create table person(lastname, firstname)")AUSTRIA=u"\xd6sterreich"# by default, rows are returned as Unicodecur.execute("select ?",(AUSTRIA,))row=cur.fetchone()assertrow[0]==AUSTRIA# but we can make pysqlite always return bytestrings ...con.text_factory=strcur.execute("select ?",(AUSTRIA,))row=cur.fetchone()asserttype(row[0])==str# 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 will ignore Unicode characters that cannot be# decoded from UTF-8con.text_factory=lambdax:unicode(x,"utf-8","ignore")cur.execute("select ?",("this is latin1 and would normally create errors"+u"\xe4\xf6\xfc".encode("latin1"),))row=cur.fetchone()asserttype(row[0])==unicode# pysqlite offers a builtin optimized text_factory that will return bytestring# objects, if the data is in ASCII only, and otherwise return unicode objectscon.text_factory=sqlite3.OptimizedUnicodecur.execute("select ?",(AUSTRIA,))row=cur.fetchone()asserttype(row[0])==unicodecur.execute("select ?",("Germany",))row=cur.fetchone()asserttype(row[0])==str

Connection.total_changes¶

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

Connection.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.

New in version 2.6.

Example:

# Convert file existing_db.db to SQL dump file dump.sqlimportsqlite3,oscon=sqlite3.connect('existing_db.db')full_dump=os.linesep.join(con.iterdump())f=open('dump.sql','w')f.writelines(full_dump)f.close()

Cursor Objects¶

ACursor instance has the following attributes and methods:

Cursor.execute(sql[,parameters])¶

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

This example shows how to use parameters with qmark style:

importsqlite3con=sqlite3.connect("mydb")cur=con.cursor()who="Yeltsin"age=72cur.execute("select name_last, age from people where name_last=? and age=?",(who,age))printcur.fetchone()

This example shows how to use the named style:

importsqlite3con=sqlite3.connect("mydb")cur=con.cursor()who="Yeltsin"age=72cur.execute("select name_last, age from people where name_last=:who and age=:age",{"who":who,"age":age})printcur.fetchone()

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

Cursor.executemany(sql,seq_of_parameters)¶

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

importsqlite3classIterChars:def__init__(self):self.count=ord('a')def__iter__(self):returnselfdefnext(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")printcur.fetchall()

Here’s a shorter example using agenerator:

importsqlite3defchar_generator():importstringforcinstring.letters[:26]: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")printcur.fetchall()

Cursor.executescript(sql_script)¶

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

sql_script can be a bytestring or a Unicode string.

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    );    """)

Cursor.fetchone()¶

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

Cursor.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.

Cursor.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.

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.

ForDELETE statements, SQLite reportsrowcount as 0 if you make aDELETEFROMtable without any condition.

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

As required by the Python DB API Spec, therowcount attribute “is -1 incase no executeXX() has been performed on the cursor or the rowcount of the lastoperation is not determinable by the interface”.

This includesSELECT statements because we cannot determine the number ofrows a query produced until all rows were fetched.

Cursor.lastrowid¶

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

SQLite and Python types¶

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

importsqlite3classPoint(object):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,))printcur.fetchone()[0]

importsqlite3classPoint(object):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,))printcur.fetchone()[0]

importsqlite3importdatetime,timedefadapt_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,))printcur.fetchone()[0]

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

importsqlite3classPoint(object):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)defconvert_point(s):x,y=map(float,s.split(";"))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()printtoday,"=>",row[0],type(row[0])printnow,"=>",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])

Controlling Transactions¶

By default, thesqlite3 module opens transactions implicitly before aData Modification Language (DML) statement (i.e. INSERT/UPDATE/DELETE/REPLACE),and commits transactions implicitly before a non-DML, non-query statement (i. e.anything other than SELECT/INSERT/UPDATE/DELETE/REPLACE).

So if you are within a transaction and issue a command likeCREATETABLE...,VACUUM,PRAGMA, thesqlite3 module will commit implicitlybefore executing that command. There are two reasons for doing that. The firstis that some of these commands don’t work within transactions. The other reasonis that pysqlite needs to keep track of the transaction state (if a transactionis active or not).

You can control which kind of “BEGIN” statements pysqlite implicitly executes(or none at all) via theisolation_level parameter to theconnect()call, or via theisolation_level property of connections.

If you wantautocommit mode, then setisolation_level to None.

Otherwise leave it at its default, which will result in a plain “BEGIN”statement, or set it to one of SQLite’s supported isolation levels: DEFERRED,IMMEDIATE or EXCLUSIVE.

Using pysqlite 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"):printrow# Using a dummy WHERE clause to not let SQLite take the shortcut table deletes.print"I just deleted",con.execute("delete from person where 1=1").rowcount,"rows"

importsqlite3con=sqlite3.connect("mydb")con.row_factory=sqlite3.Rowcur=con.cursor()cur.execute("select name_last, age from people")forrowincur:assertrow[0]==row["name_last"]assertrow["name_last"]==row["nAmE_lAsT"]assertrow[1]==row["age"]assertrow[1]==row["AgE"]

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 catchedtry:withcon:con.execute("insert into person(firstname) values (?)",("Joe",))exceptsqlite3.IntegrityError:print"couldn't add Joe twice"