Project

General

Profile

Download (42.3 KB)

Feature #2847 » sqldict.py

Anchi Cheng, 08/25/2015 11:14 PM

 
#
# COPYRIGHT:
# The Leginon software is Copyright 2003
# The Scripps Research Institute, La Jolla, CA
# For terms of the license agreement
# see http://ami.scripps.edu/software/leginon-license
#
debug = False
"""
sqldict:

This creates a database interface which works pretty much like a
Python dictionary. The data are stored in a sql table.

>>> from sqldict import *
>>> db = SQLDict()

The optional keyword arguments are:
host = "DB_HOST"
user = "DB_USER"
db = "DB_NAME"
passwd = "DB_PASS"
By default, this is in the config.py file

>>> db = SQLDict(host="YourHost")

DEFINE / CREATE A TABLE
-----------------------

presetDefinition = [{'Field': 'id', 'Type': 'int(16)', 'Key': 'PRIMARY', 'Extra':'auto_increment'},
{'Field': 'Name', 'Type': 'varchar(30)'},
{'Field': 'Width', 'Type': 'int(11)'},
{'Field': 'Height','Type': 'int(11)'},
{'Field': 'Binning', 'Type': 'int(11)'},
{'Field': 'ExpTime', 'Type': 'float(10,4)'},
{'Field': 'Dose', 'Type': 'float(10,4)'},
{'Field': 'BeamCurrent', 'Type': 'float'},
{'Field': 'LDButton', 'Type': 'varchar(20)'},
{'Field': 'Mag', 'Type': 'int(11)'},
{'Field': 'PixelSize', 'Type': 'float(10,4)'},
{'Field': 'Defocus', 'Type': 'int(11)'},
{'Field': 'SpotSize', 'Type': 'int(11)'},
{'Field': 'Intensity', 'Type': 'float(10,4)'},
{'Field': 'BShiftX', 'Type': 'float(10,4)'},
{'Field': 'BShiftY', 'Type': 'float(10,4)'},
{'Field': 'IShiftX', 'Type': 'float(10,4)'},
{'Field': 'IShiftY', 'Type': 'float(10,4)'}]

>>> db.createSQLTable('PRESET', presetDefinition)

DEFINE A MEMBER
---------------

Next, define the tables and columns of interest. It is NOT
necessary to define all columns of a particular table, only those
you need.

>>> db.Preset= db.Table('PRESET', [ 'Name', 'Width', 'Height', 'Binning', 'ExpTime', 'Dose',
'BeamCurrent', 'LDButton', 'Mag', 'PixelSize', 'Defocus', 'SpotSize',
'Intensity', 'BShiftX', 'BShiftY', 'IShiftX', 'IShiftY' ])
['focus2', 256, 256, 1, 0.3000, 41.6200, 0,'search', 66000, 0.2994, -2000, 3, 42414.5117, 106.9400, 28.7300, 198.0000, 4542.0000]



This defines a new member Preset of db, which describes the table
PRESET as having the columns 'Name', 'Width', 'Height', 'Binning', 'ExpTime', 'Dose',
'BeamCurrent', 'LDButton', 'Mag', 'PixelSize', 'Defocus', 'SpotSize',
'Intensity', 'BShiftX', 'BShiftY', 'IShiftX', 'IShiftY'

ACCESSING DATA
--------------

>>> db.Preset.Name= db.Preset.Index(['Name'])

This defines an index member to Preset called 'Name', which allows
searching by 'Name'.

>>> db.Preset.NameDesc= db.Preset.Index(['Name'], orderBy = {'fields':('id',),'sort':'DESC'})

This defines an index member to Preset called 'Name', which allows
searching by 'Name'. Also the result is sorted by id in reverse order.
Note: The default value of 'sort' is 'ASC'


Accessing your data is very similar to using dictionaries. To
retrieve information:

>>> db.Preset.Name['focus'].fetchone()

Note that the [] (__getitem__) operation returns a special cursor
with some extended properties; more on that latter.

>>> db.Preset.NameMag= db.Preset.Index(['Name', Mag'])

SQL equivalent
Make your own class to directly load and store
into the database. Assuming a pre-defined Preset object:

>>> class Preset(ObjectBuilder):
... table = "PRESET"
... columns = [ 'Name', 'Defocus', 'Dose', 'Mag' ]
... indices = [ ('Name', ['Name'], {'orderBy':{'fields':('id',)}}),
... ('NameMag', ['Name', 'Mag']) ]
...

>>> myPreset = Preset().register(db)



p2 = Preset('exposure', -2000, 0.34565, 66000)

INSERT
------

Data are inserted as a list of dictionaries:

>>> myPreset.insert([p2.dumpdict()])

OR

>>> db.Preset.insert([p2.dumpdict()])

Note: Each insert returns the last inserted ID.

UPDATE
------

Data to update are define in a dictionary. The keys from this
dictionary must match the SQL table column names.

>>> db.Preset.NameMag['focus2', 66000 ] = {'Defocus': -200}

OR

>>> myPreset.NameMag['focus2', 66000 ] = {'Defocus': -200, 'Mag': 66000 }


DELETE
------

>>> del myPreset.Name['exposure']

OR

>>> del db.Preset.Name['exposure']

"""


import sys
import sqlexpr
import copy
import sqldb
import string
import datetime
import re
import numpy
import math
import MySQLdb.cursors
from types import *
import newdict
import data
import sinedon
import pyami.mrc
import os
import dbconfig
import cPickle
from pyami import weakattr

class SQLDict(object):

"""SQLDict: An object class which implements something resembling
a Python dictionary on top of an SQL DB-API database."""

