[sqlalchemy] Re: How to filter by the result of a subquery?
On Jul 16, 2:55 am, The Devil's Programmer thedevilsprogram...@gmail.com wrote: I believe I am supposed to wrap the whole query inside another query and put the where clause on the outer query, would this be correct? I have tried messing around with this a little but haven't managed to get it to work yet. If somebody could just tell me that I'm on the right track, that would be great. First check that the database doesn't figure it out for you. A quick test on my sample database showed that on postgres this doesn't seem to be a problem (note the identical query plans): ants=# EXPLAIN ANALYZE SELECT users.id, (SELECT COUNT(*) FROM documents WHERE documents.user_id = users.id) AS doc_count FROM users WHERE (SELECT COUNT(*) FROM documents WHERE documents.user_id = users.id) 9; QUERY PLAN - Seq Scan on users (cost=0.00..236010.06 rows=7000 width=4) (actual time=0.066..190.913 rows=1883 loops=1) Filter: ((subplan) 9) SubPlan - Aggregate (cost=8.40..8.41 rows=1 width=0) (actual time=0.007..0.007 rows=1 loops=21000) - Index Scan using user_id_idx on documents (cost=0.00..8.38 rows=7 width=0) (actual time=0.003..0.005 rows=5 loops=21000) Index Cond: (user_id = $0) - Aggregate (cost=8.40..8.41 rows=1 width=0) (actual time=0.010..0.010 rows=1 loops=1883) - Index Scan using user_id_idx on documents (cost=0.00..8.38 rows=7 width=0) (actual time=0.003..0.007 rows=10 loops=1883) Index Cond: (user_id = $0) Total runtime: 191.296 ms (10 rows) ants=# EXPLAIN ANALYZE SELECT * FROM (SELECT users.id, (SELECT COUNT (*) FROM documents WHERE documents.user_id = users.id) AS doc_count FROM users) AS x WHERE x.doc_count 9; QUERY PLAN - Seq Scan on users (cost=0.00..236010.06 rows=7000 width=4) (actual time=0.064..191.524 rows=1883 loops=1) Filter: ((subplan) 9) SubPlan - Aggregate (cost=8.40..8.41 rows=1 width=0) (actual time=0.007..0.007 rows=1 loops=21000) - Index Scan using user_id_idx on documents (cost=0.00..8.38 rows=7 width=0) (actual time=0.003..0.005 rows=5 loops=21000) Index Cond: (user_id = $0) - Aggregate (cost=8.40..8.41 rows=1 width=0) (actual time=0.010..0.010 rows=1 loops=1883) - Index Scan using user_id_idx on documents (cost=0.00..8.38 rows=7 width=0) (actual time=0.003..0.007 rows=10 loops=1883) Index Cond: (user_id = $0) Total runtime: 191.931 ms (10 rows) --~--~-~--~~~---~--~~ 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] How to check for references before deleting?
Hi, I use in my database tables, that reference other tables, e.g. table A - table B. In my deletion routine, I want to prohibit people to delete rows from A, which have references to B. How would I do that? My first approach was to rely on the databases referential integrity and catch related dabases errors, however, SQLAlchemy works around these references, as due to the cascade = all rule in the mapper, the rows of table B are simply deleted before. On the other hand, I can't simply omit cascade=all, as I need this for object creation. passive_deletes, delete_orphan and the like are also no solution. What I currently do is to manually check for related objects and raise an error if I find one, but I estimate that there will be some SQLAlchemy trick that does that for me? Best Regards, Hermann -- herm...@qwer.tk GPG key ID: 299893C7 (on keyservers) FP: 0124 2584 8809 EF2A DBF9 4902 64B4 D16B 2998 93C7 --~--~-~--~~~---~--~~ 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: How to check for references before deleting?
Hermann Himmelbauer wrote: Hi, I use in my database tables, that reference other tables, e.g. table A - table B. In my deletion routine, I want to prohibit people to delete rows from A, which have references to B. How would I do that? My first approach was to rely on the databases referential integrity and catch related dabases errors, however, SQLAlchemy works around these references, as due to the cascade = all rule in the mapper, the rows of table B are simply deleted before. On the other hand, I can't simply omit cascade=all, as I need this for object creation. cascade=all includes delete. the default is save-update,merge, and that is all you need for object creation. make sure your foreign key columns are created as NOT NULL and an error will generate when SQLA attempts to set them to null. --~--~-~--~~~---~--~~ 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] Postgres Indexes: specifying 'pattern_ops' to ensure proper indexing of LIKE% queries
Background -- I found that performing a select cols from mytable where X like 'foo %' was dog slow on my postgresql database. Analysing the query plan showed that, although X was indexed, the query was always performing a table scan. The database uses UTF-8 encoding, and after a bit of research, I found that I had to define a second index on X, using 'pattern_ops' (as described in Postgresql 8.3 manual section 11.9): CREATE INDEX new_index on mytable (X varchar_pattern_ops). That did the right thing and sorted out the performance problem. Question -- Is there a way that I can specify pattern_ops in the index definition within SqlAlchemy? If not, is postgres.py/PGSchemaGenerator.visit_index the right place for me to have a go at hacking something? Thanks. --~--~-~--~~~---~--~~ 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: Postgres Indexes: specifying 'pattern_ops' to ensure proper indexing of LIKE% queries
johnnyp wrote: Background -- I found that performing a select cols from mytable where X like 'foo %' was dog slow on my postgresql database. Analysing the query plan showed that, although X was indexed, the query was always performing a table scan. The database uses UTF-8 encoding, and after a bit of research, I found that I had to define a second index on X, using 'pattern_ops' (as described in Postgresql 8.3 manual section 11.9): CREATE INDEX new_index on mytable (X varchar_pattern_ops). That did the right thing and sorted out the performance problem. Question -- Is there a way that I can specify pattern_ops in the index definition within SqlAlchemy? If not, is postgres.py/PGSchemaGenerator.visit_index the right place for me to have a go at hacking something? i would just use DDL() for this. --~--~-~--~~~---~--~~ 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] Queries in historical db's, where the join depends on having an overlapping timestamp (or other filter condition)
'''
Based on the thread titled filter relation based on column value
at
http://groups.google.com/group/sqlalchemy/browse_thread/thread/0db7423d986ba543
and some others, I'm curious about how to better get my SqlA code
working
with historical (better term?) databases, where the relational state
of
the system depends on when the question is being asked.
Group membership, where both the members of the group, and the
information
about a group can change over time is shown in the example below.
'''
## for this simple example
## every employee can be in at most one group, duirng any time period
## groups can also change name over time, which doesn't doesn't
change
## the group id.
## employees don't change name during their tenure in a group
## Analysis Questions:
## 1. Show the composition of the group (with id=ii) at a given time,
## including name and all members
## 2. Show the history of a group over time, including name changes,
## membership changes
## 3. History for an employee, including when they change groups
'''
Questions:
1. Is there a SQLAlchemical way to write group_snapshot_ts into
a declarative class, such that the joins and loader respect the
time
constraints? (Read-only is fine as well on the loaded attributes)
a. eager_loader?
b. subclassing query?
2. (Secondary, off-topic)
Is there a constraint that shows that an employees time period in
a
group isn't *overlapping* their time period in another.
That is, that they are in only one group at once?
'''
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import CheckConstraint, ForeignKey, MetaData,
PrimaryKeyConstraint
from sqlalchemy import ForeignKeyConstraint, UniqueConstraint
from sqlalchemy import Table, Column, Integer, Boolean,Unicode, String
from sqlalchemy.orm import relation, backref
from sqlalchemy.orm import mapper
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm.exc import NoResultFound
forever=2147483647 # maxint in sql system for int type
Base = declarative_base()
def db_setup(connstring='sqlite:///:memory:', echo=False):
engine = create_engine(connstring, echo=echo)
Session = sessionmaker(bind=engine, autoflush=False,
autocommit=False)
session = Session()
Base.metadata.bind = engine
Base.metadata.create_all()
return session, engine
class GroupInfo(Base):
__tablename__ = 'group_info'
group_id = Column(Integer, primary_key=True, nullable=False,
autoincrement=False)
group_name = Column(String, primary_key=True, nullable=False)
start = Column(Integer, nullable=False, primary_key=True,
autoincrement=False)
stop = Column(Integer, nullable=False, primary_key=True,
autoincrement=False)
__table_args__ = (
CheckConstraint('stop start',name='ss_check'),
UniqueConstraint('group_id','start', name='u_start'),
UniqueConstraint('group_id','stop', name='u_stop'),
{}
)
class Membership(Base):
__tablename__ = 'membership'
group_id=Column(ForeignKey(GroupInfo.group_id, onupdate=cascade,
ondelete='cascade'))
employee_id = Column(Integer, primary_key=True, nullable=False)
start = Column(Integer, nullable=False, primary_key=True,
autoincrement=False)
stop = Column(Integer, nullable=False, primary_key=True,
autoincrement=False)
__table_args__ = (
CheckConstraint('stop start',name='ss_check'),
UniqueConstraint
('group_id','employee_id','start',name='u_start'),
UniqueConstraint
('group_id','employee_id','stop',name='u_stop'),
{}
)
def group_snapshot_ts(session, groupid, ts):
GI = GroupInfo
G = session.query(GI).filter(GI.group_id==groupid)
G = G.filter(GI.start = ts).filter(GI.stop = ts)
try:
G = G.one()
except NoResultFound:
return None
M = Membership
members = session.query(M.employee_id).filter
(G.group_id==M.group_id)
members = members.filter(M.start = ts).filter(M.stop = ts)
G.members = [x[0] for x in members.all()]
return dict(id=G.group_id,name=G.group_name, members = G.members)
def demo(session):
## populate
gnames = (group_id,group_name,start,stop)
for g in [(1,group 1, 0,10),(1,new group 1, 10,20),
(2,'group2', 3, forever)]:
session.add(GroupInfo( **(dict(zip(gnames,g)
session.flush()
mnames = (group_id,employee_id,start,stop)
for e in [ (1,42,1,5),(2,42,5,100),(1,18,2,50)]:
session.add(Membership( **(dict(zip(mnames,e)
session.flush()
session.commit()
for (gid, ts) in (10,10), (1,3),(1,6),(1,11),(1,100),(2,1),(2,10):
print Group %i, ts %i % (gid,ts)
print group_snapshot_ts(session,gid,ts)
return None
## run it!
session, engine = db_setup()
demo(session)
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this
[sqlalchemy] Re: Queries in historical db's, where the join depends on having an overlapping timestamp (or other filter condition)
That's a totally fair answer! Mostly, I wish some sense of relational
change over time was built into SQL, the way it is in BigTable style
systems.
Maybe you could shed a little light on how to use the overlap
operator? I'm having trouble getting the multiple fields into the
clause statement.
In [43]:
session.query(Membership).filter(Membership.start.op('OVERLAPS',[1,100]))
TypeError: op() takes exactly 2 arguments (3 given)
It would also be have / emulate a .when(ts) method in queries (perhaps
via a query subclass) that would take the timings into account, but
this does smack of magic as you suggest!
Thanks for the advice!
Gregg
On Thu, Jul 16, 2009 at 10:17 AM, Michael Bayermike...@zzzcomputing.com wrote:
Gregg Lind wrote:
Questions:
1. Is there a SQLAlchemical way to write group_snapshot_ts into
a declarative class, such that the joins and loader respect the
time
constraints? (Read-only is fine as well on the loaded attributes)
a. eager_loader?
b. subclassing query?
im really not in favor of trying to build magic awareness of things like
this into mappers and query subclasses. If you have complex conditions
to be queried upon I am a fan of abstracting the common kinds of queries
you need into Python functions and/or class methods. no magic, just
regular old application architecture.
--~--~-~--~~~---~--~~
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: Queries in historical db's, where the join depends on having an overlapping timestamp (or other filter condition)
I believe that I asked Michael a similar question, in a different way,
a few days ago.
The answer was to use contains_eager. I used something like the
following and it worked great to query what the membership of a group
was at a specific time. The two tables remain simple, related by a
group_id and the query is dynamic according to the time that you are
interested in
map = mapper( Group,
group_table,
properties=dict( members=relation( Member,
backref='group' ) )
)
statement = names.outerjoin(members).select(
and_( Member.start = reftime, Member.stop = reftime
).apply_labels()
query = session.query(Group).options( contains_eager
('members') )
query = query.from_statement(statement)
--~--~-~--~~~---~--~~
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] Joined subquery, SQL syntax error
Hello,
I have a query that fails when executed: the engine is passed the
string representation of a sqlalchemy.sql.expression._BinaryExpression
object as a parameter, resulting in a syntax error. This expression is
part of
a subquery which is joined to a table. There is a simplified exemple
below along with the resulting traceback.
Is this a problem with my query or a bug in SA ?
Thanks in advance.
-- Test code --
from sqlalchemy import MetaData, create_engine
from sqlalchemy.orm import create_session
from sqlalchemy import Table, Column, Integer, Unicode, String,
DateTime, ForeignKey, Boolean, \
desc, select, and_, or_, subquery, CheckConstraint
from sqlalchemy.orm import relation, backref, mapper, eagerload
from sqlalchemy.orm.exc import NoResultFound
from sqlalchemy.sql import func, label, union
metadata = MetaData()
engine = create_engine('sqlite://')
session = create_session(engine, autoflush=True, autocommit=False)
foo_table = Table('foo', metadata,
Column('id', Integer, primary_key=True, nullable=False),
)
bar_table = Table('bar', metadata,
Column('id', Integer, primary_key=True, nullable=False),
Column('foo_id', Integer, ForeignKey('foo.id')),
Column('v', Integer),
)
sub = select([bar_table.c.foo_id, func.sum(bar_table.c.v).label
('sum')]).\
where(func.case([(bar_table.c.v 1, True)], else_=False)).\
group_by(bar_table.c.foo_id)
query = select([foo_table.c.id, sub.c.sum], from_obj=[foo_table.join
(bar_table, foo_table.c.id == bar_table.c.foo_id)])
print list(session.query(query))
-- Results below --
$ python test.py
Traceback (most recent call last):
File test.py, line 29, in module
print list(session.query(query))
File /usr/lib/python2.5/site-packages/SQLAlchemy-0.5.4p2-py2.5.egg/
sqlalchemy/orm/query.py, line 1287, in __iter__
return self._execute_and_instances(context)
File /usr/lib/python2.5/site-packages/SQLAlchemy-0.5.4p2-py2.5.egg/
sqlalchemy/orm/query.py, line 1290, in _execute_and_instances
result = self.session.execute(querycontext.statement,
params=self._params, mapper=self._mapper_zero_or_none())
File /usr/lib/python2.5/site-packages/SQLAlchemy-0.5.4p2-py2.5.egg/
sqlalchemy/orm/session.py, line 755, in execute
clause, params or {})
File /usr/lib/python2.5/site-packages/SQLAlchemy-0.5.4p2-py2.5.egg/
sqlalchemy/engine/base.py, line 824, in execute
return Connection.executors[c](self, object, multiparams, params)
File /usr/lib/python2.5/site-packages/SQLAlchemy-0.5.4p2-py2.5.egg/
sqlalchemy/engine/base.py, line 874, in _execute_clauseelement
return self.__execute_context(context)
File /usr/lib/python2.5/site-packages/SQLAlchemy-0.5.4p2-py2.5.egg/
sqlalchemy/engine/base.py, line 896, in __execute_context
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File /usr/lib/python2.5/site-packages/SQLAlchemy-0.5.4p2-py2.5.egg/
sqlalchemy/engine/base.py, line 950, in _cursor_execute
self._handle_dbapi_exception(e, statement, parameters, cursor,
context)
File /usr/lib/python2.5/site-packages/SQLAlchemy-0.5.4p2-py2.5.egg/
sqlalchemy/engine/base.py, line 931, in _handle_dbapi_exception
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
sqlalchemy.exc.OperationalError: (OperationalError) near GROUP:
syntax error u'SELECT id AS id, sum AS sum \nFROM (SELECT foo.id AS
id, sum \nFROM (SELECT bar.foo_id AS foo_id, sum(bar.v) AS sum \nFROM
bar \nWHERE case(?) GROUP BY bar.foo_id), foo JOIN bar ON foo.id =
bar.foo_id)' [[(sqlalchemy.sql.expression._BinaryExpression object at
0x7fbb1d6c, True)]]
-- End --
--~--~-~--~~~---~--~~
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: Queries in historical db's, where the join depends on having an overlapping timestamp (or other filter condition)
Jess,
Thanks for posting the actual class :) Just reading the description
use contains_eager didn't tell me enough about how to make it
happen.
Cheers,
Gregg
On Thu, Jul 16, 2009 at 12:54 PM, jessjesslp...@gmail.com wrote:
I believe that I asked Michael a similar question, in a different way,
a few days ago.
The answer was to use contains_eager. I used something like the
following and it worked great to query what the membership of a group
was at a specific time. The two tables remain simple, related by a
group_id and the query is dynamic according to the time that you are
interested in
map = mapper( Group,
group_table,
properties=dict( members=relation( Member,
backref='group' ) )
)
statement = names.outerjoin(members).select(
and_( Member.start = reftime, Member.stop = reftime
).apply_labels()
query = session.query(Group).options( contains_eager
('members') )
query = query.from_statement(statement)
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
