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

Question

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()

Paulo Freitas · Accepted Answer · 2013-11-11 03:07:09Z

7

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

edited Nov 11, 2013 at 3:07

answered Nov 11, 2013 at 2:39

Paulo Freitas

13.7k15 gold badges78 silver badges98 bronze badges

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

3 Comments

Paul Over a year ago

When I do this, I'm receiving an error ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(birthday,'%m/%d/%Y'), address) VALUES('id' , 'name', 'birthday', 'address' at line 1")

Paul Over a year ago

Freitas: I edited my post where I included the whole snippet regarding on how I execute the sql statement.

Paulo Freitas Over a year ago

@Paul Oops! Now I see that you're using DATE_FORMAT() in the wrong place, I've updated my answer accordingly, check if it works now. :)

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related