<filename>lib/bwDB.py
#!/usr/bin/env python3
# bwTL - BW's template library
# by <NAME> [http://bw.org/]
# Copyright 1995-2010 The BearHeart Group LLC

# save time by using normalized interface, can update as necessary

import sqlite3

__version__ = '1.0.3'

class bwDB:
    def __init__(self, **kwargs):
        '''
            db = bwDB( [ table = ''] [, filename = ''] )
            constructor method
                table is for CRUD methods
                filename is for connecting to the database file
        '''
        # see filename setter below
        self.filename = kwargs.get('filename')
        self.table = kwargs.get('table', '')

    def sql_do(self, sql, params = ()):
        '''
            db.sql_do( sql[, params] )
            method for non-select queries
                sql is string containing SQL
                params is list containing parameters
            returns nothing
        '''
        self._db.execute(sql, params)
        self._db.commit()

    def sql_query(self, sql, params = ()):
        '''
            db.sql_query( sql[, params] )
            generator method for queries
                sql is string containing SQL
                params is list containing parameters
            returns a generator with one row per iteration
            each row is a Row factory
        '''
        c = self._db.cursor()
        c.execute(sql, params)
        for r in c:
            yield r

    def sql_query_row(self, sql, params = ()):
        '''
            db.sql_query_row( sql[, params] )
            query for a single row
                sql is string containing SQL
                params is list containing parameters
            returns a single row as a Row factory
        '''
        c = self._db.cursor()
        c.execute(sql, params)
        return c.fetchone()

    def sql_query_value(self, sql, params = ()):
        '''
            db.sql_query_row( sql[, params] )
            query for a single value
                sql is string containing SQL
                params is list containing parameters
            returns a single value
        '''
        c = self._db.cursor()
        c.execute(sql, params)
        return c.fetchone()[0]

    # CRUD retrieve
    def getrec(self, id):
        '''
            db.getrec(id)
            get a single row, by id
        '''
        query = 'SELECT * FROM {} WHERE id = ?'.format(self.table)
        c = self._db.execute(query, (id,))
        return c.fetchone()

    def getrecs(self):
        '''
            db.getrecs(id)
            get all rows, returns a generator of Row factories
        '''
        query = 'SELECT * FROM {}'.format(self.table)
        c = self._db.execute(query)
        for r in c:
            yield r

    def insert(self, rec):
        '''
            db.insert(rec)
            insert a single record into the table
                rec is a dict with key/value pairs corresponding to table schema
            omit id column to let SQLite generate it
        '''
        klist = sorted(rec.keys()) # grabs all keys of dict object
        values = [ rec[v] for v in klist ]  # a list of values ordered by key
        q = 'INSERT INTO {} ({}) VALUES ({})'.format(
            self.table,
            ', '.join(klist),
            ', '.join('?' for i in range(len(values)))
        )
        c = self._db.execute(q, values)
        self._db.commit()
        return c.lastrowid

    def update(self, id, rec):
        '''
            db.update(id, rec)
            update a row in the table
                id is the value of the id column for the row to be updated
                rec is a dict with key/value pairs corresponding to table schema
        '''
        klist = sorted(rec.keys())
        values = [ rec[v] for v in klist ]  # a list of values ordered by key

        for i, k in enumerate(klist):       # don't udpate id
            if k == 'id':
                del klist[i]
                del values[i]

        q = 'UPDATE {} SET {} WHERE id = ?'.format(
            self.table,
            ',  '.join(map(lambda str: '{} = ?'.format(str), klist))
        )
        self._db.execute(q, values + [ id ])
        self._db.commit()

    def delete(self, id):
        '''
            db.delete(id)
            delete a row from the table, by id
        '''
        query = 'DELETE FROM {} WHERE id = ?'.format(self.table)
        self._db.execute(query, [id])
        self._db.commit()

    def countrecs(self):
        '''
            db.countrecs()
            count the records in the table
            returns a single integer value
        '''
        query = 'SELECT COUNT(*) FROM {}'.format(self.table)
        c = self._db.cursor()
        c.execute(query)
        return c.fetchone()[0]

    ### filename property
    @property
    def filename(self):
        return self._dbFilename

    @filename.setter
    def filename(self, fn):
            self._dbFilename = fn # sets filename
            self._db = sqlite3.connect(fn) # connects to db
            self._db.row_factory = sqlite3.Row # sets up row query

    @filename.deleter
    def filename(self):
        self.close()

    # destructor closes db
    def close(self):
            self._db.close()
            del self._dbFilename