def __init__(self, **kwargs):
"""
Create a new SQLDict object.
db: an SQL DB-API database connection object.
The optional keyword arguments are:
host = "DB_HOST"
user = "DB_USER"
db = "DB_NAME"
passwd = "DB_PASS"
"""
if 'port' in kwargs:
kwargs['port'] = int(kwargs['port'])
try:
self.db = sqldb.connect(**kwargs)
self.connected = True
except Exception,e:
self.db = None
self.connected = False
self.sqlexception = e
raise

def ping(self):
if self.db.stat() == 'MySQL server has gone away':
self.db = sqldb.connect(**self.db.kwargs)

def connect_kwargs(self):
return self.db.kwargs

def isConnected(self):
return self.connected

def sqlException(self):
return self.sqlexception

def __del__(self): self.close()

def close(self):
try: self.db.close()
except: pass

def __getattr__(self, attr):
# Get any other interesting attributes from the base class.
return getattr(self.db, attr)

def Table(self, table, columns=[]):
"""
Add a new Table member.
Usage: db.Table(tablename, columns)
Where: tablename = name of table in database
columns = tuple containing names of columns of interest
"""
return _Table(self.db, table, columns)

def createSQLTable(self, table, definition):
"""
>>> CreateTable('PEOPLE',
[{'Field': 'id', 'Type': 'int(16)', 'Key': 'PRIMARY', 'Extra':'auto_increment'},
{'Field': 'Name', 'Type': 'VARCHAR(50)'}])
"""
return _createSQLTable(self.db, table, definition)

def diffSQLTable(self, table, data_definition):
"""
Differences beetween Data table structure and Data Class
"""
diff = _diffSQLTable(self.db, table, data_definition)
return diff.diffTable()

def multipleQueries(self, queryinfo, readimages=True):
"""
Execute a list of queries, it will return a list of dictionaries
"""
return _multipleQueries(self.db, queryinfo, readimages)

def delete(self, queryinfo):
# should be just a single object for now
info = queryinfo.popitem()[1]
print 'INFO', info
tablename = info['class'].__name__
print 'TABLENAME', tablename
where = info['where'].popitem()
where = '%s = %d' % where
print 'WHERE', where
query = str(sqlexpr.Delete(tablename, where))
print 'QUERY', query
self.db.ping()
cur = self.db.cursor(cursorclass=MySQLdb.cursors.DictCursor)
cur.execute(query)

class _Table:

"""Table handler for a SQLDict object. These should not be created
directly by user code."""

def __init__(self, db, table, columns=[]):

"""Construct a new table definition. Don't invoke this
directly. Use Table method of SQLDict instead."""

self.db = db
self.table = table
self.columns = columns
self.fields = tuple(map(lambda col: sqlexpr.Field(self.table,col), self.columns))

def select(self, where=None, orderBy=None):

"""Execute a SELECT command based on this Table and Index. The
required argument i is a tuple containing the values to match
against the index columns. A string containing a WHERE clause
should be passed along, but this is technically optional. The
WHERE clause must have the same number of value placeholders
(?) as there are values in i. Returns a _Cursor object for the
matched rows.

Usually you don't need to call select() directly; this is done
by the indexing operations (Index.__getitem__)."""

if orderBy is not None:
orderBy = copy.deepcopy(orderBy)
orderBy['fields'] = map(lambda id: sqlexpr.Field(self.table, id), orderBy['fields'])

c = self.cursor()
if self.columns:
q = sqlexpr.Select(items=self.fields, table=self.table, where=where, orderBy=orderBy).sqlRepr()
else:
q = sqlexpr.SelectAll(self.table, where=where, orderBy=orderBy).sqlRepr()
c.execute(q)
return c

def insert(self, v=[], force=0):
"""Insert a list of dictionaries into a SQL table. If the data
already exist, they won't be inserted again in the table,
unless force is true. The function returns the last inserted row
id for a new insert or an existing primary key."""
c = self.cursor()
db_name = self.db.kwargs['db']

result = None

if not force:
nullfields = []
equalpairs = []
for key,value in v[0].items():
if key[:3] == 'MRC':
continue
key = sqlexpr.Field(self.table, key)
if value is None:
nullfields.append((key, value))
else:
equalpairs.append((key, value))

whereFormat = sqlexpr.AND_EQUAL(equalpairs)
whereFormatNULL = sqlexpr.AND_IS(nullfields)

if whereFormatNULL:
if whereFormat:
whereFormat = sqlexpr.AND(whereFormatNULL,whereFormat)
else:
whereFormat = whereFormatNULL

qsel = sqlexpr.SelectAll(self.table, where=whereFormat).sqlRepr()
## print qsel
try:
c.execute(qsel)
result=c.fetchone()
except:
result = None

if force or not result:
q = sqlexpr.Insert(self.table, v).sqlRepr()
q = 'use %s; %s' % (db_name,q)
if debug:
print q
c.execute(q)
## try the new lastrowid attribute first,
## then try the old insert_id() method
try:
insert_id = c.lastrowid
except:
insert_id = c.insert_id()
return insert_id

else:
try:
return result['DEF_id']
except KeyError:
qkey = sqlexpr.Show('INDEX', self.table).sqlRepr()
c.execute(qkey)
keys = c.fetchall()
prikeyfield = None
for key in keys:
if key['Key_name']=='PRIMARY':
prikeyfield = key['Column_name']
break;
if prikeyfield:
return result[prikeyfield]
else:
raise KeyError('No Primary Key found')

def update(self, v, WHERE=''):
"""Like select(), only it does an UPDATE. It is not usually
necessary to call this method directly, as it is done by
the indexing operations (Index.__setitem__)."""
q = sqlexpr.Update( self.table, v, where=WHERE).sqlRepr()
c = self.cursor()
c.execute(q)
return c

