Persistence With SQLite

The Problem-ette

I needed to keep track of up until where in a database table I had already queried. Later queries needed to draw data from that point onwards. For example if I've done the query select id, name, surname from table where id > 255623 and it yielded results with an maximum id (assuming id is an auto_increment field) of 343522 then the next query would need to be select id, name, surname from table where id > 343522. I needed, therefore, to keep track of that maximum id.

The Solution

Because I didn't want to touch the production MySQL database I decided to store the persisted script data (the maximum id) in a SQLite3 databases.

I could also have decided to persist this data using Python Pickles or any number of other method. I chose SQLite because it would be easily accessible via the sqlite3 client utility.

The following snippets went into the scripts I created.

Set Up the SQLite Table

# for speed we persist last in a sqlite3 db
# - not using 'id' as an absolute guide instead of end_date
# - just to make the sql faster
persistdb_engine = sa.create_engine('sqlite:////opt/monitoring/graphite_persist.db')
persistdb_conn = persistdb_engine.connect()
persist_table = sa.Table('persist', metadata,
    Column('scriptname', String(100), primary_key = True),
    Column('index_table', String(100), nullable = False),
    Column('index_column', String(100), nullable = False),
    Column('index_value', Integer, nullable = False))

# create sqlite table if not exists
persist_table.create(persistdb_engine, checkfirst=True)

Query the SQLite Table

scriptname = sys.argv[0].split('/')[-1]
index_table = 'audit_trail'
index_column = 'id'

# using sqlalchemy expression language
# see:
s = select([persist_table]).where(persist_table.c.scriptname == scriptname)
ret = persistdb_conn.execute(s).fetchone()
if not ret:
    index_value = 0
    ins = persist_table.insert().values(scriptname = scriptname,
                                        index_table = index_table,
                                        index_column = index_column,
                                        index_value = index_value)
    ret = persistdb_conn.execute(ins)
    index_value = int(ret['index_value'])

Update the SQLite Table with New Max(id)

# now update persist table
s = persist_table.update()\
ret = persistdb_conn.execute(s)

Worked for me.