lundi 14 décembre 2015

getting only updated data from database

I have to get the recently updated data from database. For the purpose of solving it, I have saved the last read row number into shelve of python. The following code works for a simple query like select * from rows. My code is:

from pyodbc import connect
from peewee import *
import random
import shelve
import connection


d = shelve.open("data.shelve")
db = SqliteDatabase("data.db")
class Rows(Model):
    valueone = IntegerField()
    valuetwo = IntegerField()

    class Meta:
        database = db

def CreateAndPopulate():
    db.connect()
    db.create_tables([Rows],safe=True)
    with db.atomic():
        for i in range(100):
            row = Rows(valueone=random.randrange(0,100),valuetwo=random.randrange(0,100))
            row.save()
    db.close()

def get_last_primay_key():
    return d.get('max_row',0)

def doWork():
    query = "select * from rows" #could be anything
    conn = connection.Connection("localhost","","SQLite3 ODBC Driver","data.db","","")
    max_key_query = "SELECT MAX(%s) from %s" % ("id", "rows")
    max_primary_key = conn.fetch_one(max_key_query)[0]
    print "max_primary_key " + str(max_primary_key)
    last_primary_key = get_last_primay_key()
    print "last_primary_key " + str(last_primary_key)
    if max_primary_key == last_primary_key:
        print "no new records"
    elif max_primary_key > last_primary_key:
        print "There are some datas"
        optimizedQuery = query + " where id>" + str(last_primary_key) 
        print query
        for data in conn.fetch_all(optimizedQuery):
            print data
        d['max_row'] = max_primary_key
    # print d['max_row']

# CreateAndPopulate() # to populate data
doWork()

While the code will work for a simple query without where clause, but the query can be anything from simple to complex, having joins and multiple where clauses. If so, then the portion where I'm adding where will fail. How can I get only last updated data from database whatever be the query?

PS: I cannot modify database. I just have to fetch from it.

Aucun commentaire:

Enregistrer un commentaire