def delete(self, i=(), WHERE=''):
"""Like select(), only it does an DELETE. It is not usually
necessary to call this method directly, as it is done by
the indexing operations (Index.__delitem__)."""
q=sqlexpr.Delete(self.table, where=WHERE).sqlRepr()
c = self.cursor()
c.execute(q)
return c

def load(self, v):
return v

def getall(self, where=1, orderBy=None):
q = sqlexpr.Select(items=self.fields, table=self.table, where=where, orderBy=orderBy).sqlRepr()
c = self.cursor()
c.execute(q)
return c.fetchall()

def describe(self):
q = sqlexpr.Describe(self.table).sqlRepr()
c = self.cursor()
c.execute(q)
return c.fetchall()


def Index(self, indices=[], **kwargs):

"""Create an index definition for this table.

Usage: db.table.Index(indices)
Where: indices = tuple or list of column names to key on
orderBy = optional ORDER BY clause.
WHERE = optional WHERE clause.
WHERE not implemented YET...

"""

return _Index(self, indices, **kwargs)

def cursor(self):
"""Returns a new _Cursor object which is load-aware and
otherwise behaves normally."""
return _Cursor(self.db, self.load, self.columns)


class _Cursor:

"""A subclass (shadow class?) of a cursor object which knows how to
load the tuples returned from the database into a more interesting
object."""

def __init__(self, db, load, columns):
db.ping()
self.cursor = db.cursor(cursorclass=MySQLdb.cursors.DictCursor)
self.columns = columns
self.load = load
self.db = db

def fetchone(self):
"""Fetch one object from current cursor context."""
x = self.cursor.fetchone()
if x: return self.load(x)
else: return x # only load if we really got something

def fetchall(self):
"""Fetch all objects from the current cursor context."""
return map(self.load, self.cursor.fetchall())

def fetchmany(self, *size):
"""Fetch many objects from the current cursor context.
Can specify an optional size argument for number of rows."""
return map(self.load, apply(self.cursor.fetchmany, size))

def __getattr__(self, attr):
return getattr(self.cursor, attr)

def __2dict(self,keys,values):
"""Returns a dictionary from a list or tuple of keys and values."""
if (values):
return dict(zip(keys,values))
else:
return {}


class _Index:

"""
Index handler for a _Table object.
"""

def __init__(self, table, indices, **kwargs):
self.table = table
self.kwargs= kwargs
if indices:
ind = map(lambda id: sqlexpr.Field(self.table.table, id), indices)
else:
ind=None
self.fields = ind

def __getattr__(self, attr):
c = self.table.select(where=1, **self.kwargs)
return getattr(c, attr)

def __setitem__(self, i=(), v=None):
"""Update the item in the database matching i
with the value v."""
if type(i) == ListType: i = tuple(i)
elif type(i) != TupleType: i = (i,)
if self.fields is not None:
w = sqlexpr.AND_EQUAL(zip(self.fields,i))
else: w=1
self.table.update(v, WHERE=w)

def __getitem__(self, i=()):
"""Select items in the database matching i."""
if type(i) == ListType: i = tuple(i)
elif type(i) != TupleType: i = (i,)
if self.fields is not None:
w = sqlexpr.AND_EQUAL(zip(self.fields,i))
else: w=1
return self.table.select(where=w, **self.kwargs)

def __delitem__(self, i):
"""Delete items in the database matching i."""
if type(i) == ListType: i = tuple(i)
elif type(i) != TupleType: i = (i,)
w = sqlexpr.AND_EQUAL(zip(self.fields,i))
return self.table.delete(i, WHERE=w)

class _multipleQueries:

def __init__(self, db, queryinfo, readimages=True):
self.db = db
self.queryinfo = queryinfo
self.readimages = readimages
#print 'querinfo ', self.queryinfo
self.queries = setQueries(queryinfo)
if debug:
print 'queries ', self.queries
self.cursors = {}
self.execute()

def _cursor(self):
self.db.ping()
return self.db.cursor(cursorclass=MySQLdb.cursors.DictCursor)

def execute(self):
for key,query in self.queries.items():
if isinstance(query, (data.Data,data.DataReference)):
## if we already have a data instance, then there
## is no reason to query for it.
self.cursors[key] = query
continue
c = self._cursor()
try:
## print '-----------------------------------------------'
## print 'query =', query
c.execute(query)
except (MySQLdb.ProgrammingError, MySQLdb.OperationalError), e:
errno = e.args[0]
## some version of mysqlpython parses the exception differently
if not isinstance(errno, int):
errno = errno.args[0]
## 1146: table does not exist
## 1054: column does not exist
if errno in (1146, 1054):
pass
#print 'non-fatal query error:', e
else:
raise
else:
self.cursors[key] = c

def fetchmany(self, size):
cursorresults = {}
for qikey,cursor in self.cursors.items():
## if we already have a data instance, then there
## is no reason to query for it.
if isinstance(cursor, (data.Data,data.DataReference)):
cursorresults[qikey] = cursor
continue
subfetch = cursor.fetchmany(size)
cursorresult = self._format(subfetch, qikey)
cursor.close()
cursorresults[qikey] = cursorresult

return self._joinData(cursorresults)

def fetchall(self):
cursorresults = {}
for qikey,cursor in self.cursors.items():
## if we already have a data instance, then there
## is no reason to query for it.
if isinstance(cursor, (data.Data,data.DataReference)):
cursorresults[qikey] = cursor
continue
subfetch = cursor.fetchall()
cursorresult = self._format(subfetch, qikey)
cursor.close()
cursorresults[qikey] = cursorresult

a = self._joinData(cursorresults)
return a