def test():
    import os
    fn = ':memory:'     # in-memory database
    t = 'foo'

    recs = [
        dict( string = 'one' ),
        dict( string = 'two' ),
        dict( string = 'three' )
    ]

    ### for file-based database
    # try: os.stat(fn)
    # except: pass
    # else:
    #     print('Delete', fn)
    #     os.unlink(fn)

    print('version', __version__)

    print('Create database file {} ...'.format(fn), end = '')
    db = bwDB( filename = fn, table = t )
    print('Done.')

    print('Create table ... ', end='')
    db.sql_do(' DROP TABLE IF EXISTS {} '.format(t))
    db.sql_do(' CREATE TABLE {} ( id INTEGER PRIMARY KEY, string TEXT ) '.format(t))
    print('Done.')

    print('Insert into table ... ', end = '')
    for r in recs: db.insert(r)
    print('Done.')

    print('Read from table')
    for r in db.getrecs(): print(dict(r))

    print('Update table')
    db.update(2, dict(string = 'TWO'))
    print( dict( db.getrec(2) ) )

    print('Insert an extra row ... ', end = '')
    newid = db.insert( dict( string = 'extra' ) )
    print('(id is {})'.format(newid))
    print( dict( db.getrec(newid) ) )
    print('Now delete it')
    db.delete(newid)
    for r in db.getrecs(): print(dict(r))
    db.close()

if __name__ == "__main__": test()

<filename>lib/bwDB.py
#!/usr/bin/env python3
# bwTL - BW's template library
# by <NAME> [http://bw.org/]
# Copyright 1995-2010 The BearHeart Group LLC

# save time by using normalized interface, can update as necessary

import sqlite3

__version__ = '1.0.3'

class bwDB:
    def __init__(self, **kwargs):
        '''
            db = bwDB( [ table = ''] [, filename = ''] )
            constructor method
                table is for CRUD methods
                filename is for connecting to the database file
        '''
        # see filename setter below
        self.filename = kwargs.get('filename')
        self.table = kwargs.get('table', '')

    def sql_do(self, sql, params = ()):
        '''
            db.sql_do( sql[, params] )
            method for non-select queries
                sql is string containing SQL
                params is list containing parameters
            returns nothing
        '''
        self._db.execute(sql, params)
        self._db.commit()

    def sql_query(self, sql, params = ()):
        '''
            db.sql_query( sql[, params] )
            generator method for queries
                sql is string containing SQL
                params is list containing parameters
            returns a generator with one row per iteration
            each row is a Row factory
        '''
        c = self._db.cursor()
        c.execute(sql, params)
        for r in c:
            yield r

    def sql_query_row(self, sql, params = ()):
        '''
            db.sql_query_row( sql[, params] )
            query for a single row
                sql is string containing SQL
                params is list containing parameters
            returns a single row as a Row factory
        '''
        c = self._db.cursor()
        c.execute(sql, params)
        return c.fetchone()

    def sql_query_value(self, sql, params = ()):
        '''
            db.sql_query_row( sql[, params] )
            query for a single value
                sql is string containing SQL
                params is list containing parameters
            returns a single value
        '''
        c = self._db.cursor()
        c.execute(sql, params)
        return c.fetchone()[0]

    # CRUD retrieve
    def getrec(self, id):
        '''
            db.getrec(id)
            get a single row, by id
        '''
        query = 'SELECT * FROM {} WHERE id = ?'.format(self.table)
        c = self._db.execute(query, (id,))
        return c.fetchone()

    def getrecs(self):
        '''
            db.getrecs(id)
            get all rows, returns a generator of Row factories
        '''
        query = 'SELECT * FROM {}'.format(self.table)
        c = self._db.execute(query)
        for r in c:
            yield r

    def insert(self, rec):
        '''
            db.insert(rec)
            insert a single record into the table
                rec is a dict with key/value pairs corresponding to table schema
            omit id column to let SQLite generate it
        '''
        klist = sorted(rec.keys()) # grabs all keys of dict object
        values = [ rec[v] for v in klist ]  # a list of values ordered by key
        q = 'INSERT INTO {} ({}) VALUES ({})'.format(
            self.table,
            ', '.join(klist),
            ', '.join('?' for i in range(len(values)))
        )
        c = self._db.execute(q, values)
        self._db.commit()
        return c.lastrowid

    def update(self, id, rec):
        '''
            db.update(id, rec)
            update a row in the table
                id is the value of the id column for the row to be updated
                rec is a dict with key/value pairs corresponding to table schema
        '''
        klist = sorted(rec.keys())
        values = [ rec[v] for v in klist ]  # a list of values ordered by key

        for i, k in enumerate(klist):       # don't udpate id
            if k == 'id':
                del klist[i]
                del values[i]

        q = 'UPDATE {} SET {} WHERE id = ?'.format(
            self.table,
            ',  '.join(map(lambda str: '{} = ?'.format(str), klist))
        )
        self._db.execute(q, values + [ id ])
        self._db.commit()

    def delete(self, id):
        '''
            db.delete(id)
            delete a row from the table, by id
        '''
        query = 'DELETE FROM {} WHERE id = ?'.format(self.table)
        self._db.execute(query, [id])
        self._db.commit()

    def countrecs(self):
        '''
            db.countrecs()
            count the records in the table
            returns a single integer value
        '''
        query = 'SELECT COUNT(*) FROM {}'.format(self.table)
        c = self._db.cursor()
        c.execute(query)
        return c.fetchone()[0]

    ### filename property
    @property
    def filename(self):
        return self._dbFilename

    @filename.setter
    def filename(self, fn):
            self._dbFilename = fn # sets filename
            self._db = sqlite3.connect(fn) # connects to db
            self._db.row_factory = sqlite3.Row # sets up row query

    @filename.deleter
    def filename(self):
        self.close()

    # destructor closes db
    def close(self):
            self._db.close()
            del self._dbFilename

