[sqlalchemy] any way to pre cook a monster query?
Hi All,
I have a few monster queries like this:
query = session.query(
Blah1.name.label('blah1'),
Blah2.name.label('blah2'),
blah3.name.label('blah4'),
Blah5.name.label('blah5'),
Blah6.name.label('blah6'),
func.sum(case([(Blah7.blah8_blah9=='Blah8',Blah10.qty)],
else_=Blah10.qty*-1)).label('blah11'),
func.sum(case([(Blah7.blah8_blah9=='Blah8',Blah10.qty*blah13.price)],
else_=Blah10.qty*blah13.price*-1)).label('blah12')).\
join(Blah1.participents,
Participation.blah5,
Blah5.blah10s,
Blah10.fill,
blah13.blah7,
Blah7.blah6,
Blah7.blah12,
Blah12.blah7_idea,
Blah2.blah4).\
filter(and_(Blah1.id==self.id,
Participation.valid_from = on_date,
or_(Participation.valid_to on_date,
Participation.valid_to == None))).\
group_by(Blah6).\
order_by(Blah6.name)
...apologies for the obfuscation.
Now, is there any way I can pre-cook this (eg: at module-level) such
that I can later just plug in self.id and on_date, bind to a session and
call .all() on it?
It seems a bit wasteful to do all the SQL generation on every query when
it's almost all identical all the time...
cheers,
Chris
--
Simplistix - Content Management, Batch Processing Python Consulting
- http://www.simplistix.co.uk
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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] Re: Is it possible to narrow down the generated query in SQLAlchemy if it was created via query_property?
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Boda Cydo Sent: 26 January 2010 01:35 To: sqlalchemy Subject: [sqlalchemy] Re: Is it possible to narrow down the generated query in SQLAlchemy if it was created via query_property? On Jan 25, 2:46 am, Boda Cydo bodac...@gmail.com wrote: Let me know if the question is not clearly stated. I'll update it with more details. Any ideas? When you access Comment.query, you are getting back an already instantiated Query object which, as the error message indicates, isn't callable. I guess I don't really understand why you want to use Comment.query(Comment.comment) rather than Session.query(Comment.comment). If you really want this, you could subclass Query to add a __call__ method that creates a new query instance: class CallableQuery(Query): def __call__(self, *args, **kwargs): return Session.query(*args, **kwargs) class Comments(Base): query = Session.query_property(query_cls=CallableQuery) ...but I'm still not sure what the point is. Simon -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] any way to pre cook a monster query?
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Chris Withers Sent: 26 January 2010 09:13 To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] any way to pre cook a monster query? Hi All, I have a few monster queries like this: [SNIP] Now, is there any way I can pre-cook this (eg: at module-level) such that I can later just plug in self.id and on_date, bind to a session and call .all() on it? It seems a bit wasteful to do all the SQL generation on every query when it's almost all identical all the time... cheers, Chris I think you can use bind parameter objects for this: http://www.sqlalchemy.org/docs/sqlexpression.html#bind-parameter-objects ...and use the query's params method to supply the values. Hope that helps, Simon -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] any way to pre cook a monster query?
King Simon-NFHD78 wrote: I think you can use bind parameter objects for this: http://www.sqlalchemy.org/docs/sqlexpression.html#bind-parameter-objects ...and use the query's params method to supply the values. Indeed, it gives an inkling... ...but how do I wire building the existing query, with the bind parameters, and then applying it to a session in the same way session.query does? (in short, I'm still missing the leaps to make my existing query a module-level thing, any help to make that happen would be very gratefully recieved!) Chris -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] any way to pre cook a monster query?
Wichert Akkerman wrote: snip offlist conversation ...but not without using session.Query or any joins, but the looks of it... unless I'm missing something? There is no difference between using session.Query or not. Bindparam just insert a dummy in a query, which you fill in when you call session.execute if I remember correctly. Indeed, but it's the bit in square brackets in: session.query([X.a,Y.b,Z.c).join(X,Y,Z)].whatever ...that I'm looking to make static, and I don't know how to do that... cheers, Chris -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] Re: Two Table Entity In Declarative Style [$50!]
On Tue, 2009-10-06 at 09:56 -0400, Michael Bayer wrote: Adam Tauno Williams wrote: But I have one 1:1 relation in my database that would be much easier to model as just one object. job_history job_history_info --- job_history_id (PK) -1:1- job_history_id object_version job_history_info_id (PK) job_id comment actor_iddb_status action action_date job_status db_status if you create Table objects for job_history and job_history_info, you can create a join via job_history.join(job_history_info), and then specify that to a declarative class using __table__ = myjoin instead of __tablename__. you will also want to equate job_history_id in both tables to a single attribute, as in http://www.sqlalchemy.org/docs/05/mappers.html#mapping-a-class-against-multiple-tables , which is accomplished with declarative in a similar way, i..e. id = [job_history.c.job_history_id, job_history_info.c.job_history_id. Is there, in general, a way to specify that a join is 1:1 so that the mapper property returns the entity on the other side of the join rather than a single element array? a map to a join is always 1:1 from the object perspective, but if there are multiple job_history_info rows for one job_history row, those would typically be expressed as different identities within the mapping. the primary key of your mapping defaults to [job_history.job_history_id, job_history_info.job_history_info_id]. Ok, I've spent quite a bit of time trying to get this do work. And I've completely failed! I've searched the Internet high-and-low and cannot find a *single* example of such a joined entity using the declarative syntax, anywhere. If someone is willing to make a working example of this [I'll write-up a detailed description] joining these two tables as one entity I'll (a) send you $50US via paypal, post the example back here, and to my BLOB (license: MIT/X11). -- OpenGroupware developer: awill...@whitemice.org http://whitemiceconsulting.blogspot.com/ OpenGroupare Cyrus IMAPd documenation @ http://docs.opengroupware.org/Members/whitemice/wmogag/file_view signature.asc Description: This is a digitally signed message part
Re: [sqlalchemy] any way to pre cook a monster query?
Wichert Akkerman wrote: I think you need something like this: from sqlalchemy import sql query = sql.select([X.a,Y.b,Z.c).join(X,Y,Z)], X.a==sql.bindparam(a)) I'm not sure this is valid, can anyone tell me what would be the alid form of the above? cheers, Chris -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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 narrow down the generated query in SQLAlchemy if it was created via query_property?
On Jan 26, 11:57 am, King Simon-NFHD78 simon.k...@motorola.com wrote: -Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Boda Cydo Sent: 26 January 2010 01:35 To: sqlalchemy Subject: [sqlalchemy] Re: Is it possible to narrow down the generated query in SQLAlchemy if it was created via query_property? On Jan 25, 2:46 am, Boda Cydo bodac...@gmail.com wrote: Let me know if the question is not clearly stated. I'll update it with more details. Any ideas? When you access Comment.query, you are getting back an already instantiated Query object which, as the error message indicates, isn't callable. I guess I don't really understand why you want to use Comment.query(Comment.comment) rather than Session.query(Comment.comment). If you really want this, you could subclass Query to add a __call__ method that creates a new query instance: class CallableQuery(Query): def __call__(self, *args, **kwargs): return Session.query(*args, **kwargs) class Comments(Base): query = Session.query_property(query_cls=CallableQuery) ...but I'm still not sure what the point is. Thanks for pointing out the idea of adding a __call__ method. I would not have thought of it myself if you hadn't mentioned it. Also thanks for suggesting to use Session.query() instead of Comments.query. Boda Cydo. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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: Error handling in SQLAlchemy
On Jan 26, 3:52 am, Boda Cydo bodac...@gmail.com wrote: Hello all! I have a question about handling errors in SQLAlchemy. Before I used SQLAlchemy I used to rigorously check for all errors when executing queries, like: status = db.query(INSERT INTO users ...) if !status: handle_insert_error() But now when I have switched to SQLAlchemy I write code with no error checking whatsoever, like: user = User(Boda Cydo) session.add(user) session.commit() And I do absolutely no error checking. I absolutely feel horrible for writing such code. I talked in #sqlalchemy and someone told me that SQLAlchemy throws exceptions on errors. That's better. But where is it documented? The person couldn't find anything. Neither could I. Can anyone please help with tips (or point me to documentation) about what exceptions get raised when? I absolutely don't want to write code without error handling. Thanks, Boda Cydo I asked it on Stackoverflow and got a great answer! Here it is: http://stackoverflow.com/questions/2136739/error-handling-in-sqlalchemy Boda Cydo -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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: any way to pre cook a monster query?
I'm using something similar to prefab queries on other objects as if
they were views
I need to show a list of orders (cotta) by dept/customer
(reparto,cliente) filtered by approval date (data_lancio)
in order to print some special labels
First I get only relevant info from the main orders table (data names
ending by _id are foreign keys to other tables)
selCot = sqa.select([
tabCot.c.id.label(cotta),
tabCot.c.impianti_id.label(reparto),
tabCot.c.anagrafiche_id.label(cliente),
tabCot.c.data_lancio.label(lancio),
],
).alias(etichette_per_cotta)
Then I build a list of customers including number of lots approved in
the date range
selCli = sqa.select([
selCot.c.reparto.label(reparto),
selCot.c.cliente.label(cliente),
sqa.func.count(selCot.c.cotta).label(num_cotte),
sqa.func.min(selCot.c.lancio).label(min_lancio),
sqa.func.max(selCot.c.lancio).label(max_lancio),
],
sqa.and_(
selCot.c.reparto==sqa.bindparam(reparto),
selCot.c.lancio.between(sqa.bindparam(da), sqa.bindparam
(a))
),
group_by=[selCot.c.reparto, selCot.c.cliente],
).alias(etichette_per_cliente)
Then I MAP the selCli query to some object
mapper(EtichetteCliente, selCli, primary_key=[selCli.c.reparto,
selCli.c.cliente,],[SNIP])
[SNIP] includes orm.relation(s) to connect customer description and
other data
Finally when I build the list I callect all parameters and query
objects like this:
customers = sorted(EtichetteCliente.query.params({reparto:
reparto.id, da: dataDa, a: dataA}).all())
Hope it helps
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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] Re: Two Table Entity In Declarative Style [$50!]
On Tue, 2010-01-26 at 07:36 -0500, Adam Tauno Williams wrote: On Tue, 2009-10-06 at 09:56 -0400, Michael Bayer wrote: Adam Tauno Williams wrote: But I have one 1:1 relation in my database that would be much easier to model as just one object. job_history job_history_info --- job_history_id (PK) -1:1- job_history_id object_version job_history_info_id (PK) job_id comment actor_iddb_status action action_date job_status db_status if you create Table objects for job_history and job_history_info, you can create a join via job_history.join(job_history_info), and then specify that to a declarative class using __table__ = myjoin instead of __tablename__. you will also want to equate job_history_id in both tables to a single attribute, as in http://www.sqlalchemy.org/docs/05/mappers.html#mapping-a-class-against-multiple-tables , which is accomplished with declarative in a similar way, i..e. id = [job_history.c.job_history_id, job_history_info.c.job_history_id. Is there, in general, a way to specify that a join is 1:1 so that the mapper property returns the entity on the other side of the join rather than a single element array? a map to a join is always 1:1 from the object perspective, but if there are multiple job_history_info rows for one job_history row, those would typically be expressed as different identities within the mapping. the primary key of your mapping defaults to [job_history.job_history_id, job_history_info.job_history_info_id]. Ok, I've spent quite a bit of time trying to get this do work. And I've completely failed! I've searched the Internet high-and-low and cannot find a *single* example of such a joined entity using the declarative syntax, anywhere. If someone is willing to make a working example of this [I'll write-up a detailed description] joining these two tables as one entity I'll (a) send you $50US via paypal, post the example back here, and to my BLOB (license: MIT/X11). Scope-of-work @ http://sourceforge.net/apps/trac/coils/ticket/2 -- OpenGroupware developer: awill...@whitemice.org http://whitemiceconsulting.blogspot.com/ OpenGroupare Cyrus IMAPd documenation @ http://docs.opengroupware.org/Members/whitemice/wmogag/file_view -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] any way to pre cook a monster query?
Chris Withers wrote:
Hi All,
I have a few monster queries like this:
query = session.query(
Blah1.name.label('blah1'),
Blah2.name.label('blah2'),
blah3.name.label('blah4'),
Blah5.name.label('blah5'),
Blah6.name.label('blah6'),
func.sum(case([(Blah7.blah8_blah9=='Blah8',Blah10.qty)],
else_=Blah10.qty*-1)).label('blah11'),
func.sum(case([(Blah7.blah8_blah9=='Blah8',Blah10.qty*blah13.price)],
else_=Blah10.qty*blah13.price*-1)).label('blah12')).\
join(Blah1.participents,
Participation.blah5,
Blah5.blah10s,
Blah10.fill,
blah13.blah7,
Blah7.blah6,
Blah7.blah12,
Blah12.blah7_idea,
Blah2.blah4).\
filter(and_(Blah1.id==self.id,
Participation.valid_from = on_date,
or_(Participation.valid_to on_date,
Participation.valid_to == None))).\
group_by(Blah6).\
order_by(Blah6.name)
...apologies for the obfuscation.
Now, is there any way I can pre-cook this (eg: at module-level) such
that I can later just plug in self.id and on_date, bind to a session and
call .all() on it?
It seems a bit wasteful to do all the SQL generation on every query when
it's almost all identical all the time...
easiest way, call your Query from a def.most of the work in SQL
generation isn't internally cached anyway so you aren't saving much by
having the same Query lying around.
second way, you can use bindparam() for the binds as others have
mentioned, and then params() to set the values as needed, but the missing
link is that you want the Query against your own particular session at the
moment. I haven't yet gotten the chance to add a with_session() method
to Query but you can do this easily enough yourself:
from sqlalchemy.orm.query import Query, _generative
class MyQuery(Query):
@_generative
def with_session(self, session):
self.session = session
Session = sessionmaker(query_cls=MyQuery)
so have your query lying around:
q = Session().query(...).filter(...)
then use it :
print q.with_session(my_session).params(foo='bar').all()
cheers,
Chris
--
Simplistix - Content Management, Batch Processing Python Consulting
- http://www.simplistix.co.uk
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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 sqlalch...@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] Two sessions - only 1 seeing committed data
Hi. I've been working on some unittests for a pylons application.
Part of that testing involves adding data to the database, submitting
a request through the paster testing framework and examining the
response.
I use two separate sessions for unittests. One is generated by the
pylons application itself and the other is created for the unittest.
Whenever I add data in the unittest session, it is not seen by the
pylons application. This simple script below demonstrates whats
happening during my tests. (Or at least what I think is happening).
Is there any way around this? The only alternative I see is using the
same session as the pylons application. But I've run into other
issues when I try to do that.
The output from the script below is -
0
10
0
# Begin python script
import sys
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.orm import sessionmaker
Base = declarative_base()
class SimpleTest(Base):
__tablename__ = 'simpletest'
id = Column(Integer, primary_key=True)
s1 = Column(String(255))
def main():
engine1 = create_engine('mysql://test:t...@localhost/test')
engine2 = create_engine('mysql://test:t...@localhost/test')
SimpleTest.metadata.create_all(engine1)
# Clear out the test table.
con = engine1.connect()
con.execute('TRUNCATE simpletest')
# Create two distinct sessions.
s1 = sessionmaker(bind=engine1)()
s2 = sessionmaker(bind=engine2)()
# Show empty table count on session 2.
print s2.execute('SELECT COUNT(*) FROM simpletest').scalar()
# Load the database with 10 rows.
for x in range(10):
st = SimpleTest()
st.s1 = str(x)
s1.add(st)
s1.commit()
print s1.execute('SELECT COUNT(*) FROM simpletest').scalar()
print s2.execute('SELECT COUNT(*) FROM simpletest').scalar()
if __name__ == '__main__':
sys.exit(main())
Thanks,
jw
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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] Two sessions - only 1 seeing committed data
programmer.py wrote:
Hi. I've been working on some unittests for a pylons application.
Part of that testing involves adding data to the database, submitting
a request through the paster testing framework and examining the
response.
I use two separate sessions for unittests. One is generated by the
pylons application itself and the other is created for the unittest.
Whenever I add data in the unittest session, it is not seen by the
pylons application. This simple script below demonstrates whats
happening during my tests. (Or at least what I think is happening).
Is there any way around this? The only alternative I see is using the
same session as the pylons application. But I've run into other
issues when I try to do that.
The output from the script below is -
0
10
0
when I run it the output is:
zzzeek-3:sqlalchemy classic$ python test.py
0
10
10
This is because by default the engine is MyISAM and there's no transaction
isolation between the two connections.
However, if I change the table to use InnoDB, then the transaction
isolation kicks in and we get the expected:
zzzeek-3:sqlalchemy classic$ python test.py
0
10
0
the second connection gets zero because the transaction is referencing
what's already been selected (i.e. no phantom reads). Rolling back s2
before reselecting again gives us:
zzzeek-3:sqlalchemy classic$ python test.py
0
10
10
# Begin python script
import sys
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.orm import sessionmaker
Base = declarative_base()
class SimpleTest(Base):
__tablename__ = 'simpletest'
id = Column(Integer, primary_key=True)
s1 = Column(String(255))
def main():
engine1 = create_engine('mysql://test:t...@localhost/test')
engine2 = create_engine('mysql://test:t...@localhost/test')
SimpleTest.metadata.create_all(engine1)
# Clear out the test table.
con = engine1.connect()
con.execute('TRUNCATE simpletest')
# Create two distinct sessions.
s1 = sessionmaker(bind=engine1)()
s2 = sessionmaker(bind=engine2)()
# Show empty table count on session 2.
print s2.execute('SELECT COUNT(*) FROM simpletest').scalar()
# Load the database with 10 rows.
for x in range(10):
st = SimpleTest()
st.s1 = str(x)
s1.add(st)
s1.commit()
print s1.execute('SELECT COUNT(*) FROM simpletest').scalar()
print s2.execute('SELECT COUNT(*) FROM simpletest').scalar()
if __name__ == '__main__':
sys.exit(main())
Thanks,
jw
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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 sqlalch...@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: Two sessions - only 1 seeing committed data
On Jan 26, 9:49 am, Michael Bayer mike...@zzzcomputing.com wrote:
programmer.py wrote:
Hi. I've been working on some unittests for a pylons application.
Part of that testing involves adding data to the database, submitting
a request through the paster testing framework and examining the
response.
I use two separate sessions for unittests. One is generated by the
pylons application itself and the other is created for the unittest.
Whenever I add data in the unittest session, it is not seen by the
pylons application. This simple script below demonstrates whats
happening during my tests. (Or at least what I think is happening).
Is there any way around this? The only alternative I see is using the
same session as the pylons application. But I've run into other
issues when I try to do that.
The output from the script below is -
0
10
0
when I run it the output is:
zzzeek-3:sqlalchemy classic$ python test.py
0
10
10
This is because by default the engine is MyISAM and there's no transaction
isolation between the two connections.
However, if I change the table to use InnoDB, then the transaction
isolation kicks in and we get the expected:
zzzeek-3:sqlalchemy classic$ python test.py
0
10
0
the second connection gets zero because the transaction is referencing
what's already been selected (i.e. no phantom reads). Rolling back s2
before reselecting again gives us:
zzzeek-3:sqlalchemy classic$ python test.py
0
10
10
So, the s2 query is `cached`? I'm still confused about this.
Whenever I issue a rollback() on s2, like you described, it works.
I'm just surprised, because I expected the query to always fetch fresh
results. Should I not be surprised?
Thanks for your help!
jw
# Begin python script
import sys
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.orm import sessionmaker
Base = declarative_base()
class SimpleTest(Base):
__tablename__ = 'simpletest'
id = Column(Integer, primary_key=True)
s1 = Column(String(255))
def main():
engine1 = create_engine('mysql://test:t...@localhost/test')
engine2 = create_engine('mysql://test:t...@localhost/test')
SimpleTest.metadata.create_all(engine1)
# Clear out the test table.
con = engine1.connect()
con.execute('TRUNCATE simpletest')
# Create two distinct sessions.
s1 = sessionmaker(bind=engine1)()
s2 = sessionmaker(bind=engine2)()
# Show empty table count on session 2.
print s2.execute('SELECT COUNT(*) FROM simpletest').scalar()
# Load the database with 10 rows.
for x in range(10):
st = SimpleTest()
st.s1 = str(x)
s1.add(st)
s1.commit()
print s1.execute('SELECT COUNT(*) FROM simpletest').scalar()
print s2.execute('SELECT COUNT(*) FROM simpletest').scalar()
if __name__ == '__main__':
sys.exit(main())
Thanks,
jw
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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 sqlalch...@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] Re: Two sessions - only 1 seeing committed data
programmer.py wrote:
So, the s2 query is `cached`? I'm still confused about this.
Whenever I issue a rollback() on s2, like you described, it works.
I'm just surprised, because I expected the query to always fetch fresh
results. Should I not be surprised?
here's some background:
http://en.wikipedia.org/wiki/Isolation_%28database_systems%29
and
http://dev.mysql.com/doc/refman/5.1/en/innodb-consistent-read.html
Thanks for your help!
jw
# Begin python script
import sys
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.orm import sessionmaker
Base = declarative_base()
class SimpleTest(Base):
__tablename__ = 'simpletest'
id = Column(Integer, primary_key=True)
s1 = Column(String(255))
def main():
engine1 = create_engine('mysql://test:t...@localhost/test')
engine2 = create_engine('mysql://test:t...@localhost/test')
SimpleTest.metadata.create_all(engine1)
# Clear out the test table.
con = engine1.connect()
con.execute('TRUNCATE simpletest')
# Create two distinct sessions.
s1 = sessionmaker(bind=engine1)()
s2 = sessionmaker(bind=engine2)()
# Show empty table count on session 2.
print s2.execute('SELECT COUNT(*) FROM simpletest').scalar()
# Load the database with 10 rows.
for x in range(10):
st = SimpleTest()
st.s1 = str(x)
s1.add(st)
s1.commit()
print s1.execute('SELECT COUNT(*) FROM simpletest').scalar()
print s2.execute('SELECT COUNT(*) FROM simpletest').scalar()
if __name__ == '__main__':
sys.exit(main())
Thanks,
jw
--
You received this message because you are subscribed to the Google
Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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 sqlalch...@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 sqlalch...@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] Map to Arbitrary Select Using Raw SQL
Hello, I'm new to SQLAlchemy (and really Python in general) and admittedly I'm probably not following the best process for learning it. Ultimately, I'd prefer to deal with raw SQL as opposed to working through the expression building methods despite the benefits of the framework I leave on the table. The down side, of course, is that the tutorials aren't written for this wanton approach. Presently, I'm trying to determine the best way to map a class against an arbitrary select where the select is constructed from raw SQL. Based on this, it's possible using the expression builders: http://www.sqlalchemy.org/docs/mappers.html#mapping-a-class-against-arbitrary-selects so I assume it's possible using SQL. I've researched the text() and Query from_statement() methods, but these don't appear to be applicable in this case. Is there another method to short-cut the mapping of a rowset (generated by raw SQL) to an object? Thanks! Mike -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] Map to Arbitrary Select Using Raw SQL
On Jan 26, 2010, at 7:48 PM, Michael Chambliss wrote: Hello, I'm new to SQLAlchemy (and really Python in general) and admittedly I'm probably not following the best process for learning it. Ultimately, I'd prefer to deal with raw SQL as opposed to working through the expression building methods despite the benefits of the framework I leave on the table. The down side, of course, is that the tutorials aren't written for this wanton approach. Presently, I'm trying to determine the best way to map a class against an arbitrary select where the select is constructed from raw SQL. Based on this, it's possible using the expression builders: http://www.sqlalchemy.org/docs/mappers.html#mapping-a-class-against-arbitrary-selects so I assume it's possible using SQL. I've researched the text() and Query from_statement() methods, but these don't appear to be applicable in this case. Is there another method to short-cut the mapping of a rowset (generated by raw SQL) to an object? from_statement() is the primary means of doing this, assuming you're mapped to some kind of construct already and just need to select the rows from some particular statement you happen to have as a string. This means, the configuration of your application would consist of mapping your classes to table metadata as per the documentation, and then at query time you can load and persist objects, using all hand-written SQL to load rows. But the literal request to map to an arbitrary select with raw SQL is strange, but this may be semantic - the word map in SQLA parlance means to construct a mapper(), which is a configuration-time, not a query-time, concern. Your mapper would be against the fixed SQL statement, and would be invoked when, for example, you said query.all(). However, that would be all you can do with it - SQLA doesn't parse SQL strings, so its impossible for it to, by itself, alter your string SQL statement to add filtering criterion, ordering, or do anything else with it. Your mapper also wouldn't be able to persist anything - since the requirement that you map to raw SQL means you don't want to tell it which individual tables are referenced in your select. But its all absolutely possible I think we just need more specifics as to what patterns you're looking to achieve. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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: SQL Server 2008 geography type
I am having some problems using this approach. How should I use the
class STAsText in the GisElement.wkt property (example below)?
My current attempts are not working... it seems that the correct
statement is generated SELECT :param_1.STAsText(), but the Geography
instance is not being passed as a parameter, i.e. the parameters list
is empty.
example script snippets
@compiles_as_bound
class STAsText(FunctionElement):
name = 'STAsText'
class GisElement(object):
Represents a geometry value.
@property
def wkt(self):
return STAsText(literal(self, Geography)).select()
#...
print session.scalar(r1.road_geom.wkt)
Error messages
2010-01-27 12:25:16,904 INFO sqlalchemy.engine.base.Engine.0x...4270
SELECT :param_1.STAsText()
2010-01-27 12:25:16,904 INFO sqlalchemy.engine.base.Engine.0x...4270
[]
Traceback (most recent call last):
File .\sql_server_spatial.py, line 514, in module
print session.scalar(r1.road_geom.as_wkt)
File c:\python25\lib\site-packages\SQLAlchemy-0.6beta1dev-py2.5.egg
\sqlalchemy\orm\session.py, line 742, in scalar
return self.execute(clause, params=params, mapper=mapper,
**kw).scalar()
File c:\python25\lib\site-packages\SQLAlchemy-0.6beta1dev-py2.5.egg
\sqlalchemy\orm\session.py, line 737, in execute
clause, params or {})
File c:\python25\lib\site-packages\SQLAlchemy-0.6beta1dev-py2.5.egg
\sqlalchemy\engine\base.py, line 1043, in execute
return Connection.executors[c](self, object, multiparams, params)
File c:\python25\lib\site-packages\SQLAlchemy-0.6beta1dev-py2.5.egg
\sqlalchemy\engine\base.py, line 1105, in _execute_clauseelement
return self.__execute_context(context)
File c:\python25\lib\site-packages\SQLAlchemy-0.6beta1dev-py2.5.egg
\sqlalchemy\engine\base.py, line 1128, in __execute_context
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File c:\python25\lib\site-packages\SQLAlchemy-0.6beta1dev-py2.5.egg
\sqlalchemy\engine\base.py, line 1190, in _cursor_execute
self._handle_dbapi_exception(e, statement, parameters, cursor,
context)
File c:\python25\lib\site-packages\SQLAlchemy-0.6beta1dev-py2.5.egg
\sqlalchemy\engine\base.py, line 1188, in _cursor_execute
self.dialect.do_execute(cursor, statement, parameters,
context=context)
File c:\python25\lib\site-packages\SQLAlchemy-0.6beta1dev-py2.5.egg
\sqlalchemy\engine\default.py, line 220, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (ProgrammingError) ('42000', [42000]
[Microsoft][SQL Native Client][SQL Server]Incorrect syntax near ':'.
(102) (SQLExecDirectW)) u'SELECT :param_1.STAsText()' []
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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] Re: SQL Server 2008 geography type
On Jan 26, 2010, at 11:46 PM, dan wrote:
I am having some problems using this approach. How should I use the
class STAsText in the GisElement.wkt property (example below)?
My current attempts are not working... it seems that the correct
statement is generated SELECT :param_1.STAsText(), but the Geography
instance is not being passed as a parameter, i.e. the parameters list
is empty.
example script snippets
@compiles_as_bound
class STAsText(FunctionElement):
name = 'STAsText'
class GisElement(object):
Represents a geometry value.
@property
def wkt(self):
return STAsText(literal(self, Geography)).select()
#...
print session.scalar(r1.road_geom.wkt)
oh. yeah part of the recipe I gave you has something like this: %s %
(someelement). call compiler.process(someelement) to get the correct bind
parameter representation.
Error messages
2010-01-27 12:25:16,904 INFO sqlalchemy.engine.base.Engine.0x...4270
SELECT :param_1.STAsText()
2010-01-27 12:25:16,904 INFO sqlalchemy.engine.base.Engine.0x...4270
[]
Traceback (most recent call last):
File .\sql_server_spatial.py, line 514, in module
print session.scalar(r1.road_geom.as_wkt)
File c:\python25\lib\site-packages\SQLAlchemy-0.6beta1dev-py2.5.egg
\sqlalchemy\orm\session.py, line 742, in scalar
return self.execute(clause, params=params, mapper=mapper,
**kw).scalar()
File c:\python25\lib\site-packages\SQLAlchemy-0.6beta1dev-py2.5.egg
\sqlalchemy\orm\session.py, line 737, in execute
clause, params or {})
File c:\python25\lib\site-packages\SQLAlchemy-0.6beta1dev-py2.5.egg
\sqlalchemy\engine\base.py, line 1043, in execute
return Connection.executors[c](self, object, multiparams, params)
File c:\python25\lib\site-packages\SQLAlchemy-0.6beta1dev-py2.5.egg
\sqlalchemy\engine\base.py, line 1105, in _execute_clauseelement
return self.__execute_context(context)
File c:\python25\lib\site-packages\SQLAlchemy-0.6beta1dev-py2.5.egg
\sqlalchemy\engine\base.py, line 1128, in __execute_context
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File c:\python25\lib\site-packages\SQLAlchemy-0.6beta1dev-py2.5.egg
\sqlalchemy\engine\base.py, line 1190, in _cursor_execute
self._handle_dbapi_exception(e, statement, parameters, cursor,
context)
File c:\python25\lib\site-packages\SQLAlchemy-0.6beta1dev-py2.5.egg
\sqlalchemy\engine\base.py, line 1188, in _cursor_execute
self.dialect.do_execute(cursor, statement, parameters,
context=context)
File c:\python25\lib\site-packages\SQLAlchemy-0.6beta1dev-py2.5.egg
\sqlalchemy\engine\default.py, line 220, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (ProgrammingError) ('42000', [42000]
[Microsoft][SQL Native Client][SQL Server]Incorrect syntax near ':'.
(102) (SQLExecDirectW)) u'SELECT :param_1.STAsText()' []
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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 sqlalch...@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] Map to Arbitrary Select Using Raw SQL
Michael Bayer wrote: On Jan 26, 2010, at 7:48 PM, Michael Chambliss wrote: Presently, I'm trying to determine the best way to map a class against an arbitrary select where the select is constructed from raw SQL. Based on this, it's possible using the expression builders: http://www.sqlalchemy.org/docs/mappers.html#mapping-a-class-against-arbitrary-selects from_statement() is the primary means of doing this, assuming you're mapped to some kind of construct already and just need to select the rows from some particular statement you happen to have as a string. This means, the configuration of your application would consist of mapping your classes to table metadata as per the documentation, and then at query time you can load and persist objects, using all hand-written SQL to load rows. Hey Michael - thanks for the patient and helpful response. I played around with the from_statement() approach earlier today, but what I was able to derive seemed to follow the standard model of define table, define class, map table to class, execute query. That approach would be great assuming I can map to some composite result (IE, multi-table/function). Perhaps I need to dive further into this to determine how joins are handled and how the mapping should be defined for them. The original example I linked seemed to imply some mapping magic in that the Customer class wasn't defined but was mapped to the complex Selectable. However, my research and attempts to do this mapping with from_statement() proved fruitless. In a theoretical example, say I have a CAR table that refers to both a CAR_TYPE table and CAR_ATTRIBUTES table. CAR_TYPE is simply an enumeration for a static list of types, and CAR_ATTRIBUTES is an arbitrarily long list of key,value attributes (color, weight, top speed, etc). So, ultimately, a Car is made up of these three. I'd want to bake all of these together, passing in a CAR.ID (primary key) to map to a Car instance. I prefer to live in SQL because I'm pretty good at it, and I need to reference, specifically, Oracle Spatial and Workspace functions. I do not, however, need to chain additional filters off of this, handle updates/inserts (at least at this point), etc. I'm literally just looking for a cheap way to map a row to an object and scoop up connection pooling, type handling, and other great things I'll probably learn about as I go. Thanks again for your help! Mike -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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: SQL Server 2008 geography type
That fixed it :) and thank you Michael for your quick response to my questions. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
