sqlite3 (package)


The sqlite3 extension module provides a DB-API 2.0 (PEP 249) compliant
interface to the SQLite library, and requires SQLite 3.7.15 or newer.

To use the module, start by creating a database Connection object:

    import sqlite3
    cx = sqlite3.connect("test.db")  # test.db will be created or opened

The special path name ":memory:" can be provided to connect to a transient
in-memory database:

    cx = sqlite3.connect(":memory:")  # connect to a database in RAM

Once a connection has been established, create a Cursor object and call
its execute() method to perform SQL queries:

    cu = cx.cursor()

    # create a table
    cu.execute("create table lang(name, first_appeared)")

    # insert values into a table
    cu.execute("insert into lang values (?, ?)", ("C", 1972))

    # execute a query and iterate over the result
    for row in cu.execute("select * from lang"):
        print(row)

    cx.close()

The sqlite3 module is written by Gerhard Häring <gh@ghaering.de>.

Classes

memoryview

Create a new memoryview object which references the given object.

cast(...)

  Cast a memoryview to a new format or shape.

hex(...)

  Return the data in the buffer as a str of hexadecimal numbers.

    sep
      An optional single character or byte to separate hex bytes.
    bytes_per_sep
      How many bytes between separators.  Positive values count from the
      right, negative values count from the left.

  Example:
  >>> value = memoryview(b'\xb9\x01\xef')
  >>> value.hex()
  'b901ef'
  >>> value.hex(':')
  'b9:01:ef'
  >>> value.hex(':', 2)
  'b9:01ef'
  >>> value.hex(':', -2)
  'b901:ef'

release(self, /)

  Release the underlying buffer exposed by the memoryview object.

tobytes(self, /, order='C')

  Return the data in the buffer as a byte string.

  Order can be {'C', 'F', 'A'}. When order is 'C' or 'F', the data of the
  original array is converted to C or Fortran order. For contiguous views,
  'A' returns an exact copy of the physical memory. In particular, in-memory
  Fortran order is preserved. For non-contiguous views, the data is converted
  to C first. order=None is the same as order='C'.

tolist(self, /)

  Return the data in the buffer as a list of elements.

toreadonly(self, /)

  Return a readonly version of the memoryview.

c_contiguous = <attribute 'c_contiguous' of 'memoryview' objects>
  A bool indicating whether the memory is C contiguous.

contiguous = <attribute 'contiguous' of 'memoryview' objects>
  A bool indicating whether the memory is contiguous.

f_contiguous = <attribute 'f_contiguous' of 'memoryview' objects>
  A bool indicating whether the memory is Fortran contiguous.

format = <attribute 'format' of 'memoryview' objects>
  A string containing the format (in struct module style)
   for each element in the view.

itemsize = <attribute 'itemsize' of 'memoryview' objects>
  The size in bytes of each element of the memoryview.

nbytes = <attribute 'nbytes' of 'memoryview' objects>
  The amount of space in bytes that the array would use in
   a contiguous representation.

ndim = <attribute 'ndim' of 'memoryview' objects>
  An integer indicating how many dimensions of a multi-dimensional
   array the memory represents.

obj = <attribute 'obj' of 'memoryview' objects>
  The underlying object of the memoryview.

readonly = <attribute 'readonly' of 'memoryview' objects>
  A bool indicating whether the memory is read only.

shape = <attribute 'shape' of 'memoryview' objects>
  A tuple of ndim integers giving the shape of the memory
   as an N-dimensional array.

strides = <attribute 'strides' of 'memoryview' objects>
  A tuple of ndim integers giving the size in bytes to access
   each element for each dimension of the array.

suboffsets = <attribute 'suboffsets' of 'memoryview' objects>
  A tuple of integers used internally for PIL-style arrays.

Connection

SQLite database connection object.

backup(self, /, target, *, pages=-1, progress=None, name='main', sleep=0.25)

  Makes a backup of the database.

close(self, /)

  Close the database connection.

  Any pending transaction is not committed implicitly.