def uniqueFilter(self, results, key):
if not results or key is None:
return
first = results[0]
keyfield = None
for field in first.keys():
parts = field.split('|')
field_key = parts[-1]
if field_key == key:
keyfield = field
break
if keyfield is None:
return
havedict = {}
filtered = []
for result in results:
if result[keyfield] in havedict:
continue
filtered.append(result)
havedict[result[keyfield]] = None
return filtered

def _joinData(self, cursorresults):
if not cursorresults:
return []

## some cursorresults are actually data.Data instances
def test(obj):
return not isinstance(obj, (data.Data,data.DataReference))
actualresults = filter(test, cursorresults.values())
if actualresults:
numrows = len(actualresults[0])
else:
numrows = 0
all = [{} for i in range(numrows)]

for i in range(numrows):
for qikey, cursorresult in cursorresults.items():
if isinstance(cursorresult, (data.Data,data.DataReference)):
## cursorresult was known before query
all[i][qikey] = cursorresult
elif cursorresult:
## cursorresult was fetched from query
all[i][qikey] = cursorresult[i]
else:
## does this case ever happen?
all[i][qikey] = None

rootlist = []
for d in all:
for qikey,v in d.items():
if self.queryinfo[qikey]['root']:
theroot = v
self._connectData(v, d)
rootlist.append(theroot)

return rootlist

def _format(self, sqlresult, qikey):
"""Convert SQL result to data instances. Create a new data instance
only if it does not exist.
"""
datalist = []
qikeylist = [qikey for i in range(len(sqlresult))]
qinfolist = [self.queryinfo for i in range(len(sqlresult))]
result = map(sql2data, sqlresult, qikeylist, qinfolist)

dataclass = self.queryinfo[qikey]['class']

## keep memo to ensure only creating instance once
memo = {}
for r in result:
memokey = (dataclass, r['DEF_id'])
dbid = r['DEF_id']
dbtimestamp = r['DEF_timestamp']
del r['DEF_id']
del r['DEF_timestamp']

if memokey in memo:
newdata = memo[memokey]
else:
newdata = dataclass()
memo[memokey]=newdata
try:
## this is friendly_update because
## there could be columns that
## are no longer used
newdata.friendly_update(r)
except KeyError, e:
raise

## add pending dbid for now, actual dbid
## after all items are set, otherwise __setitem__
## will reset dbid
newdata.pending_dbid = dbid
newdata.timestamp = dbtimestamp

datalist.append(newdata)
return datalist

def _connectData(self, root, pool):
'''
This connects the individual data instances together.
After connecting, it also reads in data from files.
'''
if root is None:
return

### already done
if root.dbid is not None:
return

dbinfo = self.db.kwargs

needpath = []
for key,value in root.items(dereference=False):
if isinstance(value, data.UnknownData):
target = pool[value.qikey]
root[key] = target
self._connectData(target, pool)
elif isinstance(value, newdict.FileReference):
needpath.append(key)

### find the path
if needpath:
try:
getpath = root.getpath
except AttributeError:
message = '%s object contains file references, needs a getpath() method' % (root.__class__,)
raise AttributeError(message)
imagepath = getpath()
## now set path in FileReferences, read image
for key in needpath:
fileref = root.special_getitem(key, dereference=False)
fileref.setPath(dbconfig.mapPath(imagepath))
if self.readimages:
# replace reference with actual data
root[key] = fileref.read()

## now the object is final, so we can safely set dbid
root.setPersistent(root.pending_dbid)
del root.pending_dbid

class _createSQLTable:

def __init__(self, db, table, definition):
self.db = db
self.table = table
self.definition = definition
self.create()

def _cursor(self):
self.db.ping()
return self.db.cursor(cursorclass=MySQLdb.cursors.DictCursor)

def create(self):
q = sqlexpr.CreateTable(self.table, self.definition).sqlRepr()
c = self._cursor()
if debug:
print q
c.execute(q)
c.close()
self._checkTable()

def formatDescription(self, description):
newdict = {}
newdict['Field'] = description['Field']
if description.has_key('Default'):
newdict['Default'] = description['Default']
if description['Default']=='CURRENT_TIMESTAMP':
newdict['Default'] = None
elif description['Default']=='NULL':
newdict['Default'] = None
else:
newdict['Default'] = None
typestr = description['Type'].upper()
try:
if re.findall('^TIMESTAMP', typestr):
ind = typestr.index('(')
typestr = typestr[:ind]
except ValueError:
pass
newdict['Type'] = typestr
return newdict

def _checkTable(self):
c = self._cursor()
describeTable = _Table(self.db,self.table).describe()

describe=[]
for col in describeTable:
describe.append(self.formatDescription(col))

definition=[]
for col in self.definition:
definition.append(self.formatDescription(col))

addcolumns = [col for col in definition if col not in describe]

for column in addcolumns:
queries = []
column['Null'] = 'YES'
q = sqlexpr.AlterTable(self.table, column, 'ADD').sqlRepr()
queries.append(q)
l = re.findall('^REF\%s' %(sep,),column['Field'])
if l:
q = sqlexpr.AlterTableIndex(self.table, column).sqlRepr()
queries.append(q)
try:
for q in queries:
if debug:
print q
c.execute(q)
except MySQLdb.OperationalError, e:
pass
c.close()


class _diffSQLTable(_createSQLTable):

def __init__(self, db, table, definition):
self.db = db
self.table = table
self.definition = definition

def diffTable(self):
c = self._cursor()
describeTable = _Table(self.db,self.table).describe()

describe=[]
for col in describeTable:
describe.append(self.formatDescription(col))

definition=[]
for col in self.definition:
definition.append(self.formatDescription(col))

## -------- display description from data and from DB -------- ##
##print '--------------------'
##print 'describe\n%s' % (describe,)
##print '--------------------'
##print 'definition\n%s' % (definition,)
##print '--------------------'


