Datetime string reformatting

[Andreas Perstinger]

On 22.12.2013 11:58, Igor Korot wrote:
> My script receives a data from the csv file. In this csv file there is
> a datetime field.
> This datetime field is formatted as follows: %m/%d/%Y
> %H:%M:%S.{milliseconds}. I'm reading this field into the string with
> this format.
>
> The trouble comes from the fact that I need to insert this value into
> mySQL. Doing it directly gives me an error: "Incorrect formatting".
> After a bit of googling and trial I found out that mySQL is looking
> for the format of '%Y/%m/%d %H:%M:%S.{milliseconds}.
>
> There is a mySQL function which transfers the data into the proper
> format: STR_TO_DATE(), but I can't obviously call it since it is not
> known to Python.

You don't want to call "STR_TO_DATE()" from Python but use it inside the 
SQL statement.

So instead of doing the conversion in Python as Mark suggested, you 
could do something like

sql_stmt = """INSERT ...
               VALUES (..., STR_TO_DATE(%s, "%m/%d/%Y %H:%M:%S.{%f}"),
                       ...)"""
cursor.execute(sql_stmt, (..., mydate_from_csv, ...))

(BTW: Do you mean microseconds instead of milliseconds? And are the 
"milliseconds" really inside curly braces?)

Bye, Andreas