Source

python-clinic / Doc / library / sqlite3.rst

:mod:`sqlite3` --- DB-API 2.0 interface for SQLite databases

SQLite is a C library that provides a lightweight disk-based database that doesn't require a separate server process and allows accessing the database using a nonstandard variant of the SQL query language. Some applications can use SQLite for internal data storage. It's also possible to prototype an application using SQLite and then port the code to a larger database such as PostgreSQL or Oracle.

sqlite3 was written by Gerhard Häring and provides a SQL interface compliant with the DB-API 2.0 specification described by PEP 249.

To use the module, you must first create a :class:`Connection` object that represents the database. Here the data will be stored in the :file:`/tmp/example` file:

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

You can also supply the special name :memory: to create a database in RAM.

Once you have a :class:`Connection`, you can create a :class:`Cursor` object and call its :meth:`~Cursor.execute` method to perform SQL commands:

c = conn.cursor()

# Create table
c.execute('''create table stocks
(date text, trans text, symbol text,
 qty real, price real)''')

# Insert a row of data
c.execute("""insert into stocks
          values ('2006-01-05','BUY','RHAT',100,35.14)""")

# Save (commit) the changes
conn.commit()

# We can also close the cursor if we are done with it
c.close()

Usually your SQL operations will need to use values from Python variables. You shouldn't assemble your query using Python's string operations because doing so is insecure; it makes your program vulnerable to an SQL injection attack.

Instead, use the DB-API's parameter substitution. Put ? as a placeholder wherever you want to use a value, and then provide a tuple of values as the second argument to the cursor's :meth:`~Cursor.execute` method. (Other database modules may use a different placeholder, such as %s or :1.) For example:

# Never do this -- insecure!
symbol = 'IBM'
c.execute("select * from stocks where symbol = '%s'" % symbol)

# Do this instead
t = ('IBM',)
c.execute('select * from stocks where symbol=?', t)

# Larger example
for t in [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
          ('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),
          ('2006-04-06', 'SELL', 'IBM', 500, 53.00),
         ]:
    c.execute('insert into stocks values (?,?,?,?,?)', t)

To retrieve data after executing a SELECT statement, you can either treat the cursor as an :term:`iterator`, call the cursor's :meth:`~Cursor.fetchone` method to retrieve a single matching row, or call :meth:`~Cursor.fetchall` to get a list of the matching rows.

This example uses the iterator form:

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

Module functions and constants

Connection Objects

A SQLite database connection has the following attributes and methods:

Cursor Objects

A :class:`Cursor` instance has the following attributes and methods.

Row Objects

A :class:`Row` instance serves as a highly optimized :attr:`~Connection.row_factory` for :class:`Connection` 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 and :func:`len`.

If two :class:`Row` objects have exactly the same columns and their members are equal, they compare equal.

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

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

Now we plug :class:`Row` in:

>>> conn.row_factory = sqlite3.Row
>>> c = conn.cursor()
>>> c.execute('select * from stocks')
<sqlite3.Cursor object at 0x7f4e7dd8fa80>
>>> r = c.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
>>> for member in r:
...     print(member)
...
2006-01-05
BUY
RHAT
100.0
35.14

SQLite and Python types

Introduction

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
:const:`None` NULL
:class:`int` INTEGER
:class:`float` REAL
:class:`str` TEXT
:class:`bytes` BLOB

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

SQLite type Python type
NULL :const:`None`
INTEGER :class:`int`
REAL :class:`float`
TEXT depends on text_factory, str by default
BLOB :class:`bytes`

The type system of the :mod:`sqlite3` module is extensible in two ways: you can store additional Python types in a SQLite database via object adaptation, and you can let the :mod:`sqlite3` module convert SQLite types to different Python types via converters.

Using adapters to store additional Python types in SQLite databases

As described before, SQLite supports only a limited set of types natively. To use other Python types with SQLite, you must adapt them to one of the sqlite3 module's supported types for SQLite: one of NoneType, int, float, str, bytes.

The :mod:`sqlite3` module uses Python object adaptation, as described in PEP 246 for this. The protocol to use is :class:`PrepareProtocol`.

There are two ways to enable the :mod:`sqlite3` module to adapt a custom Python type to one of the supported ones.

Letting your object adapt itself

This is a good approach if you write the class yourself. Let's suppose you have a class like this:

class Point:
    def __init__(self, x, y):
        self.x, self.y = x, y

Now you want to store the point in a single SQLite column. First you'll have to choose one of the supported types first to be used for representing the point. Let's just use str and separate the coordinates using a semicolon. Then you need to give your class a method __conform__(self, protocol) which must return the converted value. The parameter protocol will be :class:`PrepareProtocol`.

Registering an adapter callable

The other possibility is to create a function that converts the type to the string representation and register the function with :meth:`register_adapter`.

The :mod:`sqlite3` module has two default adapters for Python's built-in :class:`datetime.date` and :class:`datetime.datetime` types. Now let's suppose we want to store :class:`datetime.datetime` objects not in ISO representation, but as a Unix timestamp.

Converting SQLite values to custom Python types

Writing an adapter lets you send custom Python types to SQLite. But to make it really useful we need to make the Python to SQLite to Python roundtrip work.

Enter converters.

Let's go back to the :class:`Point` class. We stored the x and y coordinates separated via semicolons as strings in SQLite.

First, we'll define a converter function that accepts the string as a parameter and constructs a :class:`Point` object from it.

Note

Converter functions always get called with a string, no matter under which data type you sent the value to SQLite.

def convert_point(s):
    x, y = map(float, s.split(b";"))
    return Point(x, y)

Now you need to make the :mod:`sqlite3` module know that what you select from the database is actually a point. There are two ways of doing this:

  • Implicitly via the declared type
  • Explicitly via the column name

Both ways are described in section :ref:`sqlite3-module-contents`, in the entries for the constants :const:`PARSE_DECLTYPES` and :const:`PARSE_COLNAMES`.

The following example illustrates both approaches.

Default adapters and converters

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

The default converters are registered under the name "date" for :class:`datetime.date` and under the name "timestamp" for :class:`datetime.datetime`.

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

The following example demonstrates this.

Controlling Transactions

By default, the :mod:`sqlite3` module opens transactions implicitly before a Data 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 or the aforementioned).

