Loop stops after first Cursor.execute()

Question

Using pyodbc, I'm trying to loop through sheets in Excel, read ones that start with the string "Appointment", then write the results to an existing table in an Access DB.

I'm able to loop through all the sheets and identify the ones that start with "Appointment" (there's four of them - Appointment1, Appointment2, etc.). I can also read the data on any one of the sheets found, and I can write the results to the DB table. When I try to do all that in a for loop, I'm able to get all the sheet names, but as soon as I execute() a select statement, the loop stops.

It doesn't error - the print() statements work, but the loop just stops after the second print statement. If I comment the second print() out, the first print will return four results.

Cursor.commit() doesn't change the behavior.

# execute select stmt
def select_xls_data(tbl_name):
    select_stmt_XLS = 'SELECT * from [' + tbl_name + ']'
    result_XLS = crsr_XLS.execute(select_stmt_XLS).fetchall()
    return result_XLS

# loop through XLS sheets
for table_info in crsr_XLS.tables():
    tbl_name = table_info.table_name
    if (tbl_name.startswith('Appointment')):
        print(tbl_name)  # will succesfully loop through all sheets
        print(select_xls_data(tbl_name))  # will only loop once

Rob Py · Answer 1 · 2021-01-08T00:31:31.873

0

It seems you have incorrect syntax in the select statement, which should be:

select_stmt_XLS = 'SELECT * from ' + tbl_name + ''

edited Jan 08 '21 at 00:31

answered Jan 08 '21 at 00:15

Rob Py

156
1
1
9

Thanks, but the syntax is correct, it results in `SELECT * from [Appointment1$]`, or 2$, etc. – Daniel Cardenas Jan 08 '21 at 16:07

score 0 · Answer 2 · answered Jan 08 '21 at 17:30

I still don't know why the loop was stopping in my original question, but I rewrote the code to achieve my goal.

The new code does:

Loop through all the tables in the file and add the tables of interest to a list
Loop through the list created and execute a select statement for each table of interest
Add the results of each select to a results list

def select_xls_data(tbl_name):
    select_stmt_XLS = 'SELECT * from ' + tbl_name
    result_XLS = crsr_XLS.execute(select_stmt_XLS).fetchall()
    return result_XLS

# discover the tables of interest and write their names to a list
tables = []
for table_info in crsr_XLS.tables():
    tbl_name = table_info.table_name
    if (tbl_name.startswith('Appointment')): tables.append('['+tbl_name+']')

# loop through tables of interest, execute select stmt on each and append each result set to a list
results = []
for tbl_name in tables: results.append(select_xls_data(tbl_name))

Loop stops after first Cursor.execute()

2 Answers2

Linked