[sqlalchemy] Unexplainable SQL queries / Performance issues
Hi, I use SQLalchemy 0.7.9 with SQLite and have a performance issue. I have a simple database with joined table inheritance and some relationships. In short: I have a base class Human, and 3 subclasses (Men, Woman and Child), and I have a class House. The house class have a many to many relationship to the human class (a house have residents, and every human have houses). And there are two more relationship, the Woman and the Men class have a many to many relationship to the Child class (a men/woman can have many children). Here you can see my model , query code: http://pastebin.com/mcum0c7Q The issue is: if I load a house from the database with house = session.query(House).first() and then access the residents of this house with house.residents, and iterate over the residents and access the children of every resident then sqlalchemy emits a new sqlquery on every access: for resident in house.residentes: print resident.myChildren # emits new sql-query This is very bad for my performance, what I need is a solution, that load with a single session.query(House)-Command all residents AND all children of the residents at once! Is this possible? For the many to many relationship between the Women/Men and the Child class is use lazy='subquery', but sqlalchemy ignore this! Why? I hope someone could help me. -- 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/-/__1nn279L1EJ. 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] SQLAlchemy transaction problem
Hi Michael, I applied your patch on my current SA 0.7.3 without any problem, and added a few traces. Everything seems to work perfectly! Here is the first output with updates: do_begin_twophase do_prepare_twophase = True do_commit_twophase oci_prepared = True do_commit and without real updates: do_begin_twophase do_prepare_twophase = False do_commit_twophase oci_prepared = False no commit ! That's great, for me it works like a charm! Do you think that this patch could be backported to a new 0.7.x release of SA ? Best regards, Thierry 2012/11/21 Michael Bayer mike...@zzzcomputing.com great. can you try the attached patch please (latest 0.8 tip), which will not call commit if the prepare returned false. I'm not sure if this is complete though, if we should be doing a rollback afterwards, or what. not sure if I can get my local oracle XE to do two phase for real, that would help a lot. this patch would need quite a few tests. -- 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. On Nov 20, 2012, at 6:33 PM, Thierry Florac wrote: Hi Michael, Following your guidelines, this is the first test I made against cx_Oracle : conn = cx_Oracle.connect('scott','tiger','localhost:1521/ORCL', twophase=True) id = random.randint(0, 2 ** 128) xid = (0x1234, %032x % id, %032x % 9) conn.begin(*xid) conn.prepare() False conn.commit() Traceback (most recent call last): File console, line 1, in module DatabaseError: ORA-24756: la transaction n'existe pas As you can see, the prepare method returns False, meaning that the transaction was *NOT* prepared. If I add any update statement, everything is OK. Oracle 11g documentation seems to indicate that the prepare call can return three status : PreparedData on the node has been modified by a statement in the distributed transaction, and the node was successfully prepared. Read-onlyNo data on the node has been, or can be, modified (only queried), so no preparation is necessary. AbortThe node cannot successfully prepare. cx_Oracle returns False when there is nothing available to prepare, so probably at least in case 2, case for which commit shouldn't be done to avoid ORA-24756 error. So it's behaviour seems conform with Oracle OCI documentation... So my last question is : in the context of an SA session, who is responsible of the prepare/commit statements, so that commit is not called if prepared was not OK ? Best regards, Thierry 2012/11/20 Michael Bayer mike...@zzzcomputing.com Googling for the ORA code yields the usual tautological answers that say nothing. The first step here would be to isolate the issue in a simple test case with SQLAlchemy only, and then cx_oracle where I suspect the bug lies. The Session will not create a transaction at all, if no SQL is emitted. So that would mean SQL is definitely emitted. Can you confirm this ? (look at your SQL logging). Then, an initial test would be like this: s = Session(twophase=True, bind=my_oracle_engine) s.execute(select 1 from dual) s.commit() Here's my test, against Postgresql: from sqlalchemy.orm import Session from sqlalchemy import create_engine e = create_engine('postgresql://scott:tiger@localhost/test', echo=True) s = Session(e, twophase=True) s.execute(select 1) s.commit() the output: 2012-11-20 10:53:06,894 INFO sqlalchemy.engine.base.Engine select version() 2012-11-20 10:53:06,894 INFO sqlalchemy.engine.base.Engine {} 2012-11-20 10:53:06,897 INFO sqlalchemy.engine.base.Engine select current_schema() 2012-11-20 10:53:06,897 INFO sqlalchemy.engine.base.Engine {} 2012-11-20 10:53:06,900 INFO sqlalchemy.engine.base.Engine select 1 2012-11-20 10:53:06,900 INFO sqlalchemy.engine.base.Engine {} 2012-11-20 10:53:06,900 INFO sqlalchemy.engine.base.Engine PREPARE TRANSACTION '_sa_ddca4886b1f5db002e83058341de2609' 2012-11-20 10:53:06,900 INFO sqlalchemy.engine.base.Engine {} 2012-11-20 10:53:06,908 INFO sqlalchemy.engine.base.Engine COMMIT PREPARED '_sa_ddca4886b1f5db002e83058341de2609' 2012-11-20 10:53:06,908 INFO sqlalchemy.engine.base.Engine {} 2012-11-20 10:53:06,909 INFO sqlalchemy.engine.base.Engine BEGIN 2012-11-20 10:53:06,909 INFO sqlalchemy.engine.base.Engine {} which I will explain, first there's a BEGIN TWOPHASE that unfortunately is not logged, I have just committed a fix for that in tip. Then there's the PREPARE and the COMMIT - but this is Postgresql's syntax. The Oracle implementation only uses an XID when it calls cx_oracle's begin() method, and then I'd assume its up to cx_oracle to do the rest.So if there's a bug anywhere, it's probably in cx_oracle. My guess is
Re: [sqlalchemy] Re: ObjectDeletedError: Instance 'xxx' has been deleted
Hi, Michael Bayer. Thanks, that example is really helpful. In your example I used *Session.object_session*http://docs.sqlalchemy.org/en/latest/orm/session.html#sqlalchemy.orm.session.Session.object_sessionmethod to verify that main_method and method1 running in two different sessions. But my test result showing that main_method and method1 running in same session. I think, we can check this using method *Session.object_session** *, I am not sure about that. * added following code in main_method s1 = Session.object_session(db_obj1) print ===main_methodsession=, vars(s1) * added following code in method1 s2 = Session.object_session(db_obj2) print ===method1session=, vars(s2) *Result =* *===main_methodsession=* {'autocommit': False, 'autoflush': True, 'transaction': sqlalchemy.orm.session.SessionTransaction object at 0x22a5f90, *'hash_key': 36330896*, 'expire_on_commit': True, '_new': {}, 'bind': Engine(mysql://root:cvt@localhost/cvt_ee), '_deleted': {}, '_flushing': False, 'identity_map': {(class '__main__.A', (1L,)): sqlalchemy.orm.state.InstanceState object at 0x22a5c90}, '_enable_transaction_accounting': True, 'extensions': [], '_identity_cls': class 'sqlalchemy.orm.identity.WeakInstanceDict', 'twophase': False, '_Session__binds': {}, '_query_cls': class 'sqlalchemy.orm.query.Query', '_mapper_flush_opts': {}} ===method1session= {'autocommit': False, 'autoflush': True, 'transaction': sqlalchemy.orm.session.SessionTransaction object at 0x2073190,* 'hash_key': 36330896,* 'expire_on_commit': True, '_new': {}, 'bind': Engine(mysql://root:cvt@localhost/cvt_ee), '_deleted': {}, '_flushing': False, 'identity_map': {(class '__main__.A', (2L,)): sqlalchemy.orm.state.InstanceState object at 0x2073550, (class '__main__.A', (1L,)): sqlalchemy.orm.state.InstanceState object at 0x22a5c90}, '_enable_transaction_accounting': True, 'extensions': [], '_identity_cls': class 'sqlalchemy.orm.identity.WeakInstanceDict', 'twophase': False, '_Session__binds': {}, '_query_cls': class 'sqlalchemy.orm.query.Query', '_mapper_flush_opts': {}} -- 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/-/qzSbUaBu8UoJ. 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] Trying to apply result of raw sql to an 'in_' clause of mapped table
On Wed, 2012-11-21 at 13:05:22 +0100, Ralph Heinkel wrote: The latter was my favorite, but this seems to fail with [...] File /usr/local/lib/python2.7/site-packages/SQLAlchemy-0.5.8-py2.7.egg/sqlalchemy/sql/expression.py, line 1390, in in_ return self._in_impl(operators.in_op, operators.notin_op, other) File /usr/local/lib/python2.7/site-packages/SQLAlchemy-0.5.8-py2.7.egg/sqlalchemy/sql/expression.py, line 1409, in _in_impl for o in seq_or_selectable: TypeError: '_TextClause' object is not iterable Any idea how to fix this? My first guess is that you're using too old version of SQLAlchemy (0.5.8). I suggest to try out the latest version 0.7.9. -- Audrius Kažukauskas http://neutrino.lt/ pgpsGOaXAu2Ds.pgp Description: PGP signature
RE: [sqlalchemy] Inheriting a functionality in SQLA
Yes, I tried mix-in approach, and it works, thanks. From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of Robert Forkel Sent: 20 ноября 2012 г. 17:09 To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] Inheriting a functionality in SQLA As far as i know each declarative Base has its own metadata registry. You are using two. Why not use multiple mixins to inherit the columns? Am 20.11.2012 10:31 schrieb AlexVhr viho...@gmail.com: I'm trying to incapsulate some functionality (some columns mainly) into base classes to inherit my models from them. The setup looks like this: class EntityTemplate(): @declared_attr def __tablename__(cls): return cls.__name__.lower() id = Column(Integer(), primary_key=True) timestamp = Column(DateTime()) class DocumentTemplate(EntityTemplate): date = Column(Date()) number = Column(String(5)) Entity = declarative_base(cls=EntityTemplate, name='Entity') Document = declarative_base(cls=DocumentTemplate, name='Document') I'm trying to use it like this: class Customer(Entity): name = Column(String(25)) address = Column(String(50)) class Invoice(Document): customer_id = Column(Integer, ForeignKey('customer.id')) customer = relationship(Customer) total = Column(Numeric(10,2)) Entity.metadata.create_all(engine) Document.metadata.create_all(engine) But on the last line I get this: sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'invoice.customer_id' could not find table 'customer' with which to generate a foreign key to target column 'id' If I inherit Invoice from Entity instead of Document, everything is fine (except the fact that columns date and number are missing). Why? (I'm using SQLAlchemy-0.7.9-py3.2). Thanks! -- 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/-/2IsSRLhqqqAJ. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com mailto:sqlalchemy%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 mailto:sqlalchemy@googlegroups.com sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to mailto:sqlalchemy+unsubscr...@googlegroups.com sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en 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 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] Unexplainable SQL queries / Performance issues
Morning Martin: I could be wrong, but I think what you're looking for is lazy='joined' rather than lazy='subquery'. When I change the following, I see one query per showDatabase() call rather than two. class Men(Human): myChildren = relationship('Child', secondary=link_table, lazy='joined') class Woman(Human): myChildren = relationship('Child', secondary=link_table, lazy='joined') Here's how I think of it, with examples from: http://docs.sqlalchemy.org/en/rel_0_8/orm/loading.html?highlight=subqueryload#using-loader-strategies-lazy-loading-eager-loading * Case 1 (1 + N queries): # set children to load lazily session.query(Parent).options(lazyload('children')).all() * Case 2 (1 query): # set children to load eagerly with a join session.query(Parent).options(joinedload('children')).all() * Case 3 (2 queries): # set children to load eagerly with a second statement session.query(Parent).options(subqueryload('children')).all() The subqueryload() and lazy='subquery' options emit an *additional* SQL statement for each collection requested, but at least it's not N queries (one for each child). Thanks for including isolated code to easily reproduce the question. Cheers, --diana On Wed, Nov 21, 2012 at 3:25 AM, Martin84 steko...@googlemail.com wrote: Hi, I use SQLalchemy 0.7.9 with SQLite and have a performance issue. I have a simple database with joined table inheritance and some relationships. In short: I have a base class Human, and 3 subclasses (Men, Woman and Child), and I have a class House. The house class have a many to many relationship to the human class (a house have residents, and every human have houses). And there are two more relationship, the Woman and the Men class have a many to many relationship to the Child class (a men/woman can have many children). Here you can see my model , query code: http://pastebin.com/mcum0c7Q The issue is: if I load a house from the database with house = session.query(House).first() and then access the residents of this house with house.residents, and iterate over the residents and access the children of every resident then sqlalchemy emits a new sqlquery on every access: for resident in house.residentes: print resident.myChildren # emits new sql-query This is very bad for my performance, what I need is a solution, that load with a single session.query(House)-Command all residents AND all children of the residents at once! Is this possible? For the many to many relationship between the Women/Men and the Child class is use lazy='subquery', but sqlalchemy ignore this! Why? I hope someone could help me. -- 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] SQLAlchemy transaction problem
On Nov 21, 2012, at 5:32 AM, Thierry Florac wrote: Hi Michael, I applied your patch on my current SA 0.7.3 without any problem, and added a few traces. Everything seems to work perfectly! Here is the first output with updates: do_begin_twophase do_prepare_twophase = True do_commit_twophase oci_prepared = True do_commit and without real updates: do_begin_twophase do_prepare_twophase = False do_commit_twophase oci_prepared = False no commit ! That's great, for me it works like a charm! Do you think that this patch could be backported to a new 0.7.x release of SA ? I can backport but I really need to make sure it works in a good deal of scenarios, so I'm hoping whatever prevented it from working for me some years back no longer applies. its tracked at http://www.sqlalchemy.org/trac/ticket/2611 for now. Best regards, Thierry 2012/11/21 Michael Bayer mike...@zzzcomputing.com great. can you try the attached patch please (latest 0.8 tip), which will not call commit if the prepare returned false. I'm not sure if this is complete though, if we should be doing a rollback afterwards, or what. not sure if I can get my local oracle XE to do two phase for real, that would help a lot. this patch would need quite a few tests. -- 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. On Nov 20, 2012, at 6:33 PM, Thierry Florac wrote: Hi Michael, Following your guidelines, this is the first test I made against cx_Oracle : conn = cx_Oracle.connect('scott','tiger','localhost:1521/ORCL', twophase=True) id = random.randint(0, 2 ** 128) xid = (0x1234, %032x % id, %032x % 9) conn.begin(*xid) conn.prepare() False conn.commit() Traceback (most recent call last): File console, line 1, in module DatabaseError: ORA-24756: la transaction n'existe pas As you can see, the prepare method returns False, meaning that the transaction was *NOT* prepared. If I add any update statement, everything is OK. Oracle 11g documentation seems to indicate that the prepare call can return three status : Prepared Data on the node has been modified by a statement in the distributed transaction, and the node was successfully prepared. Read-onlyNo data on the node has been, or can be, modified (only queried), so no preparation is necessary. AbortThe node cannot successfully prepare. cx_Oracle returns False when there is nothing available to prepare, so probably at least in case 2, case for which commit shouldn't be done to avoid ORA-24756 error. So it's behaviour seems conform with Oracle OCI documentation... So my last question is : in the context of an SA session, who is responsible of the prepare/commit statements, so that commit is not called if prepared was not OK ? Best regards, Thierry 2012/11/20 Michael Bayer mike...@zzzcomputing.com Googling for the ORA code yields the usual tautological answers that say nothing. The first step here would be to isolate the issue in a simple test case with SQLAlchemy only, and then cx_oracle where I suspect the bug lies. The Session will not create a transaction at all, if no SQL is emitted. So that would mean SQL is definitely emitted. Can you confirm this ? (look at your SQL logging). Then, an initial test would be like this: s = Session(twophase=True, bind=my_oracle_engine) s.execute(select 1 from dual) s.commit() Here's my test, against Postgresql: from sqlalchemy.orm import Session from sqlalchemy import create_engine e = create_engine('postgresql://scott:tiger@localhost/test', echo=True) s = Session(e, twophase=True) s.execute(select 1) s.commit() the output: 2012-11-20 10:53:06,894 INFO sqlalchemy.engine.base.Engine select version() 2012-11-20 10:53:06,894 INFO sqlalchemy.engine.base.Engine {} 2012-11-20 10:53:06,897 INFO sqlalchemy.engine.base.Engine select current_schema() 2012-11-20 10:53:06,897 INFO sqlalchemy.engine.base.Engine {} 2012-11-20 10:53:06,900 INFO sqlalchemy.engine.base.Engine select 1 2012-11-20 10:53:06,900 INFO sqlalchemy.engine.base.Engine {} 2012-11-20 10:53:06,900 INFO sqlalchemy.engine.base.Engine PREPARE TRANSACTION '_sa_ddca4886b1f5db002e83058341de2609' 2012-11-20 10:53:06,900 INFO sqlalchemy.engine.base.Engine {} 2012-11-20 10:53:06,908 INFO sqlalchemy.engine.base.Engine COMMIT PREPARED '_sa_ddca4886b1f5db002e83058341de2609' 2012-11-20 10:53:06,908 INFO sqlalchemy.engine.base.Engine {} 2012-11-20 10:53:06,909 INFO sqlalchemy.engine.base.Engine BEGIN 2012-11-20 10:53:06,909 INFO sqlalchemy.engine.base.Engine {} which I will explain,
Re: [sqlalchemy] Trying to apply result of raw sql to an 'in_' clause of mapped table
On Nov 21, 2012, at 7:05 AM, Ralph Heinkel wrote: Hi Michael, thanks for your reply. but with that aside, you can use text(): from sqlalchemy import text, bindparam Person.status_id.in_(text(select status_id ..., bindparams=[bindparam('n1', 'hired')])) and the bind values you can add later too: from sqlalchemy import text Person.status_id.in_(text(select status_id ...)).params(n1='hired') The latter was my favorite, but this seems to fail with [...] File /usr/local/lib/python2.7/site-packages/SQLAlchemy-0.5.8-py2.7.egg/sqlalchemy/sql/expression.py, line 1390, in in_ return self._in_impl(operators.in_op, operators.notin_op, other) File /usr/local/lib/python2.7/site-packages/SQLAlchemy-0.5.8-py2.7.egg/sqlalchemy/sql/expression.py, line 1409, in _in_impl for o in seq_or_selectable: TypeError: '_TextClause' object is not iterable Any idea how to fix this? yeahits that 0.5.8 that's your problem on this one :). That version is three years old. text() inside of in_() started working in the 0.6 series. -- 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] Zope-SQLAlchemy extension
On 11/16/2012 9:00 PM, Michael Bayer wrote: On Nov 16, 2012, at 11:40 AM, Clemens Herschel, III wrote: In a pyramid application: In models.py: DBSession= scoped_session(sessionmaker(extensions=ZopeTransactionExtension())) In view: dbsession = DBSession def add(request): #get implant1 dbsession.add(implant) transaction.commit() dbsession = DBsession This works fine on first add. On second call to add, the first implant object is updated rather than an object added as I would expect from the sqlalchemy session docs . Using a new session after the commit is suggested in the zope-sqlalchemy docs to achieve this. The user in this application might do repeated adds into many tables in one request . So because I want to use more than one session per request, I should not use Zope-SQLAlchemy extension but SQLAlchemy ScopedSession helper class. Is that correct? Please excuse any misuse of terminology. Thanks for pointing me in the right direction. Using SQLAlchemy0.7.3 zope-sqlalchemy0.6.1 there's misunderstanding here - whether an INSERT or UPDATE is emitted is based on the state of the object passed to session.add(), whether transient (INSERT) or detached (will become persistent and UPDATE will be emitted for changes). These states are documented here: http://docs.sqlalchemy.org/en/latest/orm/session.html#quickie-intro-to-object-states .A transient object is only created via the constructor, implant = Implant(), or if a detached implant object is made transient again using the make_transient() helper function. Else your object is detached or persistent and refers to an existing row and can only invoke an UPDATE or DELETE statement. The code here doesn't describe what implant is, or where it comes from, or what exactly doesn't work fine means as I don't see a second call to add() here and I don't have detail on the context in which this code excerpt is called. There's a vague suggestion here that perhaps you're doing some kind of master/slave/replication type of thing the user might do repeated adds into many tables in one request, there's different ways to approach that of which using multiple Session objects bound to each engine is just one, though a single Session can be made to refer to multiple engines simultaneously (see http://techspot.zzzeek.org/2012/01/11/django-style-database-routers-in-sqlalchemy/ for one example). make_transient() may be a worthy helper here or even just a simple copy() method on your implant object to make new transient instances. Thanks for your help. There was a misunderstanding and general confusion on my part. make transient() IS a worthy helper and I fixed my zope transactions error as well. -- 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. -- 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] Trying to apply result of raw sql to an 'in_' clause of mapped table
The latter was my favorite, but this seems to fail with [...] File /usr/local/lib/python2.7/site-packages/SQLAlchemy-0.5.8-py2.7.egg/sqlalchemy/sql/expression.py, line 1390, in in_ return self._in_impl(operators.in_op, operators.notin_op, other) File /usr/local/lib/python2.7/site-packages/SQLAlchemy-0.5.8-py2.7.egg/sqlalchemy/sql/expression.py, line 1409, in _in_impl for o in seq_or_selectable: TypeError: '_TextClause' object is not iterable Any idea how to fix this? yeahits that 0.5.8 that's your problem on this one :). That version is three years old. text() inside of in_() started working in the 0.6 series. oh yes, I see, we are far behind ... seems like we definitely have to migrate ... Thanks for your help anyway, the other solution with creating temporary table finally worked even in my antique version of SA! Thanks a lot, Ralph -- 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] SQLAlchemy transaction problem
this will need lots more work on my end and may not be possible without major caveats. once you use connection.begin(*xid), the cx_oracle connection goes into a new mode that seems to render the usual DBAPI contract of implicit begin permanently broken. I've spent about three hours trying to get the right combination of begin()/commit()/rollback() calls into the cx_oracle dialect, but the test suite still easily puts the connection into a permanently broken state as soon as it attempts to use the DBAPI connection traditionally after a two-phase operation. On Nov 21, 2012, at 5:32 AM, Thierry Florac wrote: conn = cx_Oracle.connect('scott','tiger','localhost:1521/ORCL', twophase=True) id = random.randint(0, 2 ** 128) xid = (0x1234, %032x % id, %032x % 9) conn.begin(*xid) conn.prepare() False conn.commit() -- 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: Is it possible to define multiple model objects that refer to the same table?
Thanks so much! Your pointers were exactly what I needed, specifically the bit which led me to discover exclude_properties. I'll leave my working code here in case it ever helps anybody else out: from sqlalchemy import Column, Date, Enum, Integer, String, Table, create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import ColumnProperty, sessionmaker from sqlalchemy.orm.mapper import class_mapper engine = create_engine('mysql://user:password@localhost/ issue_tracker') Session = sessionmaker(bind=engine) Base = declarative_base() tables = {'issue_type': Table('issue_type', Base.metadata, Column('id', Integer, primary_key=True), Column('type', Enum('Bug', 'Feature', 'Improvement', 'One-Time Task')), Column('created', Date), Column('num_visits', Integer)), 'issue_type_label': Table('issue_type_label', Base.metadata, Column('id', Integer, primary_key=True), Column('type', Enum('Bug', 'Feature', 'Improvement', 'One-Time Task')), Column('label_id', String), Column('created', Date), Column('num_visits', Integer))} def get_columns(model): return [x.key for x in class_mapper(model).iterate_properties if isinstance(x, ColumnProperty)] class IssueType(Base): __table__ = tables['issue_type'] class IssueLabel(Base): __table__ = tables['issue_type_label'] __mapper_args__ = {'exclude_properties': ['type']} class IssueTypeLabel(Base): __table__ = tables['issue_type_label'] print issue type:, get_columns(IssueType) print issue label:, get_columns(IssueLabel) print issue type label:, get_columns(IssueTypeLabel) This code correctly prints the following: issue type: ['id', 'type', 'created', 'num_visits'] issue label: ['id', 'label_id', 'created', 'num_visits'] issue type label: ['id', 'type', 'label_id', 'created', 'num_visits'] On Nov 16, 8:49 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Nov 16, 2012, at 2:59 PM, Rob Crowell wrote: Thanks for the help so far Michael! I can explain a little more about what I'm trying to do (I'm using a fictional application here but I think it pretty accurately translates into my actual application). BACKGROUND -- Let's say I'm writing an issue tracking application. Each issue that we're tracking has a type (an issue must have exactly one type), and each issue may have an unlimited number of user-provided labels. Each day, people browse our issue tracker and each time they do they generate a page view on the issue. Here's an example of one day's worth of data: IssueA: Views: 1, Type: Bug, Labels: [login-page (id=1), show-stopper (id=2)] IssueB: Views: 20, Type: One-Time Task, Labels: [disk-full (id=3), show-stopper (id=2)] IssueC: Views: 300, Type: Bug, Labels: [login-page (id=1)] The BigCo. I'm working for is very interested in knowing which issues are read by the most people, and they need the ability to generate reports sliced by arbitrary date ranges. However, we can tolerate a day delay, so we are writing summary tables each night. Two of these summary tables are aggregated by either issue type or label, and we also write a third table that can be used to drill-down and see page visits bucketed by both type and label: CREATE TABLE `issue_type` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `created` datetime NOT NULL, `type` enum('Bug','Feature','Improvement','One-Time Task') NOT NULL, `num_watchers` int(10) unsigned NOT NULL, PRIMARY KEY (`id`) ) CREATE TABLE `issue_label` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `created` datetime NOT NULL, `label_id` int(10) unsigned NOT NULL, `num_watchers` int(10) unsigned NOT NULL, PRIMARY KEY (`id`) ) CREATE TABLE `issue_type_label` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `created` datetime NOT NULL, `type` enum('Bug','Feature','Improvement','One-Time Task') NOT NULL, `label_id` int(10) unsigned NOT NULL, `num_visits` int(10) unsigned NOT NULL, PRIMARY KEY (`id`) ) So we'd run these insert statements at midnight: INSERT INTO issue_type (created, type, num_visits) VALUES (2012-11-15, Bug, 301), (2012-11-15, One-Time Task, 20); INSERT INTO issue_labels (created, label_id, num_visits) VALUES (2012-11-15, 1, 301), (2012-11-15, 2, 21), (2012-11-15, 3, 20); INSERT INTO issue_type_label (created, type, label_id, num_visits) VALUES (2012-11-15, Bug, 1, 301), (2012-11-15, Bug, 2, 1), (2012-11-15, One-Time Task, 3, 20), (2012-11-15, One-Time Task, 2, 20); Now when we want to generate the summary reports, we query one of the first two tables (if we're generating a report aggregated by issue type we hit issue_type, if we're
Re: [sqlalchemy] sqlalchemy.exc.DBAPIError: (Error) ('HY003', '[HY003] [FreeTDS][SQL Server]Program type out of range (0) (SQLGetData)') None None
On Friday, November 16, 2012 7:52:00 PM UTC-6, Michael Bayer wrote: On Nov 16, 2012, at 11:44 AM, Lukasz Szybalski wrote: Hello, Any idea what this error message means. I'm trying to execute this: s=session.execute(assp_Checks @begin_date=:start, @end_date=:end,@company_id=1,params={'start':date_to_process,'end':date_to_process}).fetchall() I get: Traceback (most recent call last): File stdin, line 1, in module File stdin, line 6, in process_date File /home/unique/checkexport/env26/lib/python2.6/site-packages/SQLAlchemy-0.7.9-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py, line 3210, in fetchall self.cursor, self.context) File /home/unique/checkexport/env26/lib/python2.6/site-packages/SQLAlchemy-0.7.9-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py, line 3204, in fetchall l = self.process_rows(self._fetchall_impl()) File /home/unique/checkexport/env26/lib/python2.6/site-packages/SQLAlchemy-0.7.9-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py, line 3171, in _fetchall_impl return self.cursor.fetchall() sqlalchemy.exc.DBAPIError: (Error) ('HY003', '[HY003] [FreeTDS][SQL Server]Program type out of range (0) (SQLGetData)') None None If I use isql I have no problems. exec assp_Checks @begin_date='11/15/2012', @end_date='11/15/2012',@company_id=1 I get 10 records back. I don't have a solution for this but it is certainly due to the reduced featureset present in ODBC, particularly that of FreeTDS.You'd be looking here to find a path to getting this to work with pyodbc directly, and perhaps you'd need to email on the freetds list and/or change your approach. Just confirming nothing change in sqlalchemy. This code has worked in sa in debian old stable, but for some reason when I've upgraded OS, freeTDS, and sqlalchemy I suddenly got that error. I'll check by using pure pyodbc and will contact freeTDS then. Thanks for the guidance. Lucas -- 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/-/-c-8YwLnhUYJ. 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] pymssql and uuid.UUID convertion
Hellow, fellow developers! I have run into and issue trying to use SQLAlchemy (0.7.4) and recent pymssql (2.0.0). Precisely, pymssql can not handle uuid.UUID objects as parameters (see related post on their ML herehttps://groups.google.com/forum/?fromgroups=#!topic/pymssql/ah6f8cl2Va0), but SA uses them in generated queries for the object querying/deletion. Folks from the pymssql suggest that the convertion of uuid.UUID to the string objects should be done in the corresponding SA dialect. If so, I would be glad to fix that with a patch if someone could give me couple of pointers to writing these dialects. Best regards, Ivan Kalinin. -- 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/-/C7_dDgzrWg4J. 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] Inheriting a functionality in SQLA
Thanks for the clarification, it works now. From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of Michael Bayer Sent: 21 ноября 2012 г. 3:58 To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] Inheriting a functionality in SQLA On Nov 20, 2012, at 4:31 AM, AlexVhr wrote: I'm trying to incapsulate some functionality (some columns mainly) into base classes to inherit my models from them. The setup looks like this: class EntityTemplate(): @declared_attr def __tablename__(cls): return cls.__name__.lower() id = Column(Integer(), primary_key=True) timestamp = Column(DateTime()) class DocumentTemplate(EntityTemplate): date = Column(Date()) number = Column(String(5)) Entity = declarative_base(cls=EntityTemplate, name='Entity') Document = declarative_base(cls=DocumentTemplate, name='Document') I'm trying to use it like this: class Customer(Entity): name = Column(String(25)) address = Column(String(50)) class Invoice(Document): customer_id = Column(Integer, ForeignKey('customer.id')) customer = relationship(Customer) total = Column(Numeric(10,2)) Entity.metadata.create_all(engine) Document.metadata.create_all(engine) But on the last line I get this: sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'invoice.customer_id' could not find table 'customer' with which to generate a foreign key to target column 'id' If I inherit Invoice from Entity instead of Document, everything is fine (except the fact that columns date and number are missing). Why? (I'm using SQLAlchemy-0.7.9-py3.2). Thanks! the use of two different declarative_base() makes this more complicated as there is no common MetaData collection between the two classes which allows foreign keys to be resolve based on string names, as well as class names like Customer to be resolved. the Base + MetaData combination represent a pair of registries that allow these string lookups to work. So using one declarative base would solve the issue, else you need to forego the usage of string identifiers and pass object references instead: customer_id = Column(Integer, ForeignKey(Customer.id)) customer = relationship(Customer) but there's really no need to use two different declarative bases, your EntityTemplate and DocumentTemplate are mixins, which if you'd like them to be packed into a single base class can be accomplished using a subclass of a single Base with the __abstract__ = True flag. http://docs.sqlalchemy.org/en/rel_0_8/orm/extensions/declarative.html#abstra ct -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to mailto:sqlalchemy@googlegroups.com sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to mailto:sqlalchemy+unsubscr...@googlegroups.com sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en 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 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] Detect whether a query already has a join on a table?
I want to avoid double joining on the same table. I know query._from_obj is where the query stores the join elements. However, it's not there if the join is from query.options(joinedload('some_relation')). For example, I have the following table relations: User: * userid * name Thing * thingid * name * userid Thing.user = relation(User, User.userid==Thing.userid) If I call: query = session.query(Thing).options(joinedload('user')).filter(User.name=='blah').all() This will generate the following query: SELECT thing.thingid, thing.name, thing.userid, user1.userid, user1.name FROM thing INNER JOIN user AS user1 INNER JOIN user WHERE user.name == 'blah' Notice the double join there. Now, I wouldn't do that if I'm writing the query in a single function, but if the code is modular, the child object loading and filtering is done in separate functions, with the query being passed around. Is there a way for me to detect whether a query already has a join on a certain table, whether the join is from query.join() or query.options(joinedload(x))? Any suggestion is welcome and appreciated. -- 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/-/oFfq3pWQm5wJ. 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.