Re: [sqlalchemy] Re: problems with filter_by()
On Monday, January 14, 2008 10:59:17 AM UTC-5, Michael Bayer wrote: On Jan 14, 2008, at 10:45 AM, maxi wrote: Thaks for your help. Can you post an example over how to use filter and filter_by in new sqlalchemy versions? http://docs.sqlalchemy.org/en/rel_0_8/orm/query.html#sqlalchemy.orm.query.Query.filter_by Docs are good but for the very lazy :) filter_by(name = 'Dan') or filter(User.name == 'Dan') -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Bug in query with multiple joins when using joined inheritance?
I didn't think it would be, but it is a bug, yes, am applying the patch in http://www.sqlalchemy.org/trac/ticket/2759 right now. On Jun 14, 2013, at 11:52 PM, Seth P spadow...@gmail.com wrote: I've encountered what I believe to be a bug in SQLAlchemy (versions 0.8.0 and 0.8.1) in a query that joins class/tables that use joined inheritance. In the code below, I would expect the three queries to produce the same output, namely [u'CCC'], but the first one gives a different (incorrect) result, [u'BBB']. Is this a bug, or is the query malformed? In the second query, adding a seemingly gratuitous join with D fixes the problem. And as the third query shows, replacing C by an aliased version also fixes the problem. So whatever is going on seems rather subtle. Thanks, Seth from sqlalchemy import Column, Integer, String, ForeignKey, create_engine from sqlalchemy.ext.declarative.api import declarative_base from sqlalchemy.orm import relationship, sessionmaker, scoped_session from sqlalchemy.orm.util import aliased Base = declarative_base(object) metadata = Base.metadata class A(Base): __tablename__ = 'A' idx = Column(Integer, primary_key=True) name = Column(String(20), nullable=False) type_idx = Column(Integer, nullable=False) __mapper_args__ = { 'polymorphic_on':type_idx } class B(A): __tablename__ = 'B' idx = Column(Integer, ForeignKey(str(A.__table__) + .idx), primary_key=True) __mapper_args__ = { 'polymorphic_identity':2 } class C(A): __tablename__ = 'C' idx = Column(Integer, ForeignKey(str(A.__table__) + .idx), primary_key=True) b_idx = Column(Integer, ForeignKey(str(B.__table__) + .idx), nullable=False) b = relationship(B, foreign_keys=[b_idx]) __mapper_args__ = { 'polymorphic_identity':3 } class D(A): __tablename__ = 'D' idx = Column(Integer, ForeignKey(str(A.__table__) + .idx), primary_key=True) __mapper_args__ = { 'polymorphic_identity':4 } class CtoD(Base): __tablename__ = 'CtoD' idx = Column(Integer, primary_key=True) c_idx = Column(Integer, ForeignKey(str(C.__table__) + .idx), nullable=False) c = relationship(C, foreign_keys=[c_idx]) d_idx = Column(Integer, ForeignKey(str(D.__table__) + .idx), nullable=False) d = relationship(D, foreign_keys=[d_idx]) if __name__ == '__main__': engine = create_engine('sqlite:///:memory:', echo=False) metadata.create_all(bind=engine) Session = scoped_session(sessionmaker(bind=engine)) session = Session() # populate tables with a single entry in each table b = B(name='BBB') c = C(name='CCC', b=b) d = D(name='DDD') c_to_d = CtoD(c=c, d=d) session.add_all([b, c, d, c_to_d]) session.commit() sql_query = session.query(B, C.name).join(C, B.idx == C.b_idx).join(CtoD, C.idx == CtoD.c_idx).join(D, CtoD.d_idx == D.idx) print [name for (_, name) in sql_query.all()] # [u'BBB'] sql_query = session.query(B, C.name).join(C, B.idx == C.b_idx).join(CtoD, C.idx == CtoD.c_idx) print [name for (_, name) in sql_query.all()] # [u'CCC'] aliased_C = aliased(C) sql_query = session.query(B, aliased_C.name).join(aliased_C, B.idx == aliased_C.b_idx).join(CtoD, aliased_C.idx == CtoD.c_idx).join(D, CtoD.d_idx == D.idx).join(D, CtoD.d_idx == D.idx) print [name for (_, name) in sql_query.all()] # [u'CCC'] -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Bug in query with multiple joins when using joined inheritance?
fixed in master and rel_0_8. On Jun 15, 2013, at 3:02 PM, Michael Bayer mike...@zzzcomputing.com wrote: I didn't think it would be, but it is a bug, yes, am applying the patch in http://www.sqlalchemy.org/trac/ticket/2759 right now. On Jun 14, 2013, at 11:52 PM, Seth P spadow...@gmail.com wrote: I've encountered what I believe to be a bug in SQLAlchemy (versions 0.8.0 and 0.8.1) in a query that joins class/tables that use joined inheritance. In the code below, I would expect the three queries to produce the same output, namely [u'CCC'], but the first one gives a different (incorrect) result, [u'BBB']. Is this a bug, or is the query malformed? In the second query, adding a seemingly gratuitous join with D fixes the problem. And as the third query shows, replacing C by an aliased version also fixes the problem. So whatever is going on seems rather subtle. Thanks, Seth from sqlalchemy import Column, Integer, String, ForeignKey, create_engine from sqlalchemy.ext.declarative.api import declarative_base from sqlalchemy.orm import relationship, sessionmaker, scoped_session from sqlalchemy.orm.util import aliased Base = declarative_base(object) metadata = Base.metadata class A(Base): __tablename__ = 'A' idx = Column(Integer, primary_key=True) name = Column(String(20), nullable=False) type_idx = Column(Integer, nullable=False) __mapper_args__ = { 'polymorphic_on':type_idx } class B(A): __tablename__ = 'B' idx = Column(Integer, ForeignKey(str(A.__table__) + .idx), primary_key=True) __mapper_args__ = { 'polymorphic_identity':2 } class C(A): __tablename__ = 'C' idx = Column(Integer, ForeignKey(str(A.__table__) + .idx), primary_key=True) b_idx = Column(Integer, ForeignKey(str(B.__table__) + .idx), nullable=False) b = relationship(B, foreign_keys=[b_idx]) __mapper_args__ = { 'polymorphic_identity':3 } class D(A): __tablename__ = 'D' idx = Column(Integer, ForeignKey(str(A.__table__) + .idx), primary_key=True) __mapper_args__ = { 'polymorphic_identity':4 } class CtoD(Base): __tablename__ = 'CtoD' idx = Column(Integer, primary_key=True) c_idx = Column(Integer, ForeignKey(str(C.__table__) + .idx), nullable=False) c = relationship(C, foreign_keys=[c_idx]) d_idx = Column(Integer, ForeignKey(str(D.__table__) + .idx), nullable=False) d = relationship(D, foreign_keys=[d_idx]) if __name__ == '__main__': engine = create_engine('sqlite:///:memory:', echo=False) metadata.create_all(bind=engine) Session = scoped_session(sessionmaker(bind=engine)) session = Session() # populate tables with a single entry in each table b = B(name='BBB') c = C(name='CCC', b=b) d = D(name='DDD') c_to_d = CtoD(c=c, d=d) session.add_all([b, c, d, c_to_d]) session.commit() sql_query = session.query(B, C.name).join(C, B.idx == C.b_idx).join(CtoD, C.idx == CtoD.c_idx).join(D, CtoD.d_idx == D.idx) print [name for (_, name) in sql_query.all()] # [u'BBB'] sql_query = session.query(B, C.name).join(C, B.idx == C.b_idx).join(CtoD, C.idx == CtoD.c_idx) print [name for (_, name) in sql_query.all()] # [u'CCC'] aliased_C = aliased(C) sql_query = session.query(B, aliased_C.name).join(aliased_C, B.idx == aliased_C.b_idx).join(CtoD, aliased_C.idx == CtoD.c_idx).join(D, CtoD.d_idx == D.idx).join(D, CtoD.d_idx == D.idx) print [name for (_, name) in sql_query.all()] # [u'CCC'] -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] flask-sqlalchemy pysybase connections
On Jun 14, 2013, at 3:18 PM, Kevin S kevinrst...@gmail.com wrote: I am running into a problem while developing a flask application using flask-sqlalchemy. Now, I'm not even 100% sure my problem is sqlalchemy related, but I don't know how to debug this particular issue. To start, I have a sybase database that I want to see if I can build a report generating application for. The reports will all be custom SQL queries that are requested by our users, and they will be able to refresh throughout the day as they edit and clean up their data (we focus on a lot of data curation). We plan to do other things that merit the use of an ORM, and we have a lot of complex relationships. Anyway, that's why I'm first trying to get this to work in our flask + sqlalchemy stack. And it does work in fact. Now the problem is, my current application is not scalable, because any time I do a long query (say several seconds or more), flask will not accept any additional requests until that query finishes. (Note: I am running the application through cherrypy). I have tested various things to ensure that the application can handle multiple incoming requests. If I have it just loop through a big file, or even just sleep instead of doing a query, then I can bang away at it all I want from other browser windows, and it's fine. We also have a copy of our database that is in postgres (this is only for testing, and can't be a final solution, because it gets updated only once a week). So, I've found that if I hook the application up to the postgres version, I don't have this problem. I can initiate a long query in one browser tab, and any other page requests in subsequent windows come back fine. The problem is only when using Sybase. We have other applications that are not flask or sqlalchemy, and they don't seem to have this limitation. As far as I can tell, I've narrowed it down to as soon as it executes a query. The entire app will wait until that query finishes, not allowing any new connections. I have log statements in my request handlers, and even in my before_request method, and those will not print a thing until the moment that first query returns. Additional info: I am using Sybase 15 with the pysybase driver. I initiate the raw SQL queries like this: con = db.session.connection() results = con.execute(query) But I also see the same problem if I use object relationships via Object.query.all() or whatever. I don't expect anyone to specifically know about this sybase driver, but I'm wondering what more can I do to try to debug this? I'm mostly interested in figuring out where the limitation is coming from, i.e. is it the database, the driver, or the way I'm using the session. I can provide additional details if needed. well it's not a pooling issue because you don't have the issue with Postgresql, so its a Sybase driver issue. you'd need to see if you can boil down this same behavior to a single Python test script that uses the Sybase DBAPI directly. Though that might only manage to prove its the Sybase DBAPI, and im not sure how much those drivers are being supported. Have you tried a different DBAPI ? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.