MkSQL User's Guide

MkSQL.MkSQLDBI

This module implements a Python DBI version 2 interface (at least, as far as I understand the spec). It has been coded so that from MkSQL.MkSQLDBI import * is safe.

Connections

          from MkSQL.MkSQLDBI import *
          conn = connect('mydb.db')

The connect() function takes one argument (the filename) and returns a Connection object. If the filename does not exist, it will be created. Note that the integrity of a MetaKit file can be destroyed if it is updated by more than one process. To be safe, only one process should have it open, and only one thread should use it.

The other public methods of the Connection are commit() and rollback().

Cursors

          cur = conn.cursor()

Per the Python DBI spec, Connection.cursor() takes no arguments and returns a Cursor object.

          cur.execute(
             "select * from frequents where drinker>:name1 or drinker<:name2", 
             {'name1':'norm', 'name2':'b'} )

When a cursor is first created, the only valid methods are execute(sql, params=None) and executemany(sql, [params1, ...]).

MkSQL uses the named style of parameter passing. If parameters are used (and that is highly recommended), the parameters must be a dictionary.

MkSQLDBI caches the last 50 statements. If a sql string matches one in cache (character for character), the underlying statement will be re-used, which avoids parsing and compiling of the sql (which can be an expensive operation). The other reason for using named parameters (over building an sql string with the variables substituted) is that you do not have to worry about escaping funny characters. You should note, however, that MkSQL will not do type coersion for you.

          row0 = cur.fetchone()
          acouple = cur.fetchmany(size=2)
          therest = cur.fetchall()

Result sets are lists (or something very much like them) of rows (which are tuples, or something very much like them). MkSQL will, if possible, do lazy evaluation. In fact, if you avoid order by your result set will most likely not be a Python list. But that shouldn't matter.

After a query statement has been executed, the following Cursor methods are valid:

fetchone() gets the next row from the result set.
fetchmany(size=1) gets the next slice of size rows from the result set.
fetchall() gets the remainder of the result set.
close() releases the result set.

and the following attributes are valid:

description - the description of the result set.
rowcount - the length of the result set.

The default slice size that fetchmany() will return can be changed by setting the arraysize variable of the cursor.

Per the DBI spec, desription is a list of 7-tuples. However, only the first 2 elements (name and typecode) are meaningful. MetaKit does not limit the width of a string or binary column, so internal size is meaningless. Determining the display size of a column would require materializing the entire result set.

          cur.execute(insert|udpate|delete, params)

After a DML statement has been executed, only the rowcount attribute and close() method are meaningful.

The following Cursor methods are meaningless:

callproc()
nextset()
setinputsizes()
setoutputsizes()

Types

As far as Python is concerned, MetaKit only has four types: integer, double, string and binary (where binary is really just a string with binary contents). The typecodes used in cursor.description() are the obvious SQL equivalents: INTEGER, DOUBLE, VARCHAR and VARBINARY. Since MkSQL uses only the raw MetaKit types (no layering), whatever you code in a create statement will map to one of these four types. The only possibly non-obvious mapping is that all of the date and time types map to VARCHAR. (See SQL Grammar for details).

The DBI spec has 5 types:

STRING: maps to VARCHAR, or a MetaKit String.
NUMBER: can be INTEGER or DOUBLE (MetaKit Int or Double).
BINARY: VARBINARY, or a MetaKit Binary.
DATETIME: VARCHAR, or a MetaKit String.
ROWID: None - rowid is not part of MkSQL's dialect.

In addition, MetaKit has no concept of Null. Instead, any column whose value has not been specified (even in an outer join) gets defaults (an empty string for String or Binary, 0 for Int and 0.0 for Double). So MkSQL cannot return a Null (or rather (None,) as Python DBI specifies), and they can't be used in SQL.

MkSQLDBI exposes the follwing DBI mandated functions:

Date(year, month, day): expects integer args, returns "yyyy-mm-dd". Use 4 digit years.
Time(hour, minute, second): expects integer args, returns "hh:mm:ss". Use military time.
Timestamp(year, month, day, hour, minute, second): expects integer args, returns "yyyy-mm-dd hh:mm:ss" (as above).
DateFromTicks(ticks): expects integer or double arg, returns "yyyy-mm-dd".
TimeFromTicks(ticks): expects integer or double arg, returns "hh:mm:ss".
TimestampFromTicks(ticks): expects integer or double arg, returns "yyyy-mm-dd hh:mm:ss".
Binary(arg): returns arg unchanged.

Referential Integrity

If a MetaKit database has a view called "__tbls", MkSQL will wrap the database with a referential integrity layer. MkSQL will create (and populate) the __tbls view if given a create statement that uses constraints.

          c1 = """\
          create table drinkers (
              name char unique,
              age  integer check "lambda age: age > 17"
          ) """
          c2 = """\
          create table bars (
              name char unique,
              location char
          ) """
          c3 = """\
          create table frequents (
              drinker char foreign key references drinkers ( name ),
              bar     char foreign key references bars ( name ),
              perweek integer check "lambda pw: pw <= 7 ",
              unique ( drinker, bar )
          ) """

MkSQL handles the following constraint types:

UNIQUE constraints: May be specified at the column or table level.
PRIMARY KEY constraints: Treated as an alias for UNIQUE.
FOREIGN KEY constraints: May be specified at column or table level.
CHECK constraints: Column level only. Note the syntax.

If you add RI to a MetaKit db, you should close the connection and reopen it. That way, your connection will hold a reference to the wrapped DB. If you don't do that, the connection will still hold a reference to the "raw" MetaKit db, and each DML statement will be forced to wrap it, at considerable expense.