''' Connect, access, + manipulate external tree data from a remote SQL server or
from an sqlite file. '''
# Date: Nov 7 2013
# Author: Alex Safatli
# E-mail: safatli@cs.dal.ca
import MySQLdb as mysql, sqlite3 as sqllite
from landscape import landscape, choice
from tree import tree
from abc import ABCMeta as abstractclass, abstractmethod
[docs]class DatabaseLandscape(landscape):
''' Abstract the landscape to one comprising a database. '''
__metaclass__ = abstractclass
def _acquireNumberOfTaxa(self):
topo = self.getTree(0).toTopology()
self.leaves = len(topo.getLeaves())
@abstractmethod
def _fetchTreeFromDatabase(self,i): pass
@abstractmethod
def _fetchRearrangementsFromDatabase(self,i): pass
[docs] def getNode(self,i):
self._fetchTreeFromDatabase(self,i)
return super(DatabaseLandscape,self).getNode(self,i)
[docs]class SQLExhaustiveLandscape(DatabaseLandscape):
''' Abstract the landscape to one comprising an SQL database. '''
[docs] def __init__(self,dbobj,aliname):
''' Instantiate this landscape.
:param dbobj: a database object
:type dbobj: a :class:`.database`
:param aliname: the name of the alignment (table in the database)
:type aliname: a string
'''
# Set up fields.
super(SQLLandscape,self).__init__(None,None,False)
self.database = dbobj
self.aliname = aliname
self.queryID = lambda d: dbobj.filterRecords(
'newick','ID=%d' % (d)) # Alias lookup function.
self.queryScr = lambda d: dbobj.filterRecords(
aliname,'TID=%d' % (d))
self.tables = dbobj.getTables()
if not aliname in self.tables:
raise IOError('Could not find alignment table <%s>.' % (aliname))
self._fetchTreeFromDatabase(0) # Get root tree.
self._acquireNumberOfTaxa()
def _fetchTreeFromDatabase(self,i):
# Overrides lookup in graph object first. Looks in database.
if i in self.graph.node: return
entries = self.queryID(i)
if len(entries) == 0: return
entry = entries[0]
# Create tree object.
t = tree(entry[1])
t.setName(i)
self.addTree(t)
# Get relevant score(s).
entry = self.queryScr(i)[0]
ml,p = entry[2:4]
t.score = (float(ml),float(p))
def _fetchRearrangementsFromDatabase(self,i):
moves = self.database.filterRecords('operations','TID=%d'%(i))
return [(int(x),int(y),_,int(z)) for x,y,_,z in moves]
def _applyRearrangement(self,move):
e,v,o = move[1],move[-1],move[2]
self.graph.add_edge(e,v)
self.getTree(v).origin = o
[docs] def exploreRandomTree(self,i):
node = self.getNode(i)
if (node['explored']): return None
moves = self._fetchRearrangementsFromDatabase(i)
while (len(moves) > 0):
move = choice(moves)
moves.remove(move)
j = move[-1]
if j in self.graph.node and self.graph.has_edge(i,j):
continue
self._fetchTreeFromDatabase(j)
self._applyRearrangement(move)
self.getEdge(i,j)['weight'] = self.defaultWeight
return j
node['explored'] = True
[docs] def getDatabaseNode(self,i):
self._fetchTreeFromDatabase(i)
return super(SQLLandscape,self).getNode(i)
[docs] def exploreTree(self,i):
node = self.getNode(i)
if (node['explored']): return None
moves = self._fetchRearrangementsFromDatabase(i)
while (len(moves) > 0):
move = choice(moves)
moves.remove(move)
j = move[-1]
if j in self.graph.node and self.graph.has_edge(i,j):
continue
self._fetchTreeFromDatabase(j)
self._applyRearrangement(move)
self.getEdge(i,j)['weight'] = self.defaultWeight
node['explored'] = True
return True
[docs]class SQLiteLandscape(landscape):
''' Allow random access of all landscape data from an sqlite file found on
the hard disk. '''
[docs] def __init__(self,dbobj):
# Needs to be implemented.
raise NotImplementedError()
[docs]class database(object):
''' Allow interfacing with a SQL/sqlite database. '''
__metaclass__ = abstractclass
[docs] def __init__(self):
self.cursor = None
@abstractmethod
[docs] def getTables(self): pass
@abstractmethod
[docs] def getColumns(self,table): pass
[docs] def isEmpty(self):
''' Determine if the database is empty.
:return: a boolean
'''
return (len(self.getTables()) == 0)
[docs] def getRecordsColumn(self,table,col):
''' Get all data for a single column from records for a table. The
equivalent of a call "SELECT col FROM table".
:param table: a table name
:type table: a string
:param col: a column name
:type col: a string
:return: a list of strings
'''
self.query("""SELECT %s FROM %s""" % (col,table))
return self.cursor.fetchall()
[docs] def getRecords(self,table):
''' Get all records from a given table in the database. The
equivalent of a call "SELECT * FROM table".
:param table: a table name
:type table: a string
:return: a list of tuples
'''
self.query("""SELECT * FROM %s""" % (table))
return self.cursor.fetchall()
[docs] def iterRecords(self,table):
''' Get a record, one at a time, from a table in the database.
:param table: a table name
:type table: a string
:return: a generator of records
'''
self.query("""SELECT * FROM %s""" % (table))
nextitem = self.cursor.fetchone()
while nextitem != None:
yield nextitem
nextitem = self.cursor.fetchone()
[docs] def filterRecords(self,table,condn):
''' Get all records from a given table following a condition. The
equivalent of calling "SELECT * FROM table WHERE cond".
:param table: a table name
:type table: a string
:param condn: a condition in SQL syntax
:type condn: a string
:return: a list of tuples
'''
self.query("""SELECT * FROM %s WHERE %s""" % (table,condn))
return self.cursor.fetchall()
[docs] def getRecordsAsDict(self,table):
''' Acquires records using getRecords() and then leverages
access using a dictionary data structure where keys are
headers (column names).
:param table: a table name
:type table: a string
:return: a dictionary (of records as values)
'''
d = {}
items = self.getRecords(table)
if len(items) == 0: return {}
headers = self.getHeaders(table)
if len(items[-1]) != len(headers): return None
for header in headers: d[header] = []
for item in items:
for i in xrange(len(headers)):
header = headers[i]
value = item[i]
d[header].append(value)
return d
[docs] def newTable(self,tablename,*args):
''' Create a new table.
:param tablename: the name of this table
:type tablename: a string
'''
self.query("""CREATE TABLE %s (%s)""" % (
tablename,', '.join(['%s %s' % (x,y) for x,y in args])))
[docs] def insertRecords(self,tablename,items):
''' Insert a number of records into a table.
:param tablename: the name of the table
:type tablename: a string
:param items: a list of record tuples
:type items: a list of tuples
'''
self.querymany('''INSERT INTO %s VALUES (%s)''' % (
tablename,', '.join(['?' for x in items[0]])),items)
[docs] def insertRecord(self,tablename,record):
''' Insert a single record.
:param tablename: the name of the table
:type tablename: a string
:param record: a tuple
:type record: a tuple
'''
self.insertRecords(tablename,[record])
@abstractmethod
[docs] def query(self,q): pass
@abstractmethod
[docs] def querymany(self,q,i): pass
@abstractmethod
[docs]class SQLDatabase(database):
''' Database object to allow reading from a MySQL database. '''
[docs] def __init__(self,host,user,pw,db):
self.hostname = host
self.username = user
self.password = pw
self.database = db
self.connect()
[docs] def connect(self):
self.socket = mysql.connect(host=self.hostname,user=self.username,
passwd=self.password,db=self.database)
self.cursor = self.socket.cursor()
[docs] def getTables(self):
self.query("""SHOW TABLES""")
return [x[0] for x in self.cursor.fetchall()]
[docs] def getColumns(self,table):
''' Return column information for a given table. '''
self.query("""SHOW COLUMNS FROM %s""" % (table))
return self.cursor.fetchall()
[docs] def query(self,q):
# Execute a MySQL query.
try:
self.cursor.execute(q)
except (AttributeError,mysql.OperationalError):
# In case of server timeout, reconnect.
self.connect()
self.query(q)
[docs] def querymany(self,q,i):
try:
self.cursor.executemany(q,i)
except (AttributeError,mysql.OperationalError):
self.connect()
self.querymany(q,i)
[docs] def close(self):
# Close the connection.
self.socket.close()
[docs]class SQLiteDatabase(database):
[docs] def __init__(self,filepath):
''' Instantiate this SQLite database object.
:param filepath: a path to the file
:type filepath: a string
'''
self.filepath = filepath
self.socket = sqllite.connect(filepath)
self.cursor = self.socket.cursor()
[docs] def getColumns(self,table):
''' Return column information for a given table.
:param table: a table name
:type table: a string
'''
self.query("""PRAGMA table_info(%s)""" % (table))
return self.cursor.fetchall()
[docs] def getTables(self):
self.query("""SELECT name FROM sqlite_master WHERE type='table';""")
return [x[0] for x in self.cursor.fetchall()]
[docs] def query(self,q):
# Execute an sqlite query.
self.cursor.execute(q)
[docs] def querymany(self,q,i):
self.cursor.executemany(q,i)
[docs] def close(self):
# Close the connection.
self.socket.commit()
self.socket.close()