for d in definition:
f = d['Field']
for e in describe:
if e['Field']==f:
if d['Default'] is None:
d['Default']=e['Default']
else:
try:
if float(d['Default'])==float(e['Default']):
d['Default']=e['Default']
except:
pass
break

addcolumns = [col for col in definition if col not in describe]
dropcolumns = [{'Field':col} for col in [d['Field'] for d in describe] if col not in [f['Field'] for f in definition]]

queries = []
for column in dropcolumns:
q = sqlexpr.AlterTable(self.table, column, 'DROP').sqlRepr()
queries.append(q)

for column in addcolumns:
column['Null']='YES'
altertype = 'ADD'
if [col for col in describe if col['Field']==column['Field']]:
altertype = 'CHANGE'
q = sqlexpr.AlterTable(self.table, column, altertype).sqlRepr()
queries.append(q)

c.close()
return addcolumns, dropcolumns, queries

class ObjectBuilder:

"""This class lets you build objects for use with SQLDict, and
for other purposes. To use, define a new class, subclassing
ObjectBuilder. Define the following items:

table: Name of table in SQL database.
columns: List of columns in table.
indices: A list of tuples. The first part of the tuple is the name
of the index. The second part is a list of column names.
"""

table = None
columns = []
indices = []

def __init__(self, *args, **kw):
"""
Constructor: Accepts an argument list of values, which are assigned in
the order specified in columns. Also accepts keyword arguments,
where the keys are from columns.
"""

for k in self.columns:
setattr(self, k, None)
for i in range(len(args)):
setattr(self, self.columns[i], args[i])
self.set_keywords(dict=kw)

def __format_indices(self, indices):
nindices=[]
for indice in indices:
if len(indice)<3:
nindices.append(tuple(list(indice)+[{}]))
else:
nindices.append(indice)
return nindices


def set_keywords(self, skim=0, dict={}):
"""
Assign attributes using keyword arguments. If skim=0 (default),
keywords not present in columns raises AttributeError. Otherwise,
the keyword is ignored.
"""
for k, v in dict.items():
if k in self.columns: setattr(self, k, v)
elif not skim: raise AttributeError, k

def __setattr__(self, key, value):
try:
getattr(self, '_set_'+key)(value)
except AttributeError:
self.__dict__[key] = value

def __str__(self):
l0 = "%s(" % self.__class__.__name__
l = []
for k in self.columns:
l.append("%s=%s" % (k, repr(getattr(self, k))))
return string.join([l0, join(l, ','), ')'],'')

def __repr__(self):
r = self.dumpdict()
return "%s( %s )" % (self.__class__.__name__, r)

def dump(self):
"""dump as a tuple."""
l = []
for k in self.columns:
v = getattr(self,k)
l.append(v)
return tuple(l)

def dumpdict(self):
"""dump as a Python dictionary."""
return dict(zip(self.columns, self.dump()))
def register(self, db):
"""Register into database."""
t = db.Table(self.table, self.columns)
# loader = lambda t, s=self.__class__: apply(s, t)
# setattr(t, 'load', loader)
indices = self.__format_indices(self.indices)
for indexname, columns, args in indices:
setattr(t, indexname, t.Index(columns, **args))
return t

#########################################
# Database insert/query tool functions #
#########################################

# default separator is |
# Note: Check Regular Expression
# in unFlatDict function if changed
sep ='|'

def setQueries(queryinfo):
"""
setQueries: Build a list of SQL queries from a queryInfo dictionary.
"""
queries = {}
for key,value in queryinfo.items():
if value['known'] is not None:
## If we already have a data instance, then there
## is no reason to do a query for it.
## To indicate that, just set the query to be
## the instance.
queries[key] = value['known']
elif type(value) is type({}):
select = sqlexpr.selectAllFormat(value['alias'])
query = queryFormatOptimized(queryinfo,value['alias'])
queries[key]="%s %s" % (select, query)
return queries

def queryFormatOptimized(queryinfo,tableselect):
"""
queryFormat: format the 'SQL WHERE' and figure out the tables to join.
"""
sqlquery = ""
sqlfrom = ""
sqljoin = []
sqlwhere = []
optimizedjoinlist = []
optimizedjoinonlist = []
alljoin={}
joinon={}
onjoin={}
alljoinon={}
wherejoin={}
listselect=[]
for key,value in queryinfo.items():
if value['known']:
continue
if type(value) is not type({}):
continue
tableclass = value['class']
a = value['alias']
j = value['join']
r = value['root']
w = value['where']

if r:
sqlfrom = sqlexpr.fromFormat(tableclass, a)
sqlorder = sqlexpr.orderFormat(a)
sqllimit = sqlexpr.limitFormat(value['limit'])

for field,id in j.items():
joinTable = queryinfo[id]
refclass = joinTable['class']
joinfield = refFieldName(tableclass, refclass, field)

## if data to join is already known, then
## we need to convert the join into a where
if queryinfo[id]['known'] is not None:
defid = queryinfo[id]['known'].dbid
w[joinfield] = defid
continue

fieldname = joinFieldName(a, joinfield)
joinonalias = joinTable['alias']
alljoinon[joinonalias] = sqlexpr.joinFormat(fieldname, joinTable)
joinon[joinonalias]=a
onjoin[a]=joinonalias
if not joinonalias in optimizedjoinlist:
optimizedjoinlist.append(joinonalias)

if w:
if not a in optimizedjoinlist:
optimizedjoinlist.append(a)

sqlexprstr = sqlexpr.whereFormat(value)
if sqlexprstr:
sqlwhere.append(sqlexprstr)

if not tableselect in optimizedjoinlist:
optimizedjoinlist.append(tableselect)

