Python Forum

Hello,

I'm trying to count the number of records returned by a SQL query. The program writes to a .csv file, but I would like to output to a logfile showing any errors and the number of records returned.

The number count in place fails to return.

cursor = conn.cursor()
script = """
SELECT TOP 10 * FROM person.person
"""
cursor.execute(script)

with open("c:\\temp\\csv_from_sql.csv", "w") as csv_from_sql:
    csv_writer = csv.writer(csv_from_sql, delimiter=',', lineterminator='\n')
    # Write field name header line
    # fields = ['Field 1', 'Field 2']
    # csv_writer.writerow(fields)
    csv_writer.writerow([i[0] for i in cursor.description])
    # Write data rows
    for row in cursor:
        csv_writer.writerow(row)

# Count the number of rows in output
rows = cursor.fetchall()
row_counter = 0
for row in rows:
    row_counter =+ 1
print('Number of rows %d' % row_counter)

Any help would be great!

Thanks,
Frank

On line 14 you iterate over cursor after you have executed the query. Thus you have exhausted it and it is empty, so when you try to fetchall(), there is nothing to fetch.

If number of records in not huge you can fetchall() before writing to csv file, check len of the list and then write to csv file:

result = cursor.fetchall()
csv_writer.writerows(result)
print(f'Nimber of rows: {len(result)}')

alternatively, wrap cursor in enumerate() and write to csv file, at the end you will have number of rows

for row_num, row in enumerate(cursor, start=1):
    csv_writer.writerow(row)
print(f'Nimber of rows: {row_num}')

I went with the enumerate() function. It worked.

Thanks for your help!

by the way, cursor.rowcount (if implemented) should also return number of rows returned. My bad I overlooked when answering...

https://www.python.org/dev/peps/pep-0249/#rowcount