Michele Simionato wrote: > 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
I am no expert, but as I have not seen any other replies, I will explain how I do it. Firstly, I seem to recall that when you install psycopg, it tries to guess whether to compile it for mx.DateTime or the built-in datetime module, based on version of Python, whether mx is installed, etc. I am sure it is possible to force it to choose your preferred one by modifying the .cfg file (I think). Assuming it is set up correctly for the datetime module, this is how I insert and retrieve. If I have a datetime.datetime instance called dat, I insert it using str(dat). I found retrieving a bit trickier, as I have standardised on using the datetime.datetime type throughout my app, but I have a mixture of DATE and TIMESTAMP types in my database. Therefore I convert the result as follows - import datetime as dt def DbToDate(dat): if isinstance(dat,dt.datetime): return dat # already in datetime format if isinstance(dat,dt.date): return dt.datetime.combine(dat,dt.time(0)) # convert to datetime HTH Frank Millman -- http://mail.python.org/mailman/listinfo/python-list