Opening and Closing a Database

 cx = sqlite.connect(database, mode=0755, converters={}, autocommit=0,                     encoding=None, timeout=None, command_logfile=None) 

Opens a SQLite connection to database and returns a connection object to cx.

The mode argument is currently ignored but may be used in the future to specify the file mode with which the database file is opened.

The converters argument can be used to specify mappings from SQL data types using Python conversion functions.

Set autocommit to 1 if you want PySQLite to commit each SQL command immediately when executed, rather than the default behavior of batching groups of INSERT, UPDATE, and DELETE statements into a single transaction.

The encoding argument allows you to specify the encoding to be used on Unicode strings. Its value can be noneif only ASCII characters are to be acceptedor utf-8.

The default timeout value of None means that if the database file is locked, SQLite will return an error immediately. Setting a value in seconds instructs the database engine to keep trying to obtain a lock for the given amount of time.

Use command_logfile with a file object argument to specify a file to which all SQL statements executed through PySQLite will be written.

 cx.close() 

Closes a database connection and rolls back any uncommitted transactions. The connection object is unusable after .close() has been called.

Executing SQL Statements

All SQL statements must be executed through a cursor object.

 cu = cx.cursor() 

Creates a new cursor object for connection cx.

 cu.execute(sql) cu.execute(sql, args) 

Passes sql to SQLite for execution. Formatted strings are permitted in sql using the standard Python format codes. Unsafe characters do not require delimiting if passed as format arguments.

 cx.commit() 

Issues a COMMIT TRANSACTION command to store any unsaved changes to the database. A .commit() operation is always required to save changes when autocommit is off.

 cx.rollback() 

Issues a ROLLBACK TRANSACTION command to reject any unsaved changes.

 numrows = cu.rowcount 

Following an operation that changes the database, cu.rowcount returns the number of affected rows. Following a SELECT statement, cu.rowcount returns the number of records in the dataset.

 row = cu.fetchone() 

Fetches the next data record and advances the cursor to the next row. Returns None if there are no more rows to select.

 rows = cu.fetchmany(num) 

Fetches the next num data records and advances the cursor row number by num.

 rows = cu.fetchall() 

Fetches all remaining data records from a cursor.

 rownum = cu.rownumber 

Returns the current cursor row position.

Setting Data Type Mappings

The default mappings from SQLite data types to Python are as shown in Chapter 9, "The Python Interface," in Table 9.2.

 cu.execute("-- types col1, col2, ...") 

Specifies the expected data types for col1, col2, and so forth on the next query executed on cursor cu.