SQLAlchemy error with PostgreSQL.
Olli Wang
http://docs.turbogears.org/1.0/SQLAlchemy. Everything is ok if I
"tg-admin sql create" by default sqlite uri. But after I change the uri
to postgresql and create it(no error occurs), every page of the site
return a traceback:
500 Internal error
The server encountered an unexpected condition which prevented it from
fulfilling the request.
Traceback (most recent call last):
File
"c:\python24\lib\site-packages\CherryPy-2.2.1-py2.4.egg\cherrypy\_cphttptools.py",
line 103, in _run
applyFilters('before_main')
File
"c:\python24\lib\site-packages\CherryPy-2.2.1-py2.4.egg\cherrypy\filters\__init__.py",
line 151, in applyFilters
method()
File
"c:\python24\lib\site-packages\TurboGears-1.0b1-py2.4.egg\turbogears\visit\api.py",
line 140, in before_main
visit= _manager.visit_for_key( visit_key )
File
"c:\python24\lib\site-packages\TurboGears-1.0b1-py2.4.egg\turbogears\visit\savisit.py",
line 42, in visit_for_key
if not visit or visit.expiry < now:
TypeError: can't compare offset-naive and offset-aware datetimes
Is the postgresql not compatible with SA and TG? thanks
Robin Munn
What version of SQLAlchemy are you using? If it's the SVN version,
what revision?
http://www.sqlalchemy.org/trac/ticket/275 is exactly this issue, and a
sort-of fix was committed in SQLAlchemy revision 1824. I say a
"sort-of" fix because this isn't exactly a bug; see the ticket for the
gory details. The solution that was committed was to add a
"timezone=True" (or "timezone=False") keyword parameter that you can
pass when you create your DateTime fields, to tell PostgreSQL whether
or not to return timezone information with the DateTime.
It might be that the default TurboGears SQL creation functions for
SQLAlchemy need to be updated to use the timezone=True (or
timezone=False) keyword.
Hopefully between this summarized explanation and the notes on that
ticket, you'll be able to understand what's going on. Otherwise, I'll
try to explain better when I have a little more time.
--
Robin Munn
rm...@pobox.com
GPG key 0xD6497014
Olli Wang
the default model of TG, I don't understand what's the relationship
with that ticket. BTW, the "tg-admin sql drop" doesn't work, either. :(
Jorge Godoy
> "c:\python24\lib\site-packages\TurboGears-1.0b1-py2.4.egg\turbogears\visit\savisit.py",
> line 42, in visit_for_key
> if not visit or visit.expiry < now:
It looks like these have different mappings / datatypes and SA isn't being
able to do the casting by itself...
> TypeError: can't compare offset-naive and offset-aware datetimes
>
> Is the postgresql not compatible with SA and TG? thanks
It is compatible. The exception above talks about different objects for
datetimes. You have to check the mapping done... I don't remember having
this problem with recent TG and recent SQL Alchemy.
--
Jorge Godoy <jgo...@gmail.com>
Robin Munn
The error you're getting indicates that datetime objects without
timezones ("naive" datetimes) are being compared to datetime objects
with timezones ("aware" datetimes), which is an error. (How do you
compare the time "11:45:00" with the time "12:00:00 GMT"? The answer
depends on what timezone you're in, and in the face of ambiguity,
Python refuses to guess). The discussion on that ticket has to do with
how such a comparison might happen.
Try setting "sqlalchemy.echo=1" on in your dev.cfg and look at the SQL
being produced. You'll probably notice that the visit table's datetime
columns are being specified as "TIMESTAMP WITH TIME ZONE". But the
datetime.now() method defaults to returning a naive
(non-timezone-aware) datetime, which when compared to a "TIMESTAMP
WITH TIME ZONE" type will fail due to the timezone-comparison
ambiguity.
The solution here would be to either change the definition of the
visit (and other) tables to use "timezone=False" in their SQLAlchemy
column definitions, or else to pass a timezone argument to the
"datetime.now()" function when it's called.
Robin Munn
It seems, from looking at "tg-admin sql help", that only the "create"
function has been implemented so far for SQLAlchemy...
Cito
> The solution here would be to either change the definition of the
> visit (and other) tables to use "timezone=False" in their SQLAlchemy
> column definitions, ...
Does this really suffice? I found that it does not work with the
current and SVN versions of SQLAlchemy. Only after adding the following
line to Column's init method in schema.py, everything worked as
expected:
self.timezone = kwargs.pop('timezone', True)
Cito
directly to the Column. What you need to do is to add
DateTime(timezone=False) to the Column:
Column(..., DateTime(timezone=False))
Olli Wang
too. thanks