Menu
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()
883
Change the curdate() (current date) format in MySQL The current date format is 'YYYY-mm-dd'. To change current date format, you can use date_format().
To query data in a MySQL database from Python, you need to do the following steps: Connect to the MySQL Database, you get a MySQLConnection object. Instantiate a MySQLCursor object from the the MySQLConnection object. Use the cursor to execute a query by calling its execute() method.
To insert a date in a MySQL database, you need to have a column of Type date or datetime in your table. Once you have that, you'll need to convert your date in a string format before inserting it to your database. To do this, you can use the datetime module's strftime formatting function.
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)"""
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