commit(self, /)

  Commit any pending transaction to the database.

  If there is no open transaction, this method is a no-op.

create_aggregate(self, /, name, n_arg, aggregate_class)

  Creates a new aggregate.

create_collation(self, name, callback, /)

  Creates a collation function.

create_function(self, /, name, narg, func, *, deterministic=False)

  Creates a new function.

cursor(...)

  Return a cursor for the connection.

enable_load_extension(self, enable, /)

  Enable dynamic loading of SQLite extension modules.

execute(...)

  Executes an SQL statement.

executemany(self, sql, parameters, /)

  Repeatedly executes an SQL statement.

executescript(self, sql_script, /)

  Executes multiple SQL statements at once.

interrupt(self, /)

  Abort any pending database operation.

iterdump(self, /)

  Returns iterator to the dump of the database in an SQL text format.

load_extension(self, name, /)

  Load SQLite extension module.

rollback(self, /)

  Roll back to the start of any pending transaction.

  If there is no open transaction, this method is a no-op.

set_authorizer(self, /, authorizer_callback)

  Sets authorizer callback.

set_progress_handler(self, /, progress_handler, n)

  Sets progress handler callback.

set_trace_callback(self, /, trace_callback)

  Sets a trace callback called for each SQL statement (passed as unicode).

DataError = <member 'DataError' of 'sqlite3.Connection' objects>

DatabaseError = <member 'DatabaseError' of 'sqlite3.Connection' objects>

Error = <member 'Error' of 'sqlite3.Connection' objects>

IntegrityError = <member 'IntegrityError' of 'sqlite3.Connection' objects>

InterfaceError = <member 'InterfaceError' of 'sqlite3.Connection' objects>

InternalError = <member 'InternalError' of 'sqlite3.Connection' objects>

NotSupportedError = <member 'NotSupportedError' of 'sqlite3.Connection' objects>

OperationalError = <member 'OperationalError' of 'sqlite3.Connection' objects>

ProgrammingError = <member 'ProgrammingError' of 'sqlite3.Connection' objects>

Warning = <member 'Warning' of 'sqlite3.Connection' objects>

in_transaction = <attribute 'in_transaction' of 'sqlite3.Connection' objects>

isolation_level = <attribute 'isolation_level' of 'sqlite3.Connection' objects>

row_factory = <member 'row_factory' of 'sqlite3.Connection' objects>

text_factory = <member 'text_factory' of 'sqlite3.Connection' objects>

total_changes = <attribute 'total_changes' of 'sqlite3.Connection' objects>

Cursor

SQLite database cursor class.

close(self, /)

  Closes the cursor.

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

  Executes an SQL statement.

executemany(self, sql, seq_of_parameters, /)

  Repeatedly executes an SQL statement.

executescript(self, sql_script, /)

  Executes multiple SQL statements at once.

fetchall(self, /)

  Fetches all rows from the resultset.

fetchmany(self, /, size=1)

  Fetches several rows from the resultset.

    size
      The default value is set by the Cursor.arraysize attribute.

fetchone(self, /)

  Fetches one row from the resultset.

setinputsizes(self, sizes, /)

  Required by DB-API. Does nothing in sqlite3.

setoutputsize(self, size, column=None, /)

  Required by DB-API. Does nothing in sqlite3.

arraysize = <member 'arraysize' of 'sqlite3.Cursor' objects>

connection = <member 'connection' of 'sqlite3.Cursor' objects>

description = <member 'description' of 'sqlite3.Cursor' objects>

lastrowid = <member 'lastrowid' of 'sqlite3.Cursor' objects>

row_factory = <member 'row_factory' of 'sqlite3.Cursor' objects>

rowcount = <member 'rowcount' of 'sqlite3.Cursor' objects>

DataError

with_traceback(...)

  Exception.with_traceback(tb) --
      set self.__traceback__ to tb and return self.

args = <attribute 'args' of 'BaseException' objects>

