Return an iterator with python pyodbc

Question

I'm trying to write a DataAccessLayer with python. One of the functions should return an iterator for a large set of rows(i don't want to fetch all and eat a lot of memory).

The iterator will be used in another class that is using my DAL class to check something for each user.

I understand that i shouldn't return the cursor itself, but how can give the other class an iterator?

MY DAL:

import src.data_handler.data_handler_conf as conf
import time
import pyodbc


class DataHandler:

def __init__(self):
    # Get configuration
    self.db_url = conf.DB_URL
    self.db_name = conf.DB_NAME
    self.db_username = conf.DB_USERNMAE
    self.db_password = conf.DB_PASSWORD

    self.db_conn = None
    self.connect()

def connect(self):
    self.db_conn = pyodbc.connect("DRIVER={FreeTDS}"
                                  ";SERVER=" + self.db_url +
                                  ";DATABASE=" + self.db_name +
                                  ";UID=" + self.db_username +
                                  ";PWD=" + self.db_password)
    self.db_conn.setencoding(encoding='utf-8')

def check_users(self):
    cursor = self.db_conn.cursor()
    cursor.execute("select user_name, field1 from users")

    # What should i return?!
    return cursor

score 2 · Answer 1 · answered Mar 19 '19 at 23:35

I suspect that you should be able to use a generator, something like this:

def get_results(cnxn):
    crsr = cnxn.cursor()
    crsr.execute("SELECT * FROM MillionRows")
    row = crsr.fetchone()
    while row:
        yield row
        row = crsr.fetchone()

It will return a sequence of pyodbc Row objects.

Return an iterator with python pyodbc

1 Answers1