[sqlalchemy] Re: intersect_all vs chaining of filter
Then what is the purpose of the intersection method? It looks to me as a (bad) alternative to chained filtering!! Can you think of any case when intersection is better choice than filters? On Jun 23, 4:08 am, Mike Conley mconl...@gmail.com wrote: On Tue, Jun 21, 2011 at 6:05 AM, Eduardo ruche...@googlemail.com wrote: What is the best practice: to chain filters or to collect queries in a list and then apply intersect_all()? Overall efficiency will depend on the underlying database engine, but I can't help but expect that most databases will be more efficient with the chained filters query. It would take a really smart optimizer to make the intersect method as efficient as the chained filter. Using an unrealistic set of queries, but it shows the principle. Using intersect_all will generate SQL like this: q1 = sess.query(Book).filter(Book.title=='A') q2 = sess.query(Book).filter(Book.title=='B') q3 = sess.query(Book).filter(Book.title=='C') q4 = sess.query(Book).filter(Book.title=='D') q5 = q1.intersect_all(q2,q3,q4) SELECT anon_1.book_bookid AS anon_1_book_bookid, anon_1.book_title AS anon_1_book_title, anon_1.book_authorid AS anon_1_book_authorid FROM (SELECT book.bookid AS book_bookid, book.title AS book_title, book.authorid AS book_authorid FROM book WHERE book.title = ? INTERSECT ALL SELECT book.bookid AS book_bookid, book.title AS book_title, book.authorid AS book_authorid FROM book WHERE book.title = ? INTERSECT ALL SELECT book.bookid AS book_bookid, book.title AS book_title, book.authorid AS book_authorid FROM book WHERE book.title = ? INTERSECT ALL SELECT book.bookid AS book_bookid, book.title AS book_title, book.authorid AS book_authorid FROM book WHERE book.title = ?) AS anon_1 Chaining filters generates this SQL: q7 = sess.query(Book).filter(Book.title=='A') q7 = q7.filter(Book.title=='B') q7 = q7.filter(Book.title=='C') q7 = q7.filter(Book.title=='D') SELECT book.bookid AS book_bookid, book.title AS book_title, book.authorid AS book_authorid FROM book WHERE book.title = ? AND book.title = ? AND book.title = ? AND book.title = ? -- Mike Conley -- 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: information about filed create_engine
When I use the same script with a standalone application it works but when I try to run it as a wsgi application it fails (wsgi logs does not contain any information regarding the failure!) On Jul 14, 10:16 am, King Simon-NFHD78 simon.k...@motorolasolutions.com wrote: Eduardo wrote On Jul 13, 7:11 pm, King Simon-NFHD78 simon.k...@motorolasolutions.com wrote: Eduardo wrote Hi, I am trying to prompt an answer from a database after failed create_engine command. I searched through the source code and I found TypeError, and ValueError returns but they relate (if I understood well only to the access parameters). My problem is that I am sure that my access parameters are correct but for some reason the creation of the engine fails. Is there any way to get information why the engin could not be created. The access to db log files is not granted! Thanks What kind of database are you trying to connect to? Are you getting a Python exception, and if so, can you show us the traceback? Simon !) PostgresSQL 2) I don't get any Python exception. So how do you know it's failing then? Simon -- 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] Re: information about filed create_engine
Eduardo wrote On Jul 13, 7:11 pm, King Simon-NFHD78 simon.k...@motorolasolutions.com wrote: Eduardo wrote Hi, I am trying to prompt an answer from a database after failed create_engine command. I searched through the source code and I found TypeError, and ValueError returns but they relate (if I understood well only to the access parameters). My problem is that I am sure that my access parameters are correct but for some reason the creation of the engine fails. Is there any way to get information why the engin could not be created. The access to db log files is not granted! Thanks What kind of database are you trying to connect to? Are you getting a Python exception, and if so, can you show us the traceback? Simon !) PostgresSQL 2) I don't get any Python exception. So how do you know it's failing then? Simon -- 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] Re: information about filed create_engine
Eduardo wrote When I use the same script with a standalone application it works but when I try to run it as a wsgi application it fails (wsgi logs does not contain any information regarding the failure!) Try turning on SQL logging (either by passing echo='debug') to create_engine, or by configuring the python logging package as described on http://www.sqlalchemy.org/docs/core/engines.html#configuring-logging. Then you should see the SQL being issued and the results coming back from the database. How are you configuring transactions? Is it possible that the transaction isn't being committed at the end of the web request, so any changes you've made are being discarded? Simon -- 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] Re: information about filed create_engine
Eduardo wrote When I use the same script with a standalone application it works but when I try to run it as a wsgi application it fails (wsgi logs does not contain any information regarding the failure!) Try turning on SQL logging (either by passing echo='debug') to create_engine, or by configuring the python logging package as described on http://www.sqlalchemy.org/docs/core/engines.html#configuring-logging. Then you should see the SQL being issued and the results coming back from the database. How are you configuring transactions? Is it possible that the transaction isn't being committed at the end of the web request, so any changes you've made are being discarded? Simon -- 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] SQL Server weird sorting behaviour
Hi everyone, I'm using sqlalchemy 0.6.8 to interact with an sql server database via pyodbc. I'm getting in troubles using the 'order by' clause on a varchar column which include positive or negative integer values. When I try to get values from this column ordered in ascending mode I get: 1 -1 11 -11 111 and so on...while I expect the resutl to be something like: -1 -11 1 11 111 or even better: -11 -1 1 11 111 Is there any way to achieve (or at least workaround) that with SQLalchemy? Thanks in advance! -- 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: information about filed create_engine
My application only queries the database there are no inputs and therefore no transactions involved. On Jul 14, 10:49 am, King Simon-NFHD78 simon.k...@motorolasolutions.com wrote: Eduardo wrote When I use the same script with a standalone application it works but when I try to run it as a wsgi application it fails (wsgi logs does not contain any information regarding the failure!) Try turning on SQL logging (either by passing echo='debug') to create_engine, or by configuring the python logging package as described onhttp://www.sqlalchemy.org/docs/core/engines.html#configuring-logging. Then you should see the SQL being issued and the results coming back from the database. How are you configuring transactions? Is it possible that the transaction isn't being committed at the end of the web request, so any changes you've made are being discarded? Simon -- 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] Re: information about filed create_engine
Eduardo wrote: On Jul 14, 10:49 am, King Simon-NFHD78 simon.k...@motorolasolutions.com wrote: Eduardo wrote When I use the same script with a standalone application it works but when I try to run it as a wsgi application it fails (wsgi logs does not contain any information regarding the failure!) Try turning on SQL logging (either by passing echo='debug') to create_engine, or by configuring the python logging package as described onhttp://www.sqlalchemy.org/docs/core/engines.html#configuring- logging. Then you should see the SQL being issued and the results coming back from the database. How are you configuring transactions? Is it possible that the transaction isn't being committed at the end of the web request, so any changes you've made are being discarded? Simon My application only queries the database there are no inputs and therefore no transactions involved. What was the result of turning on SQL logging? Are you sure you're even pointing at the same database that you were when you ran the standalone script? Try printing the value of session.bind.url (or including it in HTTP response, if you don't have easy access to the stdout from your wsgi script) Simon -- 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] Oracle Connection Oddities
Platform: Windows XP
Oracle : 10.2.0.4.0
SQLAlchemy: 0.7
Python: 2.7
Driver: cx_Oracle (compiled with unicode support as per the Windows
binary)
My problem is that when I try to do introspection, sqla refuses to
connect with this error:
cursor.execute(SELECT 0.1 FROM DUAL) TypeError: expecting None or a
string
If this string is unicode it works fine (I actually edited the
cx_oracle.py file and changed this to cursor.execute(uSELECT 0.1 FROM
DUAL)) and it worked.
Next problem is a simple case doesn't work, the exact line is:
foo =
Table('MERCHANT',oracle_meta,autoload=True,autoload_with=oracle_engine)
Error is:
NoSuchTableError: MERCHANT
However, the exact same connect string with raw cx_Oracle works great:
cur.execute(uSELECT MERCHANT_NUMBER FROM MERCHANT)
r = cur.fetchall()
len(r)
2922
Please point me in the right direction.
--
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] Alter a Sequence after creation...
Hi,
i try to alter Sequence after this Sequence is created. I guess the
sqlalchemy event system is the place where i should look.
I tried this one with no succes...
from sqlalchemy import event
from sqlalchemy import DDL
from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
seq = Sequence('some_table_seq', start=100, increment=1)
class SomeClass(Base):
__tablename__ = 'some_table'
id = Column(Integer, seq, primary_key=True)
zahl = Column(Integer)
DSN = 'oracle://novareto:retonova@10.30.4.80/BGETest'
engine = create_engine(DSN, echo=True)
Base.metadata.create_all(engine)
event.listen(SomeClass, after_create, DDL('ALTER SEQUENCE
some_table_seq NO_CACHE'))
But this does not work. What should i add instead of SomeClass?
Thanks in advance
Christian
--
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] Oracle Connection Oddities
Burhan wrote:
Platform: Windows XP
Oracle : 10.2.0.4.0
SQLAlchemy: 0.7
Python: 2.7
Driver: cx_Oracle (compiled with unicode support as per the Windows
binary)
What version is in use here? Is the unicode support as per the Windows
binary you refer to cx_oracle's UNICODE mode (im guessing so since that
version seems to include builds with the mode turned on)? this setting
has been discontinued with cx_oracle and is removed in version 5.1, and is
never required for Python 2.xx. SQLAlchemy supports this mode marginally
but the error you are getting would appear that this mode is in use (and
an edge that SQLA has missed).You should upgrade to cx_oracle 5.1, or
use the 5.0 build without the needless/extremely inconvenient UNICODE
flag, ensuring the special UNICODE mode is not in use - so that
cursor.execute() accepts strings as well as Python unicode objects
equally.
Next problem is a simple case doesn't work, the exact line is:
foo =
Table('MERCHANT',oracle_meta,autoload=True,autoload_with=oracle_engine)
Error is:
NoSuchTableError: MERCHANT
SQLAlchemy will interpret 'MERCHANT' as:
'MERCHANT'
i.e., with quotes, case sensitive. Case insensitive names with SQLAlchemy
are indicated using all lowercase names, i.e. 'merchant' - else quoting is
used.The Oracle SQL statement you illustrate is using case insensitive
names. Still, its not clear how it wouldn't locate the table at all as
'MERCHANT' should equate to 'MERCHANT' (haven't tried lately though),
turn on SQL echoing with echo='debug' (after changing 'MERCHANT' to
'merchant' in code) to ensure it's talking to the right database and such.
However, the exact same connect string with raw cx_Oracle works great:
cur.execute(uSELECT MERCHANT_NUMBER FROM MERCHANT)
r = cur.fetchall()
len(r)
2922
Please point me in the right direction.
--
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] Oracle Connection Oddities
Thanks Michael for the reply. I am not sure what version of cx_Oracle it is - it was downloaded as a Windows binary - the latest version is 5.1 on the cx_Oracle download page. I did manage to solve the other problem though, but I don't know why I needed a fix. In the vanilla cx_Oracle code, I used the exact same connect string (with the Oracle service name), and ran the query and it worked fine. In sqla I had to give it a namespace definition (which I figured out by accident by browsing the tables from a third party tool), and then the exact same MERCHANT worked. -- 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/-/F_2vi1VxE28J. 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] Oracle Connection Oddities
On Thu, Jul 14, 2011 at 12:53 PM, Burhan burhan.kha...@gmail.com wrote: I am not sure what version of cx_Oracle it is - it was downloaded as a Windows binary - the latest version is 5.1 on the cx_Oracle download page. import cx_Oracle print cx_Oracle.version -- 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: setting collection backref during merge()
I was considering the one side of a many to one, where setting the
backref would be trivial during merge since we already have the object
and know what it is. However, I see how that would be
inconsistent ... why does the orm set this only in one direction?
would be the question.
Anyway, 95% of the time it doesn't matter because, as you point out,
the object is in the identity map. The problem I ran into is one
where the primary join is goofy and therefore get() could not be
utilized for grabbing the backref... instead it had to refetch it from
the database (and it seemed silly to me since it knew the backref at
merge() time). (Plus, it was worse in my case because there are a few
points where I need to transiently turn off autoflush, and this was
during one of them, so it was losing data changes I think when it
looked up the backref object.)
I worked around this programmatically, but can you recommend a hook or
event where I could place some code to do this for certain cases
(specifically many to one or one to one)?
As of 0.6.4 there is no API hook for after merge, have you ever
considered such or were you possibly even planning such?
Thanks again,
Kent
On Jul 6, 5:07 pm, Michael Bayer mike...@zzzcomputing.com wrote:
A persistent object doesn't populate an unloadedbackrefon a forward set
event. This is for efficiency so that when you do something like:
for b in Session.query(B):
b.a = some_a
it doesn't spend time loading the bscollectionof some_a, which if you had
a lot of different some_a would take a lot of time. The other direction:
for a in Session.query(A):
a.bs.append(some_b)
if you were to access some_b.a, the lookup is from the identity map since
some_b is present. There is a step that ensures that the change from
thebackrefis present in the history of the other side, but this is done in
such a way as to not force acollectionor reference load.
I frequently forget the details of behaviors like these since 90% of them
have been nailed down years ago, so if you try the following test case,
you'll see no SQL is emitted after 2..
Also I have to run out so I may be forgetting some other details about this,
I'll try to take a second look later.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
bs = relationship(B,backref=a)
class B(Base):
__tablename__ = 'b'
id = Column(Integer, primary_key=True)
a_id = Column(Integer, ForeignKey('a.id'))
e = create_engine('sqlite://', echo=True)
Base.metadata.create_all(e)
s = Session(e)
s.add(A(id=1, bs=[B(id=1), B(id=2)]))
s.commit()
s.close()
a = A(id=1, bs=[B(id=1), B(id=2)])
print 1. -
a2 = s.merge(a)
print 2. -
for b in a2.bs:
assert b.a is a2
On Jul 6, 2011, at 4:24 PM, Kent wrote:
If I merge() an object with acollectionproperty, thebackref'sare
not set as they would be if I had assigned thecollectionto the
object.
I expected that this should occur. Is there rationale for notsetting
backref'sor would it be possible to make this change?
Thanks,
Kent
--
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
athttp://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] Re: setting collection backref during merge()
Kent wrote:
I was considering the one side of a many to one, where setting the
backref would be trivial during merge since we already have the object
and know what it is. However, I see how that would be
inconsistent ... why does the orm set this only in one direction?
would be the question.
Anyway, 95% of the time it doesn't matter because, as you point out,
the object is in the identity map. The problem I ran into is one
where the primary join is goofy and therefore get() could not be
utilized for grabbing the backref... instead it had to refetch it from
the database (and it seemed silly to me since it knew the backref at
merge() time). (Plus, it was worse in my case because there are a few
points where I need to transiently turn off autoflush, and this was
during one of them, so it was losing data changes I think when it
looked up the backref object.)
specifically the reason the backrefs don't fire in this example, when the
collection is sent during merge() to the parent, the fact that the object
as pulled from the DB already has this identical collection of identities,
there is no net change and events are not fired, this is how
collections.bulk_replace() currently works.
The merge() process has checks for recursive calls to prevent endless
traversals around cycles, by placing states already seen in a set.
There is an extra usage of this set by relationship() that places a state
+ property key in the seen list as a performance enhancement, such that
backrefs won't be forced to load unnecessarily during a merge(). There
currently aren't tests to evaluate the expense saved by this call. If
the recursive check is taken out, then the test
below traverses A.bs as well as each B.a and the backrefs are populated.
Perhaps if the call does save on SQL calls, if it could be made more
intelligent such that already-loaded collections are reused.I will add
a ticket to evaluate, but I am way behind on tickets so its not likely
anything will happen on this soon.
http://www.sqlalchemy.org/trac/ticket/2221
The event that most closely matches what you'd want here would be the
load() event, i.e. the event that fires when an object is loaded from
the DB and initial attribute population has proceeded. There's no plan
for an internal merge of individual instance right now - it would need
to be driven by use cases (workarounds of behavior like this aren't great
use cases for an API). An event around merge() in the aggregate isn't
an internal process so I tend to not add events around those (since you're
the one who calls merge()).
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
bs = relationship(B,backref=a)
class B(Base):
__tablename__ = 'b'
id = Column(Integer, primary_key=True)
a_id = Column(Integer, ForeignKey('a.id'))
e = create_engine('sqlite://', echo=True)
Base.metadata.create_all(e)
s = Session(e)
a = A(id=1, bs=[B(id=1), B(id=2)])
s.add(a)
s.commit()
s = Session(e)
a = A(id=1, bs=[B(id=1), B(id=2)])
a2 = s.merge(a)
# comment out lines 737-741 of orm/properties.py
# to have these pass
assert 'a' in a2.__dict__['bs'][0].__dict__
assert 'a' in a2.__dict__['bs'][1].__dict__
--
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] Re: intersect_all vs chaining of filter
a moment of googling, INTERSECT is when you'd like to find the exact intersection of rows, including NULLs being compared: http://sqltips.wordpress.com/2007/08/15/difference-between-inner-join-and-intersect/ INTERSECT is an uncommon operator. Eduardo wrote: Then what is the purpose of the intersection method? It looks to me as a (bad) alternative to chained filtering!! Can you think of any case when intersection is better choice than filters? On Jun 23, 4:08 am, Mike Conley mconl...@gmail.com wrote: On Tue, Jun 21, 2011 at 6:05 AM, Eduardo ruche...@googlemail.com wrote: What is the best practice: to chain filters or to collect queries in a list and then apply intersect_all()? Overall efficiency will depend on the underlying database engine, but I can't help but expect that most databases will be more efficient with the chained filters query. It would take a really smart optimizer to make the intersect method as efficient as the chained filter. Using an unrealistic set of queries, but it shows the principle. Using intersect_all will generate SQL like this: q1 = sess.query(Book).filter(Book.title=='A') q2 = sess.query(Book).filter(Book.title=='B') q3 = sess.query(Book).filter(Book.title=='C') q4 = sess.query(Book).filter(Book.title=='D') q5 = q1.intersect_all(q2,q3,q4) SELECT anon_1.book_bookid AS anon_1_book_bookid, anon_1.book_title AS anon_1_book_title, anon_1.book_authorid AS anon_1_book_authorid FROM (SELECT book.bookid AS book_bookid, book.title AS book_title, book.authorid AS book_authorid FROM book WHERE book.title = ? INTERSECT ALL SELECT book.bookid AS book_bookid, book.title AS book_title, book.authorid AS book_authorid FROM book WHERE book.title = ? INTERSECT ALL SELECT book.bookid AS book_bookid, book.title AS book_title, book.authorid AS book_authorid FROM book WHERE book.title = ? INTERSECT ALL SELECT book.bookid AS book_bookid, book.title AS book_title, book.authorid AS book_authorid FROM book WHERE book.title = ?) AS anon_1 Chaining filters generates this SQL: q7 = sess.query(Book).filter(Book.title=='A') q7 = q7.filter(Book.title=='B') q7 = q7.filter(Book.title=='C') q7 = q7.filter(Book.title=='D') SELECT book.bookid AS book_bookid, book.title AS book_title, book.authorid AS book_authorid FROM book WHERE book.title = ? AND book.title = ? AND book.title = ? AND book.title = ? -- Mike Conley -- 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] Alter a Sequence after creation...
Christian Klinger wrote:
Hi,
i try to alter Sequence after this Sequence is created. I guess the
sqlalchemy event system is the place where i should look.
I tried this one with no succes...
from sqlalchemy import event
from sqlalchemy import DDL
from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
seq = Sequence('some_table_seq', start=100, increment=1)
class SomeClass(Base):
__tablename__ = 'some_table'
id = Column(Integer, seq, primary_key=True)
zahl = Column(Integer)
DSN = 'oracle://novareto:retonova@10.30.4.80/BGETest'
engine = create_engine(DSN, echo=True)
Base.metadata.create_all(engine)
event.listen(SomeClass, after_create, DDL('ALTER SEQUENCE
some_table_seq NO_CACHE'))
SomeClass here is a mapped class, not a Table object. You're probably
looking for SomeClass.__table__.
But this does not work. What should i add instead of SomeClass?
Thanks in advance
Christian
--
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.
[sqlalchemy] Re: PostgreSQL 9.1 on the horizon, cool new stuff
Not that anyone actually needed it, but it was fun to filter and summarize. (caffeine sink) On Jul 11, 11:41 pm, Warwick Prince warwi...@mushroomsys.com wrote: Thanks for the 'heads-up' Eric :-) ! Nothing to see here, move right along ! Except... Couple of interesting additions coming up in PostgreSQL 9.1 (still in beta) for anyone who's interested. Release notes: http://developer.postgresql.org/pgdocs/postgres/release-9-1.html A couple of selected items I found of interest: * New support for CREATE UNLOGGED TABLE -- a new type of table that is sort of in between temporary tables and ordinary tables. They are not crash-safe as they are not written to the write-ahead log and are not replicated if you have replication set up, but the tradeoff is they can be written to a lot faster. Could use these to speed up testing, or in other non-production scenarios where crashproofness is not a concern. * New support for Synchronous replication -- primary master waits for a standby to write the transaction information to disk before acknowledging the commit. This behavior can be enabled or disabled on a per-transaction basis. Also a number of new settings related to keeping a 'hot standby'. * They added a true serializable transaction isolation level. Previously, asking for serializable isolation guaranteed only that a single MVCC snapshot would be used for the entire transaction, which allowed certain documented anomalies. The old snapshot isolation behavior will now be accessible by using the repeatable read isolation level. --This one might be particularly interesting for SQLAlchemy-- * INSERT, UPDATE, and DELETE will now be allowed in WITH clauses; these commands can use RETURNING to pass data up to the containing query. While not strictly necesary, this can improve the clarity of SQL emitted by eliminating some nested sub-SELECTs. There is other cool stuff you can accomplish with this such as deleting rows from one table according to a WHERE clause inside of a WITH...RETURNING, and inserting the same rows into another table in the same statement. The recursive abilities of WITH statements can also be used now to perform useful maneuvers like recursive DELETEs in tree structures (as long as the data-modifying part is outside of the WITH clause). * New support for per-column collation settings (yawn... but someone somewhere needs this while migrating or something) * New support for foreign tables -- allowing data stored outside the database to be used like native postgreSQL-stored data (read-only). * Enum types can now be added to programmatically (i don't know if they can be removed from) via ALTER TYPE * Added CREATE TABLE IF NOT EXISTS syntax -- seems like SA's DDL machinery might want to use that in the checkfirst=True case to eliminate the separate check operation? A minor matter, but nice. * Added transaction-level advisory locks (non-enforced, application- defined) similar to existing session-level advisory locks. * Lots more (a dump + restore will be required between 9.0 and 9.1) -- 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 athttp://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] Re: SQL Server weird sorting behaviour
Sounds like you might want to set a different collation? I don't know if sql server lets you do that per column, per table, or just per database. http://msdn.microsoft.com/en-us/library/ms144250.aspx --- some collation examples On Jul 14, 4:51 am, Massi massi_...@msn.com wrote: Hi everyone, I'm using sqlalchemy 0.6.8 to interact with an sql server database via pyodbc. I'm getting in troubles using the 'order by' clause on a varchar column which include positive or negative integer values. When I try to get values from this column ordered in ascending mode I get: 1 -1 11 -11 111 and so on...while I expect the resutl to be something like: -1 -11 1 11 111 or even better: -11 -1 1 11 111 Is there any way to achieve (or at least workaround) that with SQLalchemy? Thanks in advance! -- 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] Re: SQL Server weird sorting behaviour
or CAST the column as int more likely http://msdn.microsoft.com/en-us/library/aa226054%28v=sql.80%29.aspx SQLA's construct: http://www.sqlalchemy.org/docs/core/expression_api.html?highlight=cast#sqlalchemy.sql.expression.cast On Jul 14, 2011, at 7:19 PM, Eric Ongerth wrote: Sounds like you might want to set a different collation? I don't know if sql server lets you do that per column, per table, or just per database. http://msdn.microsoft.com/en-us/library/ms144250.aspx --- some collation examples On Jul 14, 4:51 am, Massi massi_...@msn.com wrote: Hi everyone, I'm using sqlalchemy 0.6.8 to interact with an sql server database via pyodbc. I'm getting in troubles using the 'order by' clause on a varchar column which include positive or negative integer values. When I try to get values from this column ordered in ascending mode I get: 1 -1 11 -11 111 and so on...while I expect the resutl to be something like: -1 -11 1 11 111 or even better: -11 -1 1 11 111 Is there any way to achieve (or at least workaround) that with SQLalchemy? Thanks in advance! -- 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.
