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

annotate Doc/lib/libsqlite3.tex @ 38642:3fb38351dd39 legacy-trunk

Mention other placeholders

author	Andrew M. Kuchling <amk@amk.ca>
date	Wed, 07 Jun 2006 17:04:01 +0000
parents	0cd2273bc3b1
children	f28f9c15e2d0

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
38065 135b177883c3 - minor clarification in section title Fred Drake <fdrake@acm.org> parents: 38046 diff changeset	198 \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	199
3f51ffd5d8bf This is the start of documentation for the sqlite3 module. Please feel free to Gerhard Häring <gh@ghaering.de> parents: diff changeset	200 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	201
38169 85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	202 \label{sqlite3-Connection-IsolationLevel}
38065 135b177883c3 - minor clarification in section title Fred Drake <fdrake@acm.org> parents: 38046 diff changeset	203 \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	204 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	205 "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	206 \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	207 \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	208
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 \begin{methoddesc}{cursor}{\optional{cursorClass}}
38065 135b177883c3 - minor clarification in section title Fred Drake <fdrake@acm.org> parents: 38046 diff changeset	210 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	211 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	212 \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	213
38069 cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	214 \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	215 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	216 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	217 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	218 \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	219
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	220 \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	221 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	222 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	223 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	224 \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	225
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	226 \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	227 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	228 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	229 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	230 \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	231
38169 85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	232 \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	233
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	234 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	235 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	236 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	237 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	238
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	239 The function can return any of the types supported by SQLite: unicode, str,
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	240 int, long, float, buffer and None. Exceptions in the function are ignored and
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	241 they are handled as if the function returned None.
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	242
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	243 Example:
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	244
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	245 \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	246 \end{methoddesc}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	247
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	248 \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	249
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	250 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	251
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	252 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	253 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	254 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	255
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	256 The \code{finalize} method can return any of the types supported by SQLite:
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	257 unicode, str, int, long, float, buffer and None. Any exceptions are ignored.
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 Example:
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	260
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	261 \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	262 \end{methoddesc}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	263
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	264 \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	265
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	266 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	267 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	268 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	269 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	270 (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	271
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	272 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	273 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	274
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	275 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	276
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	277 \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	278
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	279 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	280
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	281 \begin{verbatim}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	282 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	283 \end{verbatim}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	284 \end{methoddesc}
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
38069 cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	287 \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	288 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	289 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	290 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	291 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	292
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	293 Example:
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	294
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	295 \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	296
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	297 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	298 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	299 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	300 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	301 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	302 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	303 \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	304
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	305 \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	306 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	307 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	308 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	309 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	310
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	311 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	312 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	313 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	314
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	315 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	316 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	317
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	318 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	319
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	320 \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	321 \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	322
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	323 \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	324 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	325 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	326 \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	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
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
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	331
38169 85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	332 \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	333
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	334 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	335
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	336 \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	337
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	338 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	339 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	340 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	341 style).
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	342
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	343 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	344
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	345 \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	346
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	347 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	348
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	349 \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	350
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	351 \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	352 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	353 \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	354 call.
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	355 \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	356
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{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	359 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	360 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	361 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	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 \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	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 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	366
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	367 \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	368 \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	369
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	370 \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	371
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	372 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	373 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	374 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	375
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	376 \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	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 Example:
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/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	381 \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	382
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	383 \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	384 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	385 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	386 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	387
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	388 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	389 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	390
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	391 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	392 \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	393
38169 85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	394 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	395 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	396
cf19a123a5d9 Further integration of the documentation for the sqlite3 module. There's still Gerhard Häring <gh@ghaering.de> parents: 38065 diff changeset	397 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	398 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	399 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	400 \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	401
38169 85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	402 \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	403
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	404 \subsubsection{Introduction}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	405
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	406 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	407
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	408 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	409
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	410 \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	411 \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	412 \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	413 \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	414 \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	415 \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	416 \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	417 \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	418 \end{tableii}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	419
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	420 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	421
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	422 \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	423 \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	424 \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	425 \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	426 \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	427 \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	428 \end{tableii}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	429
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	430 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	431 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	432 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	433 converters.
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	434
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	435 \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	436
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	437 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	438 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	439 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	440 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	441
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	442 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	443 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	444
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	445 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	446 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	447
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	448 \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	449
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	450 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	451 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	452
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	453 \begin{verbatim}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	454 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	455 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	456 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	457 \end{verbatim}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	458
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	459 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	460 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	461 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	462 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	463 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	464 \class{PrepareProtocol}.
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	465
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	466 \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	467
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	468 \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	469
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	470 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	471 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	472
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	473 \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	474
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	475 \begin{notice}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	476 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	477 \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	478 \end{notice}
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 The \module{sqlite3} module has two default adapters for Python's builtin
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	481 \class{datetime.date} and \class{datetime.datetime} types. Now let's suppose we
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	482 want to store \class{datetime.datetime} objects not in ISO representation, but
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	483 as Unix timestamp.
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 \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	486
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	487 \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	488
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	489 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	490 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	491 roundtrip work.
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	492
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	493 Enter converters.
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	494
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	495 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	496 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	497
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	498 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	499
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	500 \begin{notice}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	501 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	502 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	503 \end{notice}
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 \begin{notice}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	506 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	507 \end{notice}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	508
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{verbatim}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	511 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	512 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	513 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	514 \end{verbatim}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	515
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	516 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	517 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	518
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	519 \begin{itemize}
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	520 \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	521 \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	522 \end{itemize}
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 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	525 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	526
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 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	529
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	530 \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	531
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	532 \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	533
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	534 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	535 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	536
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	537 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	538 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	539
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	540 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	541 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	542 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	543
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	544 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	545
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	546 \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	547
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	548 \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	549
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	550 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	551 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	552 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	553 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	554
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	555 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	556 ...}, \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	557 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	558 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	559 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	560 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	561
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	562 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	563 (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	564 \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	565 connections.
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 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	568
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	569 Otherwise leave it at it's default, which will result in a plain "BEGIN"
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	570 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	571 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	572
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	573 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	574 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	575 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	576 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	577
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	578 \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	579
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	580 \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	581
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	582 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	583 \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	584 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	585 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	586 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	587 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	588 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	589
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	590 \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	591
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	592 \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	593
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	594 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	595 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	596
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	597 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	598 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	599
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	600 \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	601
85c828878188 Integrated the rest of the pysqlite reference manual into the Python Gerhard Häring <gh@ghaering.de> parents: 38069 diff changeset	602

Mercurial > cpython-lru_cache-weakref

annotate Doc/lib/libsqlite3.tex @ 38642:3fb38351dd39 legacy-trunk