CODEWITHSUNDEEP
pythonmysql
Paul
Paul

Reputation: 85

How do I use DATE_FORMAT inside my MySQL statement in Python?

I am trying to import rows of a csv file into a mysql table, I am using Python to do this. Here's a snippet of my mysql statement from my python script:

sql = """INSERT INTO tbl_celebrants(id, name, DATE_FORMAT(birthday,'%m/%d/%Y'), address) \
         VALUES(%s , %s, %s, %s)"""

I am getting an error where it says ValueError: unsupported format character 'm' (0x6d) at index 60

The Date format in my csv file is mm/dd/yyyy. I have tried using %% in the DATE_FORMAT( '%%m/%%d/%%Y') in my python script as suggested by what I have read somewhere in this site but it did not work for me. I appreciate any help and many thanks in advance.

P.S Here's how I am executing the statement

for row in reader:
    cursor = conn.cursor()
    sql = """INSERT INTO tbl_celebrants(id, name, DATE_FORMAT(birthday,'%%m/%%d       /%%Y'),address) VALUES(%s,%s,%s,%s)"""
    cursor.execute(sql, row)
    cursor.execute("commit")
    cursor.close()

Upvotes: 5

Views: 5878

Answers (1)

Paulo Freitas
Paulo Freitas

Reputation: 13649

It's right, you should double all % char inside DATE_FORMAT() to didn't get them interpreted as prepared statement placeholder. But you're using the DATE_FORMAT() function in the wrong place, you should use it inside the VALUES(...) declaration, so to fix all the issue try that:

sql = """INSERT INTO tbl_celebrants(id, name, birthday, address)
VALUES(%s , %s, DATE_FORMAT(%s,'%%m/%%d/%%Y'), %s)"""

Upvotes: 7

Related Questions