pwtools.sql.SQLiteDB

class pwtools.sql.SQLiteDB(db_fn, table=None)

Bases: object

Interface class which wraps the sqlite3 module. It abstacts away the connecting to the database and cursor setup and adds some convenience methods.

Examples

>>> db = SQLiteDB('test.db', table='calc')
>>> db.create_table([('a', 'float'), ('b', 'text')])
>>> db.execute("insert into %s ('a', 'b') values (1.0, 'lala')" %db.table)
>>> db.execute("insert into %s ('a', 'b') values (?,?)" %db.table, (2.0, 'huhu'))
>>> # iterator
>>> for record in db.execute("select * from calc"):
...     print(record)
(1.0, u'lala')
(2.0, u'huhu')
>>> # list
>>> print(db.execute("select * from calc").fetchall())
[(1.0, u'lala'), (2.0, u'huhu')]
>>> db.get_list1d("select a from calc")
[1.0, 2.0]
>>> db.get_list1d("select b from calc")
[u'lala', u'huhu']
>>> db.get_array1d("select a from calc")
array([ 1.,  2.])
>>> db.add_column('c', 'float')
>>> db.execute("update calc set c=5.0")
>>> db.get_array("select a,c from calc")
array([[ 1.,  5.],
       [ 2.,  5.]])
>>> # db in memory, attach and query over multiple databases, assume both
>>> # databases have a table named 'calc'
>>> db = SQLiteDB(':memory:')
>>> db.executescript("attach 'foo.db' as foo; attach 'bar.db' as bar;")
>>> db.get_array("select foo.calc.a,bar.calc.b from foo.calc,bar.calc "
... "where foo.calc.idx==bar.calc.idx and foo.calc.c not like '%gohome%'")

Notes

There are actually 2 methods to put entries into the db:

1. Use sqlite3 placeholder syntax. This is recommended. Here, automatic type conversion Python -> sqlite is done by the sqlite3 module. For instance, double numbers (i.e. Python float type) will be correctly stored as double by SQLite default.

   >>> db.execute("insert into calc ('a', 'b') values (?,?)", (1.0, 'lala'))
   

2. Write values directly into sql command. Here all values are actually strings.

   >>> db.execute("insert into calc ('a', 'b') values (1.0, 'lala')")
   >>> db.execute("insert into calc ('a', 'b') values (%e, '%s')" %(1.0, 'lala')")
   

   There are some caveats. For example, the string (‘lala’ in the example) must appear qsingle-quoted in the sqlite cmd to be recognized as such. Also aviod things like “… %s” %str(1.0). This will truncate the float after less then 16 digits and thus store the 8-byte float with less precision!

__init__(db_fn, table=None)

Parameters:

• db_fn (str) – database filename

• table (str, optional) – name of the database table, you can also use set_table() later or the table keyword in all methods which need to know to which database table you’re talking

Methods

add_column(col, sqltype[, table])	Add column col with type sqltype to the header.
add_columns(header[, table])	Convenience function to add multiple columns from header.
attach_column(col, values[, sqltype, table])	Attach (add) a new column named col of sqltype and fill it with values.
commit()	Commit changes to connection.
create_table(header[, table])	Create a table from header.
execute(*args, **kwargs)	This calls self.cur.execute().
executemany(*args, **kwargs)	This calls self.cur.executemany().
executescript(*args, **kwargs)	This calls self.cur.executescript().
fill_column(col, values[, start, extend, ...])	Fill existing column col with values from values, starting from rowid start.
finish()	Commit and close cursor.
get_array(*args, **kwargs)	Return result of self.execute().fetchall() as numpy array.
get_array1d(*args, **kwargs)	Same as get_list1d(), but return numpy array.
get_dict(*args, **kwargs)	For the provided select statement, return a dict where each key is the column name and the column is a list.
get_header([table])	Return the header of the table:
get_list1d(*args, **kwargs)	Shortcut for commonly used functionality.
get_max_rowid([table])	Return max(rowid), which is equal to the number of rows in table.
get_single(*args, **kwargs)	Return single entry from the table.
get_table()	Return string self.table.
has_column(col[, table])	Check if table already has the column col.
has_table(table)	Check if a table named table already extists.
set_table(table)	Set the table name (aka switch to another table).