cpython-lru_cache-weakref: Doc/lib/libsqlite3.tex annotate

annotate Doc/lib/libsqlite3.tex @ 39496:352ffe77b71d legacy-trunk

Patch #1535500: fix segfault in BZ2File.writelines and make sure it raises the correct exceptions.

author	Georg Brandl <georg@python.org>
date	Mon, 14 Aug 2006 21:42:55 +0000
parents	388b7d90bb89
children	527a3d0052c9

rev	line source
38046 3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	1 \section{\module{sqlite3} ---
3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	2 DB-API 2.0 interface for SQLite databases}
3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	3
3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	4 \declaremodule{builtin}{sqlite3}
38065 135b177883c3 - minor clarification in section title Fred Drake <fdrake@acm.org> parents: 38046 diff changeset	5 \modulesynopsis{A DB-API 2.0 implementation using SQLite 3.x.}
38169 85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	6 \sectionauthor{Gerhard Häring}{gh@ghaering.de}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	7 \versionadded{2.5}
38046 3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	8
38639 0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	9 SQLite is a C library that provides a SQL-language database that
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	10 stores data in disk files without requiring a separate server process.
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	11 pysqlite was written by Gerhard H\"aring and provides a SQL interface
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	12 compliant with the DB-API 2.0 specification described by
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	13 \pep{249}. This means that it should be possible to write the first
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	14 version of your applications using SQLite for data storage. If
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	15 switching to a larger database such as PostgreSQL or Oracle is
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	16 later necessary, the switch should be relatively easy.
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	17
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	18 To use the module, you must first create a \class{Connection} object
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	19 that represents the database. Here the data will be stored in the
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	20 \file{/tmp/example} file:
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	21
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	22 \begin{verbatim}
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	23 conn = sqlite3.connect('/tmp/example')
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	24 \end{verbatim}
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	25
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	26 You can also supply the special name \samp{:memory:} to create
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	27 a database in RAM.
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	28
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	29 Once you have a \class{Connection}, you can create a \class{Cursor}
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	30 object and call its \method{execute()} method to perform SQL commands:
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	31
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	32 \begin{verbatim}
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	33 c = conn.cursor()
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	34
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	35 # Create table
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	36 c.execute('''create table stocks
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	37 (date timestamp, trans varchar, symbol varchar,
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	38 qty decimal, price decimal)''')
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	39
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	40 # Insert a row of data
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	41 c.execute("""insert into stocks
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	42 values ('2006-01-05','BUY','RHAT',100,35.14)""")
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	43 \end{verbatim}
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	44
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	45 Usually your SQL operations will need to use values from Python
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	46 variables. You shouldn't assemble your query using Python's string
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	47 operations because doing so is insecure; it makes your program
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	48 vulnerable to an SQL injection attack.
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	49
38642 3fb38351dd39 Mention other placeholders Andrew M. Kuchling <amk@amk.ca> parents: 38639 diff changeset	50 Instead, use the DB-API's parameter substitution. Put \samp{?} as a
38639 0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	51 placeholder wherever you want to use a value, and then provide a tuple
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	52 of values as the second argument to the cursor's \method{execute()}
38642 3fb38351dd39 Mention other placeholders Andrew M. Kuchling <amk@amk.ca> parents: 38639 diff changeset	53 method. (Other database modules may use a different placeholder,
3fb38351dd39 Mention other placeholders Andrew M. Kuchling <amk@amk.ca> parents: 38639 diff changeset	54 such as \samp{\%s} or \samp{:1}.) For example:
38639 0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	55
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	56 \begin{verbatim}
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	57 # Never do this -- insecure!
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	58 symbol = 'IBM'
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	59 c.execute("... where symbol = '%s'" % symbol)
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	60
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	61 # Do this instead
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	62 t = (symbol,)
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	63 c.execute('select * from stocks where symbol=?', t)
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	64
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	65 # Larger example
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	66 for t in (('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	67 ('2006-04-05', 'BUY', 'MSOFT', 1000, 72.00),
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	68 ('2006-04-06', 'SELL', 'IBM', 500, 53.00),
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	69 ):
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	70 c.execute('insert into stocks values (?,?,?,?,?)', t)
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	71 \end{verbatim}
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	72
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	73 To retrieve data after executing a SELECT statement, you can either
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	74 treat the cursor as an iterator, call the cursor's \method{fetchone()}
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	75 method to retrieve a single matching row,
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	76 or call \method{fetchall()} to get a list of the matching rows.
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	77
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	78 This example uses the iterator form:
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	79
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	80 \begin{verbatim}
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	81 >>> c = conn.cursor()
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	82 >>> c.execute('select * from stocks order by price')
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	83 >>> for row in c:
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	84 ... print row
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	85 ...
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	86 (u'2006-01-05', u'BUY', u'RHAT', 100, 35.140000000000001)
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	87 (u'2006-03-28', u'BUY', u'IBM', 1000, 45.0)
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	88 (u'2006-04-06', u'SELL', u'IBM', 500, 53.0)
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	89 (u'2006-04-05', u'BUY', u'MSOFT', 1000, 72.0)
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	90 >>>
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	91 \end{verbatim}
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	92
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	93 \begin{seealso}
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	94
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	95 \seeurl{http://www.pysqlite.org}
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	96 {The pysqlite web page.}
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	97
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	98 \seeurl{http://www.sqlite.org}
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	99 {The SQLite web page; the documentation describes the syntax and the
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	100 available data types for the supported SQL dialect.}
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	101
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	102 \seepep{249}{Database API Specification 2.0}{PEP written by
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	103 Marc-Andr\'e Lemburg.}
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	104
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	105 \end{seealso}
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	106
0cd2273bc3b1 Add an SQLite introduction, taken from the 'What's New' text Andrew M. Kuchling <amk@amk.ca> parents: 38169 diff changeset	107
38169 85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	108 \subsection{Module functions and constants\label{sqlite3-Module-Contents}}
38046 3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	109
3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	110 \begin{datadesc}{PARSE_DECLTYPES}
38169 85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	111 This constant is meant to be used with the \var{detect_types} parameter of the
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	112 \function{connect} function.
38046 3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	113
38169 85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	114 Setting it makes the \module{sqlite3} module parse the declared type for each column it
38046 3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	115 returns. It will parse out the first word of the declared type, i. e. for
3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	116 "integer primary key", it will parse out "integer". Then for that column, it
38169 85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	117 will look into the converters dictionary and use the converter function
38046 3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	118 registered for that type there. Converter names are case-sensitive!
3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	119 \end{datadesc}
3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	120
3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	121
3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	122 \begin{datadesc}{PARSE_COLNAMES}
38169 85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	123 This constant is meant to be used with the \var{detect_types} parameter of the
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	124 \function{connect} function.
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	125
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	126 Setting this makes the SQLite interface parse the column name for each column
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	127 it returns. It will look for a string formed [mytype] in there, and then
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	128 decide that 'mytype' is the type of the column. It will try to find an entry of
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	129 'mytype' in the converters dictionary and then use the converter function found
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	130 there to return the value. The column name found in \member{cursor.description} is only
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	131 the first word of the column name, i. e. if you use something like
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	132 \code{'as "x [datetime]"'} in your SQL, then we will parse out everything until the
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	133 first blank for the column name: the column name would simply be "x".
38046 3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	134 \end{datadesc}
3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	135
38169 85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	136 \begin{funcdesc}{connect}{database\optional{, timeout, isolation_level, detect_types, factory}}
38046 3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	137 Opens a connection to the SQLite database file \var{database}. You can use
3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	138 \code{":memory:"} to open a database connection to a database that resides in
3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	139 RAM instead of on disk.
3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	140
3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	141 When a database is accessed by multiple connections, and one of the processes
3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	142 modifies the database, the SQLite database is locked until that transaction is
3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	143 committed. The \var{timeout} parameter specifies how long the connection should
3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	144 wait for the lock to go away until raising an exception. The default for the
3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	145 timeout parameter is 5.0 (five seconds).
3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	146
38169 85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	147 For the \var{isolation_level} parameter, please see \member{isolation_level}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	148 \ref{sqlite3-Connection-IsolationLevel} property of \class{Connection} objects.
38046 3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	149
3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	150 SQLite natively supports only the types TEXT, INTEGER, FLOAT, BLOB and NULL. If
3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	151 you want to use other types, like you have to add support for them yourself.
38169 85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	152 The \var{detect_types} parameter and the using custom \strong{converters} registered with
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	153 the module-level \function{register_converter} function allow you to easily do that.
38046 3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	154
3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	155 \var{detect_types} defaults to 0 (i. e. off, no type detection), you can set it
38169 85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	156 to any combination of \constant{PARSE_DECLTYPES} and \constant{PARSE_COLNAMES} to turn type
38046 3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	157 detection on.
3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	158
38169 85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	159 By default, the \module{sqlite3} module uses its \class{Connection} class for the
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	160 connect call. You can, however, subclass the \class{Connection} class and make
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	161 \function{connect} use your class instead by providing your class for the
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	162 \var{factory} parameter.
38046 3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	163
38169 85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	164 Consult the section \ref{sqlite3-Types} of this manual for details.
38046 3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	165
38169 85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	166 The \module{sqlite3} module internally uses a statement cache to avoid SQL parsing
38046 3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	167 overhead. If you want to explicitly set the number of statements that are
3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	168 cached for the connection, you can set the \var{cached_statements} parameter.
3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	169 The currently implemented default is to cache 100 statements.
3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	170 \end{funcdesc}
3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	171
3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	172 \begin{funcdesc}{register_converter}{typename, callable}
3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	173 Registers a callable to convert a bytestring from the database into a custom
3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	174 Python type. The callable will be invoked for all database values that are of
38169 85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	175 the type \var{typename}. Confer the parameter \var{detect_types} of the
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	176 \function{connect} function for how the type detection works. Note that the case of
38046 3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	177 \var{typename} and the name of the type in your query must match!
3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	178 \end{funcdesc}
3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	179
3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	180 \begin{funcdesc}{register_adapter}{type, callable}
3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	181 Registers a callable to convert the custom Python type \var{type} into one of
3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	182 SQLite's supported types. The callable \var{callable} accepts as single
3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	183 parameter the Python value, and must return a value of the following types:
3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	184 int, long, float, str (UTF-8 encoded), unicode or buffer.
3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	185 \end{funcdesc}
3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	186
38169 85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	187 \begin{funcdesc}{complete_statement}{sql}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	188 Returns \constant{True} if the string \var{sql} one or more complete SQL
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	189 statements terminated by semicolons. It does not verify if the SQL is
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	190 syntactically correct, only if there are no unclosed string literals and if the
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	191 statement is terminated by a semicolon.
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	192
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	193 This can be used to build a shell for SQLite, like in the following example:
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	194
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	195 \verbatiminput{sqlite3/complete_statement.py}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	196 \end{funcdesc}
38046 3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	197
38810 6f7e64b72a58 Updated documentation for pysqlite 2.3.0 API. Gerhard Häring <gh@ghaering.de> parents: 38660 diff changeset	198 \begin{funcdesc}{}enable_callback_tracebacks{flag}
6f7e64b72a58 Updated documentation for pysqlite 2.3.0 API. Gerhard Häring <gh@ghaering.de> parents: 38660 diff changeset	199 By default you will not get any tracebacks in user-defined functions,
6f7e64b72a58 Updated documentation for pysqlite 2.3.0 API. Gerhard Häring <gh@ghaering.de> parents: 38660 diff changeset	200 aggregates, converters, authorizer callbacks etc. If you want to debug them,
6f7e64b72a58 Updated documentation for pysqlite 2.3.0 API. Gerhard Häring <gh@ghaering.de> parents: 38660 diff changeset	201 you can call this function with \var{flag} as True. Afterwards, you will get
6f7e64b72a58 Updated documentation for pysqlite 2.3.0 API. Gerhard Häring <gh@ghaering.de> parents: 38660 diff changeset	202 tracebacks from callbacks on \code{sys.stderr}. Use \constant{False} to disable
6f7e64b72a58 Updated documentation for pysqlite 2.3.0 API. Gerhard Häring <gh@ghaering.de> parents: 38660 diff changeset	203 the feature again.
6f7e64b72a58 Updated documentation for pysqlite 2.3.0 API. Gerhard Häring <gh@ghaering.de> parents: 38660 diff changeset	204 \end{funcdesc}
6f7e64b72a58 Updated documentation for pysqlite 2.3.0 API. Gerhard Häring <gh@ghaering.de> parents: 38660 diff changeset	205
38065 135b177883c3 - minor clarification in section title Fred Drake <fdrake@acm.org> parents: 38046 diff changeset	206 \subsection{Connection Objects \label{sqlite3-Connection-Objects}}
38046 3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	207
3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	208 A \class{Connection} instance has the following attributes and methods:
3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	209
38169 85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	210 \label{sqlite3-Connection-IsolationLevel}
38065 135b177883c3 - minor clarification in section title Fred Drake <fdrake@acm.org> parents: 38046 diff changeset	211 \begin{memberdesc}{isolation_level}
38169 85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	212 Get or set the current isolation level. None for autocommit mode or one of
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	213 "DEFERRED", "IMMEDIATE" or "EXLUSIVE". See Controlling Transactions
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	214 \ref{sqlite3-Controlling-Transactions} for a more detailed explanation.
38065 135b177883c3 - minor clarification in section title Fred Drake <fdrake@acm.org> parents: 38046 diff changeset	215 \end{memberdesc}
38046 3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	216
3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	217 \begin{methoddesc}{cursor}{\optional{cursorClass}}
38065 135b177883c3 - minor clarification in section title Fred Drake <fdrake@acm.org> parents: 38046 diff changeset	218 The cursor method accepts a single optional parameter \var{cursorClass}.
135b177883c3 - minor clarification in section title Fred Drake <fdrake@acm.org> parents: 38046 diff changeset	219 This is a custom cursor class which must extend \class{sqlite3.Cursor}.
38046 3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	220 \end{methoddesc}
3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	221
38069 cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	222 \begin{methoddesc}{execute}{sql, \optional{parameters}}
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	223 This is a nonstandard shortcut that creates an intermediate cursor object by
38169 85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	224 calling the cursor method, then calls the cursor's \method{execute} method with the
38069 cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	225 parameters given.
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	226 \end{methoddesc}
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	227
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	228 \begin{methoddesc}{executemany}{sql, \optional{parameters}}
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	229 This is a nonstandard shortcut that creates an intermediate cursor object by
38169 85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	230 calling the cursor method, then calls the cursor's \method{executemany} method with the
38069 cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	231 parameters given.
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	232 \end{methoddesc}
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	233
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	234 \begin{methoddesc}{executescript}{sql_script}
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	235 This is a nonstandard shortcut that creates an intermediate cursor object by
38169 85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	236 calling the cursor method, then calls the cursor's \method{executescript} method with the
38069 cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	237 parameters given.
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	238 \end{methoddesc}
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	239
38169 85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	240 \begin{methoddesc}{create_function}{name, num_params, func}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	241
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	242 Creates a user-defined function that you can later use from within SQL
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	243 statements under the function name \var{name}. \var{num_params} is the number
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	244 of parameters the function accepts, and \var{func} is a Python callable that is
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	245 called as SQL function.
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	246
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	247 The function can return any of the types supported by SQLite: unicode, str,
38810 6f7e64b72a58 Updated documentation for pysqlite 2.3.0 API. Gerhard Häring <gh@ghaering.de> parents: 38660 diff changeset	248 int, long, float, buffer and None.
38169 85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	249
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	250 Example:
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	251
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	252 \verbatiminput{sqlite3/md5func.py}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	253 \end{methoddesc}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	254
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	255 \begin{methoddesc}{create_aggregate}{name, num_params, aggregate_class}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	256
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	257 Creates a user-defined aggregate function.
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	258
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	259 The aggregate class must implement a \code{step} method, which accepts the
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	260 number of parameters \var{num_params}, and a \code{finalize} method which
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	261 will return the final result of the aggregate.
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	262
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	263 The \code{finalize} method can return any of the types supported by SQLite:
38810 6f7e64b72a58 Updated documentation for pysqlite 2.3.0 API. Gerhard Häring <gh@ghaering.de> parents: 38660 diff changeset	264 unicode, str, int, long, float, buffer and None.
38169 85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	265
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	266 Example:
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	267
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	268 \verbatiminput{sqlite3/mysumaggr.py}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	269 \end{methoddesc}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	270
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	271 \begin{methoddesc}{create_collation}{name, callable}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	272
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	273 Creates a collation with the specified \var{name} and \var{callable}. The
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	274 callable will be passed two string arguments. It should return -1 if the first
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	275 is ordered lower than the second, 0 if they are ordered equal and 1 and if the
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	276 first is ordered higher than the second. Note that this controls sorting
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	277 (ORDER BY in SQL) so your comparisons don't affect other SQL operations.
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	278
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	279 Note that the callable will get its parameters as Python bytestrings, which
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	280 will normally be encoded in UTF-8.
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	281
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	282 The following example shows a custom collation that sorts "the wrong way":
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	283
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	284 \verbatiminput{sqlite3/collation_reverse.py}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	285
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	286 To remove a collation, call \code{create_collation} with None as callable:
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	287
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	288 \begin{verbatim}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	289 con.create_collation("reverse", None)
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	290 \end{verbatim}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	291 \end{methoddesc}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	292
38810 6f7e64b72a58 Updated documentation for pysqlite 2.3.0 API. Gerhard Häring <gh@ghaering.de> parents: 38660 diff changeset	293 \begin{methoddesc}{interrupt}{}
6f7e64b72a58 Updated documentation for pysqlite 2.3.0 API. Gerhard Häring <gh@ghaering.de> parents: 38660 diff changeset	294
6f7e64b72a58 Updated documentation for pysqlite 2.3.0 API. Gerhard Häring <gh@ghaering.de> parents: 38660 diff changeset	295 You can call this method from a different thread to abort any queries that
6f7e64b72a58 Updated documentation for pysqlite 2.3.0 API. Gerhard Häring <gh@ghaering.de> parents: 38660 diff changeset	296 might be executing on the connection. The query will then abort and the caller
6f7e64b72a58 Updated documentation for pysqlite 2.3.0 API. Gerhard Häring <gh@ghaering.de> parents: 38660 diff changeset	297 will get an exception.
6f7e64b72a58 Updated documentation for pysqlite 2.3.0 API. Gerhard Häring <gh@ghaering.de> parents: 38660 diff changeset	298 \end{methoddesc}
6f7e64b72a58 Updated documentation for pysqlite 2.3.0 API. Gerhard Häring <gh@ghaering.de> parents: 38660 diff changeset	299
6f7e64b72a58 Updated documentation for pysqlite 2.3.0 API. Gerhard Häring <gh@ghaering.de> parents: 38660 diff changeset	300 \begin{methoddesc}{set_authorizer}{authorizer_callback}
6f7e64b72a58 Updated documentation for pysqlite 2.3.0 API. Gerhard Häring <gh@ghaering.de> parents: 38660 diff changeset	301
6f7e64b72a58 Updated documentation for pysqlite 2.3.0 API. Gerhard Häring <gh@ghaering.de> parents: 38660 diff changeset	302 This routine registers a callback. The callback is invoked for each attempt to
6f7e64b72a58 Updated documentation for pysqlite 2.3.0 API. Gerhard Häring <gh@ghaering.de> parents: 38660 diff changeset	303 access a column of a table in the database. The callback should return
6f7e64b72a58 Updated documentation for pysqlite 2.3.0 API. Gerhard Häring <gh@ghaering.de> parents: 38660 diff changeset	304 \constant{SQLITE_OK} if access is allowed, \constant{SQLITE_DENY} if the entire
6f7e64b72a58 Updated documentation for pysqlite 2.3.0 API. Gerhard Häring <gh@ghaering.de> parents: 38660 diff changeset	305 SQL statement should be aborted with an error and \constant{SQLITE_IGNORE} if
6f7e64b72a58 Updated documentation for pysqlite 2.3.0 API. Gerhard Häring <gh@ghaering.de> parents: 38660 diff changeset	306 the column should be treated as a NULL value. These constants are available in
6f7e64b72a58 Updated documentation for pysqlite 2.3.0 API. Gerhard Häring <gh@ghaering.de> parents: 38660 diff changeset	307 the \module{sqlite3} module.
6f7e64b72a58 Updated documentation for pysqlite 2.3.0 API. Gerhard Häring <gh@ghaering.de> parents: 38660 diff changeset	308
6f7e64b72a58 Updated documentation for pysqlite 2.3.0 API. Gerhard Häring <gh@ghaering.de> parents: 38660 diff changeset	309 The first argument to the callback signifies what kind of operation is to be
6f7e64b72a58 Updated documentation for pysqlite 2.3.0 API. Gerhard Häring <gh@ghaering.de> parents: 38660 diff changeset	310 authorized. The second and third argument will be arguments or \constant{None}
6f7e64b72a58 Updated documentation for pysqlite 2.3.0 API. Gerhard Häring <gh@ghaering.de> parents: 38660 diff changeset	311 depending on the first argument. The 4th argument is the name of the database
6f7e64b72a58 Updated documentation for pysqlite 2.3.0 API. Gerhard Häring <gh@ghaering.de> parents: 38660 diff changeset	312 ("main", "temp", etc.) if applicable. The 5th argument is the name of the
6f7e64b72a58 Updated documentation for pysqlite 2.3.0 API. Gerhard Häring <gh@ghaering.de> parents: 38660 diff changeset	313 inner-most trigger or view that is responsible for the access attempt or
6f7e64b72a58 Updated documentation for pysqlite 2.3.0 API. Gerhard Häring <gh@ghaering.de> parents: 38660 diff changeset	314 \constant{None} if this access attempt is directly from input SQL code.
6f7e64b72a58 Updated documentation for pysqlite 2.3.0 API. Gerhard Häring <gh@ghaering.de> parents: 38660 diff changeset	315
6f7e64b72a58 Updated documentation for pysqlite 2.3.0 API. Gerhard Häring <gh@ghaering.de> parents: 38660 diff changeset	316 Please consult the SQLite documentation about the possible values for the first
6f7e64b72a58 Updated documentation for pysqlite 2.3.0 API. Gerhard Häring <gh@ghaering.de> parents: 38660 diff changeset	317 argument and the meaning of the second and third argument depending on the
6f7e64b72a58 Updated documentation for pysqlite 2.3.0 API. Gerhard Häring <gh@ghaering.de> parents: 38660 diff changeset	318 first one. All necessary constants are available in the \module{sqlite3}
6f7e64b72a58 Updated documentation for pysqlite 2.3.0 API. Gerhard Häring <gh@ghaering.de> parents: 38660 diff changeset	319 module.
6f7e64b72a58 Updated documentation for pysqlite 2.3.0 API. Gerhard Häring <gh@ghaering.de> parents: 38660 diff changeset	320 \end{methoddesc}
38169 85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	321
38069 cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	322 \begin{memberdesc}{row_factory}
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	323 You can change this attribute to a callable that accepts the cursor and
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	324 the original row as tuple and will return the real result row. This
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	325 way, you can implement more advanced ways of returning results, like
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	326 ones that can also access columns by name.
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	327
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	328 Example:
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	329
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	330 \verbatiminput{sqlite3/row_factory.py}
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	331
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	332 If the standard tuple types don't suffice for you, and you want name-based
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	333 access to columns, you should consider setting \member{row_factory} to the
38169 85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	334 highly-optimized sqlite3.Row type. It provides both
38069 cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	335 index-based and case-insensitive name-based access to columns with almost
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	336 no memory overhead. Much better than your own custom dictionary-based
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	337 approach or even a db_row based solution.
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	338 \end{memberdesc}
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	339
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	340 \begin{memberdesc}{text_factory}
38169 85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	341 Using this attribute you can control what objects are returned for the
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	342 TEXT data type. By default, this attribute is set to \class{unicode} and
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	343 the \module{sqlite3} module will return Unicode objects for TEXT. If you want to return
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	344 bytestrings instead, you can set it to \class{str}.
38069 cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	345
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	346 For efficiency reasons, there's also a way to return Unicode objects only
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	347 for non-ASCII data, and bytestrings otherwise. To activate it, set this
38169 85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	348 attribute to \constant{sqlite3.OptimizedUnicode}.
38069 cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	349
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	350 You can also set it to any other callable that accepts a single bytestring
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	351 parameter and returns the result object.
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	352
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	353 See the following example code for illustration:
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	354
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	355 \verbatiminput{sqlite3/text_factory.py}
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	356 \end{memberdesc}
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	357
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	358 \begin{memberdesc}{total_changes}
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	359 Returns the total number of database rows that have be modified, inserted,
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	360 or deleted since the database connection was opened.
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	361 \end{memberdesc}
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	362
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	363
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	364
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	365
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	366
38169 85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	367 \subsection{Cursor Objects \label{sqlite3-Cursor-Objects}}
38069 cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	368
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	369 A \class{Cursor} instance has the following attributes and methods:
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	370
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	371 \begin{methoddesc}{execute}{sql, \optional{parameters}}
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	372
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	373 Executes a SQL statement. The SQL statement may be parametrized (i. e.
38169 85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	374 placeholders instead of SQL literals). The \module{sqlite3} module supports two kinds of
38069 cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	375 placeholders: question marks (qmark style) and named placeholders (named
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	376 style).
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	377
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	378 This example shows how to use parameters with qmark style:
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	379
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	380 \verbatiminput{sqlite3/execute_1.py}
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	381
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	382 This example shows how to use the named style:
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	383
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	384 \verbatiminput{sqlite3/execute_2.py}
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	385
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	386 \method{execute} will only execute a single SQL statement. If you try to
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	387 execute more than one statement with it, it will raise a Warning. Use
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	388 \method{executescript} if want to execute multiple SQL statements with one
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	389 call.
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	390 \end{methoddesc}
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	391
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	392
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	393 \begin{methoddesc}{executemany}{sql, seq_of_parameters}
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	394 Executes a SQL command against all parameter sequences or mappings found in the
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	395 sequence \var{sql}. The \module{sqlite3} module also allows
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	396 to use an iterator yielding parameters instead of a sequence.
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	397
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	398 \verbatiminput{sqlite3/executemany_1.py}
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	399
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	400 Here's a shorter example using a generator:
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	401
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	402 \verbatiminput{sqlite3/executemany_2.py}
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	403 \end{methoddesc}
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	404
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	405 \begin{methoddesc}{executescript}{sql_script}
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	406
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	407 This is a nonstandard convenience method for executing multiple SQL statements
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	408 at once. It issues a COMMIT statement before, then executes the SQL script it
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	409 gets as a parameter.
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	410
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	411 \var{sql_script} can be a bytestring or a Unicode string.
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	412
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	413 Example:
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	414
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	415 \verbatiminput{sqlite3/executescript.py}
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	416 \end{methoddesc}
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	417
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	418 \begin{memberdesc}{rowcount}
38169 85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	419 Although the \class{Cursor} class of the \module{sqlite3} module implements this
38069 cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	420 attribute, the database engine's own support for the determination of "rows
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	421 affected"/"rows selected" is quirky.
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	422
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	423 For \code{SELECT} statements, \member{rowcount} is always None because we cannot
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	424 determine the number of rows a query produced until all rows were fetched.
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	425
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	426 For \code{DELETE} statements, SQLite reports \member{rowcount} as 0 if you make a
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	427 \code{DELETE FROM table} without any condition.
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	428
38169 85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	429 For \method{executemany} statements, the number of modifications are summed
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	430 up into \member{rowcount}.
38069 cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	431
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	432 As required by the Python DB API Spec, the \member{rowcount} attribute "is -1
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	433 in case no executeXX() has been performed on the cursor or the rowcount
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	434 of the last operation is not determinable by the interface".
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	435 \end{memberdesc}
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	436
38169 85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	437 \subsection{SQLite and Python types\label{sqlite3-Types}}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	438
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	439 \subsubsection{Introduction}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	440
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	441 SQLite natively supports the following types: NULL, INTEGER, REAL, TEXT, BLOB.
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	442
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	443 The following Python types can thus be sent to SQLite without any problem:
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	444
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	445 \begin{tableii} {c\|l}{code}{Python type}{SQLite type}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	446 \lineii{None}{NULL}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	447 \lineii{int}{INTEGER}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	448 \lineii{long}{INTEGER}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	449 \lineii{float}{REAL}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	450 \lineii{str (UTF8-encoded)}{TEXT}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	451 \lineii{unicode}{TEXT}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	452 \lineii{buffer}{BLOB}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	453 \end{tableii}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	454
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	455 This is how SQLite types are converted to Python types by default:
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	456
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	457 \begin{tableii} {c\|l}{code}{SQLite type}{Python type}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	458 \lineii{NULL}{None}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	459 \lineii{INTEGER}{int or long, depending on size}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	460 \lineii{REAL}{float}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	461 \lineii{TEXT}{depends on text_factory, unicode by default}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	462 \lineii{BLOB}{buffer}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	463 \end{tableii}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	464
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	465 The type system of the \module{sqlite3} module is extensible in both ways: you can store
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	466 additional Python types in a SQLite database via object adaptation, and you can
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	467 let the \module{sqlite3} module convert SQLite types to different Python types via
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	468 converters.
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	469
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	470 \subsubsection{Using adapters to store additional Python types in SQLite databases}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	471
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	472 Like described before, SQLite supports only a limited set of types natively. To
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	473 use other Python types with SQLite, you must \strong{adapt} them to one of the sqlite3
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	474 module's supported types for SQLite. So, one of NoneType, int, long, float,
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	475 str, unicode, buffer.
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	476
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	477 The \module{sqlite3} module uses the Python object adaptation, like described in PEP 246
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	478 for this. The protocol to use is \class{PrepareProtocol}.
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	479
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	480 There are two ways to enable the \module{sqlite3} module to adapt a custom Python type
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	481 to one of the supported ones.
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	482
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	483 \paragraph{Letting your object adapt itself}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	484
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	485 This is a good approach if you write the class yourself. Let's suppose you have
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	486 a class like this:
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	487
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	488 \begin{verbatim}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	489 class Point(object):
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	490 def __init__(self, x, y):
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	491 self.x, self.y = x, y
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	492 \end{verbatim}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	493
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	494 Now you want to store the point in a single SQLite column. You'll have to
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	495 choose one of the supported types first that you use to represent the point in.
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	496 Let's just use str and separate the coordinates using a semicolon. Then you
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	497 need to give your class a method \code{__conform__(self, protocol)} which must
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	498 return the converted value. The parameter \var{protocol} will be
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	499 \class{PrepareProtocol}.
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	500
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	501 \verbatiminput{sqlite3/adapter_point_1.py}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	502
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	503 \paragraph{Registering an adapter callable}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	504
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	505 The other possibility is to create a function that converts the type to the
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	506 string representation and register the function with \method{register_adapter}.
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	507
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	508 \verbatiminput{sqlite3/adapter_point_2.py}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	509
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	510 \begin{notice}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	511 The type/class to adapt must be a new-style class, i. e. it must have
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	512 \class{object} as one of its bases.
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	513 \end{notice}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	514
39296 388b7d90bb89 lots of markup nits, most commonly Unix/unix --> \UNIX Fred Drake <fdrake@acm.org> parents: 38810 diff changeset	515 The \module{sqlite3} module has two default adapters for Python's built-in
388b7d90bb89 lots of markup nits, most commonly Unix/unix --> \UNIX Fred Drake <fdrake@acm.org> parents: 38810 diff changeset	516 \class{datetime.date} and \class{datetime.datetime} types. Now let's suppose
388b7d90bb89 lots of markup nits, most commonly Unix/unix --> \UNIX Fred Drake <fdrake@acm.org> parents: 38810 diff changeset	517 we want to store \class{datetime.datetime} objects not in ISO representation,
388b7d90bb89 lots of markup nits, most commonly Unix/unix --> \UNIX Fred Drake <fdrake@acm.org> parents: 38810 diff changeset	518 but as a \UNIX{} timestamp.
38169 85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	519
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	520 \verbatiminput{sqlite3/adapter_datetime.py}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	521
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	522 \subsubsection{Converting SQLite values to custom Python types}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	523
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	524 Now that's all nice and dandy that you can send custom Python types to SQLite.
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	525 But to make it really useful we need to make the Python to SQLite to Python
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	526 roundtrip work.
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	527
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	528 Enter converters.
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	529
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	530 Let's go back to the Point class. We stored the x and y coordinates separated
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	531 via semicolons as strings in SQLite.
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	532
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	533 Let's first define a converter function that accepts the string as a parameter and constructs a Point object from it.
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	534
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	535 \begin{notice}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	536 Converter functions \strong{always} get called with a string, no matter
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	537 under which data type you sent the value to SQLite.
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	538 \end{notice}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	539
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	540 \begin{notice}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	541 Converter names are looked up in a case-sensitive manner.
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	542 \end{notice}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	543
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	544
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	545 \begin{verbatim}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	546 def convert_point(s):
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	547 x, y = map(float, s.split(";"))
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	548 return Point(x, y)
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	549 \end{verbatim}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	550
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	551 Now you need to make the \module{sqlite3} module know that what you select from the
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	552 database is actually a point. There are two ways of doing this:
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	553
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	554 \begin{itemize}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	555 \item Implicitly via the declared type
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	556 \item Explicitly via the column name
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	557 \end{itemize}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	558
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	559 Both ways are described at \ref{sqlite3-Module-Contents} in the text explaining
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	560 the constants \constant{PARSE_DECLTYPES} and \constant{PARSE_COlNAMES}.
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	561
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	562
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	563 The following example illustrates both ways.
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	564
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	565 \verbatiminput{sqlite3/converter_point.py}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	566
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	567 \subsubsection{Default adapters and converters}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	568
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	569 There are default adapters for the date and datetime types in the datetime
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	570 module. They will be sent as ISO dates/ISO timestamps to SQLite.
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	571
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	572 The default converters are registered under the name "date" for datetime.date
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	573 and under the name "timestamp" for datetime.datetime.
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	574
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	575 This way, you can use date/timestamps from Python without any additional
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	576 fiddling in most cases. The format of the adapters is also compatible with the
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	577 experimental SQLite date/time functions.
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	578
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	579 The following example demonstrates this.
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	580
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	581 \verbatiminput{sqlite3/pysqlite_datetime.py}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	582
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	583 \subsection{Controlling Transactions \label{sqlite3-Controlling-Transactions}}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	584
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	585 By default, the \module{sqlite3} module opens transactions implicitly before a DML
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	586 statement (INSERT/UPDATE/DELETE/REPLACE), and commits transactions implicitly
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	587 before a non-DML, non-DQL statement (i. e. anything other than
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	588 SELECT/INSERT/UPDATE/DELETE/REPLACE).
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	589
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	590 So if you are within a transaction, and issue a command like \code{CREATE TABLE
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	591 ...}, \code{VACUUM}, \code{PRAGMA}, the \module{sqlite3} module will commit implicitly
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	592 before executing that command. There are two reasons for doing that. The first
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	593 is that some of these commands don't work within transactions. The other reason
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	594 is that pysqlite needs to keep track of the transaction state (if a transaction
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	595 is active or not).
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	596
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	597 You can control which kind of "BEGIN" statements pysqlite implicitly executes
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	598 (or none at all) via the \var{isolation_level} parameter to the
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	599 \function{connect} call, or via the \member{isolation_level} property of
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	600 connections.
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	601
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	602 If you want \strong{autocommit mode}, then set \member{isolation_level} to None.
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	603
38660 f28f9c15e2d0 Typo fix Andrew M. Kuchling <amk@amk.ca> parents: 38642 diff changeset	604 Otherwise leave it at its default, which will result in a plain "BEGIN"
38169 85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	605 statement, or set it to one of SQLite's supported isolation levels: DEFERRED,
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	606 IMMEDIATE or EXCLUSIVE.
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	607
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	608 As the \module{sqlite3} module needs to keep track of the transaction state, you should
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	609 not use \code{OR ROLLBACK} or \code{ON CONFLICT ROLLBACK} in your SQL. Instead,
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	610 catch the \exception{IntegrityError} and call the \method{rollback} method of
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	611 the connection yourself.
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	612
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	613 \subsection{Using pysqlite efficiently}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	614
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	615 \subsubsection{Using shortcut methods}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	616
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	617 Using the nonstandard \method{execute}, \method{executemany} and
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	618 \method{executescript} methods of the \class{Connection} object, your code can
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	619 be written more concisely, because you don't have to create the - often
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	620 superfluous \class{Cursor} objects explicitly. Instead, the \class{Cursor}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	621 objects are created implicitly and these shortcut methods return the cursor
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	622 objects. This way, you can for example execute a SELECT statement and iterate
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	623 over it directly using only a single call on the \class{Connection} object.
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	624
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	625 \verbatiminput{sqlite3/shortcut_methods.py}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	626
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	627 \subsubsection{Accessing columns by name instead of by index}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	628
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	629 One cool feature of the \module{sqlite3} module is the builtin \class{sqlite3.Row} class
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	630 designed to be used as a row factory.
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	631
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	632 Rows wrapped with this class can be accessed both by index (like tuples) and
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	633 case-insensitively by name:
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	634
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	635 \verbatiminput{sqlite3/rowclass.py}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	636
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	637

Mercurial > cpython-lru_cache-weakref

annotate Doc/lib/libsqlite3.tex @ 39496:352ffe77b71d legacy-trunk