I am trying to use mysql functions date time function str_to_date in python. I need a guide on how to use mysql functions in python. Raw_Data is the name of the table.
sql = """INSERT INTO RawData(DateTime) VALUES ('%s')""" % (STR_TO_DATE(date_time,'%d/%b/%Y:%T'))
cursor.execute(sql)
error:
NameError: name 'STR_TO_DATE' is not defined
First thing first: Never but Never use string formatting on a string and then pass the string to an SQL.
you can see the full explanation here http://initd.org/psycopg/docs/usage.html#the-problem-with-the-query-parameters (although it's for PostgreSQL this is a ground rule for DB programming). Now want you want to do is to pass the MYSQL function as a parameter to the query you can see more about how to do this in this post:
Python MySQL Parameterized Queries and here: http://mysql-python.sourceforge.net/MySQLdb.html#cursor-objects now when you bind a date you wont need to convert it into a string and then back again. so this code will be ok:
import datetime
d = datetime.datetime.now()
sql = """ INSERT INTO RawData(DateTime) Values ('%s')"""
cursor.execute(sql, (d,))
Now if you do wish to use a function in your code the function must be part of your sql not a parameter (or passed as a string parameter which is bad). This pesudo code should like this (I haven't ran it):
sql = """INSERT INTO RawData(DateTime) VALUES (STR_TO_DATE('%s',%%d/%%b/%%Y:%%T'))"""
cursor.execute(sql, (d,))
execute should be a sequence (tuple or list): so it should be cursor.execute(sql, (d,)) or cursor.execute(sql, [d]) - initd.org/psycopg/docs/…change your query to:
UPDATE:
sql = """INSERT INTO RawData(DateTime) VALUES (STR_TO_DATE(%s, '%%d/%%b/%%Y:%%T'))"""% (date_time)
sql = """INSERT INTO RawData(DateTime) VALUES (STR_TO_DATE(%s, '%%d/%%b/%%Y:%%T'))"""% (date_time,)
sql = """INSERT INTO RawData(DateTime) VALUES ('%s')"""% ("STR_TO_DATE(date_time,'%d/%b/%Y:%T')") cursor.execute(sql)