CODEWITHSUNDEEP
pythonpostgresqlcursorpyodbc
H.Burns
H.Burns

Reputation: 419

How to change the cursor to the next row using pyodbc in Python

I am trying to fetch records after a regular interval from a database table which growing with records. I am using Python and its pyodbc package to carry out the fetching of records. While fetching, how can I point the cursor to the next row of the row which was read/fetched last so that with every fetch I can only get the new set of records inserted.

To explain more, my table has 100 records and they are fetched. after an interval the table has 200 records and I want to fetch rows from 101 to 200. And so on.

Is there a way with pyodbc cursor? Or any other suggestion would be very helpful.

Below is the code I am trying:

#!/usr/bin/python
import pyodbc
import csv
import time


conn_str = (
    "DRIVER={PostgreSQL Unicode};"
    "DATABASE=postgres;"
    "UID=userid;"
    "PWD=database;"
    "SERVER=localhost;"
    "PORT=5432;"
    )

conn = pyodbc.connect(conn_str)
cursor = conn.cursor()

def fetch_table(**kwargs):
    qry = kwargs['qrystr']
    try:
        #cursor = conn.cursor()
        cursor.execute(qry)
        all_rows  = cursor.fetchall()
        rowcnt =  cursor.rowcount
        rownum = cursor.description
        #return (rowcnt, rownum)
        return all_rows
    except pyodbc.ProgrammingError as e:
        print ("Exception occured as :",  type(e) , e)

def poll_db():

    for i in [1, 2]:

        stmt = "select * from my_database_table"
        rows = fetch_table(qrystr = stmt)

        print("***** For i = " , i , "******")
        for r in rows:
            print("ROW-> ", r)
        time.sleep(10)


poll_db()
conn.close()

Upvotes: 1

Views: 1779

Answers (1)

Siddardha
Siddardha

Reputation: 550

I don't think you can use pyodbc, or any other odbc package, to find "new" rows. But if there is a 'timestamp' column in your database, or if you can add such a column (some databases allow for it to be automatically populated as the time of insertion so you don't have to change the insert queries) then you can change your query to select only the rows whose timestamp is greater than the previous timestamp. And you can keep changing the prev_timestamp variable on each iteration.

def poll_db():

    prev_timestamp = ""
    for i in [1, 2]:
        if prev_timestamp == "":
            stmt = "select * from my_database_table"
        else:
            # convert your timestamp str to match the database's format
            stmt = "select * from my_database_table where timestamp > " + str(prev_timestamp)

        rows = fetch_table(qrystr = stmt)
        prev_timestamp = datetime.datetime.now()
        print("***** For i = " , i , "******")
        for r in rows:
            print("ROW-> ", r)
        time.sleep(10)

Upvotes: 1

Related Questions