0

I have written the following code to import CSV data into mysql DB via python. But am getting the following error "MySQLdb._exceptions.ProgrammingError: not enough arguments for format string"

I have tried the following code.

import pandas as pd
import MySQLdb
df = pd.read_csv('D:\\UofG\\orders.csv',  sep='|', index_col=False)
print(df)

database = MySQLdb.connect (host="localhost", user="root", passwd="050194.Piku", db = "lineitem")
cursor = database.cursor()

for row in df:
    cursor.execute("INSERT INTO ORDER_TABLE (O_ORDERKEY, O_CUSTKEY, O_ORDERSTATUS, O_TOTALPRICE, O_ORDERDATE, O_ORDERPRIORITY, O_CLERK, O_SHIPPRIORITY, O_COMMENT) VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')", row)

cursor.close()

print ("COMPLETE")

The Error:

Traceback (most recent call last):
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37-32\lib\site-packages\MySQLdb\cursors.py", line 201, in execute
    query = query % args
TypeError: not enough arguments for format string

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:/Users/Administrator/PycharmProjects/NewDB/read_data.py", line 10, in <module>
    cursor.execute("INSERT INTO ORDER_TABLE (O_ORDERKEY, O_CUSTKEY, O_ORDERSTATUS, O_TOTALPRICE, O_ORDERDATE, O_ORDERPRIORITY, O_CLERK, O_SHIPPRIORITY, O_COMMENT) VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')", row)
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37-32\lib\site-packages\MySQLdb\cursors.py", line 203, in execute
    raise ProgrammingError(str(m))
MySQLdb._exceptions.ProgrammingError: not enough arguments for format string
roganjosh
  • 12,594
  • 4
  • 29
  • 46
Udayan Mitra
  • 23
  • 2
  • `for row in df` does not do what you think it does. You should start by investigating what that actually gives. – roganjosh Jul 06 '19 at 13:11

1 Answers1

0

The line for row in df: does NOT iterate over the rows like you intended, it rather iterates over the column names (row will be of type str and hold the name of a column in this case).

Rather look into the methods .iterrows(), .itertuples() or .iteritems().

For example (code from the docs I linked above):

for label, content in df.iteritems():
    print('label:', label)
    print('content:', content)

Here is a related question about it: How to iterate over rows in a DataFrame in Pandas?

Ralf
  • 16,086
  • 4
  • 44
  • 68
  • my CSV doesn't have any headers, and i am trying to import the data that i have in that file into an existing MySQL table. Can you give me any suggestions? I have modified the code to:import pandas as pd import MySQLdb df = pd.read_csv('D:\\UofG\\orders.csv', sep='|', index_col=False) #print(df) database = MySQLdb.connect (host="localhost", user="root", passwd="050194.Piku", db = "lineitem") cursor = database.cursor() df.to_sql(con=database, name='ORDER_TABLE', if_exists='append') database.commit() cursor.close() print ("COMPLETE") – Udayan Mitra Jul 07 '19 at 00:14
  • But i get this error: "pandas.io.sql.DatabaseError: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': not all arguments converted during bytes formatting" – Udayan Mitra Jul 07 '19 at 00:18