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!"

Could you print your row object? It seems it does not have enough entries to fill all your VALUES — Sharku
– Sharku, Commented Mar 1, 2019 at 15:14
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. — Sharku
– Sharku, Commented Mar 1, 2019 at 15:31
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? — Arron
– Arron, Commented Mar 4, 2019 at 9:04

Edeki Okoh · Accepted Answer · 2019-03-01 17:17:16Z

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.

answered Mar 1, 2019 at 17:17

Edeki Okoh

1,85917 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Arron Over a year ago

I have corrected the number of columns now and %s but still getting the same message :(

Edeki Okoh Over a year ago

Can you post your updated code as an edit to the original question

Bill Karwin · Accepted Answer · 2019-03-01 17:31:40Z

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.

ali reza · Accepted Answer · 2022-10-26 13:25:11Z

0

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)

answered Oct 26, 2022 at 13:25

ali reza

1511 gold badge4 silver badges14 bronze badges

Collectives™ on Stack Overflow

Not enough arguments for format string mysql error

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related