[sqlalchemy] AuditLog/History logging
We have around 10 different tables in the database and we use sqlalchemy's declarative base style table definition. We are happy about it. Now we need to have auditlog/history of changes made to database records. For example, the entry could be new recorded added or existing record updated or deleted. These information should be reflected in the database table called history. So we end up writing very simular code for every tables in our database for each crud operation. I am thinking whether we can have simplified solution like 1. We define our own custom class derived from declarative_base instance 2. We derive the rest of our classes from our own custom class mentioned in the step 1 3. Whenever there is changes in the record, Sqlalchemy to give callback on post/pre operation and we capture those changes in the derived class and use the history class to write the changes to the database. So we can derive all our future classes from this model and I will be free to handle the rest of the information. Can you please help me to implement this? Regards, Krisj -- 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] Insert from select?!
Hi *, I am wondering if there is a way to generate an insert from select with SQLAlchemy. Consider this code: from sqlalchemy import * engine = create_engine(sqlite:///demo.ldb, echo=True) md = MetaData() users = Table(users, md, Column(id, Integer, primary_key=True), Column(name, String), Column(status, Integer)) md.create_all(engine) engine.execute(users.insert().values(name=Joe Sixpack, status=1)) engine.execute(users.insert().values(name=Jane Citizen, status=1)) # insert from select? # engine.execute(users.insert().values( # users.select([users.c.name]), status=2)) conn = engine.connect() with conn.begin(): for row in conn.execute(users.select()).fetchall(): conn.execute(users.insert().values( name=row[name], status=2)) Is there a way to generate the natural SQL for this: insert into users (name, status) select name, 2 as status from users Basically I would like to tell SQLAlchemy to use a query to provide the values for insert. StackOverflow says this can't be done, but I can't believe that. :-) Greetings, Torsten -- DYNAmore Gesellschaft fuer Ingenieurdienstleistungen mbH Torsten Landschoff Office Dresden Tel: +49-(0)351-4519587 Fax: +49-(0)351-4519561 mailto:torsten.landsch...@dynamore.de http://www.dynamore.de Registration court: Mannheim, HRB: 109659, based in Karlsruhe, Managing director: Prof. Dr. K. Schweizerhof, Dipl.-Math. U. Franz -- 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] Insert from select?!
Try this http://www.sqlalchemy.org/docs/05/reference/ext/compiler.html?highlight=compiler#compiling-sub-elements-of-a-custom-expression-construct -- Abdul Kader On Wed, Nov 3, 2010 at 5:09 PM, Torsten Landschoff torsten.landsch...@dynamore.de wrote: Hi *, I am wondering if there is a way to generate an insert from select with SQLAlchemy. Consider this code: from sqlalchemy import * engine = create_engine(sqlite:///demo.ldb, echo=True) md = MetaData() users = Table(users, md, Column(id, Integer, primary_key=True), Column(name, String), Column(status, Integer)) md.create_all(engine) engine.execute(users.insert().values(name=Joe Sixpack, status=1)) engine.execute(users.insert().values(name=Jane Citizen, status=1)) # insert from select? # engine.execute(users.insert().values( # users.select([users.c.name]), status=2)) conn = engine.connect() with conn.begin(): for row in conn.execute(users.select()).fetchall(): conn.execute(users.insert().values( name=row[name], status=2)) Is there a way to generate the natural SQL for this: insert into users (name, status) select name, 2 as status from users Basically I would like to tell SQLAlchemy to use a query to provide the values for insert. StackOverflow says this can't be done, but I can't believe that. :-) -- 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] AuditLog/History logging
I know that elixir has an extension that does just this. You don't have to subclass, you just use a acts_as_versioned statement in your class definition and that's it. In addition, it will let you get the object at any of its verison (you can revert back changes, a sort of undo funcitonnality that spans over time) via the get_as_of method http://elixir.ematia.de/apidocs/elixir.ext.versioned.html I wonder if sqlalchemy has an equivalent extension ? Cheers, Y.Chaouche - Original Message From: Gopalakrishnan Subramani gopalakrishnan.subram...@gmail.com To: sqlalchemy sqlalchemy@googlegroups.com Sent: Wed, November 3, 2010 5:21:32 AM Subject: [sqlalchemy] AuditLog/History logging We have around 10 different tables in the database and we use sqlalchemy's declarative base style table definition. We are happy about it. Now we need to have auditlog/history of changes made to database records. For example, the entry could be new recorded added or existing record updated or deleted. These information should be reflected in the database table called history. So we end up writing very simular code for every tables in our database for each crud operation. I am thinking whether we can have simplified solution like 1. We define our own custom class derived from declarative_base instance 2. We derive the rest of our classes from our own custom class mentioned in the step 1 3. Whenever there is changes in the record, Sqlalchemy to give callback on post/pre operation and we capture those changes in the derived class and use the history class to write the changes to the database. So we can derive all our future classes from this model and I will be free to handle the rest of the information. Can you please help me to implement this? Regards, Krisj -- 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] AuditLog/History logging
http://www.sqlalchemy.org/docs/orm/examples.html?highlight=versioning#versioning You can find other interesting recipes in : http://www.sqlalchemy.org/trac/wiki/UsageRecipes Cheers, Y.Chaouche - Original Message From: Gopalakrishnan Subramani gopalakrishnan.subram...@gmail.com To: sqlalchemy sqlalchemy@googlegroups.com Sent: Wed, November 3, 2010 5:21:32 AM Subject: [sqlalchemy] AuditLog/History logging We have around 10 different tables in the database and we use sqlalchemy's declarative base style table definition. We are happy about it. Now we need to have auditlog/history of changes made to database records. For example, the entry could be new recorded added or existing record updated or deleted. These information should be reflected in the database table called history. So we end up writing very simular code for every tables in our database for each crud operation. I am thinking whether we can have simplified solution like 1. We define our own custom class derived from declarative_base instance 2. We derive the rest of our classes from our own custom class mentioned in the step 1 3. Whenever there is changes in the record, Sqlalchemy to give callback on post/pre operation and we capture those changes in the derived class and use the history class to write the changes to the database. So we can derive all our future classes from this model and I will be free to handle the rest of the information. Can you please help me to implement this? Regards, Krisj -- 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] Insert from select?!
On Wed, 2010-11-03 at 17:16 +0530, akm wrote: Try this http://www.sqlalchemy.org/docs/05/reference/ext/compiler.html?highlight=compiler#compiling-sub-elements-of-a-custom-expression-construct Thanks for the pointer, missed that before. So it is in fact doable :) Greetings, Torsten -- DYNAmore Gesellschaft fuer Ingenieurdienstleistungen mbH Torsten Landschoff Office Dresden Tel: +49-(0)351-4519587 Fax: +49-(0)351-4519561 mailto:torsten.landsch...@dynamore.de http://www.dynamore.de Registration court: Mannheim, HRB: 109659, based in Karlsruhe, Managing director: Prof. Dr. K. Schweizerhof, Dipl.-Math. U. Franz -- 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] Connection / disconnect / Pool
On Nov 2, 2010, at 11:46 PM, Warwick Prince wrote: Hi Michael I have an issue I can't fathom regarding Pools.I'm doing testing and hit the following error at exatly the same point each time; File C:\Documents and Settings\wprince\Desktop\PY CODE DEVELOPMENT\pyDAP\DAPForm.py, line 3805, in _processQuery self._queryCount = self._query.count() File C:\Python26\lib\site-packages\sqlalchemy\orm\query.py, line 1888, in count should_nest = should_nest[0] File C:\Python26\lib\site-packages\sqlalchemy\orm\query.py, line 1924, in _col_aggregate mapper=self._mapper_zero()) File C:\Python26\lib\site-packages\sqlalchemy\orm\session.py, line 740, in scalar return self.execute(clause, params=params, mapper=mapper, **kw).scalar() File C:\Python26\lib\site-packages\sqlalchemy\orm\session.py, line 734, in execute return self._connection_for_bind(engine, close_with_result=True).execute( TimeoutError: QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30 I'm creating a basic create_engine using defaults for the pool settings (as you can tell). Within each Thread that does the connection, I create a new engine, metadata and scoped session. That thread then does a basic query (in this case NOT using a session at all, just a direct table query) and then I do everything I can think of to close the connections. I do this; engine.dispose() session.commit() session.close() and then the class instance that holds all these is removed and the thread terminates. All works fine (i.e. no errors or exceptions) until I reach the 10 overflow limit and then it dies. Each destroy of an engine and recreate of an engine is at least a second apart so it's not being thrashed at all. What am I missing? The error is local to a single engine. So engine.dispose() has no impact on the issue. While a Session will maintain a single checked out connection, result sets that are returned by engine.execute(some select) and some select.execute() also do so, so be sure to fully exhaust and/or close() those result sets as well. Cheers Warwick Warwick Prince Managing Director mobile: +61 411 026 992 skype: warwickprince phone: +61 7 3102 3730 fax: +61 7 3319 6734 web: www.mushroomsys.com -- 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] Connection / disconnect / Pool
On Nov 3, 2010, at 10:11 AM, Michael Bayer wrote: On Nov 2, 2010, at 11:46 PM, Warwick Prince wrote: Hi Michael I have an issue I can't fathom regarding Pools.I'm doing testing and hit the following error at exatly the same point each time; File C:\Documents and Settings\wprince\Desktop\PY CODE DEVELOPMENT\pyDAP\DAPForm.py, line 3805, in _processQuery self._queryCount = self._query.count() File C:\Python26\lib\site-packages\sqlalchemy\orm\query.py, line 1888, in count should_nest = should_nest[0] File C:\Python26\lib\site-packages\sqlalchemy\orm\query.py, line 1924, in _col_aggregate mapper=self._mapper_zero()) File C:\Python26\lib\site-packages\sqlalchemy\orm\session.py, line 740, in scalar return self.execute(clause, params=params, mapper=mapper, **kw).scalar() File C:\Python26\lib\site-packages\sqlalchemy\orm\session.py, line 734, in execute return self._connection_for_bind(engine, close_with_result=True).execute( TimeoutError: QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30 I'm creating a basic create_engine using defaults for the pool settings (as you can tell). Within each Thread that does the connection, I create a new engine, metadata and scoped session. That thread then does a basic query (in this case NOT using a session at all, just a direct table query) and then I do everything I can think of to close the connections. I do this; engine.dispose() session.commit() session.close() and then the class instance that holds all these is removed and the thread terminates. All works fine (i.e. no errors or exceptions) until I reach the 10 overflow limit and then it dies. Each destroy of an engine and recreate of an engine is at least a second apart so it's not being thrashed at all. What am I missing? The error is local to a single engine. So engine.dispose() has no impact on the issue. well, correction, yes if you dispose the engine the pool drops away and is replaced with another, but that doesn't solve the core issue of using connections and not closing them out, since dispose() does not impact connections which are checked out. -- 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: AuditLog/History logging
Chaouche, Thank you for information. At the instance level, how will I know whether the instance going to be deleted or updated or insert. How do I get the property? And during update, how to identify which field has been modified? Regards, Krish On Nov 3, 6:30 pm, chaouche yacine yacinechaou...@yahoo.com wrote: http://www.sqlalchemy.org/docs/orm/examples.html?highlight=versioning... You can find other interesting recipes in : http://www.sqlalchemy.org/trac/wiki/UsageRecipes Cheers, Y.Chaouche - Original Message From: Gopalakrishnan Subramani gopalakrishnan.subram...@gmail.com To: sqlalchemy sqlalchemy@googlegroups.com Sent: Wed, November 3, 2010 5:21:32 AM Subject: [sqlalchemy] AuditLog/History logging We have around 10 different tables in the database and we use sqlalchemy's declarative base style table definition. We are happy about it. Now we need to have auditlog/history of changes made to database records. For example, the entry could be new recorded added or existing record updated or deleted. These information should be reflected in the database table called history. So we end up writing very simular code for every tables in our database for each crud operation. I am thinking whether we can have simplified solution like 1. We define our own custom class derived from declarative_base instance 2. We derive the rest of our classes from our own custom class mentioned in the step 1 3. Whenever there is changes in the record, Sqlalchemy to give callback on post/pre operation and we capture those changes in the derived class and use the history class to write the changes to the database. So we can derive all our future classes from this model and I will be free to handle the rest of the information. Can you please help me to implement this? Regards, Krisj -- 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 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 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] Connection / disconnect / Pool
Excellent. That'll be it for sure. I have a bunch of result sets that I assumed would just go away.. :-) Cheers Warwick Warwick A. Prince Managing Director Mushroom Systems International P/L On 04/11/2010, at 1:11 AM, Michael Bayer mike...@zzzcomputing.com wrote: On Nov 2, 2010, at 11:46 PM, Warwick Prince wrote: Hi Michael I have an issue I can't fathom regarding Pools.I'm doing testing and hit the following error at exatly the same point each time; File C:\Documents and Settings\wprince\Desktop\PY CODE DEVELOPMENT\pyDAP\DAPForm.py, line 3805, in _processQuery self._queryCount = self._query.count() File C:\Python26\lib\site-packages\sqlalchemy\orm\query.py, line 1888, in count should_nest = should_nest[0] File C:\Python26\lib\site-packages\sqlalchemy\orm\query.py, line 1924, in _col_aggregate mapper=self._mapper_zero()) File C:\Python26\lib\site-packages\sqlalchemy\orm\session.py, line 740, in scalar return self.execute(clause, params=params, mapper=mapper, **kw).scalar() File C:\Python26\lib\site-packages\sqlalchemy\orm\session.py, line 734, in execute return self._connection_for_bind(engine, close_with_result=True).execute( TimeoutError: QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30 I'm creating a basic create_engine using defaults for the pool settings (as you can tell). Within each Thread that does the connection, I create a new engine, metadata and scoped session. That thread then does a basic query (in this case NOT using a session at all, just a direct table query) and then I do everything I can think of to close the connections. I do this; engine.dispose() session.commit() session.close() and then the class instance that holds all these is removed and the thread terminates. All works fine (i.e. no errors or exceptions) until I reach the 10 overflow limit and then it dies. Each destroy of an engine and recreate of an engine is at least a second apart so it's not being thrashed at all. What am I missing? The error is local to a single engine. So engine.dispose() has no impact on the issue. While a Session will maintain a single checked out connection, result sets that are returned by engine.execute(some select) and some select.execute() also do so, so be sure to fully exhaust and/or close() those result sets as well. Cheers Warwick Warwick Prince Managing Director mobile: +61 411 026 992 skype: warwickprince phone: +61 7 3102 3730 fax: +61 7 3319 6734 web: www.mushroomsys.com -- 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.
