dbms Package

dbms Package

DBMS - DataBases Made Simpler is a database adapter that wraps and provides uniform interface to other DB API compliant adapters.

It manages connections, doing automatic imports and providing a uniform connection method to most common databases. Implements DictCursor, NamedTupleCursor and OrderedDictCursor.

It currently supports IBM DB2, Firebird, MSSQL Server, MySQL, Oracle, PostgreSQL, SQLite and ODBC connections.

connect Module

Create connection objects

The dbms.connect.Connection object provides a uniform DB API 2.0 interface to the underlying database connection objects.

The different database adapters are only imported in the factory functions so they are not required unless an adapter is actually used. A reference to the adapter is saved in the connection’s interface variable.

class dbms.connect.Connection(connection, interface, dbname=None)

Bases: object

DBI 2.0 Compliant Connection object. :param connection: Connection of underlying adapter. Saved as _connection

and any methods or attributes not handled locally are delegated to this.

Parameters:

• interface – Reference to the underlying adapter. e.g. cx_Oracle, ibm_db_dbi, pymssql...
• dbname – name of connected database or name of database file

In addition to the

cursor(cursorType=<class 'dbms.cursors.DictCursor'>, **kwargs)

Return new Cursor object.

dbname = None

database name

inspect = None

get names of database objects

interface = None

reference to adapter (cx_Oracle, ibm_db_dbi...)

loggedCursor(cursorType=<class 'dbms.cursors.DictCursor'>, **kwargs)

Return new logged cursor. By default queries are logged to the configuration database and are viewable from dbms.logger.view() and dbms.logger.search().

paramHelp

Print help on this interface’s parameter style.

placeholder = None

placeholder for variable replacement

probe = None

get detailed information of database schemata

dbms.connect.db2(user, password, database, host='localhost', **kwargs)

Return a connection to IBM DB2. Adapter ibm_db_dbi is required.

dbms.connect.firebird(user, password, database, host='localhost', port=3050, **kwargs)

Return a connection to Firebird. Adapter fdb is required.

dbms.connect.mssql(user, password, database, host='localhost', port=1433, **kwargs)

Return a connection to an MS SQL Server database. Adapter pymssql is required.

dbms.connect.mysql(user, password, database='mysql', host='localhost', port=3306, **kwargs)

Return a connection to a MySQL database. Either MySQLdb or msql.connector are required.

dbms.connect.odbc(user, password, database, host='localhost', port=1433, driver='SQL Server', **kwargs)

Return a connection to an ODBC database. Adapter pyodbc is required.

dbms.connect.oracle(user, password, database, host=None, port=1521, **kwargs)

Return a connection to an Oracle database. Adapter cx_Oracle is required.

dbms.connect.postgres(user, password, database='postgres', host='localhost', port=5432, **kwargs)

Return a connection to a PostgreSQL database. Adapter psycopg2 is required.

dbms.connect.sqlite(database, **kwargs)

Return connecton to SQLite database.

cursors Module

Defines four cursor types that only differ in the data type of the record set returned. They all wrap and delegate to the adapter’s actual cursor stored in the _cursor attribute.

The default cursor is DictCursor because it offers the most flexibility.

class dbms.cursors.Cursor(*args, **kwargs)

Bases: object

Basic cursor object that returns results as list. Designed as the base class for the dictionary type cursors. But can also be used like a standard cursor.

LOWER_CASE = 'lower'

PRESERVE_CASE = 'preserve'

UPPER_CASE = 'upper'

columns(case=None)

Return list of column names.

execute(query, bindvars=())

Prepare and execute a database operation For help on parameters see your connection object’s showParmHelp()

executemany(query, bindvars)

Prepare a database operation and execute against all sequences

fetchall()

Fetch all (remaining) rows of a query result, returning them as a sequence of rows. Note the cursor’s arraysize attribute can affect the performance of this operation.

fetchmany(size=None)

Fetch the next set of rows of a query result, returning a sequence of rows. An empty sequence is returned when no more rows are available.

Parameters:size – The number of rows fetched. If it is not given, the

cursor’s arraysize attribute is used.

fetchone()

Fetch the next row of a query result, returning a single sequence, or None if no more data.

next()

Iterate over result set

selectinto(query, bindvars=())

Run query and return the result. Query must return one and only one row.

showStatement()

Output last statement and bind variables

class dbms.cursors.DictCursor(*args, **kwargs)

Bases: dbms.cursors.Cursor

Cursor that returns records as Record objects. This is the most versatile cursor.

class dbms.cursors.NamedTupleCursor(*args, **kwargs)

Bases: dbms.cursors.Cursor

Cursor that returns records as namedtuples.

