dbsql.py
'''\
Database Table classes.
Developer@Sonnack.com
June 2017
'''
from __future__ import print_function
from sys import argv, stdout, stderr, path as syspath
from os import listdir, path
from datetime import datetime
import sqlite3
from logger import logger, info, debug, trace
from files import get_file_tab
class dbQuery (object):
'''SQLite Database Query class.'''
Log = logger('dbsql')
def __init__ (self, db_name, name):
self.dbname = db_name
self.name = name
self.is_open = False
def __str__ (self):
return 'dbQuery[%s] (DB:%s)' % (self.name, self.dbname)
def db_query (self, query, params=None):
'''Query the Database.'''
self.Log.debug('dbQuery[%s]' % self.name)
self.Log.debug('sql={%s}' % query.replace('\n',' '))
if params: self.Log.trace('ps=%s' % str(params))
self.dbopen()
try:
results = self.crsr.execute(query,params) if params else self.crsr.execute(query)
return list(results)
except Exception as e:
self.Log.error(e)
raise
finally:
self.dbclose()
def dbopen (self):
'''Open the Database and create a Cursor.'''
self.Log.debug('db[%s] OPEN' % self.name)
if self.is_open:
self.Log.warn('Database was already open! (Closed it!)')
self.dbclose()
self.conn = sqlite3.connect(self.dbname)
self.crsr = self.conn.cursor()
self.is_open = True
def dbclose (self):
'''Close the Database.'''
self.Log.debug('db[%s] CLOSE' % self.name)
if not self.is_open:
self.Log.warn('Database already closed! (Nothing done.)')
return
self.conn.commit()
self.conn.close()
self.is_open = False
class dbTable (dbQuery):
'''SQLite Database Table class.'''
def __init__ (self, db_name, table_name, column_names):
dbQuery.__init__(self, db_name, table_name)
self.cols = column_names
self.col_names = []
self.col_types = []
for c in self.cols:
t = c.split()
self.col_names.append(t[0])
self.col_types.append(t[1] if 1 < len(t) else '')
def __len__ (self):
return len(self.cols)
def __str__ (self):
return 'dbTable[%s] (%s)' % (self.name, self.dbname)
def create_and_load_table (self, notice):
self.Log.info(notice)
self.db_create()
self.db_load(path.join(self.dataPath,self.tabFileName))
def destroy_table (self, notice):
self.Log.info(notice)
self.db_destroy()
def find_record (self, srch_col, srch_val):
'''Find a record given a colum index and a search value for that column.'''
sql = 'SELECT * FROM %s WHERE (%s = ?)' % (self.name, self.col_names[srch_col])
self.Log.trace(sql)
rs = list(self.db_query(sql, [srch_val]))
self.Log.trace(rs)
return rs
def list_to_file (self, records, fmt, filename, h1=None, h2=None, sql=None, ps=None):
'''Print (formatted) records to a file stream.'''
fp = open(filename, 'w')
try:
if h1: print(h1, file=fp)
if h2: print(h2, file=fp)
for rcd in records:
print(fmt % rcd, file=fp)
if h2: print(h2, file=fp)
print(file=fp)
if sql: print('{%s}' % sql, file=fp)
if ps: print('%s' % ps, file=fp)
print(file=fp)
self.Log.info('wrote: %s' % filename)
except:
raise
finally:
fp.close()
def print_records (self, fp=stdout):
'''Dump the records to a file stream. Order by first column.'''
sql = 'SELECT * FROM %s ORDER BY %s' % (self.name, self.col_names[0])
self.Log.trace(sql)
rs = self.db_query(sql)
for r in rs:
print(r, file=fp)
def db_load (self, filename):
'''Assumes a TAB file with first row of headers (which are ignored).'''
self.Log.debug('dbTable[%s] LoadRecords: %s' % (self.name, filename))
hdrs,data = get_file_tab(filename)
self.db_insert_many(data)
def db_insert (self, recd):
'''INSERT a single record into the table.'''
self.Log.debug('dbTable[%s] Insert: {%s}' % (self.name,str(recd)))
self.dbopen()
try:
t = ','.join('?'*len(self.cols))
s = 'INSERT INTO %s VALUES (%s)' % (self.name,t)
self.Log.trace(s)
self.crsr.execute(s,recd)
except Exception as e:
self.Log.error(e)
raise
finally:
self.dbclose()
def db_insert_many (self, recds):
'''INSERT multiple records into the table.'''
self.Log.debug('dbTable[%s] Insert: {records: %d}' % (self.name,len(recds)))
self.dbopen()
try:
t = ','.join('?'*len(self.cols))
s = 'INSERT INTO %s VALUES (%s)' % (self.name,t)
self.Log.trace(s)
self.crsr.executemany(s,recds)
except Exception as e:
self.Log.error(e)
raise
finally:
self.dbclose()
def db_create (self):
'''Create the table in the database. (Table must already not exist.)'''
self.Log.debug('dbTable[%s] Create' % self.name)
self.dbopen()
try:
t = ','.join(self.cols)
s = 'CREATE TABLE %s (%s)' % (self.name, t)
self.Log.trace(s)
self.crsr.execute(s)
except Exception as e:
self.Log.error(e)
raise
finally:
self.dbclose()
def db_destroy (self):
'''Remove the table from the database. (Table must exist. (Duh.))'''
self.Log.debug('dbTable[%s] Destroy' % self.name)
self.dbopen()
try:
s = 'DROP TABLE %s' % self.name
self.Log.trace(s)
self.crsr.execute(s)
except Exception as e:
self.Log.error(e)
raise
finally:
self.dbclose()
if __name__ == '__main__':
pass
'''eof'''