Data Access

Revision of the DataAccessor class using the apsw python sqlite module posted to https://excelicious.wordpress.com/2010/04/23/python-data-access-patterns-part-1/, after some helpful feedback from Roger Binns (author of the apsw module).

Something Roger pointed out which I still haven’t addressed is that when table or column names contain spaces, quotes or are SQLite reserved words, the DataAccessor object will generate invalid SQL. The APSW Shell source has a ‘_fmt_sql_identifier’ function which will handle quoting – I haven’t used anything like this here but it would be easy enough to modify this code to include it.

#!/usr/bin/env/python
#
# ADA - APSW Data Accessor
#
# Module to include as a data access component, uses apsw python
# sqlite wrapper

import apsw

class DataAccessor(object):
    '''
    Class to handle data access using apsw sqlite wrapper
    '''

    # Time in seconds before retrying on receiving a busy error
    TIMEOUT = 5
    
    def __init__(self, dbpath):
        try:
            self.conn = apsw.Connection(dbpath,
                                        flags=apsw.SQLITE_OPEN_READWRITE)
            self.conn.setbusytimeout(int(self.TIMEOUT*1000))
            
        except apsw.CantOpenError as error:
            error.dbpath = dbpath
            raise
        
    def read(self, table, columns=None, where_row=None, sort_cols=None):
        '''Executes a SELECT statement against table.

        Arguments:
        table                 -- name of the table to be read
        columns (optional)    -- list of columns to be read
                              from table
        where_row (optional)  -- dict used to build WHERE
                              clause
        sort_cols (optional)  -- list of (column, order) pairs
                              used to specify order of the
                              rows returned. Needs to be of
                              the form ('<column>', 'ASC'|'DESC')

        Returns: list of rows returned from the SELECT statement.
        '''
        try:
            stmt = 'SELECT '
            if columns:
                stmt += ', '.join(columns)
            else:
                stmt += '*'
            
            # from clause
            stmt += "\nFROM " + table
            
            # where clause
            if where_row:
                stmt += "\nWHERE "
                stmt += "\n  AND ".join(col + "=:" + col \
                                    for col in where_row)
            
            # order clause
            if sort_cols:
                stmt += "\nORDER BY "
                stmt += ', '.join(col[0] + ' ' + col[1] \
                                    for col in sort_cols)
            
            # submit and return results
            args = (stmt, where_row) if where_row else (stmt,)
            results = self.conn.cursor().execute(*args)
            
            return list(results)
        
        except Exception as error:
            error.sql = stmt
            if where_row:
                error.bindings = where_row
            raise

    def insert(self, table, values):
        '''Executes an INSERT statement against table.

        Arguments:
        table                   -- name of the table to be written to
        values                  -- list of rows (dicts) to be inserted

        Returns: Number of rows inserted
        '''
        try:
            # build list of column names
            cols = values[0].keys()
            
            # generate insert statement
            stmt = 'INSERT INTO '
            stmt += table + ' ('
            stmt += ', '.join(col for col in cols)
            stmt += ') VALUES ('
            stmt += ', '.join(':'+col for col in cols)
            stmt += ')'
            
            # submit
            with self.conn as c:
               # c.cursor().execute('BEGIN IMMEDIATE')
                c.cursor().executemany(stmt, values)
               # c.cursor().execute('COMMIT')
                inserted = c.changes()
            
            print inserted, "rows inserted."
            return inserted
            
        except Exception as error:
            error.sql = stmt
            error.bindings = values
            raise
        
    def update(self, table, values, where_row=None):
        '''Executes an UPDATE statement against table

        Arguments:
        table                   -- name of the table to be updated
        values                  -- dict of columns and values to update
        where_row (optional)    -- dict used to build WHERE clause
        
        Returns: Number of rows updated
        '''
        try:
            # bindings needs to tell set clause from where clause...
            bindings = dict((col + '_v', v) \
                             for col, v in values.items())
            
            # generate update statement
            stmt = 'UPDATE '
            stmt += table + ' SET '
            stmt += ', '.join([col + "=:" + col + '_v' \
                for col in values])
            
            if where_row:
                for col in where_row:
                    bindings[col + '_w'] = where_row[col]
                stmt += "\nWHERE "
                stmt += "\n  AND ".join(col + "=:" + col + '_w' \
                            for col in where_row)
            
            # submit
            with self.conn as c:
               # c.cursor().execute('BEGIN IMMEDIATE')
                c.cursor().execute(stmt, bindings)
               # c.cursor().execute('COMMIT')
                updated = c.changes()
            
            print updated, "rows updated."
            return updated
            
        except Exception as error:
            error.sql = stmt
            error.bindings = bindings
    
    def delete(self, table, where_row=None):
        '''Executes a DELETE statement against table
        
        Arguments:
        table               -- name of the table
        where_row(optional) -- dict used to build WHERE clause
        
        Returns: Number of rows deleted
        '''
        try:
            # generate delete statement
            stmt = 'DELETE FROM '
            stmt += table
            
            # where clause
            if where_row:
                stmt += "\nWHERE "
                stmt += "\n  AND ".join([col + "=:" + col \
                                    for col in where_row])
            
            # submit
            args = (stmt, where_row) if where_row else (stmt,)
            with self.conn as c:
               # c.cursor().execute('BEGIN IMMEDIATE')
                c.cursor().execute(*args)
               # c.cursor().execute('COMMIT')
                deleted = c.changes()
            
            print deleted, "rows deleted."
            return deleted
            
        except Exception as error:
            error.sql = stmt
            if where_row:
                error.bindings = where_row
            raise

if __name__ == '__main__':
    db = DataAccessor(':memory:')
    tab1 = 'foo'
    var1 = ('x', 'y', 'z')
    db.conn.cursor().execute("create table "+tab1+" (x,y,z)")
    db.insert(tab1, [dict(zip(var1, (1, 2, 3))), dict(zip(var1, (2, 3, 4)))])
    rows = db.read(tab1)
    for row in rows:
        print row 
    db.update(tab1, {'x':2, 'z':3})
    rows = db.read(tab1)
    for row in rows:
        print row
    db.delete(tab1)

2 thoughts on “Data Access

  1. Patrick says:

    Great snippet – thanks!
    Can you give an example how to use the read() method in order to use the where capabilities?

    my try:
    pkPrice=db.read(sqlTblPrice,sqlTblPriceVars[0],dict({sqlTblPriceVars[1] : correctQuan tity, sqlTblPriceVars[2] : correctPrice}))

    I wanna do something like:
    “select primaryKeyID from sqlTblPrice where sqlField1 = correctQuantity AND sqlField2 = correctPrice;”
    (which result should be distinct in my case)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s