getting only updated data from database

Question

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.

What do you mean by 'fail'? What error\wrong result do you get? An example would be useful... — soungalo, Dec 14 '15 at 10:52
Consider an sql like `select * from table_name join another_table on ... `, now adding `where id>...` wont give me result I desire — Mahadeva, Dec 14 '15 at 10:55

FlipperPA · Answer 1 · 2015-12-14T14:36:59.753

0

Use an OFFSET clause. For example:

SELECT * FROM [....] WHERE [....] LIMIT -1 OFFSET 1000

In your query, replace 1000 with a parameter bound to your shelve variable. That will skip the top "shelve" number of rows and only grab newer ones. You may want to consider a more robust refactor eventually, but good luck.

edited Dec 14 '15 at 14:36

answered Dec 14 '15 at 13:53

FlipperPA

13,607
4
39
71

Based on the link http://stackoverflow.com/questions/10491492/sqllite-with-skip-offset-only-not-limit I used `limit -1 offset 1000`. I will give this code a go and let you know – Mahadeva Dec 14 '15 at 14:14
I've amended my answer - its been a while since I used SQLite directly and forgot the LIMIT was required. – FlipperPA Dec 14 '15 at 14:37

getting only updated data from database

1 Answers1