edaniley edaniley - 1 year ago 105
Python Question

Python: StopIteration error being raised iterating through blank csv

I'm a new python user and have an issue. I apologize in advance if the solution is obvious.

I intend to be able to take a potentially large amount of csv files and jam them into a database which I can then use sql to query for reporting and other sweet stuff and I have the following code:

import csv

# Establishes a db connection and returns connection and cursor obj
# creates dbName.db file in given location
def openDB (dbName,location):

import sqlite3,os


conn = sqlite3.connect(dbName)
c = conn.cursor()

return conn,c

# Uses connection, cursor, csv obj and writes into table
def insertFromCsv (csvObj,connection,cursor,tableName):

c = cursor

# Just added this condition to check for blank files
# but I'm not sure if this is appropriate..
rowCount = sum(1 for row in csvObj)
if rowCount > 0:
i = 0
for row in csvObj:
tablerow = ", ".join('"' + value + '"' for value in row)
insertSQL = "INSERT INTO '%s' VALUES (%s)" % (tableName,tablerow)
i += 1

print '%s rows committed to table %s' % (i, tableName)

# creates the .reader obj
reader = csv.reader(csvFile)
# extract column names from csv header
tableFields = reader.next()
# formats the column names for the INSERT statement coming up
tableFields = ", ".join('"' + field + '"' for field in tableFields)

DB = openDB('foo.db','../bar')
tableName = myTable

insertFromCsv() takes as input a csv file .reader object, sqlite3 database connection and cursor objects, and an output table to create and insert into.

It has been working alright until recently when I tried to input a csv file which consisted of just a header. I got a StopIteration error after calling the .next() method. How can this be avoided/ what am I misunderstanding/overlooking?

I appreciate all the help and welcome any criticism!

Answer Source

You have exhausted the csvObj iterator on the line before:

rowCount = sum(1 for row in csvObj)

Once an iterator is exhausted, you can't call next() on it anymore without that raising StopIteration; you have reached the end of the iterator already.

If you want to test for a blank CSV file, read one row with the next() function, which can be given a default. next(csvObj, None) would return None rather than propagate the StopIteration exception when the iterator is exhausted for example.

Next, use SQL parameters to create one generic SQL statement, then use cursor.executemany() to have the database pull in all the rows and insert them for you:

header = next(csvObj, None)
if header:
    tablerow = ", ".join(['?'] * len(row))
    insertSQL = 'INSERT INTO "%s" VALUES (%s)' % (tableName, tablerow)
    c.executemany(insertSQL, csvObj)

The ? is a SQL parameter placeholder; executemany() will fill these from each row from csvObj.

It won't matter to the cursor.executemany() call if csvObj actually yieds any rows; if only the header exists and nothing more, then no actual INSERT statements are executed.

Note that I used "..." double quotes to correctly quote the table name, see SQLite keywords; single quotes are for string literal values, not table names.