1

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
Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
user2547084
  • 11
  • 1
  • 4
  • 1
    that's a mysql function. what is it doing in your *python* code? you can't do that. – Karoly Horvath Mar 03 '14 at 14:39
  • Don't be confused about where the calls happen. If you call a MySQL function, it'll run on the database server, not in Python. You can call it like a stored procedure in Python, but it'll execute on MySQL. – duffymo Mar 03 '14 at 14:41
  • using it to extract date time object from string.I think it should return datetime object – user2547084 Mar 03 '14 at 14:42
  • @duffymo is suggesting that you wrap that all in quotes so that it is executed on the database server -- `sql = """INSERT INTO RawData(DateTime) VALUES ('%s')"""% ("STR_TO_DATE(date_time,'%d/%b/%Y:%T')") cursor.execute(sql)` – colcarroll Mar 03 '14 at 14:43
  • is there any way of using mysql functions in python. – user2547084 Mar 03 '14 at 14:46
  • Note the passing of two arguments to cursor.execute(), to stop SQL injections and other headaches. The SQL query with placeholders, and an iterable with the arguments. – dannymilsom Mar 03 '14 at 14:49
  • You can use the sql function inside the SQL query string. It will then be executed by the database (other replies demonstrate this). If you want to convert a string into a datetime object in Python space, you can use datetime.strptime() – dannymilsom Mar 03 '14 at 14:55
  • @JLLagrange WRAPING UP DIDNT'WORK FOR THIS...ERROR INCORRECT SQL SYNTAX – user2547084 Mar 03 '14 at 15:41

2 Answers2

2

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,))
Community
  • 1
  • 1
asafm
  • 911
  • 6
  • 17
  • 1
    Very good explanation. Parameters should always be passed as parameters! – Don Mar 04 '14 at 10:00
  • Just a note: the second parameter of `execute` should be a sequence (tuple or list): so it should be `cursor.execute(sql, (d,))` or `cursor.execute(sql, [d])` - http://initd.org/psycopg/docs/usage.html#passing-parameters-to-sql-queries – Don Mar 04 '14 at 10:03
1

change your query to:
UPDATE: 

sql = """INSERT INTO RawData(DateTime) VALUES (STR_TO_DATE(%s, '%%d/%%b/%%Y:%%T'))"""% (date_time)
omid
  • 762
  • 8
  • 26
  • 1
    Even date format may be put in the SQL string: `sql = """INSERT INTO RawData(DateTime) VALUES (STR_TO_DATE(%s, '%%d/%%b/%%Y:%%T'))"""% (date_time,)` – Don Mar 03 '14 at 14:48
  • It worked for me.I was using str_to_date function of mysql in the parameter.Got the mistake.thanks! – user2547084 Mar 03 '14 at 16:03