Why "name 'cursor' is not defined" when pyodbc cursor is in main()?

Question

I'm running a Python program, condensed below, from the command line, and it gives me an error (name 'cursor' is not defined) and points to the cursor.fetchmany() row near the bottom. However, the program runs as desired if I move the first two rows of main(), the conn and cursor assignments, above and outside of main() into the body of the program. Why doesn't this work when I assign cursor inside main()? I'd love to understand whether this is just a quirk of the pyodbc library, or there is a better way to get it to work from inside main(), or it's actually okay to leave the assignments in the body of the program.

import pyodbc
import csv
#   ...other modules...

def main():
    conn = pyodbc.connect(DSN=abcxyz, autocommit=True)
    cursor = conn.cursor()
#   ...sys.argv assignments and validation...
    pull_data(query_file, start_date, end_date, out_file)

def pull_data(query_file, start_date, end_date, out_file):
#   ...prepare query as string, date range as list...
    for date in list_dates:
        cursor.execute(query, date)
        append_rows(out_file)

def append_rows(out_file):
    with open(out_file, 'a', newline='') as file:
        writer = csv.writer(file)
        while True:
            results = cursor.fetchmany(chunk_size)
            if not results:
                break
            for result in results:
                writer.writerow(result)

if __name__ == '__main__':
    main()

`conn` is local to `main`, a class would be a much better idea — Padraic Cunningham, Aug 21 '15 at 20:05
I think I agree -- if I want to guarantee the DB connection is closed in the event of an error, I'd either have to pass both `conn` and `cursor` to just about every function, or I can make an object that can always call a db_close() method. — RobinFiveWords, Aug 21 '15 at 20:31

score 3 · Answer 1 · edited May 23 '17 at 12:06

The reason that you're getting an error with cursor not being defined, is because it doesn't exists in your functions. Variables only exist in the scope they are defined in.

When you defined cursor outside of main, it was declared as a global variable, meaning that it will be accessible in all scopes of the script.

Try:

import pyodbc
import csv
#   ...other modules...

def main():
    # Since conn and cursor are being declared here, they only exist within the scope of this function, not other functions that are called.
    conn = pyodbc.connect(DSN=abcxyz, autocommit=True)
    cursor = conn.cursor()
#   ...sys.argv assignments and validation...
    # make sure we give the cursor to pull_data so it will exist in its scope!
    pull_data(query_file, start_date, end_date, out_file, cursor)

def pull_data(query_file, start_date, end_date, out_file, cursor):
#   ...prepare query as string, date range as list...
    for date in list_dates:
        cursor.execute(query, date)
        # Pass the cursor to append_rows
        append_rows(out_file, cursor)

# cursor is actually passed to the append_rows function. Now it exists in this scope
def append_rows(out_file, cursor):
    with open(out_file, 'a', newline='') as file:
        writer = csv.writer(file)
        while True:
            results = cursor.fetchall()
            for result in results:
                writer.writerow(result)

if __name__ == '__main__':
    main()

I would also recommend that you read https://stackoverflow.com/a/292502/5249060.

score 2 · Accepted Answer · answered Aug 21 '15 at 20:04

2

Because cursor is not defined in append_rows() or pull_data(). You're defining it in main(), so it's only accessible in main().

The best way to fix this is probably to pass the cursor object to append_rows() and pull_data().

answered Aug 21 '15 at 20:04

Morgan Thrapp

9,748
3
46
67

Does this mean I need to pass `cursor` as an additional argument to `pull_data()` and again to `append_rows()`? – RobinFiveWords Aug 21 '15 at 20:08
@RobinFiveWords Yup. You could define it outside of `main()` and it'll be in the global scope, but it's generally better to keep things in as local of a scope as you can. – Morgan Thrapp Aug 21 '15 at 20:09

Why "name 'cursor' is not defined" when pyodbc cursor is in main()?

2 Answers2