for l in optimizedjoinlist:
if joinon.has_key(l):
if not joinon[l] in optimizedjoinlist:
optimizedjoinlist.append(joinon[l])
if not alljoinon[l] in sqljoin:
sqljoin.append(alljoinon[l])
if onjoin.has_key(l):
if not alljoinon[onjoin[l]] in sqljoin:
sqljoin.append(alljoinon[onjoin[l]])

sqljoinstr = ' '.join(sqljoin)
### convert: JOIN ... ON (), JOIN ... ON ()
### to: JOIN ( ... ) ON ( ... AND ...)
reg_ex = 'JOIN[ ]{1,}(.*)[ ]{1,}ON[ ]{1,}\((.*)[ ]{0,}\)'
p = re.compile(reg_ex, re.IGNORECASE)
refjoinlist = []
fieldjoinlist = []
for s in sqljoin:
matches = p.search(s)
if matches is not None:
refjoinlist.append(matches.group(1))
fieldjoinlist.append(matches.group(2))

### comment the following line to use the orginal: JOIN ... ON (), JOIN ... ON ()
if len(sqljoin) > 1:
sqljoinstr = 'JOIN (' + ', '.join(refjoinlist) + ') ON ('+' AND '.join(fieldjoinlist)+')'
if sqlwhere:
sqlwherestr= 'WHERE ' + ' AND '.join(sqlwhere)
else:
sqlwherestr = ''

sqlquery = "%s %s %s %s %s" % (sqlfrom, sqljoinstr, sqlwherestr, sqlorder, sqllimit)
return sqlquery

def joinFieldName (refalias, colname):
"""
join the fieldname with the right alias.
"""
fieldname = "%s.%s" % (sqlexpr.backquote(refalias),sqlexpr.backquote(colname))
return fieldname

def flatDict(in_dict):
"""
This function returns a flat dictionary. For example:
>>> d = { 'BShift':{'X': 45.0, 'Y': 18.0}, 'IShift':{'X': 8.0, 'Y': 6.0}}
>>> flatDict(d)

{'SUBD|IShift|Y': 6.0, 'SUBD|BShift|Y': 18.0, 'SUBD|BShift|X': 45.0, 'SUBD|IShift|X': 8.0}

The keys of the sub-dictionaries concatenate the parent key.

"""

items = {}
try:
keys = in_dict.keys()
except AttributeError:
raise TypeError("Must be a Dictionary")

for key in keys:
value = in_dict[key]
if type(value) is type({}):
d = flatDict(value)
nd={}
# build the new keys
for nk in d:
lfk = ['SUBD',key,nk]
fk= sep.join(lfk)
nd.update({fk:d[nk]})

items.update(nd)
else:
items[key] = value
return items

def unflatDict(in_dict, join):
"""
This function unflat a dictionary. For example:
>>> d = {'SUBD|scope|SUBD|IShift|Y': 6.0, 'SUBD|scope|SUBD|BShift|Y': 18.0, 'SUBD|scope|SUBD|BShift|X': 45.0, 'SUBD|scope|SUBD|IShift|X': 8.0}
>>> unflatDict(d)

{'scope':{ 'BShift':{'X': 45.0, 'Y': 18.0}, 'IShift':{'X': 8.0, 'Y': 6.0}}}
"""
items = {}
try:
keys = in_dict.keys()
except AttributeError:
raise TypeError("Must be a Dictionary")

allsubdicts = {}
for key,value in in_dict.items():
a = key.split(sep)
if a[0] == 'SUBD':
name = a[1]
if not allsubdicts.has_key(name):
allsubdicts[a[1]]=None
elif a[0] != 'ARRAY':
items.update(datatype({key:value},join=join))

for subdict in allsubdicts:
dm={}
for key,value in in_dict.items():
l = re.findall('^SUBD\%s%s' %(sep,subdict,),key)
if l:
s = re.sub('^SUBD\%s%s\%s' %(sep,subdict,sep),'',key)
dm.update({s:value})

allsubdicts[subdict]=unflatDict(dm, join)

allsubdicts.update(items)
return allsubdicts

def dict2matrix(in_dict):
"""
This function returns a matrix from a dictionary.
Each key from in_dict must have 2 numbers representing [row][colum].
They can be separated by any characters.

{'m|1_1': 1, 'm|1_2': 2, 'm|2_1': 3, 'm|2_2': 4, ..., 'm|i_j':n}
_ _
| |
| 1 2 j |
| |
=> | 3 4 . |
| . |
| i . n |
|_ _|

example:
{'ARRAY|matrix|2_1': -1.24684512082676e-10, 'ARRAY|matrix|2_2': 2.0027370335951399e-10, 'ARRAY|matrix|1_2': 1.2226514813724901e-10, 'ARRAY|matrix|1_1': 1.9444897985776799e-10}
"""

# Get the shape and size of the matrix
ij=[]
for m in in_dict:
i=eval(re.findall('\d+',m)[0])
j=eval(re.findall('\d+',m)[1])
ij.append((i,j))
shape = max(ij)
size = shape[0]*shape[1]

# Build the matrix
matrix = numpy.zeros(shape, numpy.float64)
for m in in_dict:
i=eval(re.findall('\d+',m)[0])-1
j=eval(re.findall('\d+',m)[1])-1
matrix[i][j]=in_dict[m]

return matrix
def matrix2dict(matrix, name=None):
"""
This function returns a dictionary which represents a matrix.
matrix must be at least 2x1 or 1x2 numpy arrays.

_ _
| |
| 1 2 j |
| |
| 3 4 . | =>
| . |
| i . n |
|_ _|

{'m|1_1': 1, 'm|1_2': 2, 'm|2_1': 3, 'm|2_2': 4, ..., 'm|i_j':n}

"""

