dbsql.py

'''\
Database Table classes.

Developer@Sonnack.com
June 2017
'''
####################################################################################################
from __future__ import print_function
from sys import argvstdoutstderrpath as syspath
from os import listdirpath
from datetime import datetime
import sqlite3
from logger import loggerinfodebugtrace
from files import get_file_tab
####################################################################################################


##~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~##
class dbQuery (object):
    '''SQLite Database Query class.'''

    Log = logger('dbsql')

    def __init__ (selfdb_namename):
        self.dbname = db_name
        self.name = name # Just a Query name, but used as TableName in dbTable!
        self.is_open = False

    def __str__ (self):
        return 'dbQuery[%s] (DB:%s)' % (self.nameself.dbname)

    def db_query (selfqueryparams=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,paramsif 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__ (selfdb_nametable_namecolumn_names):
        dbQuery.__init__(selfdb_nametable_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[1if 1 < len(telse '')

    def __len__ (self):
        return len(self.cols)

    def __str__ (self):
        return 'dbTable[%s] (%s)' % (self.nameself.dbname)

    def create_and_load_table (selfnotice):
        self.Log.info(notice)
        self.db_create()
        self.db_load(path.join(self.dataPath,self.tabFileName))

    def destroy_table (selfnotice):
        self.Log.info(notice)
        self.db_destroy()

    def find_record (selfsrch_colsrch_val):
        '''Find a record given a colum index and a search value for that column.'''
        sql = 'SELECT * FROM %s WHERE (%s = ?)' % (self.nameself.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 (selfrecordsfmtfilenameh1=Noneh2=Nonesql=Noneps=None):
        '''Print (formatted) records to a file stream.'''
        fp = open(filename'w')
        try:
            if h1: print(h1file=fp)
            if h2: print(h2file=fp)
            for rcd in records:
                print(fmt % rcdfile=fp)
            if h2: print(h2file=fp)
            print(file=fp)
            if sql: print('{%s}' % sqlfile=fp)
            if ps: print('%s' % psfile=fp)
            print(file=fp)
            self.Log.info('wrote: %s' % filename)
        except:
            raise
        finally:
            fp.close()

    def print_records (selffp=stdout):
        '''Dump the records to a file stream. Order by first column.'''
        sql = 'SELECT * FROM %s ORDER BY %s' % (self.nameself.col_names[0])
        self.Log.trace(sql)
        rs = self.db_query(sql)
        for r in rs:
            print(rfile=fp)

    def db_load (selffilename):
        '''Assumes a TAB file with first row of headers (which are ignored).'''
        self.Log.debug('dbTable[%s] LoadRecords: %s' % (self.namefilename))
        hdrs,data = get_file_tab(filename)
        self.db_insert_many(data)

    def db_insert (selfrecd):
        '''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 (selfrecds):
        '''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.namet)
            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'''