def test():
    import os
    fn = ':memory:'     # in-memory database
    t = 'foo'

    recs = [
        dict( string = 'one' ),
        dict( string = 'two' ),
        dict( string = 'three' )
    ]

    ### for file-based database
    # try: os.stat(fn)
    # except: pass
    # else:
    #     print('Delete', fn)
    #     os.unlink(fn)

    print('version', __version__)

    print('Create database file {} ...'.format(fn), end = '')
    db = bwDB( filename = fn, table = t )
    print('Done.')

    print('Create table ... ', end='')
    db.sql_do(' DROP TABLE IF EXISTS {} '.format(t))
    db.sql_do(' CREATE TABLE {} ( id INTEGER PRIMARY KEY, string TEXT ) '.format(t))
    print('Done.')

    print('Insert into table ... ', end = '')
    for r in recs: db.insert(r)
    print('Done.')

    print('Read from table')
    for r in db.getrecs(): print(dict(r))

    print('Update table')
    db.update(2, dict(string = 'TWO'))
    print( dict( db.getrec(2) ) )

    print('Insert an extra row ... ', end = '')
    newid = db.insert( dict( string = 'extra' ) )
    print('(id is {})'.format(newid))
    print( dict( db.getrec(newid) ) )
    print('Now delete it')
    db.delete(newid)
    for r in db.getrecs(): print(dict(r))
    db.close()

if __name__ == "__main__": test()

#!/usr/bin/env python3 # bwTL - BW's template library # by <NAME> [http://bw.org/] # Copyright 1995-2010 The BearHeart Group LLC # save time by using normalized interface, can update as necessary db = bwDB( [ table = ''] [, filename = ''] )             constructor method                 table is for CRUD methods                 filename is for connecting to the database file # see filename setter below db.sql_do( sql[, params] )             method for non-select queries                 sql is string containing SQL                 params is list containing parameters             returns nothing db.sql_query( sql[, params] )             generator method for queries                 sql is string containing SQL                 params is list containing parameters             returns a generator with one row per iteration             each row is a Row factory db.sql_query_row( sql[, params] )             query for a single row                 sql is string containing SQL                 params is list containing parameters             returns a single row as a Row factory db.sql_query_row( sql[, params] )             query for a single value                 sql is string containing SQL                 params is list containing parameters             returns a single value # CRUD retrieve db.getrec(id)             get a single row, by id db.getrecs(id)             get all rows, returns a generator of Row factories db.insert(rec)             insert a single record into the table                 rec is a dict with key/value pairs corresponding to table schema             omit id column to let SQLite generate it # grabs all keys of dict object # a list of values ordered by key db.update(id, rec)             update a row in the table                 id is the value of the id column for the row to be updated                 rec is a dict with key/value pairs corresponding to table schema # a list of values ordered by key # don't udpate id db.delete(id)             delete a row from the table, by id db.countrecs()             count the records in the table             returns a single integer value ### filename property # sets filename # connects to db # sets up row query # destructor closes db # in-memory database ### for file-based database # try: os.stat(fn) # except: pass # else: #     print('Delete', fn) #     os.unlink(fn)