DBQuery 0.4.1

Set up a database for the example code

$ sqlite3 test.db
sqlite> CREATE TABLE world (id INTEGER, hello VARCHAR);
sqlite> INSERT INTO world VALUES (123, 'hello'), (456, 'another hello');

With this you can use test.db as database in the above examples. Just be sure you call python from the same directory as where the database file is.

SQLiteDB

database, **kwds parameters of the SQLiteDB constructor will be passed on the the SQLite connect function.

PostgreSQL

Accepts either the DSN string or configuration parameters for the Psqycopg2 connect function as keyword parameters.

QueryCursor

Executes the given SQL then returns the curser for direct access. Use within a context. Exiting the context will close the cursor.

For example perform a fetchone:

>>> get_first_name_cursor = db.QueryCursor(
...     "SELECT first_name FROM users where id=?")
>>> with get_first_name_cursor(123) as cursor:
...     print(cursor.fetchone())
...
('Foo',)
>>>

Manipulation

Use this to execute any INSERT, UPDATE and similar queries when the rowcount of the cursor should be returned. It is possible to automatically check the value of the row count by setting the rowcount parameter. If the resulting row count does not match the provided one a ManipulationCheckError will be raised.

This can be used to for example make sure that only one row was updated by a query:

>>> update_user_name = db.Manipulation(
...    "UPDATE users SET first_name=? WHERE id=?", rowcount=1)
>>> with db:  # start a new transaction, does not work with SQLiteDB!
...    update_user_name("new_name", 123)  # roll back if rowcount != 1
...
1
>>>

Select

Returns the result of fetchall(), making it ideal for SELECT queries.

SelectOne

Checks that only one row is returned by the specified query. Returns None otherwise. If the result row contains only one column then only that columns value will be returned:

>>> get_first_name = db.SelectOne(
...     "SELECT first_name FROM users where id=?")
>>> get_first_name(123)
'Foo'
>>>

SelectIterator

Select rows and precess them in chunks. For this purpose SelectIterator requeires a callback function together with the SQL. This callback will at query time be called with a generator which produces all the rows from the query result, directly streamed from the DB, in blocks of specified size (arraysize).

It is possible to specify additional parameters for the callback function, if needed.

>>> def callback(row_generator):
...     for row in row_generator:
...         print(row)
...
>>> get_first_names = db.SelectIterator(
...     "SELECT first_name FROM users", callback)
>>> get_first_names()
('Foo',)
>>>