DatabaseError

with_traceback(...)

  Exception.with_traceback(tb) --
      set self.__traceback__ to tb and return self.

args = <attribute 'args' of 'BaseException' objects>

date

date(year, month, day) --> date object

ctime(...)

  Return ctime() style string.

fromisocalendar(...)

  int, int, int -> Construct a date from the ISO year, week number and weekday.

  This is the inverse of the date.isocalendar() function

fromisoformat(...)

  str -> Construct a date from the output of date.isoformat()

fromordinal(...)

  int -> date corresponding to a proleptic Gregorian ordinal.

fromtimestamp(timestamp, /)

  Create a date from a POSIX timestamp.

  The timestamp is a number, e.g. created via time.time(), that is interpreted
  as local time.

isocalendar(...)

  Return a named tuple containing ISO year, week number, and weekday.

isoformat(...)

  Return string in ISO 8601 format, YYYY-MM-DD.

isoweekday(...)

  Return the day of the week represented by the date.
  Monday == 1 ... Sunday == 7

replace(...)

  Return date with new specified fields.

strftime(...)

  format -> strftime() style string.

timetuple(...)

  Return time tuple, compatible with time.localtime().

today(...)

  Current date or datetime:  same as self.__class__.fromtimestamp(time.time()).

toordinal(...)

  Return proleptic Gregorian ordinal.  January 1 of year 1 is day 1.

weekday(...)

  Return the day of the week represented by the date.
  Monday == 0 ... Sunday == 6

day = <attribute 'day' of 'datetime.date' objects>

max = datetime.date(9999, 12, 31)

min = datetime.date(1, 1, 1)

month = <attribute 'month' of 'datetime.date' objects>

resolution = datetime.timedelta(days=1)

year = <attribute 'year' of 'datetime.date' objects>

Error

with_traceback(...)

  Exception.with_traceback(tb) --
      set self.__traceback__ to tb and return self.

args = <attribute 'args' of 'BaseException' objects>

IntegrityError

with_traceback(...)

  Exception.with_traceback(tb) --
      set self.__traceback__ to tb and return self.

args = <attribute 'args' of 'BaseException' objects>

InterfaceError

with_traceback(...)

  Exception.with_traceback(tb) --
      set self.__traceback__ to tb and return self.

args = <attribute 'args' of 'BaseException' objects>

InternalError

with_traceback(...)

  Exception.with_traceback(tb) --
      set self.__traceback__ to tb and return self.

args = <attribute 'args' of 'BaseException' objects>

NotSupportedError

with_traceback(...)

  Exception.with_traceback(tb) --
      set self.__traceback__ to tb and return self.

args = <attribute 'args' of 'BaseException' objects>

OperationalError

with_traceback(...)

  Exception.with_traceback(tb) --
      set self.__traceback__ to tb and return self.

args = <attribute 'args' of 'BaseException' objects>

PrepareProtocol

PEP 246 style object adaption protocol type.

ProgrammingError

with_traceback(...)

  Exception.with_traceback(tb) --
      set self.__traceback__ to tb and return self.

args = <attribute 'args' of 'BaseException' objects>

Row

keys(self, /)

  Returns the keys of the row.

time

time([hour[, minute[, second[, microsecond[, tzinfo]]]]]) --> a time object

All arguments are optional. tzinfo may be None, or an instance of
a tzinfo subclass. The remaining arguments may be ints.

dst(...)

  Return self.tzinfo.dst(self).

fromisoformat(...)

  string -> time from time.isoformat() output

isoformat(...)

  Return string in ISO 8601 format, [HH[:MM[:SS[.mmm[uuu]]]]][+HH:MM].

  The optional argument timespec specifies the number of additional terms
  of the time to include. Valid options are 'auto', 'hours', 'minutes',
  'seconds', 'milliseconds' and 'microseconds'.

replace(...)

  Return time with new specified fields.

strftime(...)

  format -> strftime() style string.

tzname(...)

  Return self.tzinfo.tzname(self).

