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

Groups > comp.lang.python > #62534 > unrolled thread

Re: Datetime string reformatting

Back to article view | Back to comp.lang.python

This discussion starts older than the indexed window; earlier articles aren't shown. The article labeled Started by below is the oldest one visible, not the original post.

Contents

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

#62534 — Re: Datetime string reformatting

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

[toc] | [standalone]

Back to top | Article view | comp.lang.python

csiph-web