[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.