utcoffset(...)

  Return self.tzinfo.utcoffset(self).

fold = <attribute 'fold' of 'datetime.time' objects>

hour = <attribute 'hour' of 'datetime.time' objects>

max = datetime.time(23, 59, 59, 999999)

microsecond = <attribute 'microsecond' of 'datetime.time' objects>

min = datetime.time(0, 0)

minute = <attribute 'minute' of 'datetime.time' objects>

resolution = datetime.timedelta(microseconds=1)

second = <attribute 'second' of 'datetime.time' objects>

tzinfo = <attribute 'tzinfo' of 'datetime.time' objects>

datetime

datetime(year, month, day[, hour[, minute[, second[, microsecond[,tzinfo]]]]])

The year, month and day arguments are required. tzinfo may be None, or an
instance of a tzinfo subclass. The remaining arguments may be ints.

astimezone(...)

  tz -> convert to local time in new timezone tz

combine(...)

  date, time -> datetime with same date and time fields

ctime(...)

  Return ctime() style string.

date(...)

  Return date object with same year, month and day.

dst(...)

  Return self.tzinfo.dst(self).

fromisocalendar(...)

  int, int, int -> Construct a date from the ISO year, week number and weekday.

  This is the inverse of the date.isocalendar() function

fromisoformat(...)

  string -> datetime from datetime.isoformat() output

fromordinal(...)

  int -> date corresponding to a proleptic Gregorian ordinal.

fromtimestamp(...)

  timestamp[, tz] -> tz's local time from POSIX timestamp.

isocalendar(...)

  Return a named tuple containing ISO year, week number, and weekday.

isoformat(...)

  [sep] -> string in ISO 8601 format, YYYY-MM-DDT[HH[:MM[:SS[.mmm[uuu]]]]][+HH:MM].
  sep is used to separate the year from the time, and defaults to 'T'.
  The optional argument timespec specifies the number of additional terms
  of the time to include. Valid options are 'auto', 'hours', 'minutes',
  'seconds', 'milliseconds' and 'microseconds'.

isoweekday(...)

  Return the day of the week represented by the date.
  Monday == 1 ... Sunday == 7

now(tz=None)

  Returns new datetime object representing current time local to tz.

    tz
      Timezone object.

  If no tz is specified, uses local timezone.

replace(...)

  Return datetime with new specified fields.

strftime(...)

  format -> strftime() style string.

strptime(...)

  string, format -> new datetime parsed from a string (like time.strptime()).

time(...)

  Return time object with same time but with tzinfo=None.

timestamp(...)

  Return POSIX timestamp as float.

timetuple(...)

  Return time tuple, compatible with time.localtime().

timetz(...)

  Return time object with same time and tzinfo.

today(...)

  Current date or datetime:  same as self.__class__.fromtimestamp(time.time()).

toordinal(...)

  Return proleptic Gregorian ordinal.  January 1 of year 1 is day 1.

tzname(...)

  Return self.tzinfo.tzname(self).

utcfromtimestamp(...)

  Construct a naive UTC datetime from a POSIX timestamp.

utcnow(...)

  Return a new datetime representing UTC day and time.

utcoffset(...)

  Return self.tzinfo.utcoffset(self).

utctimetuple(...)

  Return UTC time tuple, compatible with time.localtime().

weekday(...)

  Return the day of the week represented by the date.
  Monday == 0 ... Sunday == 6

day = <attribute 'day' of 'datetime.date' objects>

fold = <attribute 'fold' of 'datetime.datetime' objects>

hour = <attribute 'hour' of 'datetime.datetime' objects>

max = datetime.datetime(9999, 12, 31, 23, 59, 59, 999999)

microsecond = <attribute 'microsecond' of 'datetime.datetime' objects>

min = datetime.datetime(1, 1, 1, 0, 0)

minute = <attribute 'minute' of 'datetime.datetime' objects>

month = <attribute 'month' of 'datetime.date' objects>

