RE: [sqlalchemy] Cannot retrieve PostgreSQL array column with session.query() : TypeError: unhashable type: 'list'
Thanks for your reply.
What you propose would work for me, but in the meantime I need a workaround.
I thought of serializing the arrays in SQL, then converting back to tuples
in my code, but the objects contained in the arrays are quite complex to
parse (decimals, datetims...). So I tried patching the ARRAY class to return
tuples when mutable=False, and that had no effect. If I understand
correctly, by the time unique_list() is called, ARRAY hasn't been involved
yet, and the database's array is converted to a Python list by the driver
(psycopg2 in my case).
The workaround I've found is to make the following change, in
sqlalchemy.orm.query.Query.instances :
if filtered:
#if single_entity:
#filter = lambda x: util.unique_list(x, util.IdentitySet)
#else:
#filter = util.unique_list
filter = lambda x: util.unique_list(x, util.IdentitySet)
Should I expect negative side-effects from this?
-Message d'origine-
De : sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] De la
part de Michael Bayer
Envoyé : mercredi 13 octobre 2010 23:37
À : sqlalchemy@googlegroups.com
Objet : Re: [sqlalchemy] Cannot retrieve PostgreSQL array column with
session.query() : TypeError: unhashable type: 'list'
The Query runs the result through unique_list() anytime there are mapped
entities in the columns list. The ARRAY result, returning a Python list
[], isn't hashable, so thats that.
If you only queried for columns, it wouldn't be running through
unique_list().
I suppose we'd modify ARRAY to return tuples if it's mutable flag isn't
set. that could only be in 0.7, though. Let me know if that works for
you, we'll add a ticket (hard for me to say since I never use the ARRAY
type).
On Oct 13, 2010, at 2:22 PM, Julien Demoor wrote:
Hello,
The problem I'm seeing is illustrated by the code below. I tried a
workaround using TypeDecorator with process_result_value returning a
tuple rather than a list, to no avail.
Any help will be greatly appreciated.
Regards.
Traceback :
Traceback (most recent call last):
File satest2.py, line 23, in module
session.query(Foo, 'col').from_statement(SELECT 55 AS foo_bar,
'{1,2,3}'::integer[] AS col;).first()
File /home/user/programs/env/lib/python2.6/site-packages/sqlalchemy/
orm/query.py, line 1494, in first
ret = list(self)[0:1]
File /home/user/programs/env/lib/python2.6/site-packages/sqlalchemy/
orm/query.py, line 1682, in instances
rows = filter(rows)
File /home/jdemoor/programs/km/lib/python2.6/site-packages/
sqlalchemy/util.py, line 1193, in unique_list
return [x for x in seq if x not in seen and not seen.add(x)]
TypeError: unhashable type: 'list'
Full code :
import os
from sqlalchemy import create_engine, Table, Integer, MetaData, Column
from sqlalchemy.orm import create_session, mapper
sa_engine = create_engine(os.environ['TEST_DSN'])
session = create_session(sa_engine, autoflush=True,
expire_on_commit=True, autocommit=False)
metadata = MetaData()
foo = Table('foo', metadata, Column('bar', Integer, primary_key=True))
class Foo(object):
pass
mapper(Foo, foo)
# This works
assert session.query('col').from_statement(SELECT 'abc' AS
col;).first() == ('abc',)
assert session.query('col').from_statement(SELECT
'{1,2,3}'::integer[] AS col;).first() == ([1,2,3],)
assert session.query('col1', 'col2').from_statement(SELECT
'{1,2,3}'::integer[] AS col1, 'abc' AS col2;).first() == ([1,2,3],
'abc')
foo_obj = session.query(Foo).from_statement(SELECT 1 AS
foo_bar;).first()
assert foo_obj.bar == 1
try:
# This fails
session.query(Foo, 'col').from_statement(SELECT 55 AS foo_bar,
'{1,2,3}'::integer[] AS col;).first()
except TypeError, e:
print e
from sqlalchemy.dialects.postgresql.base import ARRAY
col = Column('col', ARRAY(Integer, mutable=False))
try:
# This fails too
session.query(Foo, col).from_statement(SELECT 55 AS foo_bar,
'{1,2,3}'::integer[] AS col;).first()
except TypeError, e:
print e
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more
[sqlalchemy] Re: MapperExtension.[before|after]_update problem
Hi Connor, On 13 oct, 18:23, Conor conor.edward.da...@gmail.com wrote: On 10/13/2010 10:55 AM, Christophe de Vienne wrote: Hi all, I am running into an issue with MapperExtension.[before|after]_update. [...] AFAIK SQLAlchemy does not support the following in MapperExtensions: * lazy-loading related objects (maybe?) It looks like this. Because if I make sure the related objects are loaded before flushing the problem. * changing the flush plan, which I believe means changing which objects are considered new, dirty, or deleted Which is why I do a double flush. Your code is possibly trying to do both. Yep, but the disturbing thing is that I could not reproduce the issue in a small example, hence my question. You need to instead create a SessionExtension and override before_flush, which allows you to modify the session however you want, e.g. (untested): class MySessionExtension(object): def before_flush(self, session, flush_context, instances): for obj in session.dirty: if isinstance(obj, Parent): for child in obj.children: child.name = 'another name' This is a very interesting idea, which beside solving my issue (hopefully) would also avoid a double-flush. I guess I should also have a look to AttributeExtension which could be a solution in my case. Thanks, Christophe -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Dynamic query
Given all this magic with .with_polymorphic(), I'm now wondering if
it's really worth to have a new Query class (by subclassing orm.Query)
per mapped object just to apply some filters...
At first I found quite elegant to have one Query object per mapped
class, but now I'm wondering if it's not better after all to have a
bunch of @staticmethod in the model ...
What do you think ?
On 10/13/2010 11:21, Julien Cigar wrote:
On 10/12/2010 18:05, Julien Cigar wrote:
On 10/12/2010 17:09, Michael Bayer wrote:
On Oct 12, 2010, at 7:39 AM, Julien Cigar wrote:
Hello,
any idea why with
# Query
class BaseQuery(orm.Query):
@dynamic
def method_a(self):
...
def method_b(self):
...
class FooQuery(BaseQuery):
...
class BarQuery(FooQuery):
@dynamic
def method_c(self):
...
# Models
class BaseModel(object):
query = Session.query_property(BaseQuery)
#
myQuery = type('PolymorphicQuery, (content.BaseQuery, ),
func_list)(BaseModel)
where func_list containing all the functions decorated by @dynamic
the following fail? :
- myQuery.get(45) fails with: AttributeError: 'NoneType' object has
no attribute 'identity_map'
- myQuery.method_a().all() fails with: AttributeError: 'NoneType'
object has no attribute '_autoflush'
- etc
OK I think I found a solution, I need to pass session=Session.registry()
to my custom query:
model.content.ContentQuery.__mro__
(class 'amnesia.model.content.ContentQuery', class
'amnesia.model.root.RootQuery', class 'sqlalchemy.orm.query.Query',
type 'object')
PolymorphicQuery = type('PolymorphicQuery',
(model.content.ContentQuery, ), {})
q1 = PolymorphicQuery(model.Content)
q1.get(25)
Traceback (most recent call last):
File console, line 1, in module
File
/home/jcigar/venvs/pylons0.9.7/lib/python2.5/site-packages/SQLAlchemy-0.6.4-py2.5.egg/sqlalchemy/orm/query.py,
line 595, in get
return self._get(key, ident)
File
/home/jcigar/venvs/pylons0.9.7/lib/python2.5/site-packages/SQLAlchemy-0.6.4-py2.5.egg/sqlalchemy/orm/query.py,
line 1803, in _get
instance = self.session.identity_map.get(key)
AttributeError: 'NoneType' object has no attribute 'identity_map'
q2 = PolymorphicQuery(model.Content, session=meta.Session.registry())
q2.get(25)
amnesia.model.event.Event object at 0x939046c
I hope I'm not doing something wrong :p
My goal is to be able to build a custom Query object to use with the
.with_polymorphic() function ..
I've just grepped through all the source, examples and tests plus the
wiki trying to find what @dynamic is. Seems like something I'd have
come up with in the past but I've no clue at the moment what that is.
Sorry, I forgot to mention that it's just a custom decorator of mine
which add the function name to a list ... forget about it, it's just to
build the third argument of type() (func_list in my case)
--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
attachment: jcigar.vcf
Re: [sqlalchemy] Cannot retrieve PostgreSQL array column with session.query() : TypeError: unhashable type: 'list'
On Oct 14, 2010, at 4:38 AM, Julien Demoor wrote:
Thanks for your reply.
What you propose would work for me, but in the meantime I need a workaround.
If you need that exact pattern to work, build a TypeDecorator around ARRAY and
have it return a tuple around the result.
TypeDecorator is described at:
http://www.sqlalchemy.org/docs/core/types.html?highlight=typedecorator#sqlalchemy.types.TypeDecorator
The SQLAlchemy type is always involved in between where psycopg2 returns data
and where unique_list() is called.
As far as changing Query, the unique_list() is what makes it such that if you
load a Parent object with many Child objects in a joined-load collection, you
get just one Parent and not the same Parent for as many Child objects as are in
the result set.
I thought of serializing the arrays in SQL, then converting back to tuples
in my code, but the objects contained in the arrays are quite complex to
parse (decimals, datetims...). So I tried patching the ARRAY class to return
tuples when mutable=False, and that had no effect. If I understand
correctly, by the time unique_list() is called, ARRAY hasn't been involved
yet, and the database's array is converted to a Python list by the driver
(psycopg2 in my case).
The workaround I've found is to make the following change, in
sqlalchemy.orm.query.Query.instances :
if filtered:
#if single_entity:
#filter = lambda x: util.unique_list(x, util.IdentitySet)
#else:
#filter = util.unique_list
filter = lambda x: util.unique_list(x, util.IdentitySet)
Should I expect negative side-effects from this?
-Message d'origine-
De : sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] De la
part de Michael Bayer
Envoyé : mercredi 13 octobre 2010 23:37
À : sqlalchemy@googlegroups.com
Objet : Re: [sqlalchemy] Cannot retrieve PostgreSQL array column with
session.query() : TypeError: unhashable type: 'list'
The Query runs the result through unique_list() anytime there are mapped
entities in the columns list. The ARRAY result, returning a Python list
[], isn't hashable, so thats that.
If you only queried for columns, it wouldn't be running through
unique_list().
I suppose we'd modify ARRAY to return tuples if it's mutable flag isn't
set. that could only be in 0.7, though. Let me know if that works for
you, we'll add a ticket (hard for me to say since I never use the ARRAY
type).
On Oct 13, 2010, at 2:22 PM, Julien Demoor wrote:
Hello,
The problem I'm seeing is illustrated by the code below. I tried a
workaround using TypeDecorator with process_result_value returning a
tuple rather than a list, to no avail.
Any help will be greatly appreciated.
Regards.
Traceback :
Traceback (most recent call last):
File satest2.py, line 23, in module
session.query(Foo, 'col').from_statement(SELECT 55 AS foo_bar,
'{1,2,3}'::integer[] AS col;).first()
File /home/user/programs/env/lib/python2.6/site-packages/sqlalchemy/
orm/query.py, line 1494, in first
ret = list(self)[0:1]
File /home/user/programs/env/lib/python2.6/site-packages/sqlalchemy/
orm/query.py, line 1682, in instances
rows = filter(rows)
File /home/jdemoor/programs/km/lib/python2.6/site-packages/
sqlalchemy/util.py, line 1193, in unique_list
return [x for x in seq if x not in seen and not seen.add(x)]
TypeError: unhashable type: 'list'
Full code :
import os
from sqlalchemy import create_engine, Table, Integer, MetaData, Column
from sqlalchemy.orm import create_session, mapper
sa_engine = create_engine(os.environ['TEST_DSN'])
session = create_session(sa_engine, autoflush=True,
expire_on_commit=True, autocommit=False)
metadata = MetaData()
foo = Table('foo', metadata, Column('bar', Integer, primary_key=True))
class Foo(object):
pass
mapper(Foo, foo)
# This works
assert session.query('col').from_statement(SELECT 'abc' AS
col;).first() == ('abc',)
assert session.query('col').from_statement(SELECT
'{1,2,3}'::integer[] AS col;).first() == ([1,2,3],)
assert session.query('col1', 'col2').from_statement(SELECT
'{1,2,3}'::integer[] AS col1, 'abc' AS col2;).first() == ([1,2,3],
'abc')
foo_obj = session.query(Foo).from_statement(SELECT 1 AS
foo_bar;).first()
assert foo_obj.bar == 1
try:
# This fails
session.query(Foo, 'col').from_statement(SELECT 55 AS foo_bar,
'{1,2,3}'::integer[] AS col;).first()
except TypeError, e:
print e
from sqlalchemy.dialects.postgresql.base import ARRAY
col = Column('col', ARRAY(Integer, mutable=False))
try:
# This fails too
session.query(Foo, col).from_statement(SELECT 55 AS foo_bar,
'{1,2,3}'::integer[] AS col;).first()
except TypeError, e:
print e
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
Re: [sqlalchemy] Dynamic query
On Oct 14, 2010, at 9:28 AM, Julien Cigar wrote:
Given all this magic with .with_polymorphic(), I'm now wondering if it's
really worth to have a new Query class (by subclassing orm.Query) per mapped
object just to apply some filters...
At first I found quite elegant to have one Query object per mapped class, but
now I'm wondering if it's not better after all to have a bunch of
@staticmethod in the model ...
What do you think ?
I generally think subclasses of Query should be used very conservatively, and
that model specific behavior should be in the model, sure.
On 10/13/2010 11:21, Julien Cigar wrote:
On 10/12/2010 18:05, Julien Cigar wrote:
On 10/12/2010 17:09, Michael Bayer wrote:
On Oct 12, 2010, at 7:39 AM, Julien Cigar wrote:
Hello,
any idea why with
# Query
class BaseQuery(orm.Query):
@dynamic
def method_a(self):
...
def method_b(self):
...
class FooQuery(BaseQuery):
...
class BarQuery(FooQuery):
@dynamic
def method_c(self):
...
# Models
class BaseModel(object):
query = Session.query_property(BaseQuery)
#
myQuery = type('PolymorphicQuery, (content.BaseQuery, ),
func_list)(BaseModel)
where func_list containing all the functions decorated by @dynamic
the following fail? :
- myQuery.get(45) fails with: AttributeError: 'NoneType' object has
no attribute 'identity_map'
- myQuery.method_a().all() fails with: AttributeError: 'NoneType'
object has no attribute '_autoflush'
- etc
OK I think I found a solution, I need to pass session=Session.registry()
to my custom query:
model.content.ContentQuery.__mro__
(class 'amnesia.model.content.ContentQuery', class
'amnesia.model.root.RootQuery', class 'sqlalchemy.orm.query.Query',
type 'object')
PolymorphicQuery = type('PolymorphicQuery',
(model.content.ContentQuery, ), {})
q1 = PolymorphicQuery(model.Content)
q1.get(25)
Traceback (most recent call last):
File console, line 1, in module
File
/home/jcigar/venvs/pylons0.9.7/lib/python2.5/site-packages/SQLAlchemy-0.6.4-py2.5.egg/sqlalchemy/orm/query.py,
line 595, in get
return self._get(key, ident)
File
/home/jcigar/venvs/pylons0.9.7/lib/python2.5/site-packages/SQLAlchemy-0.6.4-py2.5.egg/sqlalchemy/orm/query.py,
line 1803, in _get
instance = self.session.identity_map.get(key)
AttributeError: 'NoneType' object has no attribute 'identity_map'
q2 = PolymorphicQuery(model.Content, session=meta.Session.registry())
q2.get(25)
amnesia.model.event.Event object at 0x939046c
I hope I'm not doing something wrong :p
My goal is to be able to build a custom Query object to use with the
.with_polymorphic() function ..
I've just grepped through all the source, examples and tests plus the
wiki trying to find what @dynamic is. Seems like something I'd have
come up with in the past but I've no clue at the moment what that is.
Sorry, I forgot to mention that it's just a custom decorator of mine
which add the function name to a list ... forget about it, it's just to
build the third argument of type() (func_list in my case)
--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
jcigar.vcf
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Checking the availablity of a booked Item
Here's the SQL I got : SELECT face.id AS face_id FROM face LEFT OUTER JOIN face_bookings__booking_faces AS face_bookings__booking_faces_1 ON face.id = face_bookings__booking_faces_1.face_id LEFT OUTER JOIN booking ON booking.id = face_bookings__booking_faces_1.booking_id JOIN time_period ON booking.time_period_id = time_period.id WHERE time_period.start_date %(start_date_1)s OR time_period.end_date %(end_date_1)s With the following code : class Booking(BaseModel): using_options(tablename=booking) reprattr = time_period faces = ManyToMany(Face) # A client has one and only one booking per time period time_period = ManyToOne(TimePeriod) @classmethod def get_available_faces(self,time_period): Return faces that are not booked during the given time_period. from timeperiod import TimePeriod from face import Face start_date_cond = TimePeriod.start_date time_period.end_date end_date_cond = TimePeriod.end_date time_period.start_date unbooked = or_(start_date_cond,end_date_cond) # query = Face.query.filter(Face.bookings.any(Booking.time_period.has(unbooked))) # return query.all() query = Face.query.filter(unbooked) #return query.all() query = query.outerjoin(Face.bookings) #return query.all() query = query.join(Booking.time_period) return query.all() And still not the expected results (it should return faces with no bookings at all but it doesen't). Thanks for any help. Y.Chaouche --- On Wed, 10/13/10, chaouche yacine yacinechaou...@yahoo.com wrote: From: chaouche yacine yacinechaou...@yahoo.com Subject: Re: [sqlalchemy] Checking the availablity of a booked Item To: sqlalchemy@googlegroups.com Date: Wednesday, October 13, 2010, 5:25 AM Thank you Thadeus, I believe Face.query.filter(filter_cond).outerjoin(join_clause).all() does a full outerjoin, or is there another way to do it ? Y.Chaouche --- On Wed, 10/13/10, Thadeus Burgess thade...@thadeusb.com wrote: From: Thadeus Burgess thade...@thadeusb.com Subject: Re: [sqlalchemy] Checking the availablity of a booked Item To: sqlalchemy@googlegroups.com Date: Wednesday, October 13, 2010, 12:04 AM For outer joins you need a where clause on the joined tables. http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html Using a full outer join should return the expected results. -- Thadeus On Tue, Oct 12, 2010 at 1:41 PM, chaouche yacine yacinechaou...@yahoo.com wrote: Hello, Here's my simple model (For simplification, consider Face as a Billboard) : +-+ +---+ +--+ |Face |.. |Campaign | ...|TimePeriod| +-+ . +---+ . +--+ |code | . |time_period| |start_time| +-+ . +---+ +--+ |faces | |end_time | +---+ +--+ One way to read this model is : A campaign can book multiple faces during a certain period of time. What I want to do is get all the available Faces for a given period of time, to see what faces can I book for a new campaign that longs for that particular period of time. I would typically have a Face.get_available(time_period) class method that does the job. This method would look for all the faces that don't have an ongoing booking. My question is : how to write such a method ? Here's how I figured it out (couldn't get it to work) : class Face(Entity): using_options(tablename=faces) �...@classmethod def get_available(self,time_period): Return faces that are not booked (not in any campaign) during the given time_period. # start_date_cond = TimePeriod.start_date time_period.end_date # end_date_cond = TimePeriod.end_date time_period.start_date # available_periods = Campaign.time_period.has(or_(start_date_cond,end_date_cond)) # unavailable_periods = not(available_periods) # I am pretty sure that the time conditions are good. # Here's a good way to convince yourself (read from bottom to top) : # L1 0--- # L2 --| # L3 0[]-- # L3 represents the desired period (passed as argument) going from [ to ] # place the start date of the booked face anywhere on L2 # place the end date of the booked face anywhere on L1 # of course, end date must be after start date... # Anyway you do it, your face isn't available for the period of time in L3. start_date_cond =
Re: [sqlalchemy] Checking the availablity of a booked Item
Here's the SQL I got : SELECT face.id AS face_id FROM face LEFT OUTER JOIN face_bookings__booking_faces AS face_bookings__booking_faces_1 ON face.id = face_bookings__booking_faces_1.face_id LEFT OUTER JOIN booking ON booking.id = face_bookings__booking_faces_1.booking_id JOIN time_period ON booking.time_period_id = time_period.id WHERE time_period.start_date %(start_date_1)s OR time_period.end_date %(end_date_1)s With the following code : class Booking(BaseModel): using_options(tablename=booking) reprattr = time_period faces = ManyToMany(Face) # A client has one and only one booking per time period time_period = ManyToOne(TimePeriod) @classmethod def get_available_faces(self,time_period): Return faces that are not booked during the given time_period. from timeperiod import TimePeriod from face import Face start_date_cond = TimePeriod.start_date time_period.end_date end_date_cond = TimePeriod.end_date time_period.start_date unbooked = or_(start_date_cond,end_date_cond) # query = Face.query.filter(Face.bookings.any(Booking.time_period.has(unbooked))) # return query.all() query = Face.query.filter(unbooked) #return query.all() query = query.outerjoin(Face.bookings) #return query.all() query = query.join(Booking.time_period) return query.all() And still not the expected results (it should return faces with no bookings at all but it doesen't). Thanks for any help. Y.Chaouche --- On Wed, 10/13/10, chaouche yacine yacinechaou...@yahoo.com wrote: From: chaouche yacine yacinechaou...@yahoo.com Subject: Re: [sqlalchemy] Checking the availablity of a booked Item To: sqlalchemy@googlegroups.com Date: Wednesday, October 13, 2010, 5:25 AM Thank you Thadeus, I believe Face.query.filter(filter_cond).outerjoin(join_clause).all() does a full outerjoin, or is there another way to do it ? Y.Chaouche --- On Wed, 10/13/10, Thadeus Burgess thade...@thadeusb.com wrote: From: Thadeus Burgess thade...@thadeusb.com Subject: Re: [sqlalchemy] Checking the availablity of a booked Item To: sqlalchemy@googlegroups.com Date: Wednesday, October 13, 2010, 12:04 AM For outer joins you need a where clause on the joined tables. http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html Using a full outer join should return the expected results. -- Thadeus On Tue, Oct 12, 2010 at 1:41 PM, chaouche yacine yacinechaou...@yahoo.com wrote: Hello, Here's my simple model (For simplification, consider Face as a Billboard) : +-+ +---+ +--+ |Face |.. |Campaign | ...|TimePeriod| +-+ . +---+ . +--+ |code | . |time_period| |start_time| +-+ . +---+ +--+ |faces | |end_time | +---+ +--+ One way to read this model is : A campaign can book multiple faces during a certain period of time. What I want to do is get all the available Faces for a given period of time, to see what faces can I book for a new campaign that longs for that particular period of time. I would typically have a Face.get_available(time_period) class method that does the job. This method would look for all the faces that don't have an ongoing booking. My question is : how to write such a method ? Here's how I figured it out (couldn't get it to work) : class Face(Entity): using_options(tablename=faces) �...@classmethod def get_available(self,time_period): Return faces that are not booked (not in any campaign) during the given time_period. # start_date_cond = TimePeriod.start_date time_period.end_date # end_date_cond = TimePeriod.end_date time_period.start_date # available_periods = Campaign.time_period.has(or_(start_date_cond,end_date_cond)) # unavailable_periods = not(available_periods) # I am pretty sure that the time conditions are good. # Here's a good way to convince yourself (read from bottom to top) : # L1 0--- # L2 --| # L3 0[]-- # L3 represents the desired period (passed as argument) going from [ to ] # place the start date of the booked face anywhere on L2 # place the end date of the booked face anywhere on L1 # of course, end date must be after start date... # Anyway you do it, your face isn't available for the period of time in L3. start_date_cond =
[sqlalchemy] Subtransactions problem
Hello! I am dealing with Turbogears and SQL Alchemy. I need to make one import from a CSV file to a MySQL DB. I need also to controll which rows fails on import. I am working with the next code inside a loop: try: session.begin(subtransactions=True) session.add(importe) session.flush() nimportados += 1 except Exception as ex: log.debug(ex) session.rollback() nerroneos += 1 During exec I get: - 12:55:45,598 INFO [sqlalchemy.engine.base.Engine.0x...f1ac] BEGIN 12:55:45,600 INFO [sqlalchemy.engine.base.Engine.0x...f1ac] INSERT INTO importes (tarifas_id, prefijo, destino, importe, tipo, pc, psi, ps, sg) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s) 12:55:45,600 INFO [sqlalchemy.engine.base.Engine.0x...f1ac] [u'1', 'PREFIJO', 'DESTINO', 'IMPORTE', u'', 0, 60, 60, 0] 12:55:45,601 INFO [sqlalchemy.engine.base.Engine.0x...f1ac] ROLLBACK 12:55:45,603 DEBUG [svbpanel.controllers] (IntegrityError) (1062, Duplicate entry 'PREFIJO' for key 'prefijo') u'INSERT INTO importes (tarifas_id, prefijo, destino, importe, tipo, pc, psi, ps, sg) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)' [u'1', 'PREFIJO', 'DESTINO', 'IMPORTE', u'', 0, 60, 60, 0] 12:55:45,603 DEBUG [svbpanel.controllers] The transaction is inactive due to a rollback in a subtransaction. Issue rollback() to cancel the transaction. - Peopel on TG2 group told me about the appearance of not init the subtransaction. I thing TG2 init a own transaction in an automatic way so I should work with subtransactions (I think) Commiting instead flushing doesn't work... Any idea about this problem? Regards -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Checking the availablity of a booked Item
http://www.sqlalchemy.org/docs/reference/orm/query.html#sqlalchemy.orm.join isouter = True -- Thadeus On Thu, Oct 14, 2010 at 10:26 AM, chaouche yacine yacinechaou...@yahoo.comwrote: Here's the SQL I got : SELECT face.id AS face_id FROM face LEFT OUTER JOIN face_bookings__booking_faces AS face_bookings__booking_faces_1 ON face.id = face_bookings__booking_faces_1.face_id LEFT OUTER JOIN booking ON booking.id = face_bookings__booking_faces_1.booking_id JOIN time_period ON booking.time_period_id = time_period.id WHERE time_period.start_date %(start_date_1)s OR time_period.end_date %(end_date_1)s With the following code : class Booking(BaseModel): using_options(tablename=booking) reprattr= time_period faces = ManyToMany(Face) # A client has one and only one booking per time period time_period = ManyToOne(TimePeriod) @classmethod def get_available_faces(self,time_period): Return faces that are not booked during the given time_period. from timeperiod import TimePeriod from face import Face start_date_cond = TimePeriod.start_date time_period.end_date end_date_cond = TimePeriod.end_date time_period.start_date unbooked= or_(start_date_cond,end_date_cond) # query = Face.query.filter(Face.bookings.any(Booking.time_period.has(unbooked))) # return query.all() query = Face.query.filter(unbooked) #return query.all() query = query.outerjoin(Face.bookings) #return query.all() query = query.join(Booking.time_period) return query.all() And still not the expected results (it should return faces with no bookings at all but it doesen't). Thanks for any help. Y.Chaouche --- On *Wed, 10/13/10, chaouche yacine yacinechaou...@yahoo.com* wrote: From: chaouche yacine yacinechaou...@yahoo.com Subject: Re: [sqlalchemy] Checking the availablity of a booked Item To: sqlalchemy@googlegroups.com Date: Wednesday, October 13, 2010, 5:25 AM Thank you Thadeus, I believe Face.query.filter(filter_cond).outerjoin(join_clause).all() does a full outerjoin, or is there another way to do it ? Y.Chaouche --- On *Wed, 10/13/10, Thadeus Burgess thade...@thadeusb.com* wrote: From: Thadeus Burgess thade...@thadeusb.com Subject: Re: [sqlalchemy] Checking the availablity of a booked Item To: sqlalchemy@googlegroups.com Date: Wednesday, October 13, 2010, 12:04 AM For outer joins you need a where clause on the joined tables. http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html Using a full outer join should return the expected results. -- Thadeus On Tue, Oct 12, 2010 at 1:41 PM, chaouche yacine yacinechaou...@yahoo.com wrote: Hello, Here's my simple model (For simplification, consider Face as a Billboard) : +-+ +---+ +--+ |Face |.. |Campaign | ...|TimePeriod| +-+ . +---+ . +--+ |code | . |time_period| |start_time| +-+ . +---+ +--+ |faces | |end_time | +---+ +--+ One way to read this model is : A campaign can book multiple faces during a certain period of time. What I want to do is get all the available Faces for a given period of time, to see what faces can I book for a new campaign that longs for that particular period of time. I would typically have a Face.get_available(time_period) class method that does the job. This method would look for all the faces that don't have an ongoing booking. My question is : how to write such a method ? Here's how I figured it out (couldn't get it to work) : class Face(Entity): using_options(tablename=faces) @classmethod def get_available(self,time_period): Return faces that are not booked (not in any campaign) during the given time_period. # start_date_cond = TimePeriod.start_date time_period.end_date # end_date_cond = TimePeriod.end_date time_period.start_date # available_periods = Campaign.time_period.has(or_(start_date_cond,end_date_cond)) # unavailable_periods = not(available_periods) # I am pretty sure that the time conditions are good. # Here's a good way to convince yourself (read from bottom to top) : # L1 0--- # L2 --| # L3 0[]-- # L3 represents the desired period (passed as argument) going from [ to ] # place the start date of the booked face anywhere on L2 # place the end date of the booked face anywhere on L1
Re: [sqlalchemy] Subtransactions problem
On Oct 14, 2010, at 11:40 AM, Juan Antonio Ibáñez wrote: Hello! I am dealing with Turbogears and SQL Alchemy. I need to make one import from a CSV file to a MySQL DB. I need also to controll which rows fails on import. I am working with the next code inside a loop: try: session.begin(subtransactions=True) session.add(importe) session.flush() nimportados += 1 except Exception as ex: log.debug(ex) session.rollback() nerroneos += 1 During exec I get: - 12:55:45,598 INFO [sqlalchemy.engine.base.Engine.0x...f1ac] BEGIN 12:55:45,600 INFO [sqlalchemy.engine.base.Engine.0x...f1ac] INSERT INTO importes (tarifas_id, prefijo, destino, importe, tipo, pc, psi, ps, sg) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s) 12:55:45,600 INFO [sqlalchemy.engine.base.Engine.0x...f1ac] [u'1', 'PREFIJO', 'DESTINO', 'IMPORTE', u'', 0, 60, 60, 0] 12:55:45,601 INFO [sqlalchemy.engine.base.Engine.0x...f1ac] ROLLBACK 12:55:45,603 DEBUG [svbpanel.controllers] (IntegrityError) (1062, Duplicate entry 'PREFIJO' for key 'prefijo') u'INSERT INTO importes (tarifas_id, prefijo, destino, importe, tipo, pc, psi, ps, sg) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)' [u'1', 'PREFIJO', 'DESTINO', 'IMPORTE', u'', 0, 60, 60, 0] 12:55:45,603 DEBUG [svbpanel.controllers] The transaction is inactive due to a rollback in a subtransaction. Issue rollback() to cancel the transaction. - Peopel on TG2 group told me about the appearance of not init the subtransaction. I thing TG2 init a own transaction in an automatic way so I should work with subtransactions (I think) Commiting instead flushing doesn't work... Any idea about this problem? perhaps you're looking for SAVEPOINT ? The Session provides this via begin_nested(). begin(subtransactions=True) is only an in-Python nesting concept. Regards -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
RE: [sqlalchemy] Cannot retrieve PostgreSQL array column with session.query() : TypeError: unhashable type: 'list'
Thanks again for your help.
I had tried using TypeDecorator without success. Now I tested further and
found the problem is more narrow in scope than I thought. If I create a
table MyTable that contains a column MyArray, with MyArray a TypeDecorator
subclass that converts lists to tuples, insert a row, then do
session.query(MyTable).get(row_id), it works fine and I get a tuple for my
array. (The code is at the bottom of this message)
If I do session.query(MyTable).from_statement('SELECT ... FROM
my_table;').first(), then MyArray.process_result_value() is not called and
the returned instance's array attribute is a list rather than a tuple. In
fact, ARRAY's result processor is not used either in that case. I added some
print statements to ResultMetaData.__init__ to try to understand why : with
a regular query, the column type is MyArray; with a query that uses
from_statement(), the column type is NullType.
From there I'm lost. Is there a way to force Query() to a apply a column
type with from_statement()?
CODE :
import os
from sqlalchemy import create_engine, Table, Integer, MetaData, Column
from sqlalchemy.orm import create_session, mapper
from sqlalchemy.dialects.postgresql.base import ARRAY
sa_engine = create_engine(os.environ['TEST_DSN'])
session = create_session(sa_engine, autoflush=True, expire_on_commit=True,
autocommit=False)
from sqlalchemy import types
class MyArray(types.TypeDecorator):
impl = ARRAY
def process_bind_param(self, value, engine):
return value
def process_result_value(self, value, engine):
print 'process_result_value() called'
if value is None:
return None
else:
return tuple(value)
def copy(self):
return MyArray(self.impl.item_type, self.impl.mutable)
metadata = MetaData(bind=sa_engine)
foo = Table('foo', metadata,
Column('bar', Integer, primary_key=True),
Column('my_array', MyArray(Integer, mutable=False))
)
class Foo(object):
pass
mapper(Foo, foo)
foo_obj = session.query(Foo).from_statement(SELECT 1 AS foo_bar,
'{1,2,3}'::integer[] AS foo_my_array;).first()
print foo_obj.my_array # A list
foo.drop(checkfirst=True)
foo.create()
foo_obj = Foo()
foo_obj.bar = -1
foo_obj.my_array = [-1, -2]
session.add(foo_obj)
session.flush()
session.expunge_all()
del foo_obj
foo_obj = session.query(Foo).get(-1)
print foo_obj.my_array # A tuple
session.expunge_all()
del foo_obj
foo_obj = session.query(Foo).from_statement(SELECT * FROM foo WHERE
bar=-1;).first()
print foo_obj.my_array # A list
-Message d'origine-
De : sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] De la
part de Michael Bayer
Envoyé : jeudi 14 octobre 2010 15:52
À : sqlalchemy@googlegroups.com
Objet : Re: [sqlalchemy] Cannot retrieve PostgreSQL array column with
session.query() : TypeError: unhashable type: 'list'
On Oct 14, 2010, at 4:38 AM, Julien Demoor wrote:
Thanks for your reply.
What you propose would work for me, but in the meantime I need a
workaround.
If you need that exact pattern to work, build a TypeDecorator around ARRAY
and have it return a tuple around the result.
TypeDecorator is described at:
http://www.sqlalchemy.org/docs/core/types.html?highlight=typedecorator#sqlal
chemy.types.TypeDecorator
The SQLAlchemy type is always involved in between where psycopg2 returns
data and where unique_list() is called.
As far as changing Query, the unique_list() is what makes it such that if
you load a Parent object with many Child objects in a joined-load
collection, you get just one Parent and not the same Parent for as many
Child objects as are in the result set.
I thought of serializing the arrays in SQL, then converting back to tuples
in my code, but the objects contained in the arrays are quite complex to
parse (decimals, datetims...). So I tried patching the ARRAY class to
return
tuples when mutable=False, and that had no effect. If I understand
correctly, by the time unique_list() is called, ARRAY hasn't been involved
yet, and the database's array is converted to a Python list by the driver
(psycopg2 in my case).
The workaround I've found is to make the following change, in
sqlalchemy.orm.query.Query.instances :
if filtered:
#if single_entity:
#filter = lambda x: util.unique_list(x, util.IdentitySet)
#else:
#filter = util.unique_list
filter = lambda x: util.unique_list(x, util.IdentitySet)
Should I expect negative side-effects from this?
-Message d'origine-
De : sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] De
la
part de Michael Bayer
Envoyé : mercredi 13 octobre 2010 23:37
À : sqlalchemy@googlegroups.com
Objet : Re: [sqlalchemy] Cannot retrieve PostgreSQL array column with
session.query() : TypeError: unhashable type: 'list'
The Query runs the result through unique_list() anytime there are mapped
Re: [sqlalchemy] Very strange behaviour in SqlAlchemy (maybe a bug)
On Oct 13, 2010, at 10:48 AM, Christian Démolis wrote:
Hi,
q = model.session.query(
#
model.Collaborateur.LesIns.any(model.or_(model.Instruction.FinValiditetime.time(),
model.Instruction.FinValidite==None)),
model.Collaborateur.Fonction
)
q = q.limit(5)
print str(q)
for e in q.all() :
print Fonction, e.Fonction
This is the result (result1.jpg)
q = model.session.query(
model.Collaborateur.LesIns.any(model.or_(model.Instruction.FinValiditetime.time(),
model.Instruction.FinValidite==None)),
model.Collaborateur.Fonction
)
q = q.limit(5)
print str(q)
for e in q.all() :
print Fonction, e.Fonction
The SQL is correct. The any() has no label and is throwing off the label names
- this is a bug and is fixed in rd67812029db9, downloadable at
http://hg.sqlalchemy.org/sqlalchemy/archive/default.tar.gz . Otherwise, apply
any(...).label('some label') so that e.Fonction targets the column you want.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Cannot retrieve PostgreSQL array column with session.query() : TypeError: unhashable type: 'list'
On Oct 14, 2010, at 11:53 AM, Julien Demoor wrote:
Thanks again for your help.
I had tried using TypeDecorator without success. Now I tested further and
found the problem is more narrow in scope than I thought. If I create a
table MyTable that contains a column MyArray, with MyArray a TypeDecorator
subclass that converts lists to tuples, insert a row, then do
session.query(MyTable).get(row_id), it works fine and I get a tuple for my
array. (The code is at the bottom of this message)
If I do session.query(MyTable).from_statement('SELECT ... FROM
my_table;').first(), then MyArray.process_result_value() is not called and
the returned instance's array attribute is a list rather than a tuple. In
fact, ARRAY's result processor is not used either in that case. I added some
print statements to ResultMetaData.__init__ to try to understand why : with
a regular query, the column type is MyArray; with a query that uses
from_statement(), the column type is NullType.
From there I'm lost. Is there a way to force Query() to a apply a column
type with from_statement()?
oh, right, with from_statement() SQLA knows nothing about the types - and in
fact in that case you are getting psycopg2's returned array directly.
For that you can use the text() construct:
query.from_statement(text(select * from ...,
typemap={'your_array_column':MyArrayType}))
CODE :
import os
from sqlalchemy import create_engine, Table, Integer, MetaData, Column
from sqlalchemy.orm import create_session, mapper
from sqlalchemy.dialects.postgresql.base import ARRAY
sa_engine = create_engine(os.environ['TEST_DSN'])
session = create_session(sa_engine, autoflush=True, expire_on_commit=True,
autocommit=False)
from sqlalchemy import types
class MyArray(types.TypeDecorator):
impl = ARRAY
def process_bind_param(self, value, engine):
return value
def process_result_value(self, value, engine):
print 'process_result_value() called'
if value is None:
return None
else:
return tuple(value)
def copy(self):
return MyArray(self.impl.item_type, self.impl.mutable)
metadata = MetaData(bind=sa_engine)
foo = Table('foo', metadata,
Column('bar', Integer, primary_key=True),
Column('my_array', MyArray(Integer, mutable=False))
)
class Foo(object):
pass
mapper(Foo, foo)
foo_obj = session.query(Foo).from_statement(SELECT 1 AS foo_bar,
'{1,2,3}'::integer[] AS foo_my_array;).first()
print foo_obj.my_array # A list
foo.drop(checkfirst=True)
foo.create()
foo_obj = Foo()
foo_obj.bar = -1
foo_obj.my_array = [-1, -2]
session.add(foo_obj)
session.flush()
session.expunge_all()
del foo_obj
foo_obj = session.query(Foo).get(-1)
print foo_obj.my_array # A tuple
session.expunge_all()
del foo_obj
foo_obj = session.query(Foo).from_statement(SELECT * FROM foo WHERE
bar=-1;).first()
print foo_obj.my_array # A list
-Message d'origine-
De : sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] De la
part de Michael Bayer
Envoyé : jeudi 14 octobre 2010 15:52
À : sqlalchemy@googlegroups.com
Objet : Re: [sqlalchemy] Cannot retrieve PostgreSQL array column with
session.query() : TypeError: unhashable type: 'list'
On Oct 14, 2010, at 4:38 AM, Julien Demoor wrote:
Thanks for your reply.
What you propose would work for me, but in the meantime I need a
workaround.
If you need that exact pattern to work, build a TypeDecorator around ARRAY
and have it return a tuple around the result.
TypeDecorator is described at:
http://www.sqlalchemy.org/docs/core/types.html?highlight=typedecorator#sqlal
chemy.types.TypeDecorator
The SQLAlchemy type is always involved in between where psycopg2 returns
data and where unique_list() is called.
As far as changing Query, the unique_list() is what makes it such that if
you load a Parent object with many Child objects in a joined-load
collection, you get just one Parent and not the same Parent for as many
Child objects as are in the result set.
I thought of serializing the arrays in SQL, then converting back to tuples
in my code, but the objects contained in the arrays are quite complex to
parse (decimals, datetims...). So I tried patching the ARRAY class to
return
tuples when mutable=False, and that had no effect. If I understand
correctly, by the time unique_list() is called, ARRAY hasn't been involved
yet, and the database's array is converted to a Python list by the driver
(psycopg2 in my case).
The workaround I've found is to make the following change, in
sqlalchemy.orm.query.Query.instances :
if filtered:
#if single_entity:
#filter = lambda x: util.unique_list(x, util.IdentitySet)
#else:
#filter = util.unique_list
filter = lambda x: util.unique_list(x, util.IdentitySet)
Should I expect negative side-effects from
Re: [sqlalchemy] getting floats instead of decimals from session.execute
On Oct 13, 2010, at 8:04 PM, Chris Withers wrote: On 13/10/2010 22:37, Michael Bayer wrote: use text() with Numeric(as_decimal=False) I can't quite join the dots on this one... I get passed a bunch of sql to execute, I don't have control over that. Where do I wire in Numeric(as_decimal=False) and how do I tell where I need to given an arbitrary select as a string of sql? text() is in the docs, including an example of typemap: http://www.sqlalchemy.org/docs/core/expression_api.html?highlight=text#sqlalchemy.sql.expression.text -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
RE: [sqlalchemy] Cannot retrieve PostgreSQL array column with session.query() : TypeError: unhashable type: 'list'
Thank you very much!
-Message d'origine-
De : sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] De la
part de Michael Bayer
Envoyé : jeudi 14 octobre 2010 18:08
À : sqlalchemy@googlegroups.com
Objet : Re: [sqlalchemy] Cannot retrieve PostgreSQL array column with
session.query() : TypeError: unhashable type: 'list'
On Oct 14, 2010, at 11:53 AM, Julien Demoor wrote:
Thanks again for your help.
I had tried using TypeDecorator without success. Now I tested further and
found the problem is more narrow in scope than I thought. If I create a
table MyTable that contains a column MyArray, with MyArray a TypeDecorator
subclass that converts lists to tuples, insert a row, then do
session.query(MyTable).get(row_id), it works fine and I get a tuple for my
array. (The code is at the bottom of this message)
If I do session.query(MyTable).from_statement('SELECT ... FROM
my_table;').first(), then MyArray.process_result_value() is not called and
the returned instance's array attribute is a list rather than a tuple. In
fact, ARRAY's result processor is not used either in that case. I added
some
print statements to ResultMetaData.__init__ to try to understand why :
with
a regular query, the column type is MyArray; with a query that uses
from_statement(), the column type is NullType.
From there I'm lost. Is there a way to force Query() to a apply a column
type with from_statement()?
oh, right, with from_statement() SQLA knows nothing about the types - and in
fact in that case you are getting psycopg2's returned array directly.
For that you can use the text() construct:
query.from_statement(text(select * from ...,
typemap={'your_array_column':MyArrayType}))
CODE :
import os
from sqlalchemy import create_engine, Table, Integer, MetaData, Column
from sqlalchemy.orm import create_session, mapper
from sqlalchemy.dialects.postgresql.base import ARRAY
sa_engine = create_engine(os.environ['TEST_DSN'])
session = create_session(sa_engine, autoflush=True, expire_on_commit=True,
autocommit=False)
from sqlalchemy import types
class MyArray(types.TypeDecorator):
impl = ARRAY
def process_bind_param(self, value, engine):
return value
def process_result_value(self, value, engine):
print 'process_result_value() called'
if value is None:
return None
else:
return tuple(value)
def copy(self):
return MyArray(self.impl.item_type, self.impl.mutable)
metadata = MetaData(bind=sa_engine)
foo = Table('foo', metadata,
Column('bar', Integer, primary_key=True),
Column('my_array', MyArray(Integer, mutable=False))
)
class Foo(object):
pass
mapper(Foo, foo)
foo_obj = session.query(Foo).from_statement(SELECT 1 AS foo_bar,
'{1,2,3}'::integer[] AS foo_my_array;).first()
print foo_obj.my_array # A list
foo.drop(checkfirst=True)
foo.create()
foo_obj = Foo()
foo_obj.bar = -1
foo_obj.my_array = [-1, -2]
session.add(foo_obj)
session.flush()
session.expunge_all()
del foo_obj
foo_obj = session.query(Foo).get(-1)
print foo_obj.my_array # A tuple
session.expunge_all()
del foo_obj
foo_obj = session.query(Foo).from_statement(SELECT * FROM foo WHERE
bar=-1;).first()
print foo_obj.my_array # A list
-Message d'origine-
De : sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] De
la
part de Michael Bayer
Envoyé : jeudi 14 octobre 2010 15:52
À : sqlalchemy@googlegroups.com
Objet : Re: [sqlalchemy] Cannot retrieve PostgreSQL array column with
session.query() : TypeError: unhashable type: 'list'
On Oct 14, 2010, at 4:38 AM, Julien Demoor wrote:
Thanks for your reply.
What you propose would work for me, but in the meantime I need a
workaround.
If you need that exact pattern to work, build a TypeDecorator around ARRAY
and have it return a tuple around the result.
TypeDecorator is described at:
http://www.sqlalchemy.org/docs/core/types.html?highlight=typedecorator#sqlal
chemy.types.TypeDecorator
The SQLAlchemy type is always involved in between where psycopg2 returns
data and where unique_list() is called.
As far as changing Query, the unique_list() is what makes it such that if
you load a Parent object with many Child objects in a joined-load
collection, you get just one Parent and not the same Parent for as many
Child objects as are in the result set.
I thought of serializing the arrays in SQL, then converting back to
tuples
in my code, but the objects contained in the arrays are quite complex to
parse (decimals, datetims...). So I tried patching the ARRAY class to
return
tuples when mutable=False, and that had no effect. If I understand
correctly, by the time unique_list() is called, ARRAY hasn't been
involved
yet, and the database's array is converted to a Python list by the driver
(psycopg2 in my case).
The workaround
Re: [sqlalchemy] getting floats instead of decimals from session.execute
On 14/10/2010 17:10, Michael Bayer wrote: I get passed a bunch of sql to execute, I don't have control over that. Where do I wire in Numeric(as_decimal=False) and how do I tell where I need to given an arbitrary select as a string of sql? text() is in the docs, including an example of typemap: http://www.sqlalchemy.org/docs/core/expression_api.html?highlight=text#sqlalchemy.sql.expression.text Okay, but I don't see how a typemap helps. All I'm getting is a string of sql, I don't know what column names are included in that, and even if I did, I wouldn't know the schema of the table to know what they should be mapped to. How can I influence what column type is mapped to a particular python type without knowing the names or types of the columns? cheers, Chris -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] getting floats instead of decimals from session.execute
On Oct 14, 2010, at 1:35 PM, Chris Withers wrote: On 14/10/2010 17:10, Michael Bayer wrote: I get passed a bunch of sql to execute, I don't have control over that. Where do I wire in Numeric(as_decimal=False) and how do I tell where I need to given an arbitrary select as a string of sql? text() is in the docs, including an example of typemap: http://www.sqlalchemy.org/docs/core/expression_api.html?highlight=text#sqlalchemy.sql.expression.text Okay, but I don't see how a typemap helps. All I'm getting is a string of sql, I don't know what column names are included in that, If you don't know your columns, neither does SQLAlchemy.Depending on your DBAPI you may be able to get the DBAPI itself to not use the Decimal type, see the docs on your DBAPI for information on type coercion at the DBAPI level. Otherwise, you'd filter your result set, do isinstance(x, Decimal), convert to float. You may be able to use result.cursor.description if the DBAPI returns reasonable types. We could someday add an execution_option() that allows a map of DBAPI types mapped to result types to be passed. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] efficiently setting many to many collections when I have a sequence of ids.
Hi All,
I currently have code that looks like this:
recipients = []
if recipient_ids:
for id in recipient_ids.split(','):
recipients.append(
session.query(recipient.Recipient).filter_by(id=id).one()
)
else:
recipient_ids = ()
feed.recipients = recipients
Where the models are:
feed_recipient = Table('feed_recipient', Base.metadata,
Column('feed_id', String(length=32), ForeignKey('feed.id')),
Column('recipient_id', Integer, ForeignKey('recipient.id')),
)
class Feed(Base):
__tablename__ = 'feed'
id = Column(String(length=32), primary_key=True)
recipients = relation('Recipient', secondary=feed_recipient)
class Recipient(Base,ComputedMapperArgs):
__tablename__='recipient'
feeds = relation('Feed', secondary=feed_recipient)
It feels like a horribly inefficient way of updating the many-to-many
relationship. I guess I could just use the sql abstraction layer, but
that feels like circumventing the ORM without just cause ;-)
Am I missing something? If I have a sequence of ids where I want to
update the many to many relationship as above, what's the best way of
doing it?
cheers,
Chris
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Polyphormic class insertion by SA .vs. by database triggers
Hi list,
I've a sample application that makes use of polymorphism both with and
without SQLAlchemy.
On the non-SA side, I've implemented record insertion via stored procedures.
Now, integrating new code based on SA, the ORM behaviour is to create both
the base and the inherited record. SA fails at this points because the
inherited records has already been created on the database side.
Sample error:
---
sqlalchemy.exc.IntegrityError: (IntegrityError) duplicate key value violates
unique constraint compound_item_pkey
'INSERT INTO compound_item (id) VALUES (%(id)s)' {'id': 7}
---
I dont't have the option to touch the legacy (but current) code, so I expect
to be able to make SA adapt to this situation. I guess I'd need a mapper
option that does not insert inherited records or maybe ignores if they're
already there.
Do you guys think I'm in the right direction? Does SA have this already? Any
pointers to where to start to code this?
Thanks!
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] efficiently setting many to many collections when I have a sequence of ids.
On 10/14/2010 01:30 PM, Chris Withers wrote:
Hi All,
I currently have code that looks like this:
recipients = []
if recipient_ids:
for id in recipient_ids.split(','):
recipients.append(
session.query(recipient.Recipient).filter_by(id=id).one()
)
else:
recipient_ids = ()
feed.recipients = recipients
Where the models are:
feed_recipient = Table('feed_recipient', Base.metadata,
Column('feed_id', String(length=32), ForeignKey('feed.id')),
Column('recipient_id', Integer, ForeignKey('recipient.id')),
)
class Feed(Base):
__tablename__ = 'feed'
id = Column(String(length=32), primary_key=True)
recipients = relation('Recipient', secondary=feed_recipient)
class Recipient(Base,ComputedMapperArgs):
__tablename__='recipient'
feeds = relation('Feed', secondary=feed_recipient)
It feels like a horribly inefficient way of updating the many-to-many
relationship. I guess I could just use the sql abstraction layer, but
that feels like circumventing the ORM without just cause ;-)
Am I missing something? If I have a sequence of ids where I want to
update the many to many relationship as above, what's the best way of
doing it?
You can at least reduce it to a single query:
recipient_id_list = recipient_ids.split(',')
q = session.query(recipient.Recipient)
q = q.filter(recipient.Recipient.id.in_(recipient_id_list))
recipients = q.all()
if len(recipients) != len(recipient_id_list):
invalid_ids = (set(recipient_id_list)
- set(x.id for x in recipients))
raise StandardError(Invalid recipient ids: %s % sorted(invalid_ids))
feed.recipients = recipients
I wish there was an easy way to do, say:
feed.recipients.ids = recipient_id_list
which would let you avoid querying at all. The downside is you won't
notice invalid ids until the next flush(). Alternatively, you could also
accomplish this via:
feed.recipients = [session.merge(recipient.Recipient(id=id), load=False)
for id in recipient_id_list]
if session.merge() allowed you to merge newly-created transient objects
with load=False. Maybe add a force parameter to merge, to tell
SQLAlchemy that you really know what you are doing?
-Conor
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Polyphormic class insertion by SA .vs. by database triggers
On Oct 14, 2010, at 4:25 PM, Cleber Rodrigues wrote:
Hi list,
I've a sample application that makes use of polymorphism both with and
without SQLAlchemy.
On the non-SA side, I've implemented record insertion via stored procedures.
Now, integrating new code based on SA, the ORM behaviour is to create both
the base and the inherited record. SA fails at this points because the
inherited records has already been created on the database side.
Sample error:
---
sqlalchemy.exc.IntegrityError: (IntegrityError) duplicate key value violates
unique constraint compound_item_pkey
'INSERT INTO compound_item (id) VALUES (%(id)s)' {'id': 7}
---
I dont't have the option to touch the legacy (but current) code, so I expect
to be able to make SA adapt to this situation. I guess I'd need a mapper
option that does not insert inherited records or maybe ignores if they're
already there.
Do you guys think I'm in the right direction? Does SA have this already? Any
pointers to where to start to code this?
What would it do , INSERT into the parent table, then the child row is created
implicitly and then.do an UPDATE to the child table ? where does the
stored procedure get the values to be set up for the child row ?
So yeah I think you'd have to be using a mapper here that doesn't know about
the second table, or isn't expecting to INSERT into it.
Some pattern like this:
# when we do #1895, this will be possible
mapper(Base, base_table, polymorphic_on=base_table.c.type)
mapper(Child, child_table, inherits=Base, polymorphic_identity='child')
session.add_all([Base(type='child'), Base(type='child'), Base(type='child'))
# until then, probably like this:
mapper(InsertChild, base_table)
mapper(Base, base_table, polymorphic_on=base_table.c.type)
mapper(Child, child_table, inherits=Base, polymorphic_identity='child')
session.add_all([InsertBase(type='child'), InsertBase(type='child'),
InsertBase(type='child'))
c1, c2, c3 = Session.query(Child).all()
You could use MapperExtension to issue an UPDATE to the child table, using the
connection given, as the InsertBase objects are inserted.
It would all be very inconvenient.One way to change your stored procedure
or trigger in a minimal way, depending on the database in use, is to either
disable the trigger for the connection using a pool listener, or have your
stored procedure check some connection-wide variable, again which you'd set up
on each connection using a pool listener.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Error - class 'sqlalchemy.exc.OperationalError': (OperationalError) (2006, 'MySQL server has gone away')
HiTimmy What OS are you running under for each? Cheers Warwick Warwick Prince Managing Director mobile: +61 411 026 992 skype: warwickprince phone: +61 7 3102 3730 fax: +61 7 3319 6734 web: www.mushroomsys.com On 15/10/2010, at 7:48 AM, Timmy Chan wrote: sorry if this is addressed, but i'm running apache2 SQLAlchemy 0.5.8 Pylons 1.0 Python 2.5.2 and on a simple page (just retrieve data from DB), I get: Error - class 'sqlalchemy.exc.OperationalError': (OperationalError) (2006, 'MySQL server has gone away') every few other requests, not after a long time as other posts I've searched for. I still added sqlalchemy.pool_recycle = 1800 but that did not fix the issue. After a fresh apache restart, every 5th or 6th requests gets a 500 from the above error. thanks -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Error - class 'sqlalchemy.exc.OperationalError': (OperationalError) (2006, 'MySQL server has gone away')
sorry if this is addressed, but i'm running apache2 SQLAlchemy 0.5.8 Pylons 1.0 Python 2.5.2 and on a simple page (just retrieve data from DB), I get: Error - class 'sqlalchemy.exc.OperationalError': (OperationalError) (2006, 'MySQL server has gone away') every few other requests, not after a long time as other posts I've searched for. I still added sqlalchemy.pool_recycle = 1800 but that did not fix the issue. After a fresh apache restart, every 5th or 6th requests gets a 500 from the above error. thanks -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Error - class 'sqlalchemy.exc.OperationalError': (OperationalError) (2006, 'MySQL server has gone away')
hey, Linux ded-230-114 2.6.30-bpo.1-686 #1 SMP Mon Aug 17 14:57:26 UTC 2009 i686 GNU/Linux thanks! On Thu, Oct 14, 2010 at 5:56 PM, Warwick Prince warwi...@mushroomsys.comwrote: HiTimmy What OS are you running under for each? Cheers Warwick *Warwick Prince* Managing Director mobile: +61 411 026 992 skype: warwickprince [image: If you have Skype installed, click here to call me now!] [image: Mushroom Systems] phone: +61 7 3102 3730 fax: +61 7 3319 6734 web: www.mushroomsys.com [image: I'm Green with Little Green Genie]http://littlegreengenie.com/index.html?referer=WARWICKP On 15/10/2010, at 7:48 AM, Timmy Chan wrote: sorry if this is addressed, but i'm running apache2 SQLAlchemy 0.5.8 Pylons 1.0 Python 2.5.2 and on a simple page (just retrieve data from DB), I get: Error - class 'sqlalchemy.exc.OperationalError': (OperationalError) (2006, 'MySQL server has gone away') every few other requests, not after a long time as other posts I've searched for. I still added sqlalchemy.pool_recycle = 1800 but that did not fix the issue. After a fresh apache restart, every 5th or 6th requests gets a 500 from the above error. thanks -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.comsqlalchemy%2bunsubscr...@googlegroups.com . For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Error - class 'sqlalchemy.exc.OperationalError': (OperationalError) (2006, 'MySQL server has gone away')
On Oct 14, 5:48 pm, Timmy Chan timmy.cha...@gmail.com wrote: sorry if this is addressed, but i'm running apache2 SQLAlchemy 0.5.8 Pylons 1.0 Python 2.5.2 and on a simple page (just retrieve data from DB), I get: Error - class 'sqlalchemy.exc.OperationalError': (OperationalError) (2006, 'MySQL server has gone away') every few other requests, not after a long time as other posts I've searched for. I still added sqlalchemy.pool_recycle = 1800 but that did not fix the issue. After a fresh apache restart, every 5th or 6th requests gets a 500 from the above error. The most likely explanation is that you are holding connections and not returning them to the pool. After enough idle time has elapsed the connection was closed (possibly by a firewall) and no longer working, but you are still trying to use it. Close your connections and return them to the pool as soon as the current commands are completed. If you enable echo in your database engine, engine.echo=True you will get a huge amount of debugging data that will include information about connections. You should be able to determine which connections are not getting closed. thanks -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
