cpython-lru_cache-weakref: Doc/library/sqlite3.rst annotate

annotate Doc/library/sqlite3.rst @ 54294:dba767bc3155 legacy-trunk

remove mention of old ctypes version

author	Benjamin Peterson <benjamin@python.org>
date	Sat, 23 May 2009 20:59:09 +0000
parents	6e40add3ee02
children	bbd1d90d158a

rev	line source
42604 9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	1 :mod:`sqlite3` --- DB-API 2.0 interface for SQLite databases
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	2 ============================================================
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	3
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	4 .. module:: sqlite3
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	5 :synopsis: A DB-API 2.0 implementation using SQLite 3.x.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	6 .. sectionauthor:: Gerhard HÃ¤ring <gh@ghaering.de>
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	7
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	8
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	9 .. versionadded:: 2.5
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	10
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	11 SQLite is a C library that provides a lightweight disk-based database that
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	12 doesn't require a separate server process and allows accessing the database
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	13 using a nonstandard variant of the SQL query language. Some applications can use
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	14 SQLite for internal data storage. It's also possible to prototype an
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	15 application using SQLite and then port the code to a larger database such as
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	16 PostgreSQL or Oracle.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	17
54223 6e40add3ee02 #6055: refer to "sqlite3" consistently. Georg Brandl <georg@python.org> parents: 51745 diff changeset	18 sqlite3 was written by Gerhard Häring and provides a SQL interface compliant
42604 9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	19 with the DB-API 2.0 specification described by :pep:`249`.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	20
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	21 To use the module, you must first create a :class:`Connection` object that
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	22 represents the database. Here the data will be stored in the
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	23 :file:`/tmp/example` file::
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	24
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	25 conn = sqlite3.connect('/tmp/example')
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	26
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	27 You can also supply the special name ``:memory:`` to create a database in RAM.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	28
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	29 Once you have a :class:`Connection`, you can create a :class:`Cursor` object
49473 4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	30 and call its :meth:`~Cursor.execute` method to perform SQL commands::
42604 9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	31
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	32 c = conn.cursor()
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	33
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	34 # Create table
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	35 c.execute('''create table stocks
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	36 (date text, trans text, symbol text,
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	37 qty real, price real)''')
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	38
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	39 # Insert a row of data
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	40 c.execute("""insert into stocks
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	41 values ('2006-01-05','BUY','RHAT',100,35.14)""")
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	42
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	43 # Save (commit) the changes
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	44 conn.commit()
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	45
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	46 # We can also close the cursor if we are done with it
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	47 c.close()
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	48
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	49 Usually your SQL operations will need to use values from Python variables. You
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	50 shouldn't assemble your query using Python's string operations because doing so
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	51 is insecure; it makes your program vulnerable to an SQL injection attack.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	52
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	53 Instead, use the DB-API's parameter substitution. Put ``?`` as a placeholder
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	54 wherever you want to use a value, and then provide a tuple of values as the
54223 6e40add3ee02 #6055: refer to "sqlite3" consistently. Georg Brandl <georg@python.org> parents: 51745 diff changeset	55 second argument to the cursor's :meth:`~Cursor.execute` method. (Other database
6e40add3ee02 #6055: refer to "sqlite3" consistently. Georg Brandl <georg@python.org> parents: 51745 diff changeset	56 modules may use a different placeholder, such as ``%s`` or ``:1``.) For
6e40add3ee02 #6055: refer to "sqlite3" consistently. Georg Brandl <georg@python.org> parents: 51745 diff changeset	57 example::
42604 9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	58
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	59 # Never do this -- insecure!
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	60 symbol = 'IBM'
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	61 c.execute("... where symbol = '%s'" % symbol)
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	62
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	63 # Do this instead
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	64 t = (symbol,)
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	65 c.execute('select * from stocks where symbol=?', t)
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	66
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	67 # Larger example
49656 79c9ab6498fd #4267: small fixes in sqlite3 docs. Georg Brandl <georg@python.org> parents: 49473 diff changeset	68 for t in [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
42604 9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	69 ('2006-04-05', 'BUY', 'MSOFT', 1000, 72.00),
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	70 ('2006-04-06', 'SELL', 'IBM', 500, 53.00),
49656 79c9ab6498fd #4267: small fixes in sqlite3 docs. Georg Brandl <georg@python.org> parents: 49473 diff changeset	71 ]:
42604 9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	72 c.execute('insert into stocks values (?,?,?,?,?)', t)
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	73
43630 6df985744d96 Add :term:s for iterator. Georg Brandl <georg@python.org> parents: 43629 diff changeset	74 To retrieve data after executing a SELECT statement, you can either treat the
49473 4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	75 cursor as an :term:`iterator`, call the cursor's :meth:`~Cursor.fetchone` method to
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	76 retrieve a single matching row, or call :meth:`~Cursor.fetchall` to get a list of the
43630 6df985744d96 Add :term:s for iterator. Georg Brandl <georg@python.org> parents: 43629 diff changeset	77 matching rows.
42604 9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	78
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	79 This example uses the iterator form::
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	80
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	81 >>> c = conn.cursor()
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	82 >>> c.execute('select * from stocks order by price')
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	83 >>> for row in c:
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	84 ... print row
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	85 ...
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	86 (u'2006-01-05', u'BUY', u'RHAT', 100, 35.140000000000001)
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	87 (u'2006-03-28', u'BUY', u'IBM', 1000, 45.0)
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	88 (u'2006-04-06', u'SELL', u'IBM', 500, 53.0)
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	89 (u'2006-04-05', u'BUY', u'MSOFT', 1000, 72.0)
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	90 >>>
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	91
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	92
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	93 .. seealso::
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	94
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	95 http://www.pysqlite.org
54223 6e40add3ee02 #6055: refer to "sqlite3" consistently. Georg Brandl <georg@python.org> parents: 51745 diff changeset	96 The pysqlite web page -- sqlite3 is developed externally under the name
6e40add3ee02 #6055: refer to "sqlite3" consistently. Georg Brandl <georg@python.org> parents: 51745 diff changeset	97 "pysqlite".
42604 9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	98
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	99 http://www.sqlite.org
54223 6e40add3ee02 #6055: refer to "sqlite3" consistently. Georg Brandl <georg@python.org> parents: 51745 diff changeset	100 The SQLite web page; the documentation describes the syntax and the
6e40add3ee02 #6055: refer to "sqlite3" consistently. Georg Brandl <georg@python.org> parents: 51745 diff changeset	101 available data types for the supported SQL dialect.
42604 9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	102
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	103 :pep:`249` - Database API Specification 2.0
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	104 PEP written by Marc-André Lemburg.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	105
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	106
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	107 .. _sqlite3-module-contents:
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	108
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	109 Module functions and constants
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	110 ------------------------------
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	111
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	112
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	113 .. data:: PARSE_DECLTYPES
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	114
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	115 This constant is meant to be used with the detect_types parameter of the
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	116 :func:`connect` function.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	117
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	118 Setting it makes the :mod:`sqlite3` module parse the declared type for each
46659 28b099ac4cd8 Implemented feature request 2157: Converter names are cut off at '(' Gerhard Häring <gh@ghaering.de> parents: 46198 diff changeset	119 column it returns. It will parse out the first word of the declared type,
28b099ac4cd8 Implemented feature request 2157: Converter names are cut off at '(' Gerhard Häring <gh@ghaering.de> parents: 46198 diff changeset	120 i. e. for "integer primary key", it will parse out "integer", or for
28b099ac4cd8 Implemented feature request 2157: Converter names are cut off at '(' Gerhard Häring <gh@ghaering.de> parents: 46198 diff changeset	121 "number(10)" it will parse out "number". Then for that column, it will look
28b099ac4cd8 Implemented feature request 2157: Converter names are cut off at '(' Gerhard Häring <gh@ghaering.de> parents: 46198 diff changeset	122 into the converters dictionary and use the converter function registered for
28b099ac4cd8 Implemented feature request 2157: Converter names are cut off at '(' Gerhard Häring <gh@ghaering.de> parents: 46198 diff changeset	123 that type there.
42604 9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	124
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	125
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	126 .. data:: PARSE_COLNAMES
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	127
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	128 This constant is meant to be used with the detect_types parameter of the
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	129 :func:`connect` function.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	130
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	131 Setting this makes the SQLite interface parse the column name for each column it
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	132 returns. It will look for a string formed [mytype] in there, and then decide
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	133 that 'mytype' is the type of the column. It will try to find an entry of
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	134 'mytype' in the converters dictionary and then use the converter function found
49473 4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	135 there to return the value. The column name found in :attr:`Cursor.description`
42604 9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	136 is only the first word of the column name, i. e. if you use something like
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	137 ``'as "x [datetime]"'`` in your SQL, then we will parse out everything until the
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	138 first blank for the column name: the column name would simply be "x".
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	139
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	140
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	141 .. function:: connect(database[, timeout, isolation_level, detect_types, factory])
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	142
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	143 Opens a connection to the SQLite database file database. You can use
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	144 ``":memory:"`` to open a database connection to a database that resides in RAM
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	145 instead of on disk.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	146
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	147 When a database is accessed by multiple connections, and one of the processes
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	148 modifies the database, the SQLite database is locked until that transaction is
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	149 committed. The timeout parameter specifies how long the connection should wait
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	150 for the lock to go away until raising an exception. The default for the timeout
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	151 parameter is 5.0 (five seconds).
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	152
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	153 For the isolation_level parameter, please see the
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	154 :attr:`Connection.isolation_level` property of :class:`Connection` objects.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	155
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	156 SQLite natively supports only the types TEXT, INTEGER, FLOAT, BLOB and NULL. If
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	157 you want to use other types you must add support for them yourself. The
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	158 detect_types parameter and the using custom converters registered with the
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	159 module-level :func:`register_converter` function allow you to easily do that.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	160
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	161 detect_types defaults to 0 (i. e. off, no type detection), you can set it to
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	162 any combination of :const:`PARSE_DECLTYPES` and :const:`PARSE_COLNAMES` to turn
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	163 type detection on.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	164
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	165 By default, the :mod:`sqlite3` module uses its :class:`Connection` class for the
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	166 connect call. You can, however, subclass the :class:`Connection` class and make
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	167 :func:`connect` use your class instead by providing your class for the factory
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	168 parameter.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	169
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	170 Consult the section :ref:`sqlite3-types` of this manual for details.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	171
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	172 The :mod:`sqlite3` module internally uses a statement cache to avoid SQL parsing
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	173 overhead. If you want to explicitly set the number of statements that are cached
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	174 for the connection, you can set the cached_statements parameter. The currently
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	175 implemented default is to cache 100 statements.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	176
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	177
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	178 .. function:: register_converter(typename, callable)
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	179
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	180 Registers a callable to convert a bytestring from the database into a custom
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	181 Python type. The callable will be invoked for all database values that are of
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	182 the type typename. Confer the parameter detect_types of the :func:`connect`
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	183 function for how the type detection works. Note that the case of typename and
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	184 the name of the type in your query must match!
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	185
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	186
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	187 .. function:: register_adapter(type, callable)
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	188
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	189 Registers a callable to convert the custom Python type type into one of
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	190 SQLite's supported types. The callable callable accepts as single parameter
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	191 the Python value, and must return a value of the following types: int, long,
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	192 float, str (UTF-8 encoded), unicode or buffer.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	193
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	194
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	195 .. function:: complete_statement(sql)
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	196
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	197 Returns :const:`True` if the string sql contains one or more complete SQL
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	198 statements terminated by semicolons. It does not verify that the SQL is
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	199 syntactically correct, only that there are no unclosed string literals and the
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	200 statement is terminated by a semicolon.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	201
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	202 This can be used to build a shell for SQLite, as in the following example:
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	203
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	204
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	205 .. literalinclude:: ../includes/sqlite3/complete_statement.py
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	206
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	207
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	208 .. function:: enable_callback_tracebacks(flag)
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	209
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	210 By default you will not get any tracebacks in user-defined functions,
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	211 aggregates, converters, authorizer callbacks etc. If you want to debug them, you
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	212 can call this function with flag as True. Afterwards, you will get tracebacks
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	213 from callbacks on ``sys.stderr``. Use :const:`False` to disable the feature
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	214 again.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	215
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	216
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	217 .. _sqlite3-connection-objects:
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	218
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	219 Connection Objects
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	220 ------------------
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	221
49473 4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	222 .. class:: Connection
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	223
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	224 A SQLite database connection has the following attributes and methods:
42604 9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	225
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	226 .. attribute:: Connection.isolation_level
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	227
49860 85b1de855a13 fix typo in sqlite3 docs Benjamin Peterson <benjamin@python.org> parents: 49656 diff changeset	228 Get or set the current isolation level. :const:`None` for autocommit mode or
85b1de855a13 fix typo in sqlite3 docs Benjamin Peterson <benjamin@python.org> parents: 49656 diff changeset	229 one of "DEFERRED", "IMMEDIATE" or "EXCLUSIVE". See section
42604 9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	230 :ref:`sqlite3-controlling-transactions` for a more detailed explanation.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	231
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	232
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	233 .. method:: Connection.cursor([cursorClass])
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	234
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	235 The cursor method accepts a single optional parameter cursorClass. If
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	236 supplied, this must be a custom cursor class that extends
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	237 :class:`sqlite3.Cursor`.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	238
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	239
46180 0ea5c055ab54 Brought documentation for sqlite3 module up-to-date. Fixed Issue1625205 which Gerhard Häring <gh@ghaering.de> parents: 46159 diff changeset	240 .. method:: Connection.commit()
0ea5c055ab54 Brought documentation for sqlite3 module up-to-date. Fixed Issue1625205 which Gerhard Häring <gh@ghaering.de> parents: 46159 diff changeset	241
0ea5c055ab54 Brought documentation for sqlite3 module up-to-date. Fixed Issue1625205 which Gerhard Häring <gh@ghaering.de> parents: 46159 diff changeset	242 This method commits the current transaction. If you don't call this method,
49473 4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	243 anything you did since the last call to ``commit()`` is not visible from from
46180 0ea5c055ab54 Brought documentation for sqlite3 module up-to-date. Fixed Issue1625205 which Gerhard Häring <gh@ghaering.de> parents: 46159 diff changeset	244 other database connections. If you wonder why you don't see the data you've
0ea5c055ab54 Brought documentation for sqlite3 module up-to-date. Fixed Issue1625205 which Gerhard Häring <gh@ghaering.de> parents: 46159 diff changeset	245 written to the database, please check you didn't forget to call this method.
0ea5c055ab54 Brought documentation for sqlite3 module up-to-date. Fixed Issue1625205 which Gerhard Häring <gh@ghaering.de> parents: 46159 diff changeset	246
0ea5c055ab54 Brought documentation for sqlite3 module up-to-date. Fixed Issue1625205 which Gerhard Häring <gh@ghaering.de> parents: 46159 diff changeset	247 .. method:: Connection.rollback()
0ea5c055ab54 Brought documentation for sqlite3 module up-to-date. Fixed Issue1625205 which Gerhard Häring <gh@ghaering.de> parents: 46159 diff changeset	248
50508 c52177f36852 Remove trailing whitespace. Georg Brandl <georg@python.org> parents: 49860 diff changeset	249 This method rolls back any changes to the database since the last call to
46180 0ea5c055ab54 Brought documentation for sqlite3 module up-to-date. Fixed Issue1625205 which Gerhard Häring <gh@ghaering.de> parents: 46159 diff changeset	250 :meth:`commit`.
0ea5c055ab54 Brought documentation for sqlite3 module up-to-date. Fixed Issue1625205 which Gerhard Häring <gh@ghaering.de> parents: 46159 diff changeset	251
0ea5c055ab54 Brought documentation for sqlite3 module up-to-date. Fixed Issue1625205 which Gerhard Häring <gh@ghaering.de> parents: 46159 diff changeset	252 .. method:: Connection.close()
0ea5c055ab54 Brought documentation for sqlite3 module up-to-date. Fixed Issue1625205 which Gerhard Häring <gh@ghaering.de> parents: 46159 diff changeset	253
0ea5c055ab54 Brought documentation for sqlite3 module up-to-date. Fixed Issue1625205 which Gerhard Häring <gh@ghaering.de> parents: 46159 diff changeset	254 This closes the database connection. Note that this does not automatically
0ea5c055ab54 Brought documentation for sqlite3 module up-to-date. Fixed Issue1625205 which Gerhard Häring <gh@ghaering.de> parents: 46159 diff changeset	255 call :meth:`commit`. If you just close your database connection without
0ea5c055ab54 Brought documentation for sqlite3 module up-to-date. Fixed Issue1625205 which Gerhard Häring <gh@ghaering.de> parents: 46159 diff changeset	256 calling :meth:`commit` first, your changes will be lost!
0ea5c055ab54 Brought documentation for sqlite3 module up-to-date. Fixed Issue1625205 which Gerhard Häring <gh@ghaering.de> parents: 46159 diff changeset	257
42604 9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	258 .. method:: Connection.execute(sql, [parameters])
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	259
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	260 This is a nonstandard shortcut that creates an intermediate cursor object by
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	261 calling the cursor method, then calls the cursor's :meth:`execute` method with
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	262 the parameters given.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	263
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	264
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	265 .. method:: Connection.executemany(sql, [parameters])
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	266
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	267 This is a nonstandard shortcut that creates an intermediate cursor object by
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	268 calling the cursor method, then calls the cursor's :meth:`executemany` method
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	269 with the parameters given.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	270
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	271 .. method:: Connection.executescript(sql_script)
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	272
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	273 This is a nonstandard shortcut that creates an intermediate cursor object by
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	274 calling the cursor method, then calls the cursor's :meth:`executescript` method
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	275 with the parameters given.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	276
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	277
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	278 .. method:: Connection.create_function(name, num_params, func)
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	279
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	280 Creates a user-defined function that you can later use from within SQL
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	281 statements under the function name name. num_params is the number of
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	282 parameters the function accepts, and func is a Python callable that is called
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	283 as the SQL function.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	284
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	285 The function can return any of the types supported by SQLite: unicode, str, int,
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	286 long, float, buffer and None.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	287
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	288 Example:
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	289
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	290 .. literalinclude:: ../includes/sqlite3/md5func.py
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	291
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	292
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	293 .. method:: Connection.create_aggregate(name, num_params, aggregate_class)
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	294
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	295 Creates a user-defined aggregate function.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	296
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	297 The aggregate class must implement a ``step`` method, which accepts the number
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	298 of parameters num_params, and a ``finalize`` method which will return the
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	299 final result of the aggregate.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	300
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	301 The ``finalize`` method can return any of the types supported by SQLite:
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	302 unicode, str, int, long, float, buffer and None.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	303
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	304 Example:
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	305
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	306 .. literalinclude:: ../includes/sqlite3/mysumaggr.py
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	307
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	308
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	309 .. method:: Connection.create_collation(name, callable)
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	310
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	311 Creates a collation with the specified name and callable. The callable will
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	312 be passed two string arguments. It should return -1 if the first is ordered
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	313 lower than the second, 0 if they are ordered equal and 1 if the first is ordered
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	314 higher than the second. Note that this controls sorting (ORDER BY in SQL) so
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	315 your comparisons don't affect other SQL operations.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	316
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	317 Note that the callable will get its parameters as Python bytestrings, which will
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	318 normally be encoded in UTF-8.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	319
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	320 The following example shows a custom collation that sorts "the wrong way":
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	321
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	322 .. literalinclude:: ../includes/sqlite3/collation_reverse.py
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	323
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	324 To remove a collation, call ``create_collation`` with None as callable::
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	325
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	326 con.create_collation("reverse", None)
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	327
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	328
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	329 .. method:: Connection.interrupt()
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	330
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	331 You can call this method from a different thread to abort any queries that might
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	332 be executing on the connection. The query will then abort and the caller will
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	333 get an exception.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	334
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	335
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	336 .. method:: Connection.set_authorizer(authorizer_callback)
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	337
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	338 This routine registers a callback. The callback is invoked for each attempt to
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	339 access a column of a table in the database. The callback should return
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	340 :const:`SQLITE_OK` if access is allowed, :const:`SQLITE_DENY` if the entire SQL
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	341 statement should be aborted with an error and :const:`SQLITE_IGNORE` if the
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	342 column should be treated as a NULL value. These constants are available in the
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	343 :mod:`sqlite3` module.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	344
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	345 The first argument to the callback signifies what kind of operation is to be
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	346 authorized. The second and third argument will be arguments or :const:`None`
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	347 depending on the first argument. The 4th argument is the name of the database
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	348 ("main", "temp", etc.) if applicable. The 5th argument is the name of the
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	349 inner-most trigger or view that is responsible for the access attempt or
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	350 :const:`None` if this access attempt is directly from input SQL code.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	351
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	352 Please consult the SQLite documentation about the possible values for the first
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	353 argument and the meaning of the second and third argument depending on the first
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	354 one. All necessary constants are available in the :mod:`sqlite3` module.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	355
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	356
46180 0ea5c055ab54 Brought documentation for sqlite3 module up-to-date. Fixed Issue1625205 which Gerhard Häring <gh@ghaering.de> parents: 46159 diff changeset	357 .. method:: Connection.set_progress_handler(handler, n)
0ea5c055ab54 Brought documentation for sqlite3 module up-to-date. Fixed Issue1625205 which Gerhard Häring <gh@ghaering.de> parents: 46159 diff changeset	358
0ea5c055ab54 Brought documentation for sqlite3 module up-to-date. Fixed Issue1625205 which Gerhard Häring <gh@ghaering.de> parents: 46159 diff changeset	359 .. versionadded:: 2.6
0ea5c055ab54 Brought documentation for sqlite3 module up-to-date. Fixed Issue1625205 which Gerhard Häring <gh@ghaering.de> parents: 46159 diff changeset	360
0ea5c055ab54 Brought documentation for sqlite3 module up-to-date. Fixed Issue1625205 which Gerhard Häring <gh@ghaering.de> parents: 46159 diff changeset	361 This routine registers a callback. The callback is invoked for every n
0ea5c055ab54 Brought documentation for sqlite3 module up-to-date. Fixed Issue1625205 which Gerhard Häring <gh@ghaering.de> parents: 46159 diff changeset	362 instructions of the SQLite virtual machine. This is useful if you want to
0ea5c055ab54 Brought documentation for sqlite3 module up-to-date. Fixed Issue1625205 which Gerhard Häring <gh@ghaering.de> parents: 46159 diff changeset	363 get called from SQLite during long-running operations, for example to update
0ea5c055ab54 Brought documentation for sqlite3 module up-to-date. Fixed Issue1625205 which Gerhard Häring <gh@ghaering.de> parents: 46159 diff changeset	364 a GUI.
0ea5c055ab54 Brought documentation for sqlite3 module up-to-date. Fixed Issue1625205 which Gerhard Häring <gh@ghaering.de> parents: 46159 diff changeset	365
0ea5c055ab54 Brought documentation for sqlite3 module up-to-date. Fixed Issue1625205 which Gerhard Häring <gh@ghaering.de> parents: 46159 diff changeset	366 If you want to clear any previously installed progress handler, call the
0ea5c055ab54 Brought documentation for sqlite3 module up-to-date. Fixed Issue1625205 which Gerhard Häring <gh@ghaering.de> parents: 46159 diff changeset	367 method with :const:`None` for handler.
0ea5c055ab54 Brought documentation for sqlite3 module up-to-date. Fixed Issue1625205 which Gerhard Häring <gh@ghaering.de> parents: 46159 diff changeset	368
0ea5c055ab54 Brought documentation for sqlite3 module up-to-date. Fixed Issue1625205 which Gerhard Häring <gh@ghaering.de> parents: 46159 diff changeset	369
42604 9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	370 .. attribute:: Connection.row_factory
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	371
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	372 You can change this attribute to a callable that accepts the cursor and the
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	373 original row as a tuple and will return the real result row. This way, you can
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	374 implement more advanced ways of returning results, such as returning an object
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	375 that can also access columns by name.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	376
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	377 Example:
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	378
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	379 .. literalinclude:: ../includes/sqlite3/row_factory.py
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	380
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	381 If returning a tuple doesn't suffice and you want name-based access to
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	382 columns, you should consider setting :attr:`row_factory` to the
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	383 highly-optimized :class:`sqlite3.Row` type. :class:`Row` provides both
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	384 index-based and case-insensitive name-based access to columns with almost no
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	385 memory overhead. It will probably be better than your own custom
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	386 dictionary-based approach or even a db_row based solution.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	387
44383 3625234bf3d2 Some cleanup in the docs. Georg Brandl <georg@python.org> parents: 43631 diff changeset	388 .. XXX what's a db_row-based solution?
42604 9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	389
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	390
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	391 .. attribute:: Connection.text_factory
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	392
49473 4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	393 Using this attribute you can control what objects are returned for the ``TEXT``
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	394 data type. By default, this attribute is set to :class:`unicode` and the
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	395 :mod:`sqlite3` module will return Unicode objects for ``TEXT``. If you want to
42604 9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	396 return bytestrings instead, you can set it to :class:`str`.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	397
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	398 For efficiency reasons, there's also a way to return Unicode objects only for
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	399 non-ASCII data, and bytestrings otherwise. To activate it, set this attribute to
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	400 :const:`sqlite3.OptimizedUnicode`.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	401
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	402 You can also set it to any other callable that accepts a single bytestring
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	403 parameter and returns the resulting object.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	404
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	405 See the following example code for illustration:
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	406
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	407 .. literalinclude:: ../includes/sqlite3/text_factory.py
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	408
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	409
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	410 .. attribute:: Connection.total_changes
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	411
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	412 Returns the total number of database rows that have been modified, inserted, or
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	413 deleted since the database connection was opened.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	414
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	415
46159 f9a629f41656 Accept patch issue2426 by Paul Kippes (kippesp). Gregory P. Smith <greg@mad-scientist.com> parents: 44791 diff changeset	416 .. attribute:: Connection.iterdump
f9a629f41656 Accept patch issue2426 by Paul Kippes (kippesp). Gregory P. Smith <greg@mad-scientist.com> parents: 44791 diff changeset	417
f9a629f41656 Accept patch issue2426 by Paul Kippes (kippesp). Gregory P. Smith <greg@mad-scientist.com> parents: 44791 diff changeset	418 Returns an iterator to dump the database in an SQL text format. Useful when
f9a629f41656 Accept patch issue2426 by Paul Kippes (kippesp). Gregory P. Smith <greg@mad-scientist.com> parents: 44791 diff changeset	419 saving an in-memory database for later restoration. This function provides
f9a629f41656 Accept patch issue2426 by Paul Kippes (kippesp). Gregory P. Smith <greg@mad-scientist.com> parents: 44791 diff changeset	420 the same capabilities as the :kbd:`.dump` command in the :program:`sqlite3`
f9a629f41656 Accept patch issue2426 by Paul Kippes (kippesp). Gregory P. Smith <greg@mad-scientist.com> parents: 44791 diff changeset	421 shell.
f9a629f41656 Accept patch issue2426 by Paul Kippes (kippesp). Gregory P. Smith <greg@mad-scientist.com> parents: 44791 diff changeset	422
f9a629f41656 Accept patch issue2426 by Paul Kippes (kippesp). Gregory P. Smith <greg@mad-scientist.com> parents: 44791 diff changeset	423 .. versionadded:: 2.6
f9a629f41656 Accept patch issue2426 by Paul Kippes (kippesp). Gregory P. Smith <greg@mad-scientist.com> parents: 44791 diff changeset	424
f9a629f41656 Accept patch issue2426 by Paul Kippes (kippesp). Gregory P. Smith <greg@mad-scientist.com> parents: 44791 diff changeset	425 Example::
f9a629f41656 Accept patch issue2426 by Paul Kippes (kippesp). Gregory P. Smith <greg@mad-scientist.com> parents: 44791 diff changeset	426
f9a629f41656 Accept patch issue2426 by Paul Kippes (kippesp). Gregory P. Smith <greg@mad-scientist.com> parents: 44791 diff changeset	427 # Convert file existing_db.db to SQL dump file dump.sql
51745 9815da43a1ce revert r69777 since all the experts agree that extra import lines distract from the code Benjamin Peterson <benjamin@python.org> parents: 51742 diff changeset	428 import sqlite3, os
46159 f9a629f41656 Accept patch issue2426 by Paul Kippes (kippesp). Gregory P. Smith <greg@mad-scientist.com> parents: 44791 diff changeset	429
f9a629f41656 Accept patch issue2426 by Paul Kippes (kippesp). Gregory P. Smith <greg@mad-scientist.com> parents: 44791 diff changeset	430 con = sqlite3.connect('existing_db.db')
49656 79c9ab6498fd #4267: small fixes in sqlite3 docs. Georg Brandl <georg@python.org> parents: 49473 diff changeset	431 with open('dump.sql', 'w') as f:
79c9ab6498fd #4267: small fixes in sqlite3 docs. Georg Brandl <georg@python.org> parents: 49473 diff changeset	432 for line in con.iterdump():
79c9ab6498fd #4267: small fixes in sqlite3 docs. Georg Brandl <georg@python.org> parents: 49473 diff changeset	433 f.write('%s\n' % line)
46159 f9a629f41656 Accept patch issue2426 by Paul Kippes (kippesp). Gregory P. Smith <greg@mad-scientist.com> parents: 44791 diff changeset	434
f9a629f41656 Accept patch issue2426 by Paul Kippes (kippesp). Gregory P. Smith <greg@mad-scientist.com> parents: 44791 diff changeset	435
42604 9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	436 .. _sqlite3-cursor-objects:
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	437
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	438 Cursor Objects
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	439 --------------
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	440
49473 4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	441 .. class:: Cursor
42604 9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	442
49473 4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	443 A SQLite database cursor has the following attributes and methods:
42604 9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	444
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	445 .. method:: Cursor.execute(sql, [parameters])
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	446
44791 15536865198a #1509: fix sqlite3 docstrings and docs w.r.t. cursor.fetchXXX methods. Georg Brandl <georg@python.org> parents: 44383 diff changeset	447 Executes an SQL statement. The SQL statement may be parametrized (i. e.
42604 9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	448 placeholders instead of SQL literals). The :mod:`sqlite3` module supports two
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	449 kinds of placeholders: question marks (qmark style) and named placeholders
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	450 (named style).
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	451
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	452 This example shows how to use parameters with qmark style:
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	453
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	454 .. literalinclude:: ../includes/sqlite3/execute_1.py
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	455
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	456 This example shows how to use the named style:
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	457
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	458 .. literalinclude:: ../includes/sqlite3/execute_2.py
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	459
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	460 :meth:`execute` will only execute a single SQL statement. If you try to execute
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	461 more than one statement with it, it will raise a Warning. Use
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	462 :meth:`executescript` if you want to execute multiple SQL statements with one
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	463 call.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	464
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	465
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	466 .. method:: Cursor.executemany(sql, seq_of_parameters)
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	467
44791 15536865198a #1509: fix sqlite3 docstrings and docs w.r.t. cursor.fetchXXX methods. Georg Brandl <georg@python.org> parents: 44383 diff changeset	468 Executes an SQL command against all parameter sequences or mappings found in
43630 6df985744d96 Add :term:s for iterator. Georg Brandl <georg@python.org> parents: 43629 diff changeset	469 the sequence sql. The :mod:`sqlite3` module also allows using an
6df985744d96 Add :term:s for iterator. Georg Brandl <georg@python.org> parents: 43629 diff changeset	470 :term:`iterator` yielding parameters instead of a sequence.
42604 9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	471
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	472 .. literalinclude:: ../includes/sqlite3/executemany_1.py
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	473
43629 4f662ba09f1f Add :term: for generators. Georg Brandl <georg@python.org> parents: 42775 diff changeset	474 Here's a shorter example using a :term:`generator`:
42604 9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	475
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	476 .. literalinclude:: ../includes/sqlite3/executemany_2.py
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	477
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	478
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	479 .. method:: Cursor.executescript(sql_script)
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	480
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	481 This is a nonstandard convenience method for executing multiple SQL statements
49473 4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	482 at once. It issues a ``COMMIT`` statement first, then executes the SQL script it
42604 9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	483 gets as a parameter.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	484
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	485 sql_script can be a bytestring or a Unicode string.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	486
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	487 Example:
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	488
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	489 .. literalinclude:: ../includes/sqlite3/executescript.py
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	490
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	491
50508 c52177f36852 Remove trailing whitespace. Georg Brandl <georg@python.org> parents: 49860 diff changeset	492 .. method:: Cursor.fetchone()
c52177f36852 Remove trailing whitespace. Georg Brandl <georg@python.org> parents: 49860 diff changeset	493
44791 15536865198a #1509: fix sqlite3 docstrings and docs w.r.t. cursor.fetchXXX methods. Georg Brandl <georg@python.org> parents: 44383 diff changeset	494 Fetches the next row of a query result set, returning a single sequence,
49473 4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	495 or :const:`None` when no more data is available.
44791 15536865198a #1509: fix sqlite3 docstrings and docs w.r.t. cursor.fetchXXX methods. Georg Brandl <georg@python.org> parents: 44383 diff changeset	496
15536865198a #1509: fix sqlite3 docstrings and docs w.r.t. cursor.fetchXXX methods. Georg Brandl <georg@python.org> parents: 44383 diff changeset	497
15536865198a #1509: fix sqlite3 docstrings and docs w.r.t. cursor.fetchXXX methods. Georg Brandl <georg@python.org> parents: 44383 diff changeset	498 .. method:: Cursor.fetchmany([size=cursor.arraysize])
50508 c52177f36852 Remove trailing whitespace. Georg Brandl <georg@python.org> parents: 49860 diff changeset	499
44791 15536865198a #1509: fix sqlite3 docstrings and docs w.r.t. cursor.fetchXXX methods. Georg Brandl <georg@python.org> parents: 44383 diff changeset	500 Fetches the next set of rows of a query result, returning a list. An empty
15536865198a #1509: fix sqlite3 docstrings and docs w.r.t. cursor.fetchXXX methods. Georg Brandl <georg@python.org> parents: 44383 diff changeset	501 list is returned when no more rows are available.
50508 c52177f36852 Remove trailing whitespace. Georg Brandl <georg@python.org> parents: 49860 diff changeset	502
44791 15536865198a #1509: fix sqlite3 docstrings and docs w.r.t. cursor.fetchXXX methods. Georg Brandl <georg@python.org> parents: 44383 diff changeset	503 The number of rows to fetch per call is specified by the size parameter.
15536865198a #1509: fix sqlite3 docstrings and docs w.r.t. cursor.fetchXXX methods. Georg Brandl <georg@python.org> parents: 44383 diff changeset	504 If it is not given, the cursor's arraysize determines the number of rows
15536865198a #1509: fix sqlite3 docstrings and docs w.r.t. cursor.fetchXXX methods. Georg Brandl <georg@python.org> parents: 44383 diff changeset	505 to be fetched. The method should try to fetch as many rows as indicated by
15536865198a #1509: fix sqlite3 docstrings and docs w.r.t. cursor.fetchXXX methods. Georg Brandl <georg@python.org> parents: 44383 diff changeset	506 the size parameter. If this is not possible due to the specified number of
15536865198a #1509: fix sqlite3 docstrings and docs w.r.t. cursor.fetchXXX methods. Georg Brandl <georg@python.org> parents: 44383 diff changeset	507 rows not being available, fewer rows may be returned.
50508 c52177f36852 Remove trailing whitespace. Georg Brandl <georg@python.org> parents: 49860 diff changeset	508
44791 15536865198a #1509: fix sqlite3 docstrings and docs w.r.t. cursor.fetchXXX methods. Georg Brandl <georg@python.org> parents: 44383 diff changeset	509 Note there are performance considerations involved with the size parameter.
15536865198a #1509: fix sqlite3 docstrings and docs w.r.t. cursor.fetchXXX methods. Georg Brandl <georg@python.org> parents: 44383 diff changeset	510 For optimal performance, it is usually best to use the arraysize attribute.
15536865198a #1509: fix sqlite3 docstrings and docs w.r.t. cursor.fetchXXX methods. Georg Brandl <georg@python.org> parents: 44383 diff changeset	511 If the size parameter is used, then it is best for it to retain the same
15536865198a #1509: fix sqlite3 docstrings and docs w.r.t. cursor.fetchXXX methods. Georg Brandl <georg@python.org> parents: 44383 diff changeset	512 value from one :meth:`fetchmany` call to the next.
50508 c52177f36852 Remove trailing whitespace. Georg Brandl <georg@python.org> parents: 49860 diff changeset	513
c52177f36852 Remove trailing whitespace. Georg Brandl <georg@python.org> parents: 49860 diff changeset	514 .. method:: Cursor.fetchall()
44791 15536865198a #1509: fix sqlite3 docstrings and docs w.r.t. cursor.fetchXXX methods. Georg Brandl <georg@python.org> parents: 44383 diff changeset	515
15536865198a #1509: fix sqlite3 docstrings and docs w.r.t. cursor.fetchXXX methods. Georg Brandl <georg@python.org> parents: 44383 diff changeset	516 Fetches all (remaining) rows of a query result, returning a list. Note that
15536865198a #1509: fix sqlite3 docstrings and docs w.r.t. cursor.fetchXXX methods. Georg Brandl <georg@python.org> parents: 44383 diff changeset	517 the cursor's arraysize attribute can affect the performance of this operation.
15536865198a #1509: fix sqlite3 docstrings and docs w.r.t. cursor.fetchXXX methods. Georg Brandl <georg@python.org> parents: 44383 diff changeset	518 An empty list is returned when no rows are available.
15536865198a #1509: fix sqlite3 docstrings and docs w.r.t. cursor.fetchXXX methods. Georg Brandl <georg@python.org> parents: 44383 diff changeset	519
15536865198a #1509: fix sqlite3 docstrings and docs w.r.t. cursor.fetchXXX methods. Georg Brandl <georg@python.org> parents: 44383 diff changeset	520
42604 9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	521 .. attribute:: Cursor.rowcount
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	522
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	523 Although the :class:`Cursor` class of the :mod:`sqlite3` module implements this
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	524 attribute, the database engine's own support for the determination of "rows
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	525 affected"/"rows selected" is quirky.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	526
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	527 For ``DELETE`` statements, SQLite reports :attr:`rowcount` as 0 if you make a
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	528 ``DELETE FROM table`` without any condition.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	529
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	530 For :meth:`executemany` statements, the number of modifications are summed up
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	531 into :attr:`rowcount`.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	532
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	533 As required by the Python DB API Spec, the :attr:`rowcount` attribute "is -1 in
49473 4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	534 case no ``executeXX()`` has been performed on the cursor or the rowcount of the
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	535 last operation is not determinable by the interface".
42604 9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	536
42775 d7517c306791 Merged revisions 57221-57391 via svnmerge from Guido van Rossum <guido@python.org> parents: 42604 diff changeset	537 This includes ``SELECT`` statements because we cannot determine the number of
d7517c306791 Merged revisions 57221-57391 via svnmerge from Guido van Rossum <guido@python.org> parents: 42604 diff changeset	538 rows a query produced until all rows were fetched.
d7517c306791 Merged revisions 57221-57391 via svnmerge from Guido van Rossum <guido@python.org> parents: 42604 diff changeset	539
46198 e4486c343f7f Documented the lastrowid attribute. Gerhard Häring <gh@ghaering.de> parents: 46180 diff changeset	540 .. attribute:: Cursor.lastrowid
e4486c343f7f Documented the lastrowid attribute. Gerhard Häring <gh@ghaering.de> parents: 46180 diff changeset	541
e4486c343f7f Documented the lastrowid attribute. Gerhard Häring <gh@ghaering.de> parents: 46180 diff changeset	542 This read-only attribute provides the rowid of the last modified row. It is
e4486c343f7f Documented the lastrowid attribute. Gerhard Häring <gh@ghaering.de> parents: 46180 diff changeset	543 only set if you issued a ``INSERT`` statement using the :meth:`execute`
e4486c343f7f Documented the lastrowid attribute. Gerhard Häring <gh@ghaering.de> parents: 46180 diff changeset	544 method. For operations other than ``INSERT`` or when :meth:`executemany` is
e4486c343f7f Documented the lastrowid attribute. Gerhard Häring <gh@ghaering.de> parents: 46180 diff changeset	545 called, :attr:`lastrowid` is set to :const:`None`.
42604 9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	546
49473 4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	547 .. attribute:: Cursor.description
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	548
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	549 This read-only attribute provides the column names of the last query. To
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	550 remain compatible with the Python DB API, it returns a 7-tuple for each
50508 c52177f36852 Remove trailing whitespace. Georg Brandl <georg@python.org> parents: 49860 diff changeset	551 column where the last six items of each tuple are :const:`None`.
c52177f36852 Remove trailing whitespace. Georg Brandl <georg@python.org> parents: 49860 diff changeset	552
49473 4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	553 It is set for ``SELECT`` statements without any matching rows as well.
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	554
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	555 .. _sqlite3-row-objects:
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	556
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	557 Row Objects
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	558 -----------
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	559
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	560 .. class:: Row
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	561
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	562 A :class:`Row` instance serves as a highly optimized
50508 c52177f36852 Remove trailing whitespace. Georg Brandl <georg@python.org> parents: 49860 diff changeset	563 :attr:`~Connection.row_factory` for :class:`Connection` objects.
49473 4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	564 It tries to mimic a tuple in most of its features.
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	565
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	566 It supports mapping access by column name and index, iteration,
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	567 representation, equality testing and :func:`len`.
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	568
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	569 If two :class:`Row` objects have exactly the same columns and their
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	570 members are equal, they compare equal.
50508 c52177f36852 Remove trailing whitespace. Georg Brandl <georg@python.org> parents: 49860 diff changeset	571
49473 4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	572 .. versionchanged:: 2.6
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	573 Added iteration and equality (hashability).
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	574
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	575 .. method:: keys
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	576
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	577 This method returns a tuple of column names. Immediately after a query,
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	578 it is the first member of each tuple in :attr:`Cursor.description`.
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	579
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	580 .. versionadded:: 2.6
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	581
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	582 Let's assume we initialize a table as in the example given above::
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	583
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	584 conn = sqlite3.connect(":memory:")
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	585 c = conn.cursor()
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	586 c.execute('''create table stocks
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	587 (date text, trans text, symbol text,
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	588 qty real, price real)''')
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	589 c.execute("""insert into stocks
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	590 values ('2006-01-05','BUY','RHAT',100,35.14)""")
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	591 conn.commit()
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	592 c.close()
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	593
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	594 Now we plug :class:`Row` in::
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	595
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	596 >>> conn.row_factory = sqlite3.Row
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	597 >>> c = conn.cursor()
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	598 >>> c.execute('select * from stocks')
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	599 <sqlite3.Cursor object at 0x7f4e7dd8fa80>
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	600 >>> r = c.fetchone()
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	601 >>> type(r)
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	602 <type 'sqlite3.Row'>
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	603 >>> r
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	604 (u'2006-01-05', u'BUY', u'RHAT', 100.0, 35.140000000000001)
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	605 >>> len(r)
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	606 5
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	607 >>> r[2]
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	608 u'RHAT'
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	609 >>> r.keys()
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	610 ['date', 'trans', 'symbol', 'qty', 'price']
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	611 >>> r['qty']
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	612 100.0
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	613 >>> for member in r: print member
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	614 ...
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	615 2006-01-05
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	616 BUY
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	617 RHAT
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	618 100.0
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	619 35.14
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	620
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	621
42604 9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	622 .. _sqlite3-types:
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	623
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	624 SQLite and Python types
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	625 -----------------------
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	626
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	627
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	628 Introduction
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	629 ^^^^^^^^^^^^
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	630
49473 4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	631 SQLite natively supports the following types: ``NULL``, ``INTEGER``,
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	632 ``REAL``, ``TEXT``, ``BLOB``.
42604 9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	633
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	634 The following Python types can thus be sent to SQLite without any problem:
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	635
49473 4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	636 +-----------------------------+-------------+
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	637 \| Python type \| SQLite type \|
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	638 +=============================+=============+
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	639 \| :const:`None` \| ``NULL`` \|
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	640 +-----------------------------+-------------+
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	641 \| :class:`int` \| ``INTEGER`` \|
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	642 +-----------------------------+-------------+
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	643 \| :class:`long` \| ``INTEGER`` \|
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	644 +-----------------------------+-------------+
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	645 \| :class:`float` \| ``REAL`` \|
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	646 +-----------------------------+-------------+
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	647 \| :class:`str` (UTF8-encoded) \| ``TEXT`` \|
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	648 +-----------------------------+-------------+
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	649 \| :class:`unicode` \| ``TEXT`` \|
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	650 +-----------------------------+-------------+
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	651 \| :class:`buffer` \| ``BLOB`` \|
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	652 +-----------------------------+-------------+
42604 9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	653
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	654 This is how SQLite types are converted to Python types by default:
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	655
49473 4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	656 +-------------+----------------------------------------------+
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	657 \| SQLite type \| Python type \|
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	658 +=============+==============================================+
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	659 \| ``NULL`` \| :const:`None` \|
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	660 +-------------+----------------------------------------------+
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	661 \| ``INTEGER`` \| :class:`int` or :class:`long`, \|
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	662 \| \| depending on size \|
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	663 +-------------+----------------------------------------------+
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	664 \| ``REAL`` \| :class:`float` \|
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	665 +-------------+----------------------------------------------+
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	666 \| ``TEXT`` \| depends on :attr:`~Connection.text_factory`, \|
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	667 \| \| :class:`unicode` by default \|
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	668 +-------------+----------------------------------------------+
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	669 \| ``BLOB`` \| :class:`buffer` \|
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	670 +-------------+----------------------------------------------+
42604 9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	671
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	672 The type system of the :mod:`sqlite3` module is extensible in two ways: you can
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	673 store additional Python types in a SQLite database via object adaptation, and
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	674 you can let the :mod:`sqlite3` module convert SQLite types to different Python
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	675 types via converters.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	676
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	677
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	678 Using adapters to store additional Python types in SQLite databases
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	679 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	680
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	681 As described before, SQLite supports only a limited set of types natively. To
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	682 use other Python types with SQLite, you must adapt them to one of the
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	683 sqlite3 module's supported types for SQLite: one of NoneType, int, long, float,
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	684 str, unicode, buffer.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	685
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	686 The :mod:`sqlite3` module uses Python object adaptation, as described in
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	687 :pep:`246` for this. The protocol to use is :class:`PrepareProtocol`.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	688
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	689 There are two ways to enable the :mod:`sqlite3` module to adapt a custom Python
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	690 type to one of the supported ones.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	691
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	692
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	693 Letting your object adapt itself
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	694 """"""""""""""""""""""""""""""""
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	695
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	696 This is a good approach if you write the class yourself. Let's suppose you have
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	697 a class like this::
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	698
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	699 class Point(object):
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	700 def __init__(self, x, y):
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	701 self.x, self.y = x, y
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	702
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	703 Now you want to store the point in a single SQLite column. First you'll have to
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	704 choose one of the supported types first to be used for representing the point.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	705 Let's just use str and separate the coordinates using a semicolon. Then you need
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	706 to give your class a method ``__conform__(self, protocol)`` which must return
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	707 the converted value. The parameter protocol will be :class:`PrepareProtocol`.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	708
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	709 .. literalinclude:: ../includes/sqlite3/adapter_point_1.py
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	710
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	711
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	712 Registering an adapter callable
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	713 """""""""""""""""""""""""""""""
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	714
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	715 The other possibility is to create a function that converts the type to the
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	716 string representation and register the function with :meth:`register_adapter`.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	717
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	718 .. note::
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	719
43631 03056365a1b6 Add :term:s for "new-style class". Georg Brandl <georg@python.org> parents: 43630 diff changeset	720 The type/class to adapt must be a :term:`new-style class`, i. e. it must have
42604 9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	721 :class:`object` as one of its bases.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	722
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	723 .. literalinclude:: ../includes/sqlite3/adapter_point_2.py
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	724
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	725 The :mod:`sqlite3` module has two default adapters for Python's built-in
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	726 :class:`datetime.date` and :class:`datetime.datetime` types. Now let's suppose
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	727 we want to store :class:`datetime.datetime` objects not in ISO representation,
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	728 but as a Unix timestamp.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	729
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	730 .. literalinclude:: ../includes/sqlite3/adapter_datetime.py
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	731
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	732
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	733 Converting SQLite values to custom Python types
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	734 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	735
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	736 Writing an adapter lets you send custom Python types to SQLite. But to make it
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	737 really useful we need to make the Python to SQLite to Python roundtrip work.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	738
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	739 Enter converters.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	740
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	741 Let's go back to the :class:`Point` class. We stored the x and y coordinates
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	742 separated via semicolons as strings in SQLite.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	743
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	744 First, we'll define a converter function that accepts the string as a parameter
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	745 and constructs a :class:`Point` object from it.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	746
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	747 .. note::
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	748
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	749 Converter functions always get called with a string, no matter under which
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	750 data type you sent the value to SQLite.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	751
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	752 ::
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	753
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	754 def convert_point(s):
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	755 x, y = map(float, s.split(";"))
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	756 return Point(x, y)
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	757
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	758 Now you need to make the :mod:`sqlite3` module know that what you select from
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	759 the database is actually a point. There are two ways of doing this:
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	760
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	761 * Implicitly via the declared type
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	762
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	763 * Explicitly via the column name
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	764
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	765 Both ways are described in section :ref:`sqlite3-module-contents`, in the entries
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	766 for the constants :const:`PARSE_DECLTYPES` and :const:`PARSE_COLNAMES`.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	767
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	768 The following example illustrates both approaches.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	769
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	770 .. literalinclude:: ../includes/sqlite3/converter_point.py
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	771
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	772
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	773 Default adapters and converters
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	774 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	775
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	776 There are default adapters for the date and datetime types in the datetime
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	777 module. They will be sent as ISO dates/ISO timestamps to SQLite.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	778
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	779 The default converters are registered under the name "date" for
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	780 :class:`datetime.date` and under the name "timestamp" for
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	781 :class:`datetime.datetime`.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	782
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	783 This way, you can use date/timestamps from Python without any additional
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	784 fiddling in most cases. The format of the adapters is also compatible with the
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	785 experimental SQLite date/time functions.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	786
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	787 The following example demonstrates this.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	788
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	789 .. literalinclude:: ../includes/sqlite3/pysqlite_datetime.py
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	790
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	791
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	792 .. _sqlite3-controlling-transactions:
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	793
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	794 Controlling Transactions
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	795 ------------------------
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	796
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	797 By default, the :mod:`sqlite3` module opens transactions implicitly before a
50508 c52177f36852 Remove trailing whitespace. Georg Brandl <georg@python.org> parents: 49860 diff changeset	798 Data Modification Language (DML) statement (i.e.
49473 4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	799 ``INSERT``/``UPDATE``/``DELETE``/``REPLACE``), and commits transactions
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	800 implicitly before a non-DML, non-query statement (i. e.
4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	801 anything other than ``SELECT`` or the aforementioned).
42604 9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	802
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	803 So if you are within a transaction and issue a command like ``CREATE TABLE
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	804 ...``, ``VACUUM``, ``PRAGMA``, the :mod:`sqlite3` module will commit implicitly
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	805 before executing that command. There are two reasons for doing that. The first
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	806 is that some of these commands don't work within transactions. The other reason
54223 6e40add3ee02 #6055: refer to "sqlite3" consistently. Georg Brandl <georg@python.org> parents: 51745 diff changeset	807 is that sqlite3 needs to keep track of the transaction state (if a transaction
42604 9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	808 is active or not).
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	809
54223 6e40add3ee02 #6055: refer to "sqlite3" consistently. Georg Brandl <georg@python.org> parents: 51745 diff changeset	810 You can control which kind of ``BEGIN`` statements sqlite3 implicitly executes
42604 9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	811 (or none at all) via the isolation_level parameter to the :func:`connect`
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	812 call, or via the :attr:`isolation_level` property of connections.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	813
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	814 If you want autocommit mode, then set :attr:`isolation_level` to None.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	815
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	816 Otherwise leave it at its default, which will result in a plain "BEGIN"
49656 79c9ab6498fd #4267: small fixes in sqlite3 docs. Georg Brandl <georg@python.org> parents: 49473 diff changeset	817 statement, or set it to one of SQLite's supported isolation levels: "DEFERRED",
79c9ab6498fd #4267: small fixes in sqlite3 docs. Georg Brandl <georg@python.org> parents: 49473 diff changeset	818 "IMMEDIATE" or "EXCLUSIVE".
42604 9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	819
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	820
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	821
54223 6e40add3ee02 #6055: refer to "sqlite3" consistently. Georg Brandl <georg@python.org> parents: 51745 diff changeset	822 Using :mod:`sqlite3` efficiently
6e40add3ee02 #6055: refer to "sqlite3" consistently. Georg Brandl <georg@python.org> parents: 51745 diff changeset	823 --------------------------------
42604 9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	824
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	825
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	826 Using shortcut methods
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	827 ^^^^^^^^^^^^^^^^^^^^^^
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	828
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	829 Using the nonstandard :meth:`execute`, :meth:`executemany` and
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	830 :meth:`executescript` methods of the :class:`Connection` object, your code can
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	831 be written more concisely because you don't have to create the (often
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	832 superfluous) :class:`Cursor` objects explicitly. Instead, the :class:`Cursor`
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	833 objects are created implicitly and these shortcut methods return the cursor
49473 4845810f588e #4059: patch up some sqlite docs. Georg Brandl <georg@python.org> parents: 49052 diff changeset	834 objects. This way, you can execute a ``SELECT`` statement and iterate over it
42604 9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	835 directly using only a single call on the :class:`Connection` object.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	836
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	837 .. literalinclude:: ../includes/sqlite3/shortcut_methods.py
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	838
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	839
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	840 Accessing columns by name instead of by index
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	841 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	842
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	843 One useful feature of the :mod:`sqlite3` module is the builtin
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	844 :class:`sqlite3.Row` class designed to be used as a row factory.
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	845
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	846 Rows wrapped with this class can be accessed both by index (like tuples) and
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	847 case-insensitively by name:
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	848
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	849 .. literalinclude:: ../includes/sqlite3/rowclass.py
9e1529bf0442 Move the 3k reST doc tree in place. Georg Brandl <georg@python.org> parents: diff changeset	850
46180 0ea5c055ab54 Brought documentation for sqlite3 module up-to-date. Fixed Issue1625205 which Gerhard Häring <gh@ghaering.de> parents: 46159 diff changeset	851
0ea5c055ab54 Brought documentation for sqlite3 module up-to-date. Fixed Issue1625205 which Gerhard Häring <gh@ghaering.de> parents: 46159 diff changeset	852 Using the connection as a context manager
0ea5c055ab54 Brought documentation for sqlite3 module up-to-date. Fixed Issue1625205 which Gerhard Häring <gh@ghaering.de> parents: 46159 diff changeset	853 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
0ea5c055ab54 Brought documentation for sqlite3 module up-to-date. Fixed Issue1625205 which Gerhard Häring <gh@ghaering.de> parents: 46159 diff changeset	854
0ea5c055ab54 Brought documentation for sqlite3 module up-to-date. Fixed Issue1625205 which Gerhard Häring <gh@ghaering.de> parents: 46159 diff changeset	855 .. versionadded:: 2.6
0ea5c055ab54 Brought documentation for sqlite3 module up-to-date. Fixed Issue1625205 which Gerhard Häring <gh@ghaering.de> parents: 46159 diff changeset	856
0ea5c055ab54 Brought documentation for sqlite3 module up-to-date. Fixed Issue1625205 which Gerhard Häring <gh@ghaering.de> parents: 46159 diff changeset	857 Connection objects can be used as context managers
0ea5c055ab54 Brought documentation for sqlite3 module up-to-date. Fixed Issue1625205 which Gerhard Häring <gh@ghaering.de> parents: 46159 diff changeset	858 that automatically commit or rollback transactions. In the event of an
0ea5c055ab54 Brought documentation for sqlite3 module up-to-date. Fixed Issue1625205 which Gerhard Häring <gh@ghaering.de> parents: 46159 diff changeset	859 exception, the transaction is rolled back; otherwise, the transaction is
0ea5c055ab54 Brought documentation for sqlite3 module up-to-date. Fixed Issue1625205 which Gerhard Häring <gh@ghaering.de> parents: 46159 diff changeset	860 committed:
0ea5c055ab54 Brought documentation for sqlite3 module up-to-date. Fixed Issue1625205 which Gerhard Häring <gh@ghaering.de> parents: 46159 diff changeset	861
0ea5c055ab54 Brought documentation for sqlite3 module up-to-date. Fixed Issue1625205 which Gerhard Häring <gh@ghaering.de> parents: 46159 diff changeset	862 .. literalinclude:: ../includes/sqlite3/ctx_manager.py

Mercurial > cpython-lru_cache-weakref

annotate Doc/library/sqlite3.rst @ 54294:dba767bc3155 legacy-trunk