ska_dbi

ska_dbi provides simple methods for database access and data insertion. Features:

  • Sqlite connections are supported.

  • Integration with numpy record arrays.

  • Verbose mode to show transaction information.

Functions

ska_dbi.DBI._denumpy(x)[source]

Try using the numpy.tolist() to convert to native python type. DBI’s can’t typically handle numpy vals.

Classes

class ska_dbi.DBI.DBI(dbi=None, server=None, numpy=True, autocommit=True, verbose=False, **kwargs)[source]

Bases: object

Database interface class.

Example usage:

db = DBI(dbi='sqlite', server=dbfile, numpy=False, verbose=True)
Parameters:

  • dbi – Database interface name (sqlite)

  • server – Server name (or file name for sqlite)

  • user – User name (optional)

  • autocommit – Automatically commit after each transaction. Slower but easier to code.

  • numpy – Return multirow results as numpy.recarray; input vals can be numpy types

  • verbose – Print transaction info

  • authdir – Directory containing authorization files

Return type:

DBI object

commit()[source]

Commit transactions

execute(expr, vals=None, commit=None)[source]

Run self.cursor.execute(expr, vals) with possibility of verbose output and commit.

Multiple commands can by executed by separating them with a semicolon at the end of a line. If vals are supplied they will be applied to each of the commands.

Parameters:

  • expr – SQL expression to execute

  • vals – Values associated with the expression (optional)

  • commit – Commit after executing C{expr} (default = self.autocommit)

Return type:

None

fetch(expr, vals=None)[source]

Return a generator that will fetch one row at a time after executing with args.

Example usage:

for row in db.fetch(expr, vals):
    print row['column']
Parameters:

  • expr – SQL expression to execute

  • vals – Values associated with the expression (optional)

Return type:

Generator that will get one row of database as dict() via next()

fetchall(expr, vals=None)[source]

Fetch all rows after executing args.

Example usage:

rows = db.fetchall(expr, vals)
print rows[1:5]['column']
Parameters:

  • expr – SQL expression to execute

  • vals – Values associated with the expression (optional)

Return type:

All rows of database as numpy.rec.recarray or list of dicts, depending on self.numpy

fetchone(expr, vals=None)[source]

Fetch one row after executing args. This always gets the first row of the SQL query. Use ska_dbi.fetch() to get multiple rows one at a time.

Example usage:

row = db.fetchone(expr, vals)
print row['column']
Parameters:

  • expr – SQL expression to execute

  • vals – Values associated with the expression (optional)

Return type:

One row of database as dict()

insert(row, tablename, replace=False, commit=None)[source]

Insert data row into table tablename.

Parameters:

  • row – Data row for insertion (dict or numpy.record)

  • tablename – Table name

  • replace – If true then replace database record if it already exists

  • commit – Commit insertion (default = self.autocommit)

Return type:

None