So if you are within a transaction and issue a command like CREATE TABLE ..., VACUUM, PRAGMA, the :mod:`sqlite3` module will commit implicitly before executing that command. There are two reasons for doing that. The first is that some of these commands don't work within transactions. The other reason is that sqlite3 needs to keep track of the transaction state (if a transaction is active or not). The current transaction state is exposed through the :attr:`Connection.in_transaction` attribute of the connection object.

You can control which kind of BEGIN statements sqlite3 implicitly executes (or none at all) via the isolation_level parameter to the :func:`connect` call, or via the :attr:`isolation_level` property of connections.

If you want autocommit mode, then set :attr:`isolation_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 :mod:`sqlite3` efficiently

Using shortcut methods

Using the nonstandard :meth:`execute`, :meth:`executemany` and :meth:`executescript` methods of the :class:`Connection` object, your code can be written more concisely because you don't have to create the (often superfluous) :class:`Cursor` objects explicitly. Instead, the :class:`Cursor` objects are created implicitly and these shortcut methods return the cursor objects. This way, you can execute a SELECT statement and iterate over it directly using only a single call on the :class:`Connection` object.

Accessing columns by name instead of by index

One useful feature of the :mod:`sqlite3` module is the built-in :class:`sqlite3.Row` class designed to be used as a row factory.

Rows wrapped with this class can be accessed both by index (like tuples) and case-insensitively by name:

Using the connection as a context manager

Connection objects can be used as context managers that automatically commit or rollback transactions. In the event of an exception, the transaction is rolled back; otherwise, the transaction is committed:

Common issues

Multithreading

Older SQLite versions had issues with sharing connections between threads. That's why the Python module disallows sharing connections and cursors between threads. If you still try to do so, you will get an exception at runtime.

The only exception is calling the :meth:`~Connection.interrupt` method, which only makes sense to call from a different thread.

Footnotes

[1]The sqlite3 module is not built with loadable extension support by default, because some platforms (notably Mac OS X) have SQLite libraries which are compiled without this feature. To get loadable extension support, you must pass --enable-loadable-sqlite-extensions to configure.