[sqlalchemy] Re: Replacing columns in a Select
Thank you, I did not think of with_only_columns for the last part. The part that bother me the most is how to build the column list. I rewrote my code to only use public attributes and methods: columns = ColumnCollection(*myselect.inner_columns) columns.replace(my_column) myselect.with_only_columns(columns) It feels very verbose for what it is. Is this a common use case, worth a 'generative' helper method on the select ? For example: myselect = myselect.replace_column(mycolumn) On Mar 14, 8:27 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Mar 14, 2012, at 7:50 AM, Ronan Dunklau wrote: Hello. I'd like to append a column clause to a select, or replace an existing column if one exists with the same key. I've seen that ColumnCollection has a convenient 'replace' method, but I don't know how to achieve that with a select instance. I can achieve what I want with the following code: columns_dict = OrderedDict((col, key) for col in my_select._raw_columns)) columns_dict['mycol'] = mynewcol my_select.raw_columns = columns_dict.values() my_select._reset_exported. But surely there must be something easier/not involving protected attributes ? select() has a method with_only_colunms() for this purpose: http://docs.sqlalchemy.org/en/latest/core/expression_api.html#sqlalch... -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@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] Returningquery results with the result number
Hi, In order to avoid bottlenecks I am force to limit the number of returned results using LIMIT and OFFSET. Since I am not returning all results upon a query I need to include the number of hits in the result. somequery.count() somequery.limit(n).offset(m).all() The problem is that response time takes twice as long as for either the count query or the query retrieving results. Is there any way to do this more efficiently, to make a query first, then to count results and return the result chunk defined with LIMIT and OFFSET? What is the best practice for this? Thanks ED -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/XXZtDSStLO8J. To post to this group, send email to sqlalchemy@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] modifying enable_seqscan option
Hi, Is it possible to set this option to off by using sqlalchemy? Thanks Ed -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/JGkEvUVvoncJ. To post to this group, send email to sqlalchemy@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] Returningquery results with the result number
On 03/15/2012 04:52 AM, Eduardo wrote: Hi, In order to avoid bottlenecks I am force to limit the number of returned results using LIMIT and OFFSET. Since I am not returning all results upon a query I need to include the number of hits in the result. somequery.count() somequery.limit(n).offset(m).all() The problem is that response time takes twice as long as for either the count query or the query retrieving results. Is there any way to do this more efficiently, to make a query first, then to count results and return the result chunk defined with LIMIT and OFFSET? What is the best practice for this? Thanks ED If your DB supports window functions and you are using SQLAlchemy 0.7+, you can include func.count().over() in your query, which effectively includes somequery.count() as a column in each row. So this: somequery = session.query(SomeClass).filter(...) count = somequery.count() some_class_instances = somequery.limit(n).offset(m).all() becomes: rows = session.query(SomeClass, func.count().over().label(count)).filter(...).limit(n).offset(m).all() if rows: count = rows[0][1] some_class_instances = [row[0] for row in rows] else: # Either no rows matched or the limit+offset is out of range. We will assume the former. count = 0 some_class_instances = [] -Conor -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@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] modifying enable_seqscan option
On Mar 15, 2012, at 5:54 AM, Eduardo wrote: Hi, Is it possible to set this option to off by using sqlalchemy? Thanks Ed connection.execute(SET enable_seqscan TO off;) But if you use that in production, you're nuts. Cheers, M -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@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: Handle many to many relationship
Hi,
You can define a SA object associated to the table tarife_dossier:
- you set its IdDossier (IdAt) attributes to its associated dossier
(article) id
- or you set dossier (article_tarife) to its associated dossier (articke)
object
and you save it !
PS: tarife ou tarif ?
On Thursday, March 15, 2012 12:38:26 PM UTC+1, Christian Démolis wrote:
Hi all,
DossierTarife = Table('tarifer_dossier', Base.metadata,
Column('IdDossier', Integer, ForeignKey('dossier.IdDossier')),
Column('IdAt', Integer, ForeignKey('article_tarife.IdAt'))
)
Dossier.LesTar = relation(ArticleTarife, secondary=DossierTarife,
backref=backref('dossier'))
ArticleTarife.LesTar = relation(Dossier, secondary=DossierTarife,
backref=backref('article_tarife'))
When i want to change a many to many relation, the tutorial says that i
must add object in the relationship (list) :
self.tarif_cible.LesTar.append(d)
where d is an instance of Dossier. But Dossier is an heavy table, so
object is heavy too.
Is it any other simple way to change a many to many association (example :
directly access DossierTarife table) ???
Thx in advance,
Chris
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/FZb31d8fPMcJ.
To post to this group, send email to sqlalchemy@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: Bidirectional, self-referential association table
I transform it into declarative way and its seems to work but it's not
bidirectional.
To support bidirectional friendship(A, B friends) i would say that you
might transform it into 2 unidirectional friendships (A friend of B and B
friend of A).
You could perhaps hide this complexity with an association_proxy which
creator_factory creates both both friendships
class User(Mixin, BASE):
id = Column(Integer, primary_key=True)
__tablename__ = 'user'
friends = None
class FriendShip(Mixin, BASE):
__tablename__ = 'friendship'
user_id = Column(Integer, ForeignKey(User.id))
friend_id = Column(Integer, ForeignKey(User.id))
User.friends = relationship(User, secondary='friendship',
primaryjoin=User.id==FriendShip.user_id,
secondaryjoin=User.id==FriendShip.friend_id)
On Thursday, March 15, 2012 1:03:27 AM UTC+1, Jon Parise wrote:
I'm considering modeling many-to-many friend relationships between
users using an association table. The tricky aspect is that I'd like
the association table's entries to be bidirectional, meaning the
single entry (A,B) represents friendship in both directions.
The standard way to model this doesn't support that usage:
friendship_table = Table('friendship',
Column('user_id', Integer, ForeignKey('user.id')),
Column('friend_id', Integer, ForeignKey('user.id'))
)
class User(Base):
id = Column(db.Integer, primary_key=True)
friends = db.relationship('User',
secondary=friendship_table,
primaryjoin=id==friendship_table.c.user_id,
secondaryjoin=id==friendship_table.c.friend_id)
... and I understand why it can't work as-is. My question is whether
or not this type of relationship is possible to represent using
relationship() (perhaps by using association_proxy()?).
For the time being, I've just been writing per-operation queries in
User instance methods and @property descriptors, but it would be nice
to wrap this up in an proper attributed object that plays nicely with
the session.
On Thursday, March 15, 2012 1:03:27 AM UTC+1, Jon Parise wrote:
I'm considering modeling many-to-many friend relationships between
users using an association table. The tricky aspect is that I'd like
the association table's entries to be bidirectional, meaning the
single entry (A,B) represents friendship in both directions.
The standard way to model this doesn't support that usage:
friendship_table = Table('friendship',
Column('user_id', Integer, ForeignKey('user.id')),
Column('friend_id', Integer, ForeignKey('user.id'))
)
class User(Base):
id = Column(db.Integer, primary_key=True)
friends = db.relationship('User',
secondary=friendship_table,
primaryjoin=id==friendship_table.c.user_id,
secondaryjoin=id==friendship_table.c.friend_id)
... and I understand why it can't work as-is. My question is whether
or not this type of relationship is possible to represent using
relationship() (perhaps by using association_proxy()?).
For the time being, I've just been writing per-operation queries in
User instance methods and @property descriptors, but it would be nice
to wrap this up in an proper attributed object that plays nicely with
the session.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/f5ydw1T4gloJ.
To post to this group, send email to sqlalchemy@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] referencing main query entity from EXISTS [any()] subquery
On Mar 15, 2012, at 7:40 PM, Gunnar Schaefer wrote: I don't quite know how to explain it right, but I guess I want to create a subquery that references the entity of the main query. in SQL we call this a correlated subquery. The subquery refers to a table in the enclosing query. Here is the SQL that does exactly what I want: SELECT * FROM job JOIN datacontainer ON datacontainer.id = job.data_container_id WHERE NOT (EXISTS (SELECT 1 FROM job AS other_job WHERE other_job.data_container_id = datacontainer.id AND other_job.id job.id)); The key is that in 'other_job.id job.id' job is the job from the main query. And here is my best attempt at doing the same thing in sqlalchemy: Job_A = aliased(Job) DBSession.query(Job_A).join(DataContainer).filter(~ DataContainer.jobs.any(Job.id Job_A.id)).all() OK well the awkwardness here is that the DataContainer.jobs.any() clause doesn't have the ability for the subject of the any(), that is Job, to be aliased on that end, which is actually what your desired query is looking for (that is, FROM job AS other_job). The correlation logic of any() isn't figuring out what you want. So we need to generate what any() does more manually (still beats writing a SQL string!): Job_A = aliased(Job) any_query = exists().where(Job_A.container_id==DataContainer.id).\ where(Job_A.idJob.id) session.query(Job).join(DataContainer).filter(~any_query).all() there's some inconsistencies here we'll fix in 0.8 - the exists() above lets you deal with what's essentially a select() construct - it auto-correlates to its parent selectable by default. The Query() OTOH doesn't do this, which we'll fix in 0.8 since auto-correlation is more what we usually want (Query also has a correlate() method). To explicitly correlate any_query, we can say: any_query = exists().where(Job_A.container_id==DataContainer.id).\ where(Job_A.idJob.id).\ correlate(Job.__table__).\ correlate(DataContainer.__table__) where similarly, the need to say __table__ will also be fixed in 0.8, you'll be able to say things like correlate(Job). -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@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] referencing main query entity from EXISTS [any()] subquery
On Mar 15, 2012, at 10:03 PM, Michael Bayer wrote: And here is my best attempt at doing the same thing in sqlalchemy: Job_A = aliased(Job) DBSession.query(Job_A).join(DataContainer).filter(~ DataContainer.jobs.any(Job.id Job_A.id)).all() any_query = exists().where(Job_A.container_id==DataContainer.id).\ where(Job_A.idJob.id).\ correlate(Job.__table__).\ correlate(DataContainer.__table__) where similarly, the need to say __table__ will also be fixed in 0.8, you'll be able to say things like correlate(Job). this works too, to get what you were trying to do in the original: q = session.query(Job_A).join(DataContainer).\ filter(~DataContainer.jobs.any(Job_A.idJob.container_id). correlate(Job.__table__)) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@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] PostgreSQL Multidimensional arrays in SQLAlchemy
Hi,
I posted
http://stackoverflow.com/questions/9729175/multidimensional-arrays-in-sqlalchemy
to StackOverflow. Reproduced below. Please CC me on any reply. Thanks.
Regards, Faheem
I'm using SQLAlchemy 0.6.3 with PostgreSQL 8.4 on Debian squeeze. I want a
table where one column stores something in PostgreSQL that shows up in
Python as a list of integer lists or tuples of integer tuples. E.g.
((1,2), (3,4), (5,6,7))
In the example below the column is `model`. I thought that a reasonable
approach might be to store stuff as an PG 2 dimensional table, which in PG
looks like `integer[][]`. I don't know in what form SQLA will return this
to Python, but I'm hoping it is something like a tuple of tuples.
However, I can't figure out how to tell SQLA to give me a two dimensional
Integer array. The
[documentation](http://docs.sqlalchemy.org/en/rel_0_6/dialects/postgresql.html#postgresql-data-types)
for `sqlalchemy.dialects.postgresql.ARRAY` says
item_type – The data type of items of this array. Note that
dimensionality is irrelevant here, so multi-dimensional arrays like
INTEGER[][], are constructed as ARRAY(Integer), not as
ARRAY(ARRAY(Integer)) or such. The type mapping figures out on the fly.
Unfortunately, I have no idea what that means. How can the type mapping
figure this out on the fly? It needs to create the correct DDL.
My first and only guess for how to do this would have been
`ARRAY(ARRAY(Integer))`. Currently I have
crossval_table = Table(
name, meta,
Column('id', Integer, primary_key=True),
Column('created', TIMESTAMP(), default=now()),
Column('sample', postgresql.ARRAY(Integer)),
Column('model', postgresql.ARRAY(Integer)),
Column('time', Float),
schema = schema,
This creates the following DDL
CREATE TABLE crossval (
id integer NOT NULL,
created timestamp without time zone,
sample integer[],
model integer[],
time double precision
);
which isn't right, of course. What am I missing?
##
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@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.
