Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.python > #62534

Re: Datetime string reformatting

Show key headers only | View raw

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

Back to comp.lang.python | Previous | Next | Find similar | Unroll thread

Thread

Re: Datetime string reformatting Andreas Perstinger <andipersti@gmail.com> - 2013-12-22 18:03 +0100

csiph-web