| \section{\module{sqlite3} --- |
| DB-API 2.0 interface for SQLite databases} |
| |
| \declaremodule{builtin}{sqlite3} |
| \modulesynopsis{A DB-API 2.0 implementation using SQLite 3.x.} |
| \sectionauthor{Gerhard Häring}{gh@ghaering.de} |
| \versionadded{2.5} |
| |
| SQLite is a C library that provides a lightweight disk-based database |
| that doesn't require a separate server process and allows accessing |
| the database using a nonstandard variant of the SQL query language. |
| Some applications can use SQLite for internal data storage. It's also |
| possible to prototype an application using SQLite and then port the |
| code to a larger database such as PostgreSQL or Oracle. |
| |
| pysqlite was written by Gerhard H\"aring and provides a SQL interface |
| compliant with the DB-API 2.0 specification described by |
| \pep{249}. |
| |
| To use the module, you must first create a \class{Connection} object |
| that represents the database. Here the data will be stored in the |
| \file{/tmp/example} file: |
| |
| \begin{verbatim} |
| conn = sqlite3.connect('/tmp/example') |
| \end{verbatim} |
| |
| You can also supply the special name \samp{:memory:} to create |
| a database in RAM. |
| |
| Once you have a \class{Connection}, you can create a \class{Cursor} |
| object and call its \method{execute()} method to perform SQL commands: |
| |
| \begin{verbatim} |
| c = conn.cursor() |
| |
| # Create table |
| c.execute('''create table stocks |
| (date text, trans text, symbol text, |
| qty real, price real)''') |
| |
| # Insert a row of data |
| c.execute("""insert into stocks |
| values ('2006-01-05','BUY','RHAT',100,35.14)""") |
| \end{verbatim} |
| |
| Usually your SQL operations will need to use values from Python |
| variables. You shouldn't assemble your query using Python's string |
| operations because doing so is insecure; it makes your program |
| vulnerable to an SQL injection attack. |
| |
| Instead, use the DB-API's parameter substitution. Put \samp{?} as a |
| placeholder wherever you want to use a value, and then provide a tuple |
| of values as the second argument to the cursor's \method{execute()} |
| method. (Other database modules may use a different placeholder, |
| such as \samp{\%s} or \samp{:1}.) For example: |
| |
| \begin{verbatim} |
| # Never do this -- insecure! |
| symbol = 'IBM' |
| c.execute("... where symbol = '%s'" % symbol) |
| |
| # Do this instead |
| t = (symbol,) |
| c.execute('select * from stocks where symbol=?', t) |
| |
| # Larger example |
| for t in (('2006-03-28', 'BUY', 'IBM', 1000, 45.00), |
| ('2006-04-05', 'BUY', 'MSOFT', 1000, 72.00), |
| ('2006-04-06', 'SELL', 'IBM', 500, 53.00), |
| ): |
| c.execute('insert into stocks values (?,?,?,?,?)', t) |
| \end{verbatim} |
| |
| To retrieve data after executing a SELECT statement, you can either |
| treat the cursor as an iterator, call the cursor's \method{fetchone()} |
| method to retrieve a single matching row, |
| or call \method{fetchall()} to get a list of the matching rows. |
| |
| This example uses the iterator form: |
| |
| \begin{verbatim} |
| >>> c = conn.cursor() |
| >>> c.execute('select * from stocks order by price') |
| >>> for row in c: |
| ... print row |
| ... |
| (u'2006-01-05', u'BUY', u'RHAT', 100, 35.140000000000001) |
| (u'2006-03-28', u'BUY', u'IBM', 1000, 45.0) |
| (u'2006-04-06', u'SELL', u'IBM', 500, 53.0) |
| (u'2006-04-05', u'BUY', u'MSOFT', 1000, 72.0) |
| >>> |
| \end{verbatim} |
| |
| \begin{seealso} |
| |
| \seeurl{http://www.pysqlite.org} |
| {The pysqlite web page.} |
| |
| \seeurl{http://www.sqlite.org} |
| {The SQLite web page; the documentation describes the syntax and the |
| available data types for the supported SQL dialect.} |
| |
| \seepep{249}{Database API Specification 2.0}{PEP written by |
| Marc-Andr\'e Lemburg.} |
| |
| \end{seealso} |
| |
| |
| \subsection{Module functions and constants\label{sqlite3-Module-Contents}} |
| |
| \begin{datadesc}{PARSE_DECLTYPES} |
| This constant is meant to be used with the \var{detect_types} parameter of the |
| \function{connect} function. |
| |
| Setting it makes the \module{sqlite3} module parse the declared type for each column it |
| returns. It will parse out the first word of the declared type, i. e. for |
| "integer primary key", it will parse out "integer". Then for that column, it |
| will look into the converters dictionary and use the converter function |
| registered for that type there. Converter names are case-sensitive! |
| \end{datadesc} |
| |
| |
| \begin{datadesc}{PARSE_COLNAMES} |
| This constant is meant to be used with the \var{detect_types} parameter of the |
| \function{connect} function. |
| |
| Setting this makes the SQLite interface parse the column name for each column |
| it returns. It will look for a string formed [mytype] in there, and then |
| decide that 'mytype' is the type of the column. It will try to find an entry of |
| 'mytype' in the converters dictionary and then use the converter function found |
| there to return the value. The column name found in \member{cursor.description} is only |
| the first word of the column name, i. e. if you use something like |
| \code{'as "x [datetime]"'} in your SQL, then we will parse out everything until the |
| first blank for the column name: the column name would simply be "x". |
| \end{datadesc} |
| |
| \begin{funcdesc}{connect}{database\optional{, timeout, isolation_level, detect_types, factory}} |
| Opens a connection to the SQLite database file \var{database}. You can use |
| \code{":memory:"} to open a database connection to a database that resides in |
| RAM instead of on disk. |
| |
| When a database is accessed by multiple connections, and one of the processes |
| modifies the database, the SQLite database is locked until that transaction is |
| committed. The \var{timeout} parameter specifies how long the connection should |
| wait for the lock to go away until raising an exception. The default for the |
| timeout parameter is 5.0 (five seconds). |
| |
| For the \var{isolation_level} parameter, please see the \member{isolation_level} |
| property of \class{Connection} objects in section~\ref{sqlite3-Connection-IsolationLevel}. |
| |
| SQLite natively supports only the types TEXT, INTEGER, FLOAT, BLOB and NULL. If |
| you want to use other types you must add support for them yourself. |
| The \var{detect_types} parameter and the using custom \strong{converters} registered with |
| the module-level \function{register_converter} function allow you to easily do that. |
| |
| \var{detect_types} defaults to 0 (i. e. off, no type detection), you can set it |
| to any combination of \constant{PARSE_DECLTYPES} and \constant{PARSE_COLNAMES} to turn type |
| detection on. |
| |
| By default, the \module{sqlite3} module uses its \class{Connection} class for the |
| connect call. You can, however, subclass the \class{Connection} class and make |
| \function{connect} use your class instead by providing your class for the |
| \var{factory} parameter. |
| |
| Consult the section \ref{sqlite3-Types} of this manual for details. |
| |
| The \module{sqlite3} module internally uses a statement cache to avoid SQL parsing |
| overhead. If you want to explicitly set the number of statements that are |
| cached for the connection, you can set the \var{cached_statements} parameter. |
| The currently implemented default is to cache 100 statements. |
| \end{funcdesc} |
| |
| \begin{funcdesc}{register_converter}{typename, callable} |
| Registers a callable to convert a bytestring from the database into a custom |
| Python type. The callable will be invoked for all database values that are of |
| the type \var{typename}. Confer the parameter \var{detect_types} of the |
| \function{connect} function for how the type detection works. Note that the case of |
| \var{typename} and the name of the type in your query must match! |
| \end{funcdesc} |
| |
| \begin{funcdesc}{register_adapter}{type, callable} |
| Registers a callable to convert the custom Python type \var{type} into one of |
| SQLite's supported types. The callable \var{callable} accepts as single |
| parameter the Python value, and must return a value of the following types: |
| int, long, float, str (UTF-8 encoded), unicode or buffer. |
| \end{funcdesc} |
| |
| \begin{funcdesc}{complete_statement}{sql} |
| Returns \constant{True} if the string \var{sql} one or more complete SQL |
| statements terminated by semicolons. It does not verify if the SQL is |
| syntactically correct, only if there are no unclosed string literals and if the |
| statement is terminated by a semicolon. |
| |
| This can be used to build a shell for SQLite, like in the following example: |
| |
| \verbatiminput{sqlite3/complete_statement.py} |
| \end{funcdesc} |
| |
| \begin{funcdesc}{enable_callback_tracebacks}{flag} |
| By default you will not get any tracebacks in user-defined functions, |
| aggregates, converters, authorizer callbacks etc. If you want to debug them, |
| you can call this function with \var{flag} as True. Afterwards, you will get |
| tracebacks from callbacks on \code{sys.stderr}. Use \constant{False} to disable |
| the feature again. |
| \end{funcdesc} |
| |
| \subsection{Connection Objects \label{sqlite3-Connection-Objects}} |
| |
| A \class{Connection} instance has the following attributes and methods: |
| |
| \label{sqlite3-Connection-IsolationLevel} |
| \begin{memberdesc}{isolation_level} |
| Get or set the current isolation level. None for autocommit mode or one of |
| "DEFERRED", "IMMEDIATE" or "EXLUSIVE". See ``Controlling Transactions'', |
| section~\ref{sqlite3-Controlling-Transactions}, for a more detailed explanation. |
| \end{memberdesc} |
| |
| \begin{methoddesc}{cursor}{\optional{cursorClass}} |
| The cursor method accepts a single optional parameter \var{cursorClass}. |
| If supplied, this must be a custom cursor class that extends |
| \class{sqlite3.Cursor}. |
| \end{methoddesc} |
| |
| \begin{methoddesc}{execute}{sql, \optional{parameters}} |
| This is a nonstandard shortcut that creates an intermediate cursor object by |
| calling the cursor method, then calls the cursor's \method{execute} method with the |
| parameters given. |
| \end{methoddesc} |
| |
| \begin{methoddesc}{executemany}{sql, \optional{parameters}} |
| This is a nonstandard shortcut that creates an intermediate cursor object by |
| calling the cursor method, then calls the cursor's \method{executemany} method with the |
| parameters given. |
| \end{methoddesc} |
| |
| \begin{methoddesc}{executescript}{sql_script} |
| This is a nonstandard shortcut that creates an intermediate cursor object by |
| calling the cursor method, then calls the cursor's \method{executescript} method with the |
| parameters given. |
| \end{methoddesc} |
| |
| \begin{methoddesc}{create_function}{name, num_params, func} |
| |
| Creates a user-defined function that you can later use from within SQL |
| statements under the function name \var{name}. \var{num_params} is the number |
| of parameters the function accepts, and \var{func} is a Python callable that is |
| called as the SQL function. |
| |
| The function can return any of the types supported by SQLite: unicode, str, |
| int, long, float, buffer and None. |
| |
| Example: |
| |
| \verbatiminput{sqlite3/md5func.py} |
| \end{methoddesc} |
| |
| \begin{methoddesc}{create_aggregate}{name, num_params, aggregate_class} |
| |
| Creates a user-defined aggregate function. |
| |
| The aggregate class must implement a \code{step} method, which accepts the |
| number of parameters \var{num_params}, and a \code{finalize} method which |
| will return the final result of the aggregate. |
| |
| The \code{finalize} method can return any of the types supported by SQLite: |
| unicode, str, int, long, float, buffer and None. |
| |
| Example: |
| |
| \verbatiminput{sqlite3/mysumaggr.py} |
| \end{methoddesc} |
| |
| \begin{methoddesc}{create_collation}{name, callable} |
| |
| Creates a collation with the specified \var{name} and \var{callable}. The |
| callable will be passed two string arguments. It should return -1 if the first |
| is ordered lower than the second, 0 if they are ordered equal and 1 if the |
| first is ordered higher than the second. Note that this controls sorting |
| (ORDER BY in SQL) so your comparisons don't affect other SQL operations. |
| |
| Note that the callable will get its parameters as Python bytestrings, which |
| will normally be encoded in UTF-8. |
| |
| The following example shows a custom collation that sorts "the wrong way": |
| |
| \verbatiminput{sqlite3/collation_reverse.py} |
| |
| To remove a collation, call \code{create_collation} with None as callable: |
| |
| \begin{verbatim} |
| con.create_collation("reverse", None) |
| \end{verbatim} |
| \end{methoddesc} |
| |
| \begin{methoddesc}{interrupt}{} |
| |
| You can call this method from a different thread to abort any queries that |
| might be executing on the connection. The query will then abort and the caller |
| will get an exception. |
| \end{methoddesc} |
| |
| \begin{methoddesc}{set_authorizer}{authorizer_callback} |
| |
| This routine registers a callback. The callback is invoked for each attempt to |
| access a column of a table in the database. The callback should return |
| \constant{SQLITE_OK} if access is allowed, \constant{SQLITE_DENY} if the entire |
| SQL statement should be aborted with an error and \constant{SQLITE_IGNORE} if |
| the column should be treated as a NULL value. These constants are available in |
| the \module{sqlite3} module. |
| |
| The first argument to the callback signifies what kind of operation is to be |
| authorized. The second and third argument will be arguments or \constant{None} |
| depending on the first argument. The 4th argument is the name of the database |
| ("main", "temp", etc.) if applicable. The 5th argument is the name of the |
| inner-most trigger or view that is responsible for the access attempt or |
| \constant{None} if this access attempt is directly from input SQL code. |
| |
| Please consult the SQLite documentation about the possible values for the first |
| argument and the meaning of the second and third argument depending on the |
| first one. All necessary constants are available in the \module{sqlite3} |
| module. |
| \end{methoddesc} |
| |
| \begin{memberdesc}{row_factory} |
| You can change this attribute to a callable that accepts the cursor and |
| the original row as a tuple and will return the real result row. This |
| way, you can implement more advanced ways of returning results, such |
| as returning an object that can also access columns by name. |
| |
| Example: |
| |
| \verbatiminput{sqlite3/row_factory.py} |
| |
| If returning a tuple doesn't suffice and you want name-based |
| access to columns, you should consider setting \member{row_factory} to the |
| highly-optimized \class{sqlite3.Row} type. \class{Row} provides both |
| index-based and case-insensitive name-based access to columns with almost |
| no memory overhead. It will probably be better than your own custom |
| dictionary-based approach or even a db_row based solution. |
| % XXX what's a db_row-based solution? |
| \end{memberdesc} |
| |
| \begin{memberdesc}{text_factory} |
| Using this attribute you can control what objects are returned for the |
| TEXT data type. By default, this attribute is set to \class{unicode} and |
| the \module{sqlite3} module will return Unicode objects for TEXT. If you want to return |
| bytestrings instead, you can set it to \class{str}. |
| |
| For efficiency reasons, there's also a way to return Unicode objects only |
| for non-ASCII data, and bytestrings otherwise. To activate it, set this |
| attribute to \constant{sqlite3.OptimizedUnicode}. |
| |
| You can also set it to any other callable that accepts a single bytestring |
| parameter and returns the resulting object. |
| |
| See the following example code for illustration: |
| |
| \verbatiminput{sqlite3/text_factory.py} |
| \end{memberdesc} |
| |
| \begin{memberdesc}{total_changes} |
| Returns the total number of database rows that have been modified, inserted, |
| or deleted since the database connection was opened. |
| \end{memberdesc} |
| |
| |
| |
| |
| |
| \subsection{Cursor Objects \label{sqlite3-Cursor-Objects}} |
| |
| A \class{Cursor} instance has the following attributes and methods: |
| |
| \begin{methoddesc}{execute}{sql, \optional{parameters}} |
| |
| Executes a SQL statement. The SQL statement may be parametrized (i. e. |
| placeholders instead of SQL literals). The \module{sqlite3} module supports two kinds of |
| placeholders: question marks (qmark style) and named placeholders (named |
| style). |
| |
| This example shows how to use parameters with qmark style: |
| |
| \verbatiminput{sqlite3/execute_1.py} |
| |
| This example shows how to use the named style: |
| |
| \verbatiminput{sqlite3/execute_2.py} |
| |
| \method{execute()} will only execute a single SQL statement. If you try to |
| execute more than one statement with it, it will raise a Warning. Use |
| \method{executescript()} if you want to execute multiple SQL statements with one |
| call. |
| \end{methoddesc} |
| |
| |
| \begin{methoddesc}{executemany}{sql, seq_of_parameters} |
| Executes a SQL command against all parameter sequences or mappings found in the |
| sequence \var{sql}. The \module{sqlite3} module also allows |
| using an iterator yielding parameters instead of a sequence. |
| |
| \verbatiminput{sqlite3/executemany_1.py} |
| |
| Here's a shorter example using a generator: |
| |
| \verbatiminput{sqlite3/executemany_2.py} |
| \end{methoddesc} |
| |
| \begin{methoddesc}{executescript}{sql_script} |
| |
| This is a nonstandard convenience method for executing multiple SQL statements |
| at once. It issues a COMMIT statement first, then executes the SQL script it |
| gets as a parameter. |
| |
| \var{sql_script} can be a bytestring or a Unicode string. |
| |
| Example: |
| |
| \verbatiminput{sqlite3/executescript.py} |
| \end{methoddesc} |
| |
| \begin{memberdesc}{rowcount} |
| Although the \class{Cursor} class of the \module{sqlite3} module implements this |
| attribute, the database engine's own support for the determination of "rows |
| affected"/"rows selected" is quirky. |
| |
| For \code{SELECT} statements, \member{rowcount} is always None because we cannot |
| determine the number of rows a query produced until all rows were fetched. |
| |
| For \code{DELETE} statements, SQLite reports \member{rowcount} as 0 if you make a |
| \code{DELETE FROM table} without any condition. |
| |
| For \method{executemany} statements, the number of modifications are summed |
| up into \member{rowcount}. |
| |
| As required by the Python DB API Spec, the \member{rowcount} attribute "is -1 |
| in case no executeXX() has been performed on the cursor or the rowcount |
| of the last operation is not determinable by the interface". |
| \end{memberdesc} |
| |
| \subsection{SQLite and Python types\label{sqlite3-Types}} |
| |
| \subsubsection{Introduction} |
| |
| SQLite natively supports the following types: NULL, INTEGER, REAL, TEXT, BLOB. |
| |
| The following Python types can thus be sent to SQLite without any problem: |
| |
| \begin{tableii} {c|l}{code}{Python type}{SQLite type} |
| \lineii{None}{NULL} |
| \lineii{int}{INTEGER} |
| \lineii{long}{INTEGER} |
| \lineii{float}{REAL} |
| \lineii{str (UTF8-encoded)}{TEXT} |
| \lineii{unicode}{TEXT} |
| \lineii{buffer}{BLOB} |
| \end{tableii} |
| |
| This is how SQLite types are converted to Python types by default: |
| |
| \begin{tableii} {c|l}{code}{SQLite type}{Python type} |
| \lineii{NULL}{None} |
| \lineii{INTEGER}{int or long, depending on size} |
| \lineii{REAL}{float} |
| \lineii{TEXT}{depends on text_factory, unicode by default} |
| \lineii{BLOB}{buffer} |
| \end{tableii} |
| |
| The type system of the \module{sqlite3} module is extensible in two ways: you can store |
| additional Python types in a SQLite database via object adaptation, and you can |
| let the \module{sqlite3} module convert SQLite types to different Python types via |
| converters. |
| |
| \subsubsection{Using adapters to store additional Python types in SQLite databases} |
| |
| As described before, SQLite supports only a limited set of types natively. To |
| use other Python types with SQLite, you must \strong{adapt} them to one of the sqlite3 |
| module's supported types for SQLite: one of NoneType, int, long, float, |
| str, unicode, buffer. |
| |
| The \module{sqlite3} module uses Python object adaptation, as described in \pep{246} for this. The protocol to use is \class{PrepareProtocol}. |
| |
| There are two ways to enable the \module{sqlite3} module to adapt a custom Python type |
| to one of the supported ones. |
| |
| \paragraph{Letting your object adapt itself} |
| |
| This is a good approach if you write the class yourself. Let's suppose you have |
| a class like this: |
| |
| \begin{verbatim} |
| class Point(object): |
| def __init__(self, x, y): |
| self.x, self.y = x, y |
| \end{verbatim} |
| |
| Now you want to store the point in a single SQLite column. First you'll have to |
| choose one of the supported types first to be used for representing the point. |
| Let's just use str and separate the coordinates using a semicolon. Then you |
| need to give your class a method \code{__conform__(self, protocol)} which must |
| return the converted value. The parameter \var{protocol} will be |
| \class{PrepareProtocol}. |
| |
| \verbatiminput{sqlite3/adapter_point_1.py} |
| |
| \paragraph{Registering an adapter callable} |
| |
| The other possibility is to create a function that converts the type to the |
| string representation and register the function with \method{register_adapter}. |
| |
| \begin{notice} |
| The type/class to adapt must be a new-style class, i. e. it must have |
| \class{object} as one of its bases. |
| \end{notice} |
| |
| \verbatiminput{sqlite3/adapter_point_2.py} |
| |
| The \module{sqlite3} module has two default adapters for Python's built-in |
| \class{datetime.date} and \class{datetime.datetime} types. Now let's suppose |
| we want to store \class{datetime.datetime} objects not in ISO representation, |
| but as a \UNIX{} timestamp. |
| |
| \verbatiminput{sqlite3/adapter_datetime.py} |
| |
| \subsubsection{Converting SQLite values to custom Python types} |
| |
| Writing an adapter lets you send custom Python types to SQLite. |
| But to make it really useful we need to make the Python to SQLite to Python |
| roundtrip work. |
| |
| Enter converters. |
| |
| Let's go back to the \class{Point} class. We stored the x and y |
| coordinates separated via semicolons as strings in SQLite. |
| |
| First, we'll define a converter function that accepts the string as a |
| parameter and constructs a \class{Point} object from it. |
| |
| \begin{notice} |
| Converter functions \strong{always} get called with a string, no matter |
| under which data type you sent the value to SQLite. |
| \end{notice} |
| |
| \begin{notice} |
| Converter names are looked up in a case-sensitive manner. |
| \end{notice} |
| |
| |
| \begin{verbatim} |
| def convert_point(s): |
| x, y = map(float, s.split(";")) |
| return Point(x, y) |
| \end{verbatim} |
| |
| Now you need to make the \module{sqlite3} module know that what you select from the |
| database is actually a point. There are two ways of doing this: |
| |
| \begin{itemize} |
| \item Implicitly via the declared type |
| \item Explicitly via the column name |
| \end{itemize} |
| |
| Both ways are described in ``Module Constants'', section~\ref{sqlite3-Module-Contents}, in |
| the entries for the constants \constant{PARSE_DECLTYPES} and |
| \constant{PARSE_COLNAMES}. |
| |
| |
| The following example illustrates both approaches. |
| |
| \verbatiminput{sqlite3/converter_point.py} |
| |
| \subsubsection{Default adapters and converters} |
| |
| There are default adapters for the date and datetime types in the datetime |
| module. They will be sent as ISO dates/ISO timestamps to SQLite. |
| |
| The default converters are registered under the name "date" for \class{datetime.date} |
| and under the name "timestamp" for \class{datetime.datetime}. |
| |
| This way, you can use date/timestamps from Python without any additional |
| fiddling in most cases. The format of the adapters is also compatible with the |
| experimental SQLite date/time functions. |
| |
| The following example demonstrates this. |
| |
| \verbatiminput{sqlite3/pysqlite_datetime.py} |
| |
| \subsection{Controlling Transactions \label{sqlite3-Controlling-Transactions}} |
| |
| By default, the \module{sqlite3} module opens transactions implicitly before a Data Modification Language (DML) |
| statement (i.e. INSERT/UPDATE/DELETE/REPLACE), and commits transactions implicitly |
| before a non-DML, non-query statement (i. e. anything other than |
| SELECT/INSERT/UPDATE/DELETE/REPLACE). |
| |
| So if you are within a transaction and issue a command like \code{CREATE TABLE |
| ...}, \code{VACUUM}, \code{PRAGMA}, the \module{sqlite3} module will commit implicitly |
| before executing that command. There are two reasons for doing that. The first |
| is that some of these commands don't work within transactions. The other reason |
| is that pysqlite needs to keep track of the transaction state (if a transaction |
| is active or not). |
| |
| You can control which kind of "BEGIN" statements pysqlite implicitly executes |
| (or none at all) via the \var{isolation_level} parameter to the |
| \function{connect} call, or via the \member{isolation_level} property of |
| connections. |
| |
| If you want \strong{autocommit mode}, then set \member{isolation_level} to None. |
| |
| Otherwise leave it at its default, which will result in a plain "BEGIN" |
| statement, or set it to one of SQLite's supported isolation levels: DEFERRED, |
| IMMEDIATE or EXCLUSIVE. |
| |
| As the \module{sqlite3} module needs to keep track of the transaction state, you should |
| not use \code{OR ROLLBACK} or \code{ON CONFLICT ROLLBACK} in your SQL. Instead, |
| catch the \exception{IntegrityError} and call the \method{rollback} method of |
| the connection yourself. |
| |
| \subsection{Using pysqlite efficiently} |
| |
| \subsubsection{Using shortcut methods} |
| |
| Using the nonstandard \method{execute}, \method{executemany} and |
| \method{executescript} methods of the \class{Connection} object, your code can |
| be written more concisely because you don't have to create the (often |
| superfluous) \class{Cursor} objects explicitly. Instead, the \class{Cursor} |
| objects are created implicitly and these shortcut methods return the cursor |
| objects. This way, you can execute a SELECT statement and iterate |
| over it directly using only a single call on the \class{Connection} object. |
| |
| \verbatiminput{sqlite3/shortcut_methods.py} |
| |
| \subsubsection{Accessing columns by name instead of by index} |
| |
| One useful feature of the \module{sqlite3} module is the builtin \class{sqlite3.Row} class |
| designed to be used as a row factory. |
| |
| Rows wrapped with this class can be accessed both by index (like tuples) and |
| case-insensitively by name: |
| |
| \verbatiminput{sqlite3/rowclass.py} |
| |
| |