if name is None:
name='m'
try:
if not (matrix.shape >= (1, 1) and len(matrix.shape) > 1):
raise ValueError("Wrong shape: must be at least 2x1 or 1x2")
except AttributeError:
raise TypeError("Must be numpy array")
# force numpy.matrix to numpy.ndarray
matrix = numpy.array(matrix)

d={}
i=0
for row in matrix:
i+=1
j=1
for col in row:
k = sep.join(['ARRAY',name,'%s_%s'%(i,j)])
v = float(matrix[i-1,j-1])
if hasattr(math,'isnan') and math.isnan(v):
# isnan is only an attribute of math at python 2.6 and above
v = None
d[k]=v
j+=1
return d

def object2sqlColumn(key):
"""
Add PICKLE| if value is instance of newdict.AnyObject
"""
return "PICKLE%s%s"%(sep,key,)

def seq2sqlColumn(key):
"""
Add SEQ|if key is tuple or list
"""
return "SEQ%s%s"%(sep,key,)

def sql2data(in_dict, qikey=None, qinfo=None):
"""
This function converts any result of an SQL query to an Data type:

>>> d = {'SUBD|camera|exposure time': 1, 'SUBD|camera|SUBD|binning|x': 1,
'SUBD|camera|SUBD|binning|y': 1, 'SUBD|scope|SUBD|gun shift|y': 1,
'SUBD|scope|SUBD|gun shift|x': 1,
'ARRAY|matrix|1_1': 1.9444897985776799e-10,
'SUBD|scope|dark field mode': 1, 'ARRAY|matrix|2_1': -1.24684512082676e-10,
'SEQ|id': "('manager', 'corrector', 49)",
'ARRAY|matrix|2_2': 2.0027370335951399e-10,
'SUBD|camera|SUBD|camera size|y': 1, 'SUBD|camera|SUBD|camera size|x': 1,
'SUBD|camera|SUBD|dimension|y': 1, 'SUBD|camera|SUBD|dimension|x': 1,
'ARRAY|matrix|1_2': 1.2226514813724901e-10, 'SUBD|camera|SUBD|offset|y': 1,
'SUBD|camera|SUBD|offset|x': 1, 'database filename': 1}
>>> sql2data(d)
{'camera': {'exposure time': 1, 'camera size': {'y': 1, 'x': 1},
'dimension': {'y': 1, 'x': 1}, 'binning': {'y': 1, 'x': 1},
'offset': {'y': 1, 'x': 1}},
'matrix': array([[ 1.94448980e-10, 1.22265148e-10],
[ -1.24684512e-10, 2.00273703e-10]]),
'database filename': 1,
'scope': {'gun shift': {'y': 1, 'x': 1}, 'dark field mode': 1},
'id': ('manager', 'corrector', 49)}
"""
content={}
allsubdicts={}

if None in (qikey,qinfo):
join = None
else:
join = qinfo[qikey]['join']
parentclass = qinfo[qikey]['class']
content = datatype(in_dict, join=join, parentclass=parentclass)

return content

## get rid of this function when field names are converted to have full
## absolute module name
wrong_names = {}
def findWrongName(modulename):
## try cache of wrong names
if modulename in wrong_names:
return wrong_names[modulename]
## try sys.modules (last component of each name)
for sysmodname,sysmod in sys.modules.items():
if sysmodname.split('.')[-1] == modulename:
wrong_names[modulename] = sysmod
return sysmod
return None

def findDataClass(modulename, classname):
if modulename in sys.modules:
mod = sys.modules[modulename]
else:
# remove findWrongName when DB is converted
mod = findWrongName(modulename)
if mod is None:
raise RuntimeError('Cannot find class %s. Module %s not loaded.' % (classname, modulename))
try:
cls = getattr(mod, classname)
except:
return None
return cls

def datatype(in_dict, join=None, parentclass=None):
"""
This function converts a specific string or a SQL type to
a python type.
"""
content={}
allarrays={}
subditems = {}
for key,value in in_dict.items():
a = key.split(sep)
a0 = a[0]
if a0 == 'ARRAY':
name = a[1]
if not allarrays.has_key(name):
allarrays[name]=None
elif a0 == 'SEQ':
if value is None:
content[a[1]] = None
else:
try:
content[a[1]] = eval(value)
except SyntaxError:
content[a[1]] = None
elif a0 == 'PICKLE':
## contains a python pickle string,
## convert it to newdict.AnyObject
try:
value = value.tostring()
except AttributeError:
pass
try:
ob = cPickle.loads(value)
except:
ob = None
content[a[1]] = newdict.AnyObject(ob)
elif a0 == 'MRC':
## set up a FileReference, to be used later
## when we know the full path
if value is None:
content[a[1]] = None
else:
content[a[1]] = newdict.FileReference(value, pyami.mrc.read)
elif a0 == 'REF':
fieldname = a[-1]
tablename = a[-2]
# By default, references are to the current database.
# An extra parameter can indicate a different database.
if len(a) == 4:
modulename = a[-3]
else:
modulename = parentclass.__module__
if value == 0 or value is None:
### NULL reference
content[fieldname] = None
elif fieldname in join:
## referenced data is part of result
jqikey = join[fieldname]
content[fieldname] = data.UnknownData(jqikey)
else:
## not in result, but create reference
dclassname = tablename
dclass = findDataClass(modulename, dclassname)
## If the data class does not exist, then this column should be ignored
if dclass is None:
continue
# host and name should come from parent object
content[fieldname] = data.DataReference(dataclass=dclass, dbid=value)
elif a0 == 'SUBD':
subditems[key] = value
else:
content[key]=value

# build dictionaries
allsubdicts=unflatDict(subditems, join)
content.update(allsubdicts)

