[sqlalchemy] The number of connection exceeded
Dear all, I am writing an application to scan a directory system and store metadata in DB. For each directory I create a separate process in which scanning and metadata feed is performed. Now I have following problems: 1) I am forced to start a session in each process and bind them for the engine engine = create_engine(dbfile, poolclass=NullPool) With other poolclass (including default) I get the error that number of connection are exceeded for the non super users. Is this common practice to handle this (I mean NullPool) or are there any way to get around this. How NullPool option affects the performance of the DB? 2) I create a loop in which various operation are performed (adding, deleting, updating of each instances): for elem in mydict: . . session.add(someinst) . . session.delete(inst2) . session.refresh(inst3) I am concerned about performance issues . Should I commit changes: after each operation (add, delete, refresh), after each loop or after the loop has run its course? Is there any advantage if I create a new session for each operation and then close it. Is it how the data get faster into the DB? Thank you 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] logging bug in 0.6.6?
I'm trying to diagnose an issue with temporary tables, so I cranked up the debug levels to DEBUG. I noticed something strange: 2011-01-27 09:34:12,818 DEBUG [sqlalchemy.pool.QueuePool.0x...e410] Connection connection object at 0x12e1d50; dsn: 'dbname=BLAH host=localhost user=BLAH password=xxx', closed: 0 checked out from pool 2011-01-27 09:34:12,819INFO [sqlalchemy.engine.base.Engine.0x...e510] BEGIN (implicit) NOTE: The connection object (0x12e1d50) doesn't match the log string of the subsequent statement (0x...e510). That identifier matches an /earlier/ connection that was checked out and returned to the pool. BUG? -- Jon -- 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] logging bug in 0.6.6?
On Jan 27, 2011, at 10:38 AM, Jon Nelson wrote: I'm trying to diagnose an issue with temporary tables, so I cranked up the debug levels to DEBUG. I noticed something strange: 2011-01-27 09:34:12,818 DEBUG [sqlalchemy.pool.QueuePool.0x...e410] Connection connection object at 0x12e1d50; dsn: 'dbname=BLAH host=localhost user=BLAH password=xxx', closed: 0 checked out from pool 2011-01-27 09:34:12,819INFO [sqlalchemy.engine.base.Engine.0x...e510] BEGIN (implicit) NOTE: The connection object (0x12e1d50) doesn't match the log string of the subsequent statement (0x...e510). That identifier matches an /earlier/ connection that was checked out and returned to the pool. BUG? The log string 0x...e510 refers to the identity of the Engine itself, not any of the DBAPI connections that happen to be stored in its connection pool. I've never had anyone confused by that before but FWIW the hex string from engine logging won't be present anymore in 0.7. -- 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] Pass query with as parameter (avoid creating a method and hardcoding a query)
2011/1/16 Tamás Bajusz gbt...@gmail.com: Is your work available, or do you plan to put it public somewhere? Mmm... maybe... contact me privately if you're interested -- 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] autocommit on for DDL
On Jan 26, 2011, at 7:47 PM, Michael Bayer wrote:
On Jan 26, 2011, at 6:32 PM, A.M. wrote:
Well, I spoke too soon :( What is the mistake in the following sample code
which causes the COMMITs to be emitted? Setting autocommit to either True or
False emits the same SQL. I think this is a case of staring at the same code
too long causing brain damage- thanks for your patience and help!
from sqlalchemy.engine import create_engine
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.orm import scoped_session
from sqlalchemy.schema import DDL,MetaData,Table
engine = create_engine('postgresql://localhost/test',echo=True)
session = scoped_session(sessionmaker(bind=engine))
metadata = MetaData()
metadata.bind = engine
Table('test1',metadata)
Table('test2',metadata)
metadata.create_all()
metadata.create_all() looks at the bind attribute, then uses it to execute
each DDL statement. The bind here is an engine so it uses connectionless
execution. connectionless execution is usually autocommit as documented
here:
http://www.sqlalchemy.org/docs/core/connections.html#connectionless-execution-implicit-execution
to emit create_all() in a transaction:
conn = engine.connect()
with conn.begin():
metadata.create_all(conn)
Ugh- thanks for being patient with a noob- I had erroneously assumed that the
creation of a session would assume responsibility for transaction management
like the zope transaction handler. It makes sense now that the session is
exclusively specific to ORM management- the section Joining a Session into an
External Transaction helped to clear things up- there is indeed some
interaction between connection and session transactions.
Cheers,
M
--
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] The number of connection exceeded
On Jan 27, 2011, at 5:11 AM, Eduardo wrote: Dear all, I am writing an application to scan a directory system and store metadata in DB. For each directory I create a separate process in which scanning and metadata feed is performed. Now I have following problems: 1) I am forced to start a session in each process and bind them for the engine engine = create_engine(dbfile, poolclass=NullPool) With other poolclass (including default) I get the error that number of connection are exceeded for the non super users. Is this common practice to handle this (I mean NullPool) or are there any way to get around this. How NullPool option affects the performance of the DB? there is no difference between NullPool and QueuePool regarding number of connections used, except that QueuePool can be configured to put a hard limit on how many are in use, and that if you are opening lots of connections with your pool, QueuePool will leave 5 of them hanging around by default whereas NullPool will not, so it sounds like you are opening too many connections in your child processes. Set pool_size=1 and max_overflow=0 with those child procs and that will ensure just one connection per subprocess. 2) I create a loop in which various operation are performed (adding, deleting, updating of each instances): for elem in mydict: . . session.add(someinst) . . session.delete(inst2) . session.refresh(inst3) I am concerned about performance issues . Should I commit changes: after each operation (add, delete, refresh), after each loop or after the loop has run its course? you should commit after the full operation is complete. If you'd like results from your operation to become available as it runs through a large number of records, you can use a scheme like committing every 1000 records or something like that. Consider turning off expire_on_commit as that will otherwise force everything in the session to reload after a commit, that is if you reference the same objects across multiple operations. Is there any advantage if I create a new session for each operation and then close it. Is it how the data get faster into the DB? closing the session means the next one has to load data all over again. you can only tell what work is being done and which of it may be unnecessary by watching your SQL logs as the operation proceeds. -- 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] Pass query with as parameter (avoid creating a method and hardcoding a query)
Sorry for late reply, but I was rather busy with real life work. I believe your code will be useful for me and for others too. Anyhow, thank you very much for it! On Thu, Jan 27, 2011 at 5:17 PM, Hector Blanco white.li...@gmail.com wrote: 2011/1/16 Tamás Bajusz gbt...@gmail.com: Is your work available, or do you plan to put it public somewhere? Mmm... maybe... contact me privately if you're interested -- 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] datetime interval to a number..
hi all, I've been trying to work out some SQLalchemy code to query two datetime columns and choose the one closest in time. I have some code that does this, but it requires using PostgreSQL and looks a little messy: query = query.order_by(ABS(EXTRACT(EPOCH FROM (header.utdatetime - :utdatetime_x.params(utdatetime_x= self.header.utdatetime) I've been working on a piece of code that is all SQLalchemy, but I keep getting an error that I can't use the absolute value function on an interval and casting the interval to Numeric isn't working either: query = query.order_by(func.abs(cast(Header.utdatetime - self.header.utdatetime, Numeric())).asc()) any help would be greatly appreciated. -- 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] Multi-get?
Yeah, that's what we do right now. On Wed, Jan 26, 2011 at 8:03 PM, Mike Conley mconl...@gmail.com wrote: On Wed, Jan 26, 2011 at 8:17 PM, Yang Zhang yanghates...@gmail.com wrote: Is there something similar to the .get() method in SqlSoup and Session but which allows me to fetch more than one object by ID, so as to save on round trips to the DB? (This could be done by composing using the IN operator in SQL.) Thanks in advance. Did you try something like session.query(MyClass).filter(MyClass.id.in_([...list of ids...])).all() -- 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. -- Yang Zhang http://yz.mit.edu/ -- 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] Compound Join
I'm trying to construct a query where in the from clause I would end up with something like SELECT foo FROM table1 JOIN table2 ON table1.id1 = table2.id1 JOIN table3 ON table1.id1=table3.id1 JOIN table4 ON table2.id2=table4.id2 AND table3.id3=table4.id3 I have tried various join combinations but I can only get it to join table4 to table2 or table 3, not both. Thanks, - Eric -- 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] counting queries
Hi all, I have an application replying on sqlalchemy that deals with many recursive methods. We manipulate complex graphs. I have tests that validates we get the expected results for each method. Still, many optimization might be done to the code. What I would like is, in my test environment, evaluate the number of queries to the database. I'm aware that the debug mode will output every generated SQL, but I deal with a number of queries in the 100s range ... Is there a way to know how many SQL statement have been executed, from the python testing code ? Regards NiL -- 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] counting queries
On Jan 27, 2011, at 8:12 PM, NiL wrote: Hi all, I have an application replying on sqlalchemy that deals with many recursive methods. We manipulate complex graphs. I have tests that validates we get the expected results for each method. Still, many optimization might be done to the code. What I would like is, in my test environment, evaluate the number of queries to the database. I'm aware that the debug mode will output every generated SQL, but I deal with a number of queries in the 100s range ... Is there a way to know how many SQL statement have been executed, from the python testing code ? you use a ConnectionProxy for this (will be the execute event in 0.7). This example uses a timer, you'd adapt this idea to instead issue a count. You can use threadlocal variables or similar to pull out/reset the count of statements after an execution: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/Profiling Regards NiL -- 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] datetime interval to a number..
you can emit that exact SQL using func.abs() in conjunction with the extract()
function, which is a standalone SQLA construct.
from sqlalchemy import func, extract
func.abs(extract('epoch', header.udatetime - self.header.udatetime))
On Jan 27, 2011, at 4:21 PM, Petra Clementson wrote:
hi all,
I've been trying to work out some SQLalchemy code to query two datetime
columns and choose the one closest in time. I have some code that does this,
but it requires using PostgreSQL and looks a little messy:
query = query.order_by(ABS(EXTRACT(EPOCH FROM (header.utdatetime -
:utdatetime_x.params(utdatetime_x= self.header.utdatetime)
I've been working on a piece of code that is all SQLalchemy, but I keep
getting an error that I can't use the absolute value function on an interval
and casting the interval to Numeric isn't working either:
query = query.order_by(func.abs(cast(Header.utdatetime -
self.header.utdatetime, Numeric())).asc())
any help would be greatly appreciated.
--
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] Compound Join
On Jan 27, 2011, at 8:06 PM, Eric N wrote: I'm trying to construct a query where in the from clause I would end up with something like SELECT foo FROM table1 JOIN table2 ON table1.id1 = table2.id1 JOIN table3 ON table1.id1=table3.id1 JOIN table4 ON table2.id2=table4.id2 AND table3.id3=table4.id3 I have tried various join combinations but I can only get it to join table4 to table2 or table 3, not both. the and_() function would be used as the onclause: from sqlalchemy import and_ select = select.select_from( table1.join(table2, table2.c.id1==table1.c.id1).\ join(table3, table1.c.id1==table3.c.id1).\ join(table4, and_(table2.c.id2==table4.c.id2, table3.c.id3==table4.c.id3)) ) You didn't say if you were using ORM or expression language, that above is expression language. Same idea applies to ORM, use and_() in the ON clause. -- 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] datetime interval to a number..
haha you guys make it so easy for us and yet we still can't get. Thanks so
much for your help! I think that'll do me just fine.
On Thu, Jan 27, 2011 at 3:57 PM, Michael Bayer mike...@zzzcomputing.comwrote:
you can emit that exact SQL using func.abs() in conjunction with the
extract() function, which is a standalone SQLA construct.
from sqlalchemy import func, extract
func.abs(extract('epoch', header.udatetime - self.header.udatetime))
On Jan 27, 2011, at 4:21 PM, Petra Clementson wrote:
hi all,
I've been trying to work out some SQLalchemy code to query two datetime
columns and choose the one closest in time. I have some code that does this,
but it requires using PostgreSQL and looks a little messy:
query = query.order_by(ABS(EXTRACT(EPOCH FROM (header.utdatetime -
:utdatetime_x.params(utdatetime_x= self.header.utdatetime)
I've been working on a piece of code that is all SQLalchemy, but I keep
getting an error that I can't use the absolute value function on an interval
and casting the interval to Numeric isn't working either:
query = query.order_by(func.abs(cast(Header.utdatetime -
self.header.utdatetime, Numeric())).asc())
any help would be greatly appreciated.
--
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.comsqlalchemy%2bunsubscr...@googlegroups.com
.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.comsqlalchemy%2bunsubscr...@googlegroups.com
.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to 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: UserDefinedType for tuples
Hi Folks, I was hoping to still be able to get guidance on creating my UserDefinedType. If I just knew exactly what the purpose of the bind and result processes were supposed to be, I'm sure I could look it up from there. -- 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: UserDefinedType for tuples
Assuming you're using types that are provided by the database (i.e. VARCHAR, ARRAY, INTEGER, etc.), you use a TypeDecorator to add some kind of in-Python marshalling behavior to some Python type. On Jan 28, 2011, at 12:14 AM, Enrico wrote: Hi Folks, I was hoping to still be able to get guidance on creating my UserDefinedType. If I just knew exactly what the purpose of the bind and result processes were supposed to be, I'm sure I could look it up from there. -- 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.
