[sqlalchemy] incoherent behavior between oracle and postgres on engine.rowcount
Hi all, I wonder why there's such difference between oracle and pg: oracle: (Pdb) engine.connect().execute(sql).fetchone() select * from ruolo_permesso where cod_ruolo = 'SYSADMIN' and cod_permesso='TIPO_FIGURA' and inserimento='1' None (1273, 'SYSADMIN', 'TIPO_FIGURA', 1, 1, 1, 1) (Pdb) engine.connect().execute(sql).rowcount select * from ruolo_permesso where cod_ruolo = 'SYSADMIN' and cod_permesso='TIPO_FIGURA' and inserimento='1' None 0 --- zero??!! === postgres: (Pdb) engine.connect().execute(sql).fetchone() select * from ruolo_permesso where cod_ruolo = 'SYSADMIN' and cod_permesso='TIPO_FIGURA' and inserimento='1' None (1533, 'SYSADMIN', 'TIPO_FIGURA', True, True, True, True) (Pdb) engine.connect().execute(sql).rowcount select * from ruolo_permesso where cod_ruolo = 'SYSADMIN' and cod_permesso='TIPO_FIGURA' and inserimento='1' None 1 - j --~--~-~--~~~---~--~~ 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: Full connection pool close
On 23 Gen, 23:43, Rick Morrison rickmorri...@gmail.com wrote: From your earlier post: a_session.close() sa_Session.close_all() sa_engine.dispose() del sa_engine but it does not close the connection! Here's Engine.dispose (line 1152, engine/base.py) def dispose(self): self.pool.dispose() self.pool = self.pool.recreate() ..and here's QueuePool.dispose (the default pool, line 646, pool.py) def dispose(self): while True: try: conn = self._pool.get(False) conn.close() except Queue.Empty: break self._overflow = 0 - self.size() if self._should_log_info: self.log(Pool disposed. + self.status()) So the normal path would be to indeed close the connection (but not necessarily to delete the connection itself, it just falls out of scope). Can you trace into the dispose() call and verify that these are being run? Hey, seems that you've got the problem. conn = self._pool.get( False ) is the problem It raises an Empty error...: File E:\test.py, line 175, in module Data.sa_engine.dispose() File c:\Python25\Lib\site-packages\sqlalchemy-0.4.8-py2.5.egg \sqlalchemy\engine\base.py, line 1133, in dispose self.pool.dispose() File C:\Python25\Lib\site-packages\sqlalchemy-0.4.8-py2.5.egg \sqlalchemy\pool.py, line 626, in dispose conn = self._pool.get(False) File c:\Python25\Lib\site-packages\sqlalchemy-0.4.8-py2.5.egg \sqlalchemy\queue.py, line 140, in get raise Empty --~--~-~--~~~---~--~~ 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: incoherent behavior between oracle and postgres on engine.rowcount
rowcount is pulled directly from the DBAPI cursor and is usually only reliable for an UPDATE or DELETE statement.Feel free to consult on the cx_oracle mailing list for why it might return 0 for a one-row SELECT statement - my guess would be that no rows were fetched from the server. On Jan 24, 2009, at 3:10 AM, Jose Soares wrote: Hi all, I wonder why there's such difference between oracle and pg: oracle: (Pdb) engine.connect().execute(sql).fetchone() select * from ruolo_permesso where cod_ruolo = 'SYSADMIN' and cod_permesso='TIPO_FIGURA' and inserimento='1' None (1273, 'SYSADMIN', 'TIPO_FIGURA', 1, 1, 1, 1) (Pdb) engine.connect().execute(sql).rowcount select * from ruolo_permesso where cod_ruolo = 'SYSADMIN' and cod_permesso='TIPO_FIGURA' and inserimento='1' None 0 --- zero??!! = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = == postgres: (Pdb) engine.connect().execute(sql).fetchone() select * from ruolo_permesso where cod_ruolo = 'SYSADMIN' and cod_permesso='TIPO_FIGURA' and inserimento='1' None (1533, 'SYSADMIN', 'TIPO_FIGURA', True, True, True, True) (Pdb) engine.connect().execute(sql).rowcount select * from ruolo_permesso where cod_ruolo = 'SYSADMIN' and cod_permesso='TIPO_FIGURA' and inserimento='1' None 1 - j --~--~-~--~~~---~--~~ 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: How to improve performance of sqlalchemy based application?
I drastically sped up my inserts by precomputing any defaults on a column and passing them explicitly instead of calculating them on each insert. For example, each row had a timestamp and the timestamp was being calculated on each insert for each row. Since I was inserting them all at the same time I created the timestamp once and passed the value for each row. Easily halved the time it took to insert 20,000 rows. --~--~-~--~~~---~--~~ 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: How to improve performance of sqlalchemy based application?
See it here on lines 323-352: http://bazaar.launchpad.net/~bauble/bauble/trunk/annotate/head%3A/bauble/plugins/imex/csv_.py --~--~-~--~~~---~--~~ 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] SQLAlchemy 0.5.2 released
This release fixes a few bugs, some preexisting, some introduced in 0.5.0, and one introduced in 0.5.1.Upgrading is recommended if you were affected by any of the bugs mentioned below. There is also a refinement to the delete-orphan on many-to-many/many- to-one policy, which will make itself apparent via warnings only in this release. The warnings can be resolved using new configuration options on relation() which establish the parent side of the relationship as single parent. Also, PDF output with sphinx is busted at the moment, the download link on the site has been repaired but the file you'll get is the 0.5.0 file. Download 0.5.2 at: http://www.sqlalchemy.org/download.html 0.5.2 == - orm - Further refined 0.5.1's warning about delete-orphan cascade placed on a many-to-many relation. First, the bad news: the warning will apply to both many-to-many as well as many-to-one relations. This is necessary since in both cases, SQLA does not scan the full set of potential parents when determining orphan status - for a persistent object it only detects an in-python de-association event to establish the object as an orphan. Next, the good news: to support one-to-one via a foreign key or assocation table, or to support one-to-many via an association table, a new flag single_parent=True may be set which indicates objects linked to the relation are only meant to have a single parent. The relation will raise an error if multiple parent-association events occur within Python. - Adjusted the attribute instrumentation change from 0.5.1 to fully establish instrumentation for subclasses where the mapper was created after the superclass had already been fully instrumented. [ticket:1292] - Fixed bug in delete-orphan cascade whereby two one-to-one relations from two different parent classes to the same target class would prematurely expunge the instance. - Fixed an eager loading bug whereby self-referential eager loading would prevent other eager loads, self referential or not, from joining to the parent JOIN properly. Thanks to Alex K for creating a great test case. - session.expire() and related methods will not expire() unloaded deferred attributes. This prevents them from being needlessly loaded when the instance is refreshed. - query.join()/outerjoin() will now properly join an aliased() construct to the existing left side, even if query.from_self() or query.select_from(someselectable) has been called. [ticket:1293] - sql - Further fixes to the percent signs and spaces in column/table names functionality. [ticket:1284] - mssql - Restored convert_unicode handling. Results were being passed on through without conversion. [ticket:1291] - Really fixing the decimal handling this time. [ticket:1282]. - Modified table reflection code to use only kwargs when constructing tables. [Ticket:1289] --~--~-~--~~~---~--~~ 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: Full connection pool close
Hey, seems that you've got the problem. conn = self._pool.get( False ) is the problem It raises an Empty error...: It's supposed to; that's the exit condition for the while True loop. It does make it at least once through the loop, though right? Enough to close any connections you may have open? --~--~-~--~~~---~--~~ 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] unions and order_by
The script below is giving me the following error:
sqlalchemy.exc.OperationalError: (OperationalError) ORDER BY clause
should come after UNION not before u'SELECT anything.id, anything.any,
anything.something_id \nFROM anything JOIN something ON something.id =
anything.something_id ORDER BY any UNION SELECT anything.id,
anything.any, anything.something_id \nFROM anything JOIN something ON
something.id = anything.something_id ORDER BY any' []
This works with 0.5.0rc4 but started showing up with 0.5.0.
The SQL generated with 0.5.0rc4:
SELECT anything.id, anything.any, anything.something_id
FROM anything JOIN something ON something.id = anything.something_id
UNION SELECT anything.id, anything.any, anything.something_id
FROM anything JOIN something ON something.id = anything.something_id
The SQL generated with 0.5.2:
SELECT anything.id, anything.any, anything.something_id
FROM anything JOIN something ON something.id = anything.something_id
ORDER BY any UNION SELECT anything.id, anything.any,
anything.something_id
FROM anything JOIN something ON something.id = anything.something_id
ORDER BY any
Is this a bug or do I have something setup wrong?
-
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Something(Base):
__tablename__ = 'something'
__mapper_args__ = {'order_by': 'some'}
id = Column(Integer, primary_key=True)
some = Column(String)
class Anything(Base):
__tablename__ = 'anything'
__mapper_args__ = {'order_by': 'any'}
id = Column(Integer, primary_key=True)
any = Column(String)
something_id = Column(Integer, ForeignKey('something.id'))
somethings = relation(Something)
uri = 'sqlite:///:memory:'
engine = create_engine(uri)
engine.connect()
metadata = Base.metadata
metadata.bind = engine
metadata.create_all()
Session = sessionmaker(bind=engine)
session = Session()
q1 = session.query(Anything).join('somethings')
q2 = session.query(Anything).join('somethings')
u = union(q1.statement, q2.statement)
print list(session.query(Anything).from_statement(u))
-
--~--~-~--~~~---~--~~
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: Full connection pool close
On 24 Gen, 21:27, Rick Morrison rickmorri...@gmail.com wrote: Hey, seems that you've got the problem. conn = self._pool.get( False ) is the problem It raises an Empty error...: It's supposed to; that's the exit condition for the while True loop. It does make it at least once through the loop, though right? Enough to close any connections you may have open? Oh... i didn't explain myself... I mean that it's already empty at the first cycle of the loop... --~--~-~--~~~---~--~~ 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: Full connection pool close
Oh... i didn't explain myself... I mean that it's already empty at the first cycle of the loop... It would be normal to not enter the loop if you haven't yet opened any connections, as connections are opened on demand. Make sure your program issues at least one query during this test. If you are already issuing queries, then bundle up this as a simple test case as you can make, and we'll have a look at it. --~--~-~--~~~---~--~~ 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: unions and order_by
you shouldnt be using order_by on your mapper(). thats a really old
option in any case.if you need it to be there, say
query.order_by(None).statement to cancel the order_by in each separate
part of the union. however it would be even easier if you just said
query.union(q1, q2) here (still might need the order_by(None) though).
On Jan 24, 2009, at 3:56 PM, Brett wrote:
The script below is giving me the following error:
sqlalchemy.exc.OperationalError: (OperationalError) ORDER BY clause
should come after UNION not before u'SELECT anything.id, anything.any,
anything.something_id \nFROM anything JOIN something ON something.id =
anything.something_id ORDER BY any UNION SELECT anything.id,
anything.any, anything.something_id \nFROM anything JOIN something ON
something.id = anything.something_id ORDER BY any' []
This works with 0.5.0rc4 but started showing up with 0.5.0.
The SQL generated with 0.5.0rc4:
SELECT anything.id, anything.any, anything.something_id
FROM anything JOIN something ON something.id = anything.something_id
UNION SELECT anything.id, anything.any, anything.something_id
FROM anything JOIN something ON something.id = anything.something_id
The SQL generated with 0.5.2:
SELECT anything.id, anything.any, anything.something_id
FROM anything JOIN something ON something.id = anything.something_id
ORDER BY any UNION SELECT anything.id, anything.any,
anything.something_id
FROM anything JOIN something ON something.id = anything.something_id
ORDER BY any
Is this a bug or do I have something setup wrong?
-
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Something(Base):
__tablename__ = 'something'
__mapper_args__ = {'order_by': 'some'}
id = Column(Integer, primary_key=True)
some = Column(String)
class Anything(Base):
__tablename__ = 'anything'
__mapper_args__ = {'order_by': 'any'}
id = Column(Integer, primary_key=True)
any = Column(String)
something_id = Column(Integer, ForeignKey('something.id'))
somethings = relation(Something)
uri = 'sqlite:///:memory:'
engine = create_engine(uri)
engine.connect()
metadata = Base.metadata
metadata.bind = engine
metadata.create_all()
Session = sessionmaker(bind=engine)
session = Session()
q1 = session.query(Anything).join('somethings')
q2 = session.query(Anything).join('somethings')
u = union(q1.statement, q2.statement)
print list(session.query(Anything).from_statement(u))
-
--~--~-~--~~~---~--~~
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: unions and order_by
youre going to want to set order_by like this too instead of the
string 'any'
class Anything(Base):
__tablename__ = 'anything'
id = Column(Integer, primary_key=True)
any = Column(String)
something_id = Column(Integer, ForeignKey('something.id'))
somethings = relation(Something)
__mapper_args__ = {'order_by': any}
On Jan 24, 2009, at 3:56 PM, Brett wrote:
The script below is giving me the following error:
sqlalchemy.exc.OperationalError: (OperationalError) ORDER BY clause
should come after UNION not before u'SELECT anything.id, anything.any,
anything.something_id \nFROM anything JOIN something ON something.id =
anything.something_id ORDER BY any UNION SELECT anything.id,
anything.any, anything.something_id \nFROM anything JOIN something ON
something.id = anything.something_id ORDER BY any' []
This works with 0.5.0rc4 but started showing up with 0.5.0.
The SQL generated with 0.5.0rc4:
SELECT anything.id, anything.any, anything.something_id
FROM anything JOIN something ON something.id = anything.something_id
UNION SELECT anything.id, anything.any, anything.something_id
FROM anything JOIN something ON something.id = anything.something_id
The SQL generated with 0.5.2:
SELECT anything.id, anything.any, anything.something_id
FROM anything JOIN something ON something.id = anything.something_id
ORDER BY any UNION SELECT anything.id, anything.any,
anything.something_id
FROM anything JOIN something ON something.id = anything.something_id
ORDER BY any
Is this a bug or do I have something setup wrong?
-
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Something(Base):
__tablename__ = 'something'
__mapper_args__ = {'order_by': 'some'}
id = Column(Integer, primary_key=True)
some = Column(String)
class Anything(Base):
__tablename__ = 'anything'
__mapper_args__ = {'order_by': 'any'}
id = Column(Integer, primary_key=True)
any = Column(String)
something_id = Column(Integer, ForeignKey('something.id'))
somethings = relation(Something)
uri = 'sqlite:///:memory:'
engine = create_engine(uri)
engine.connect()
metadata = Base.metadata
metadata.bind = engine
metadata.create_all()
Session = sessionmaker(bind=engine)
session = Session()
q1 = session.query(Anything).join('somethings')
q2 = session.query(Anything).join('somethings')
u = union(q1.statement, q2.statement)
print list(session.query(Anything).from_statement(u))
-
--~--~-~--~~~---~--~~
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] Equivalent of UPDATE ... WHERE ... in ORM?
Hi,
Is there a way to update a large number of objects without looping
through each one, using SA's ORM?
E.g.
I want to achieve the following:
for o in session.query(MyClass).filter_by(prop='some value'):
o.prop = 'new value'
session.update(o)
Without fetching and saving each object from the database. I.e.
something which would produce SQL like this:
UPDATE `my_class` SET `prop`='new value' WHERE `prop` = 'some value'
If not, am I safe mixing and matching ORM operations with SQL
operations like:
u = my_classes.update(my_class.c.prop=='some value'), values=
{'prop':'new value'})?
Thanks!
James
--~--~-~--~~~---~--~~
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: Equivalent of UPDATE ... WHERE ... in ORM?
the update() method on Query accomplishes this. Make sure you read
the docstring for it which describes some various behaviors you'll
want to be aware of.
alternatively, any SQL expression, like table.update(), UPDATE
table can be issued within the ORM's transaction using
session.execute().
On Jan 24, 2009, at 7:14 PM, James wrote:
Hi,
Is there a way to update a large number of objects without looping
through each one, using SA's ORM?
E.g.
I want to achieve the following:
for o in session.query(MyClass).filter_by(prop='some value'):
o.prop = 'new value'
session.update(o)
Without fetching and saving each object from the database. I.e.
something which would produce SQL like this:
UPDATE `my_class` SET `prop`='new value' WHERE `prop` = 'some value'
If not, am I safe mixing and matching ORM operations with SQL
operations like:
u = my_classes.update(my_class.c.prop=='some value'), values=
{'prop':'new value'})?
Thanks!
James
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