for matrix in allarrays:
dm={}
for key,value in in_dict.items():
l = re.findall('^ARRAY\%s%s' %(sep,matrix,),key)
if l:
dm.update({key:value})
allarrays[matrix]=dict2matrix(dm)

content.update(allarrays)
return content

def sqltype(o):
return _sqltype(type(o))

def _sqltype(t):
"""
Convert a python type to an SQL type
"""
if t is str:
return "TEXT"
elif issubclass(t, float):
return "DOUBLE"
elif t is bool:
return "TINYINT(1)"
elif issubclass(t, (int,long)):
return "INT(20)"
elif t is datetime.datetime:
return "TIMESTAMP"
elif t is datetime.date:
return "DATE"
else:
return None

def refFieldName(tableclass, refclass, key):
refmodule = refclass.__module__

#### XXX remove the following line when absolute modules names are
#### considered final:
refmodule = refmodule.split('.')[-1]

tablename = refclass.__name__
#### XXX fix following when absolute modules names are
#### considered final:
tablemodule = tableclass.__module__.split('.')[-1]
parts = ['REF']
if tablemodule != refmodule:
parts.append(refmodule)
parts.extend([tablename, key])
colname = sep.join(parts)
return colname

def keyMRC(name):
return sep.join(['MRC', name])

def saveMRC(object, name, path, filename, thumb=False):
"""
Save numpy array to MRC file and replace it with filename
"""
d={}
k = keyMRC(name)
fullname = dbconfig.mapPath(os.path.join(path,filename))
if object is None or isinstance(object, newdict.FileReference):
## either there is no image data, or it is already saved
pass
else:
#print 'saving MRC', fullname
pyami.mrc.write(object, fullname)

d[k] = filename
return d

def subSQLColumns(value_dict, data_instance):
columns = []
row = {}
for key, value in value_dict.items():
value_type = type(value)

result = type2column(key, value, value_type, data_instance)
if result is not None:
columns.append(result[0])
row.update(result[1])
continue

result = type2columns(key, value, value_type, data_instance)
if result is not None:
columns += result[0]
row.update(result[1])
continue

return columns, row

def dataSQLColumns(data_instance, fail=True):
columns = []
row = {}
# default columns
columns.append({
'Field': 'DEF_id',
'Type': 'int(16)',
'Key': 'PRIMARY',
'Extra':'auto_increment',
})
columns.append({
'Field': 'DEF_timestamp',
'Type': 'timestamp',
'Key': 'INDEX',
'Index': ['DEF_timestamp']
})

if hasattr(data_instance, "timestamp") and data_instance.timestamp is not None:
row['DEF_timestamp'] = data_instance.timestamp

type_dict = dict(data_instance.typemap())

for key, value in data_instance.items(dereference=False):
try:
value_type = type_dict[key]
except KeyError:
raise ValueError, value_type.__name__

result = type2column(key, value, value_type, data_instance)
if result is not None:
columns.append(result[0])
row.update(result[1])
continue

result = type2columns(key, value, value_type, data_instance)
if result is not None:
columns += result[0]
row.update(result[1])
continue

if fail is True:
raise ValueError, value_type.__name__
else:
print "ERROR", value_type.__name__

return columns, row

def type2column(key, value, value_type, parentdata):
column = {}
row = {}
sql_type = _sqltype(value_type)
if sql_type is not None:
# simple types
column['Field'] = key
column['Type'] = sql_type
### index all bools
if column['Type'] == 'TINYINT(1)':
column['Key'] = 'INDEX'
row[key] = value
else:
try:
if issubclass(value_type, (sinedon.data.Data, sinedon.data.DataReference)):
# data.Data reference
tableclass = parentdata.__class__
field = refFieldName(tableclass, value_type, key)
column['Field'] = field
column['Type'] = 'INT(20)'
column['Key'] = 'INDEX'
column['Index'] = [column['Field']]
if value is None:
row[field] = None
else:
row[field] = value.dbid
elif issubclass(value_type, newdict.AnyObject):
field = object2sqlColumn(key)
column['Field'] = field
column['Type'] = 'LONGBLOB'
row[field] = cPickle.dumps(value.o, cPickle.HIGHEST_PROTOCOL)
else:
return None
except TypeError:
return None

column['Null'] = 'YES'
if not ('TEXT' in column['Type'] or 'BLOB' in column['Type']):
column['Default'] = 'NULL'
if column['Type'] == 'TINYINT(1)':
column['Default'] = '0'
return column, row

def type2columns(key, value, value_type, parentdata):
if value_type is newdict.DatabaseArrayType:
if value is None:
column_dict = value_dict = {}
else:
column_dict = value_dict = matrix2dict(value, key)
elif value_type is newdict.MRCArrayType:
if value is None:
column_dict = {keyMRC(key): ''}
value_dict = {keyMRC(key): None}
else:
filename = parentdata.filename()
path = parentdata.mkpath()
column_dict = value_dict = saveMRC(value, key, path, filename)
elif value_type is dict:
# python dict
if value is None:
column_dict = value_dict = {}
else:
column_dict = value_dict = flatDict({key: value})
elif value_type in (tuple, list):
# python sequences
if value is None:
column_dict = value_dict = {}
else:
column_dict = value_dict = {seq2sqlColumn(key): repr(value)}
else:
return None
columns, row = subSQLColumns(column_dict, parentdata)
columns.sort()
row.update(value_dict)
return columns, row


if __name__ == '__main__':
data_instance = data.AcquisitionImageData()
columns, row = dataSQLColumns(data_instance)
for column in columns:
field = column['Field']
print field
print column
if field in ('DEF_id', 'DEF_timestamp'):
print
continue
print row[field]
print

(2-2/2)