471,055 Members | 2,037 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,055 software developers and data experts.

inserting/retriving dates in psycopg

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

Jan 4 '06 #1
4 2987

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

Jan 5 '06 #2
Frank Millman:
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


This is exactly the type checking I would like to avoid :-/

Michele Simionato

Jan 5 '06 #3

Michele Simionato wrote:
Frank Millman:
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


This is exactly the type checking I would like to avoid :-/

Michele Simionato


psycopg returns a datetime.datetime object from a TIMESTAMP column, and
a datetime.date object from a DATE column. You should not have to do
any type checking unless you are doing something odd, like I am, and
wanting to convert them all into datetime.datetime objects.

Perhaps if you explain what you are trying to do, I may be able to
suggest something.

Frank

Jan 5 '06 #4
Frank Millan:
Perhaps if you explain what you are trying to do, I may be able to
suggest something.


I am looking for an adaptation/type cast mechanism and looking at the
sources I think I have
found it in doc/examples/usercast.py. I am doing some experiment now
....

Michele Simionato

Jan 5 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by Alban Hertroys | last post: by
12 posts views Thread by Alban Hertroys | last post: by
4 posts views Thread by Alban Hertroys | last post: by
11 posts views Thread by Alban Hertroys | last post: by
1 post views Thread by roderik | last post: by
7 posts views Thread by jslowery | last post: by
2 posts views Thread by Martin P. Hellwig | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.