Ska.DBI

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

• Sqlite and sybase connections are supported.
• Automatic fetching of Ska database account passwords.
• Integration with numpy record arrays.
• Verbose mode to show transaction information.
• Insert method smooths over syntax differences between sqlite and sybase.

Functions

Ska.DBI._denumpy(x)

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=None, server=None, user=None, passwd=None, database=None, numpy=True, autocommit=True, verbose=False, authdir='/proj/sot/ska/data/aspect_authorization', **kwargs)

Bases: object

Database interface class.

Example usage:

db = DBI(dbi='sqlite', server=dbfile, numpy=False, verbose=True)
db = DBI(dbi='sybase', server='sybase', user='aca_ops', database='aca')
db = DBI(dbi='sybase')   # Use defaults (same as above)

Parameters:

• dbi – Database interface name (sqlite, sybase)
• server – Server name (or file name for sqlite)
• user – User name (optional)
• passwd – Password (optional). Read from aspect authorization if required and not supplied.
• database – Database name for sybase (default = SKA_DATABASE env. or package default ‘aca’).
• 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()

Commit transactions

execute(expr, vals=None, commit=None)

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)

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)

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)

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)

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