resolution = datetime.timedelta(microseconds=1)

second = <attribute 'second' of 'datetime.datetime' objects>

tzinfo = <attribute 'tzinfo' of 'datetime.datetime' objects>

year = <attribute 'year' of 'datetime.date' objects>

Warning

with_traceback(...)

  Exception.with_traceback(tb) --
      set self.__traceback__ to tb and return self.

args = <attribute 'args' of 'BaseException' objects>

Functions

DateFromTicks

DateFromTicks(ticks)

TimeFromTicks

TimeFromTicks(ticks)

TimestampFromTicks

TimestampFromTicks(ticks)

adapt

adapt(...)

  Adapt given object to given protocol.

complete_statement

complete_statement(statement)

  Checks if a string contains a complete SQL statement.

connect

connect(...)

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

  Opens a connection to the SQLite database file *database*. You can use
  ":memory:" to open a database connection to a database that resides in
  RAM instead of on disk.

enable_callback_tracebacks

enable_callback_tracebacks(enable, /)

  Enable or disable callback functions throwing errors to stderr.

enable_shared_cache

enable_shared_cache(enable)

register_adapter

register_adapter(type, adapter, /)

  Register a function to adapt Python objects to SQLite values.

register_converter

register_converter(typename, converter, /)

  Register a function to convert SQLite values to Python objects.

Other members

PARSE_COLNAMES = 2

PARSE_DECLTYPES = 1

SQLITE_ALTER_TABLE = 26

SQLITE_ANALYZE = 28

SQLITE_ATTACH = 24

SQLITE_CREATE_INDEX = 1

SQLITE_CREATE_TABLE = 2

SQLITE_CREATE_TEMP_INDEX = 3

SQLITE_CREATE_TEMP_TABLE = 4

SQLITE_CREATE_TEMP_TRIGGER = 5

SQLITE_CREATE_TEMP_VIEW = 6

SQLITE_CREATE_TRIGGER = 7

SQLITE_CREATE_VIEW = 8

SQLITE_CREATE_VTABLE = 29

SQLITE_DELETE = 9

SQLITE_DENY = 1

SQLITE_DETACH = 25

SQLITE_DONE = 101

SQLITE_DROP_INDEX = 10

SQLITE_DROP_TABLE = 11

SQLITE_DROP_TEMP_INDEX = 12

SQLITE_DROP_TEMP_TABLE = 13

SQLITE_DROP_TEMP_TRIGGER = 14

SQLITE_DROP_TEMP_VIEW = 15

SQLITE_DROP_TRIGGER = 16

SQLITE_DROP_VIEW = 17

SQLITE_DROP_VTABLE = 30

SQLITE_FUNCTION = 31

SQLITE_IGNORE = 2

SQLITE_INSERT = 18

SQLITE_OK = 0

SQLITE_PRAGMA = 19

SQLITE_READ = 20

SQLITE_RECURSIVE = 33

SQLITE_REINDEX = 27

SQLITE_SAVEPOINT = 32

SQLITE_SELECT = 21

SQLITE_TRANSACTION = 22

SQLITE_UPDATE = 23

adapters = {(<class 'datetime.date'>, <class 'sqlite3.PrepareProtocol'>): <function register_adapters_and_converters.<locals>.adapt_date at 0x7f75e0c4beb0>, (<class 'datetime.datetime'>, <class 'sqlite3.PrepareProtocol'>): <function register_adapters_and_converters.<locals>.adapt_datetime at 0x7f75e0c4bf40>}

apilevel = '2.0'

converters = {'DATE': <function register_adapters_and_converters.<locals>.convert_date at 0x7f75e09e0040>, 'TIMESTAMP': <function register_adapters_and_converters.<locals>.convert_timestamp at 0x7f75e09e00d0>}

paramstyle = 'qmark'

sqlite_version = '3.39.4'

sqlite_version_info = (3, 39, 4)

threadsafety = 1

version = '2.6.0'

version_info = (2, 6, 0)

Modules