Skip to content
Advertisement

Python and SQL: Getting rows from csv results in ERROR: “There are more columns in the INSERT statement than values specified in the VALUES clause.”

I have a csv file with several records that I am trying to import into a SQL table via a Python script. My csv file (now reduced to) just one row of 1s. Here is what I am trying to do (after successfully connecting to the database etc etc…):

def add_records():
    with open('C:/testing/myCSV.csv') as csvFile:

        for row in csvFile:
            cursor.execute(
                "INSERT INTO MY_Table (thing1, thing2, thing3, thing4, thing5)"
                "VALUES (?)", row
            )

No matter how I format the data in the csv (right now it’s all 1s) I get the error:

There are more columns in the INSERT statement than values specified in the VALUES clause

Advertisement

Answer

You need to specify a value (or ? placeholder) in the values clause for each column you’re inserting:

cursor.execute(
    "INSERT INTO MY_Table (thing1, thing2, thing3, thing4, thing5)"
    "VALUES (?, ?, ?, ?, ?)", row
)

EDIT:
row is just a simple line read from a CSV file. You should probably use a csv reader to break it up to its individual components:

with open('C:/testing/myCSV.csv') as csvFile:
    csvReader = csv.reader(csvFile)
    for row in csvReader:
        cursor.execute(
            "INSERT INTO MY_Table (thing1, thing2, thing3, thing4, thing5)"
            "VALUES (?, ?, ?, ?, ?)", row
        )
User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement