1

I followed the recommendations made here and here to insert records into a MySQL table, but the "not all arguments converted during string formatting" error persists.

import csv
import MySQLdb

//snip// # MySQL server login code

MyCSV = open('file.csv')
csv_data = csv.reader(MyCSV)

records = []
for row in csv_data:
    if "Request_Date" in row:
        # skip header row (field 1 header = 'Request_Date')
        continue
    else:
        records.append(tuple(row))

cursor.executemany('INSERT INTO SchemaName.TableName VALUES 
(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', records)

MyDB.commit()
cursor.close()
MyDB.close()

Each tuple in 'records' contains 15 elements, some of which are blank.

print(records[:2]) generates...

[('MM/DD/YYYY', 'first last', 'a-b/c', 'abcde-f', '', '', 'No', '', '', '', '', '', '', 'category-a', ''), ('MM/DD/YYYY', 'first last', 'e-f/g', 'abcde-f', '', '', 'No', '', '', '', '', '', '', 'category-b', '')]

Here is the error that results when the 'cursor.executemany' line is run:

  File "C:\Users\mburnett\AppData\Local\Programs\Python\Python36-32\lib\site-packages\MySQLdb\cursors.py", line 238, in execute
    query = query % args TypeError: not all arguments converted during string formatting

During handling of the above exception, another exception occurred:

<<<...>>>

  File "C:\Users\mburnett\AppData\Local\Programs\Python\Python36-32\lib\site-packages\MySQLdb\connections.py", line 52, in defaulterrorhandler
    raise errorclass(errorvalue)
_mysql_exceptions.ProgrammingError: not all arguments converted during string formatting

I noticed in Kashyap's answer (second link) that his list of tuples ('purchases') has a comma before the last bracket. My list of tuples does not have this trailing comma. If that last comma is necessary for this to work, what's the best method of getting it there?

Thanks in advance!

running Python 3.6.4/Windows 10

chepner
  • 497,756
  • 71
  • 530
  • 681
MBB70
  • 375
  • 2
  • 16
  • The placeholder is `%s` (don't confuse with string formatting), not `?`. – Ilja Everilä May 08 '18 at 21:21
  • Thank you, Ilja. I tested this and it works, but I noticed it doesn't work if I change the date string from "MM/DD/YYYY" to "YYYY-MM-DD". I'm just moving digits around and using hyphens instead of slashes, so I'm not sure why it wouldn't work. I'll update the original post. – MBB70 May 09 '18 at 13:56
  • Hold on a moment...my new csv somehow has a 16th field tacked on to the end. I'll get back with you. In the meantime, fields one and six in my destination are actually supposed to be datetime and not text. I tested the functional csv with those two fields set up that way and it didn't work. Are there special placeholders that need to be used for datetime? Thanks! – MBB70 May 09 '18 at 14:07
  • No, you'd either pass a (text) literal that's convertible to a datetime in the DB as is, pass some text as an argument (using placeholders) to some function that's able to convert, or if using a DB-API driver that understands Python's `datetime`, pass a `datetime` object. – Ilja Everilä May 09 '18 at 14:25
  • OK, that's fixed....YYYY-MM-DD isn't a problem. Excel's UsedRange command was pulling in an extra column. Thanks for your help. – MBB70 May 09 '18 at 14:29
  • I just tried this again with the correctly configured csv and fields one and six set to datetime and it working. PyCharm was spitting out a slew of data truncation warnings, which made me think there was trouble, but these were caused by blank values in field 6 (which won't always contain data). The table is populated correctly. I'd like for these warning to not appear....I'll do some research on that. Thanks again. – MBB70 May 09 '18 at 14:41

0 Answers0