class dbms.cursors.OrderedDictCursor(*args, **kwargs)

Bases: dbms.cursors.Cursor

Cursor that returns records OrderedDict. Not as efficient or functional as DictCursor or NamedTupleCursor.

class dbms.cursors.Record(*args)

Bases: list

Row object is a memory optimized object that allows access by: column name row[‘column_name’] attributes row.column_name column index row[3] slicing row[1:4]

Record will be dynamically subclassed as dbms.cursors.Record each time a DictCursor is executed.

copy() → a dict representation of Record

get(k[, d]) → Record[k] if k in Record, else d

has_key(k) → True if Record has key k, else False

items() → list of Record's (key, value) pairs as 2-tuples

iteritems() → iterator over (key, value) items of Record

iterkeys() → iterator over keys of Record

itervalues() → iterator over values of Record

keys() → list of Record's keys

pprint()

Pretty Print record.

classmethod setColumns(*args)

values() → list of Record's values

class dbms.cursors.RecordOD(*args, **kwds)

Bases: collections.OrderedDict

OrderedDict that allows value to be fetched by index.

pprint()

dbms.cursors.iskeyword()

x.__contains__(y) <==> y in x.

probe Module

Probe queries the database to get detailed information about its structure.

If the server type can be determined, the Connection object instantiates Probe, or one of its subclasses, in the Connection.probe attribute.

class dbms.probe.Probe(connection)

Bases: object

Probe database schemata.

There is no universal way to probe a database’s structure. But Probe will work with databases that provide information_schema views. Other database must subclass Probe and return the same column names.

getColumns(table, schema=None)

Return list of table/view columns. [Record(name, data_type, is_nullable, is_pk)]

getDatabases()

Return list of databases.

getFunctions(schema=None)

Return list of functions. [Record(name, schema_name, sys_ind)]

getProcedures(schema=None)

Return list of procedures. [Record(name, schema_name, sys_ind)]

getSchemas()

Return list of schemas. [Record(name, is_connected_db, sys_ind)].

getServerVersion()

Get version of database engine

getTables(schema=None)

Return list of tables. [Record(name, schema_name, sys_ind)]

getViews(schema=None)

Return list of views. [Record(name, schema_name, sys_ind)]

hasSchema = True

class dbms.probe.ProbeDB2(connection)

Bases: dbms.probe.Probe

getColumns(table, schema=None)

Return list of table/view columns. [Record(name, data_type, is_nullable, is_pk)]

getDatabases()

Return list of databases/catalogs.

getFunctions(schema=None)

Return list of functions. [Record(name, schema_name, sys_ind)]

getProcedures(schema=None)

Return list of procedures. [Record(name, schema_name, sys_ind)]

getSchemas()

Return list of schemas. [Record(name, is_connected_db, sys_ind)].

getServerVersion()

Get version of database engine

getTables(schema=None)

Return list of tables. [Record(name, schema_name, sys_ind)]

getViews(schema=None)

Return list of views. [Record(name, schema_name, sys_ind)]

class dbms.probe.ProbeFirebird(connection)

Bases: dbms.probe.Probe

getColumns(table, schema=None)

Return list of table/view columns. [Record(name, data_type, is_nullable, is_pk)]

getDatabases()

Return list of databases/catalogs.

getFunctions(schema=None)

Return list of functions. [Record(name, schema_name, sys_ind)]

getProcedures(schema=None)

Return list of procedures. [Record(name, schema_name, sys_ind)]

getSchemas()

Return list of schemas. [Record(name, is_connected_db, sys_ind)].

getServerVersion()

Get version of database engine

getTables(schema=None)

Return list of tables. [Record(name, schema_name, sys_ind)]

getViews(schema=None)

Return list of views. [Record(name, schema_name, sys_ind)]

hasSchema = False

class dbms.probe.ProbeMSSQL(connection)

Bases: dbms.probe.Probe

getDatabases()

Return list of databases/catalogs.

getServerVersion()

Get version of database engine.

class dbms.probe.ProbeMySQL(connection)

Bases: dbms.probe.Probe

getDatabases()

Return list of databases/catalogs.

getSchemas()

Return list of schemas. [Record(name, is_connected_db, sys_ind)].

class dbms.probe.ProbeOracle(connection)

Bases: dbms.probe.Probe

getColumns(table, schema=None)

Return list of table/view columns. [Record(name, data_type, is_nullable, is_pk)]

getDatabases()

getFunctions(schema=None)

Return list of functions. [Record(name, schema_name, sys_ind)]

getPackageProcedures(package, schema=None)

Return list of functions/procedures in a package. [Record(name, package_name, schema_name)]

getPackages(schema=None)

Return list of packages. [Record(name, schema_name, sys_ind)]

