Not enough arguments for format string mysql error

Question

Can anyone help, I have no idea why it keeps returing the error

ERROR: Not enough arguments for format string

It's reading from a csv where the headers are named Property ID, Reference Number etc. The only difference is the addition of the _ in the table column names.

Here is my script for reference:

import csv
import pymysql
mydb = pymysql.connect(host='127.0.0.1', user='root', passwd='root', db='jupix', unix_socket="/Applications/MAMP/tmp/mysql/mysql.sock")
cursor = mydb.cursor()
with open("activeproperties.csv") as f:
 reader = csv.reader(f)
 # next(reader) # skip header
 data = []
 for row in reader:
 cursor.executemany('INSERT INTO ACTIVE_PROPERTIES(Property_ID, Reference_Number,Address_Name,Address_Number,Address_Street,Address_2,Address_3,Address_4,Address_Postcode,Owner_Contact_ID,Owner_Name,Owner_Number_Type_1,Owner_Contact_Number_1,Owner_Number_Type_2,Owner_Contact_Number_2,Owner_Number_Type_3,Owner_Contact_Number_3,Owner_Email_Address,Display_Property_Type,Property_Type,Property_Style,Property_Bedrooms,Property_Bathrooms,Property_Ensuites,Property_Toilets,Property_Reception_Rooms,Property_Kitchens,Floor_Area_Sq_Ft,Acres,Rent,Rent_Frequency,Furnished,Next_Available_Date,Property_Status,Office_Name,Negotiator,Date_Created)''VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)', row) 
mydb.commit()
cursor.close()
print"Imported!"

• Sharku: Could you print your row object? It seems it does not have enough entries to fill all your VALUES
• Arron: Not sure what you mean?
• Sharku: The %s are filled up with the content of the row object, so it has to be a list with exactly as many entries as you have %ss . Since you are getting a format error I assumed that is not the case. Oh and I just realized your intentantion in you for loop is off.
• Edeki Okoh: You have 37 columns but 38 %s which is the issue.
• Arron: I have corrected the amount however I am still getting the same error message "Not enough arguments for format string". I have 37 columns and 37 %s so what would still be causing this error?

Answer 1

The error is happening because you have 37 columns that you are trying to insert data into, but 38 inputs that you are sending to the database i.e %s. Therefore you are telling the cursor to send a piece of data to the database but the cursor does not know where to insert the data into. You either forgot to include a column in your INSERT INTO statement, or have an extra %s in your statement.

Therefore you need to remove one of the %s in your SQL statement, or add a column in your database to send the last piece of data into.

• Arron: I have corrected the number of columns now and %s but still getting the same message :(
• Edeki Okoh: Can you post your updated code as an edit to the original question

Answer 2

When you have a large number of columns, it can be a challenge to make sure you have one %s for each column. There's an alternative syntax for INSERT that makes this easier.

Instead of this:

INSERT INTO ACTIVE_PROPERTIES(Property_ID, Reference_Number,
 Address_Name, Address_Number, Address_Street, Address_2, Address_3,
 Address_4, Address_Postcode, Owner_Contact_ID, Owner_Name,
 Owner_Number_Type_1, Owner_Contact_Number_1, Owner_Number_Type_2,
 Owner_Contact_Number_2, Owner_Number_Type_3, Owner_Contact_Number_3,
 Owner_Email_Address, Display_Property_Type, Property_Type,
 Property_Style, Property_Bedrooms, Property_Bathrooms, Property_Ensuites,
 Property_Toilets, Property_Reception_Rooms, Property_Kitchens,
 Floor_Area_Sq_Ft, Acres, Rent, Rent_Frequency, Furnished,
 Next_Available_Date, Property_Status, Office_Name, Negotiator,
 Date_Created)
VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
 %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
 %s, %s, %s, %s, %s)

Try the following, to make it easier to match up columns with %s parameters, so you don't miscount:

INSERT INTO ACTIVE_PROPERTIES
SET Property_ID = %s,
 Reference_Number = %s,
 Address_Name = %s,
 Address_Number = %s,
 Address_Street = %s,
 Address_2 = %s,
 Address_3 = %s,
 Address_4 = %s,
 Address_Postcode = %s,
 Owner_Contact_ID = %s,
 Owner_Name = %s,
 Owner_Number_Type_1 = %s,
 Owner_Contact_Number_1 = %s,
 Owner_Number_Type_2 = %s,
 Owner_Contact_Number_2 = %s,
 Owner_Number_Type_3 = %s,
 Owner_Contact_Number_3 = %s,
 Owner_Email_Address = %s,
 Display_Property_Type = %s,
 Property_Type = %s,
 Property_Style = %s,
 Property_Bedrooms = %s,
 Property_Bathrooms = %s,
 Property_Ensuites = %s,
 Property_Toilets = %s,
 Property_Reception_Rooms = %s,
 Property_Kitchens = %s,
 Floor_Area_Sq_Ft = %s,
 Acres = %s,
 Rent = %s,
 Rent_Frequency = %s,
 Furnished = %s,
 Next_Available_Date = %s,
 Property_Status = %s,
 Office_Name = %s,
 Negotiator = %s,
 Date_Created = %s;

It's not standard SQL, but it's supported by MySQL. It does the same thing internally, it's just more convenient syntax, at least when you're inserting a single row at a time.

Answer 3

a simple example. also you can manipulate it for your needs.

myDict = {'key-1': 193699, 'key-2': 206050, 'key-3': 0, 'key-N': 9999999}
values = ', '.join(f"'{str(x)}'" for x in myDict.values())
columns = ', '.join(myDict.keys())
sql = f"INSERT INTO YourTableName ({columns}) VALUES ({values});"
conn = pymysql.connect(autocommit=True, host="localhost",database='DbName', user='UserName', password='PassWord',)
with conn.cursor() as cursor:
 cursor.execute(sql)