inserting/retriving dates in psycopg

[Michele Simionato]

Look at this example:

>>> import psycopg
>>> psycopg.__version__
'1.1.19'
>>> import datetime
>>> today = datetime.datetime.today()
>>> co = psycopg.connect('')
>>> cu = co.cursor()

>>> cu.execute('CREATE TABLE example (date date)')
>>> cu.execute("INSERT into example VALUES (%s)", (today,))
Traceback (most recent call last):
  File "/usr/lib/python2.4/doctest.py", line 1243, in __run
    compileflags, 1) in test.globs
  File "<doctest __main__[6]>", line 1, in ?
    cu.execute("INSERT into example VALUES (%s)", (today,))
ProgrammingError: ERROR:  syntax error at or near "17" at character 40

    INSERT into example VALUES (2006-01-04 17:19:03.354615)

Is there a smart way of solving this, without stripping the date by
hand?
Also, if I insert the date as a string

>>> cu.execute("INSERT into example VALUES ('2006-01-04')")
>>> co.commit()

I get back a DateTime object (I think a mxDateTime object)

>>> cu.execute("SELECT * from example")
>>> d = cu.fetchone()[0]
>>> print type(d)
 <type 'DateTime'>

and NOT a datetime.datetime.

So I need an adaptation mechanism; alternatively it would be enough for
me to be able
to redefine the __str__ representation of psycopg DateTime objects (
which are defined
at C level, so I cannot just override the __str__ method).

Any  hints? I am sure there is a custom way to do this.

                Michele Simionato