getProcedures(schema=None)

Return list of procedures. [Record(name, schema_name, sys_ind)]

getSchemas()

Return list of schemas. [Record(name, is_connected_db, sys_ind)].

getServerVersion()

Get version of database engine

getTables(schema=None)

Return list of tables. [Record(name, schema_name, sys_ind)]

getViews(schema=None)

Return list of views. [Record(name, schema_name, sys_ind)]

class dbms.probe.ProbePostgres(connection)

Bases: dbms.probe.Probe

getDatabases()

Return list of databases/catalogs.

class dbms.probe.ProbeSQLite(connection)

Bases: dbms.probe.Probe

getColumns(table, schema=None)

Return list of table/view columns. [Record(name, data_type, is_nullable, is_pk)]

getDatabases()

Return list of databases.

getFunctions(schema=None)

Return list of functions. [Record(name, schema_name, sys_ind)]

getProcedures(schema=None)

Return list of procedures. [Record(name, schema_name, sys_ind)]

getSchemas()

Return list of schemas. [Record(name, is_connected_db, sys_ind)].

getServerVersion()

Get version of database engine

getTables(schema=None)

Return list of tables. [Record(name, schema_name, sys_ind)]

getViews(schema=None)

Return list of views. [Record(name, schema_name, sys_ind)]

hasSchema = False

schema Module

class dbms.schema.Inspect(probe)

Bases: object

Inspect returns a simple list of object names. The Connection object instantiates Inspect in the Connection.inspect attribute. The database schemata is cached so if you are executing DDL statements you must call inspect.refresh() to see the changes.

If you need detailed information, rather than a list of names, use Connection.probe instead.

columns(table, schema=None)

databases()

formatName(name, schema=None)

Properly quote identifiers if needed.

functions(schema=None)

procedures(schema=None)

refresh(object_type='tree')

Refresh objects

schemas()

systemTables(schema=None)

systemViews(schema=None)

tables(schema=None)

views(schema=None)

settings Module

The settings module manages the configuration db, saved connections and query logging.

class dbms.settings.Logger

Bases: object

Log queries and view query history.

log(connection, query, params=None, status=None)

search(searchTerms, connection=None)

Search for matches in the query log.

view(connection=None, limit=50, offset=0)

List entries in the query log.

class dbms.settings.Servers

Bases: object

Manage saved connections.

delete(name)

Delete a saved connection.

list()

List saved connections.

open(name)

Open saved connection.

save(name, serverType, **kwargs)

Save connection to config db.

param name:name to save connection as param serverType:  connection factory from dbms.connect (‘oracle’, ‘mysql’, ‘mssql’) param kwargs:arguments to be passed to connection factory (user=’dba’, password=’secret’)

setMasterPassword(value)

Set MasterPassword to enable encryption/decryption of stored passwords.

utils Module

Utilities to work with cursors and write queries independent of server implementations.

class dbms.utils.NamedParam(name, value)

Bases: dbms.utils.Param

Named parameter to use with formatQuery()

class dbms.utils.Param(value)

Bases: object

Parameter to use with formatQuery()

dbms.utils.createInsert(cur, table, paramstyle=None)

Create Insert Statement

dbms.utils.cursorToCSV(cur, filename)

Exports cursor to CSV file

dbms.utils.cursorToCursorCopy(src, dest, destTable)

Copy record set from source to destination cursor. This can be used to copy data across different servers. However, their data types need to be similar.

This does not perform constraint checks and will fail if you try to insert a record that already exists on the destination table.

dbms.utils.cursorToFixedWidth(cur, filename=None, colWidths=())

Exports cursor to fixed width text. If colWidths are passed in values will be truncated if they exceed that. If colWidths are not passed, they will be calculated based on the first 20 rows and values will not be truncated.

dbms.utils.cursorToJSON(cur, filename=None)

Export cursor to JSON encoded string

dbms.utils.cursorToXLSX(cur, filename, exportQuery=True)

Exports cursor to Excel file. Requires xlswriter.

dbms.utils.cursorToXML(cur, filename, prettyprint=True, exportQuery=True)

Exports cursor to XML file

dbms.utils.formatQuery(chunks, paramstyle)

Return formatted query and parameter list

chunks is an array of query pieces that are either text or instance of Param class. Usage: query = (‘SELECT * FROM people WHERE status = ‘,

Param(‘A’), ‘ AND last_name LIKE ‘, NamedParam(‘last_name’, ‘Smith%’), ‘ ORDER BY last_name, first_name’)

sql, params = formatQuery(query, ‘named’) Adapted from: http://code.activestate.com/recipes/278612-single-parameters-style-for-db-api-modules/