Converting DD MM YYYY into YYYY-MM-DD?

[Ben Finney]

Gilles Ganault <nospam at nospam.com> writes:

> 	I need to convert DD MM YYYY dates into the MySQL-friendly
> YYYY-MM-DD

This is not specific to MySQL. It is the common international standard
date representation format defined by ISO 8601.

> and translate the month name from literal French to its numeric
> equivalent (eg. "Janvier" into "01").

The simplest way to do this would be by a mapping from month-name to
month-number.

An obvious, and wrong, approach to this would be to hard-code the twelve
month names into your program data.

Instead, you should generate the map based on the standard library (in
this case, the underlying C standard library) locale database
<URL:http://docs.python.org/library/locale.html?highlight=locale%20date#locale.nl_langinfo>:

>>> import locale
>>> locale.setlocale(locale.LC_TIME, "en_AU.UTF-8")
>>> months = dict(
...     (locale.nl_langinfo(getattr(locale, key)), i)
...     for (key, i) in (
...         ('MON_%(i)d' % vars(), i)
...         for i in range(1, 12+1)))

>>> import pprint
>>> pprint.pprint(months)
{'April': 4,
 'August': 8,
 'December': 12,
 'February': 2,
 'January': 1,
 'July': 7,
 'June': 6,
 'March': 3,
 'May': 5,
 'November': 11,
 'October': 10,
 'September': 9}

Of course, if you can avoid having to generate this mapping at all in
your program, that's best; see below.

> Here's an example:
>
> SELECT dateinscription, dateconnexion FROM membres LIMIT 1;
> 26 Mai 2007|17 Août 2009 - 09h20
>
> I'd like to update the row into "2007-05-26" and "2009-08-17 09:20",
> respectively.

Storing a timestamp as a text attribute in a database seems perverse and
begging for trouble. Doesn't the database have a timestamp data type? Or
perhaps that's what you're trying to achieve?

> What is the best way to do this in Python?

The ‘datetime.strptime’ function will create a Python ‘datetime’ object
from a string, parsed according to a format
<URL:http://docs.python.org/library/datetime.html?highlight=parse%20date%20time#datetime.datetime.strptime>.

I don't know whether that function allows for month names in the current
locale (as set by ‘locale.setlocale(locale.LC_TIME, …)’). If it does,
that's the right way, since it doesn't involve explciitly generating the
mapping as shown above.

Use your preferred Python-to-database library to feed that ‘datetime’
object directly to the database and store it in an attribute of the
native database timestamp type.

Then, format the timestamp value at the point of outputting that value,
instead of storing the text representation in the database.

-- 
 \         “To succeed in the world it is not enough to be stupid, you |
  `\                            must also be well-mannered.” —Voltaire |
_o__)                                                                  |
Ben Finney