[sqlalchemy] 0.6 and c extensions
Hi, a really interesting feature in sa 0.6 are the c extensions, however I think they should be implemented using ctypes so if python ctypes extension is available (default in python =2.5 and available even for 2.4) the c extensions are used as default, using ctypes no compilation is needed you can use the same code on x86,x86_64, arm ecc.. what do you think about? drakkan -- 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] 0.6 and c extensions
Le dimanche 21 mars 2010 à 02:10 -0700, drakkan a écrit : Hi, a really interesting feature in sa 0.6 are the c extensions, however I think they should be implemented using ctypes so if python ctypes extension is available (default in python =2.5 and available even for 2.4) the c extensions are used as default, ctypes cannot compile your own C code. -- 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] SQLAlchemy 0.6beta2 released
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Michael Bayer ha scritto: SQLAlchemy 0.6beta2 is now available.This beta may be the last before the 0.6.0 final release. We've hopefully gotten every largish change into the release as possible so that people can test. 0.6 is already running on a number of production servers and is already widely tested on mainstream platforms. Big new things in this release include: [...] Do you plan to implement ticket #877 for the 0.6.0 final release? What about ticket #1679, for SQL Schema support? Thanks Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkumMJMACgkQscQJ24LbaUTitgCeJXSTpWdcWZDwishGvIScFkm7 C2kAn2OVRHyglzTzad7NI4tExoQ4R7p5 =tRls -END PGP SIGNATURE- -- 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] SQLAlchemy 0.6beta2 released
Hello, On Mar 21, 2010, at 10:43 AM, Manlio Perillo wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Michael Bayer ha scritto: SQLAlchemy 0.6beta2 is now available.This beta may be the last before the 0.6.0 final release. We've hopefully gotten every largish change into the release as possible so that people can test. 0.6 is already running on a number of production servers and is already widely tested on mainstream platforms. Big new things in this release include: [...] Do you plan to implement ticket #877 for the 0.6.0 final release? What about ticket #1679, for SQL Schema support? #877 would need some tests to be considered. The patch doesn't seem to be available for #1679. I guess a result of the new server migration. Michael -- 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: 0.6 and c extensions
On 21 Mar, 14:31, Antoine Pitrou solip...@pitrou.net wrote: Le dimanche 21 mars 2010 à 02:10 -0700, drakkan a écrit : Hi, a really interesting feature in sa 0.6 are the c extensions, however I think they should be implemented using ctypes so if python ctypes extension is available (default in python =2.5 and available even for 2.4) the c extensions are used as default, ctypes cannot compile your own C code. Yes I know my question is why sqlalchemy wrote his own C code and not use ctypes? -- 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] SQLAlchemy 0.6beta2 released
On Mar 21, 2010, at 10:43 AM, Manlio Perillo wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Michael Bayer ha scritto: What about ticket #1679, for SQL Schema support? the patch needs to be re-added. CREATE SCHEMA / DROP SCHEMA is also not going to have any connection to the schema keyword, only to CreateSchema and DropSchema. The bigger picture needs to be considered here - schemas in SQLAlchemy are not necessarily schemas in the real world. On Oracle and Sybase, they are usernames.the schema keyword in SQLA is not really a schema - it is only, the keyword that goes before the dot in somename.tablename. that is it. It may even have several dotted names in it (i.e. like dbo.foobar).It will be much easier to accept a patch that does not attempt to change what the schema keyword means. Thanks Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkumMJMACgkQscQJ24LbaUTitgCeJXSTpWdcWZDwishGvIScFkm7 C2kAn2OVRHyglzTzad7NI4tExoQ4R7p5 =tRls -END PGP SIGNATURE- -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: 0.6 and c extensions
On Sun, Mar 21, 2010 at 17:13, drakkan drakkan1...@gmail.com wrote: On 21 Mar, 14:31, Antoine Pitrou solip...@pitrou.net wrote: Le dimanche 21 mars 2010 à 02:10 -0700, drakkan a écrit : a really interesting feature in sa 0.6 are the c extensions, however I think they should be implemented using ctypes so if python ctypes extension is available (default in python =2.5 and available even for 2.4) the c extensions are used as default, ctypes cannot compile your own C code. Yes I know my question is why sqlalchemy wrote his own C code and not use ctypes? Correct me if I'm wrong, but AFAIK ctypes is a way to interface your Python code with external C libraries. You cannot create any new functionality with ctypes. And what I did for the C extension was rewrite in C the most speed-critical parts of *SQLAlchemy* (not of an external lib) ! I could have used cython (and I might actually rewrite what I have done thus far in cython at some point in the future), but ctypes??? -- Gaëtan de Menten -- 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] callproc support
On Mar 21, 2010, at 12:50 PM, Kevin Wormington wrote: Hi, I am using SQLAlchemy 0.5.8 with the ibm_db_sa (DB2) adapter and I am wanting to add simple session.callproc support so that I can get results from stored procedures that don't use a select or table format. I haven't done any development on SA before but at first glance it appears that I would just need to add the method in the ibm dialect class and into engines/base.py to raise an exception for other dialects that don't support it. Can it really be that easy? Also, if I implement this will it be a possibility to get it integrated into future releases? So here's the things i ask/state about this: 1. IBM's dialect works with 0.5 ? it says its only for 0.4. 2. the callproc requirement here assumes that there's absolutely no way to make this work using plain execute(). For example with cx_oracle you can use outparams with plain execute(). I don't know the details here for DB2, but if callproc() really provides functionality that is impossible otherwise, then yes we need to examine ways to call it. 3. the way this feature would work would be: from sqlalchemy import procedure result = engine.execute(procedure.name_of_my_procedure(arg1, arg2, ...)) or with plain text or such, you can do this: result = engine.connect().execution_options(callproc=True).execute(my procedure...) 4. execution_options is only availble in 0.6. Similarly, any built in version of this feature would be for 0.6. 5. I'd like to have IBM's dialect on 0.6. Two ways to do this would either be to write a new dialect from scratch using their DBAPI, or to just port their 0.4/0.5 dialect to 0.6. I'd prefer the latter, but I don't know the licensing details. If IBM were to give me the green light I'd just wrap their dialect into SQLAlchemy core where all the other ones are. 6. in 0.5 or any, the functionality of #3 can be achieved using public API points, here's a demo: from sqlalchemy.ext.compiler import compiles from sqlalchemy.interfaces import ConnectionProxy from sqlalchemy.sql.expression import ClauseElement from sqlalchemy import create_engine class procedure(ClauseElement): supports_execution = True _autocommit = False _execution_options = {} def __init__(self, name, *args): self.name = name self.args = args @compiles(procedure) def compile_procedure(element, compiler, **kw): return %s(%s) % (element.name, ,.join(str(expr) for expr in element.args)) class ProcedureProxy(ConnectionProxy): def cursor_execute(self, execute, cursor, statement, parameters, context, executemany): Intercept low-level cursor execute() events. if context and isinstance(context.compiled.statement, procedure): engine.logger.info(calling stored procedure: %s, statement) if hasattr(cursor, 'callproc'): return cursor.callproc(statement, parameters) else: return cursor.execute(statement, parameters) else: return execute(cursor, statement, parameters, context) engine = create_engine('sqlite://', proxy=ProcedureProxy(), echo=True) print engine.execute(procedure(pragma table_info, foo)) Kevin -- 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] callproc support
Michael Bayer wrote: On Mar 21, 2010, at 12:50 PM, Kevin Wormington wrote: Hi, I am using SQLAlchemy 0.5.8 with the ibm_db_sa (DB2) adapter and I am wanting to add simple session.callproc support so that I can get results from stored procedures that don't use a select or table format. I haven't done any development on SA before but at first glance it appears that I would just need to add the method in the ibm dialect class and into engines/base.py to raise an exception for other dialects that don't support it. Can it really be that easy? Also, if I implement this will it be a possibility to get it integrated into future releases? So here's the things i ask/state about this: 1. IBM's dialect works with 0.5 ? it says its only for 0.4. Yes, with a few minor changes to match renamed logging components. 2. the callproc requirement here assumes that there's absolutely no way to make this work using plain execute(). For example with cx_oracle you can use outparams with plain execute(). I don't know the details here for DB2, but if callproc() really provides functionality that is impossible otherwise, then yes we need to examine ways to call it. I have tried several different ways of getting plain execute to work including via the low-level ibm_db.execute interface with no luck. I have also tried changing the DB2 side to be a function instead of a stored procedure so that something like select myfunctionname(parameter) from SYSIBM.SYSDUMMY1 would work; this crashes under SA but works with the low-level ibm_db. The ibm_db.callproc() function works as expected at the low level and appears to be AFAIK the only way to get a stored procedure to return results for DB2. I'm using DB2 Express-C 9.7. 3. the way this feature would work would be: from sqlalchemy import procedure result = engine.execute(procedure.name_of_my_procedure(arg1, arg2, ...)) or with plain text or such, you can do this: result = engine.connect().execution_options(callproc=True).execute(my procedure...) 4. execution_options is only availble in 0.6. Similarly, any built in version of this feature would be for 0.6. 5. I'd like to have IBM's dialect on 0.6. Two ways to do this would either be to write a new dialect from scratch using their DBAPI, or to just port their 0.4/0.5 dialect to 0.6. I'd prefer the latter, but I don't know the licensing details. If IBM were to give me the green light I'd just wrap their dialect into SQLAlchemy core where all the other ones are. I initially tried porting it to 0.6 but there are substantial differences...ok more than I could fix on one bottle of Mountain Dew! 6. in 0.5 or any, the functionality of #3 can be achieved using public API points, here's a demo: from sqlalchemy.ext.compiler import compiles from sqlalchemy.interfaces import ConnectionProxy from sqlalchemy.sql.expression import ClauseElement from sqlalchemy import create_engine class procedure(ClauseElement): supports_execution = True _autocommit = False _execution_options = {} def __init__(self, name, *args): self.name = name self.args = args @compiles(procedure) def compile_procedure(element, compiler, **kw): return %s(%s) % (element.name, ,.join(str(expr) for expr in element.args)) class ProcedureProxy(ConnectionProxy): def cursor_execute(self, execute, cursor, statement, parameters, context, executemany): Intercept low-level cursor execute() events. if context and isinstance(context.compiled.statement, procedure): engine.logger.info(calling stored procedure: %s, statement) if hasattr(cursor, 'callproc'): return cursor.callproc(statement, parameters) else: return cursor.execute(statement, parameters) else: return execute(cursor, statement, parameters, context) engine = create_engine('sqlite://', proxy=ProcedureProxy(), echo=True) print engine.execute(procedure(pragma table_info, foo)) I will give this a try. Would this be thread-safe under Pylons? Kevin -- 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] callproc support
I get the following when trying the example implemention: Traceback (most recent call last): File test.py, line 6, in module class procedure(ClauseElement): File test.py, line 15, in procedure @compiles(procedure) NameError: name 'procedure' is not defined Here is the code for that class: class procedure(ClauseElement): supports_execution = True _autocommit = False _execution_options = {} def __init__(self, name, *args): self.name = name self.args = args @compiles(procedure) def compile_procedure(element, compiler, **kw): return %s(%s) % (element.name, ,.join(str(expr) for expr in element.args)) Kevin Wormington wrote: Michael Bayer wrote: On Mar 21, 2010, at 12:50 PM, Kevin Wormington wrote: Hi, I am using SQLAlchemy 0.5.8 with the ibm_db_sa (DB2) adapter and I am wanting to add simple session.callproc support so that I can get results from stored procedures that don't use a select or table format. I haven't done any development on SA before but at first glance it appears that I would just need to add the method in the ibm dialect class and into engines/base.py to raise an exception for other dialects that don't support it. Can it really be that easy? Also, if I implement this will it be a possibility to get it integrated into future releases? So here's the things i ask/state about this: 1. IBM's dialect works with 0.5 ? it says its only for 0.4. Yes, with a few minor changes to match renamed logging components. 2. the callproc requirement here assumes that there's absolutely no way to make this work using plain execute(). For example with cx_oracle you can use outparams with plain execute(). I don't know the details here for DB2, but if callproc() really provides functionality that is impossible otherwise, then yes we need to examine ways to call it. I have tried several different ways of getting plain execute to work including via the low-level ibm_db.execute interface with no luck. I have also tried changing the DB2 side to be a function instead of a stored procedure so that something like select myfunctionname(parameter) from SYSIBM.SYSDUMMY1 would work; this crashes under SA but works with the low-level ibm_db. The ibm_db.callproc() function works as expected at the low level and appears to be AFAIK the only way to get a stored procedure to return results for DB2. I'm using DB2 Express-C 9.7. 3. the way this feature would work would be: from sqlalchemy import procedure result = engine.execute(procedure.name_of_my_procedure(arg1, arg2, ...)) or with plain text or such, you can do this: result = engine.connect().execution_options(callproc=True).execute(my procedure...) 4. execution_options is only availble in 0.6. Similarly, any built in version of this feature would be for 0.6. 5. I'd like to have IBM's dialect on 0.6. Two ways to do this would either be to write a new dialect from scratch using their DBAPI, or to just port their 0.4/0.5 dialect to 0.6. I'd prefer the latter, but I don't know the licensing details. If IBM were to give me the green light I'd just wrap their dialect into SQLAlchemy core where all the other ones are. I initially tried porting it to 0.6 but there are substantial differences...ok more than I could fix on one bottle of Mountain Dew! 6. in 0.5 or any, the functionality of #3 can be achieved using public API points, here's a demo: from sqlalchemy.ext.compiler import compiles from sqlalchemy.interfaces import ConnectionProxy from sqlalchemy.sql.expression import ClauseElement from sqlalchemy import create_engine class procedure(ClauseElement): supports_execution = True _autocommit = False _execution_options = {} def __init__(self, name, *args): self.name = name self.args = args @compiles(procedure) def compile_procedure(element, compiler, **kw): return %s(%s) % (element.name, ,.join(str(expr) for expr in element.args)) class ProcedureProxy(ConnectionProxy): def cursor_execute(self, execute, cursor, statement, parameters, context, executemany): Intercept low-level cursor execute() events. if context and isinstance(context.compiled.statement, procedure): engine.logger.info(calling stored procedure: %s, statement) if hasattr(cursor, 'callproc'): return cursor.callproc(statement, parameters) else: return cursor.execute(statement, parameters) else: return execute(cursor, statement, parameters, context) engine = create_engine('sqlite://', proxy=ProcedureProxy(), echo=True) print engine.execute(procedure(pragma table_info, foo)) I will give this a try. Would this be thread-safe under Pylons? Kevin -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to
Re: [sqlalchemy] callproc support
Please disregard...cut paste messed up the indention. Kevin Wormington wrote: I get the following when trying the example implemention: Traceback (most recent call last): File test.py, line 6, in module class procedure(ClauseElement): File test.py, line 15, in procedure @compiles(procedure) NameError: name 'procedure' is not defined Here is the code for that class: class procedure(ClauseElement): supports_execution = True _autocommit = False _execution_options = {} def __init__(self, name, *args): self.name = name self.args = args @compiles(procedure) def compile_procedure(element, compiler, **kw): return %s(%s) % (element.name, ,.join(str(expr) for expr in element.args)) Kevin Wormington wrote: Michael Bayer wrote: On Mar 21, 2010, at 12:50 PM, Kevin Wormington wrote: Hi, I am using SQLAlchemy 0.5.8 with the ibm_db_sa (DB2) adapter and I am wanting to add simple session.callproc support so that I can get results from stored procedures that don't use a select or table format. I haven't done any development on SA before but at first glance it appears that I would just need to add the method in the ibm dialect class and into engines/base.py to raise an exception for other dialects that don't support it. Can it really be that easy? Also, if I implement this will it be a possibility to get it integrated into future releases? So here's the things i ask/state about this: 1. IBM's dialect works with 0.5 ? it says its only for 0.4. Yes, with a few minor changes to match renamed logging components. 2. the callproc requirement here assumes that there's absolutely no way to make this work using plain execute(). For example with cx_oracle you can use outparams with plain execute(). I don't know the details here for DB2, but if callproc() really provides functionality that is impossible otherwise, then yes we need to examine ways to call it. I have tried several different ways of getting plain execute to work including via the low-level ibm_db.execute interface with no luck. I have also tried changing the DB2 side to be a function instead of a stored procedure so that something like select myfunctionname(parameter) from SYSIBM.SYSDUMMY1 would work; this crashes under SA but works with the low-level ibm_db. The ibm_db.callproc() function works as expected at the low level and appears to be AFAIK the only way to get a stored procedure to return results for DB2. I'm using DB2 Express-C 9.7. 3. the way this feature would work would be: from sqlalchemy import procedure result = engine.execute(procedure.name_of_my_procedure(arg1, arg2, ...)) or with plain text or such, you can do this: result = engine.connect().execution_options(callproc=True).execute(my procedure...) 4. execution_options is only availble in 0.6. Similarly, any built in version of this feature would be for 0.6. 5. I'd like to have IBM's dialect on 0.6. Two ways to do this would either be to write a new dialect from scratch using their DBAPI, or to just port their 0.4/0.5 dialect to 0.6. I'd prefer the latter, but I don't know the licensing details. If IBM were to give me the green light I'd just wrap their dialect into SQLAlchemy core where all the other ones are. I initially tried porting it to 0.6 but there are substantial differences...ok more than I could fix on one bottle of Mountain Dew! 6. in 0.5 or any, the functionality of #3 can be achieved using public API points, here's a demo: from sqlalchemy.ext.compiler import compiles from sqlalchemy.interfaces import ConnectionProxy from sqlalchemy.sql.expression import ClauseElement from sqlalchemy import create_engine class procedure(ClauseElement): supports_execution = True _autocommit = False _execution_options = {} def __init__(self, name, *args): self.name = name self.args = args @compiles(procedure) def compile_procedure(element, compiler, **kw): return %s(%s) % (element.name, ,.join(str(expr) for expr in element.args)) class ProcedureProxy(ConnectionProxy): def cursor_execute(self, execute, cursor, statement, parameters, context, executemany): Intercept low-level cursor execute() events. if context and isinstance(context.compiled.statement, procedure): engine.logger.info(calling stored procedure: %s, statement) if hasattr(cursor, 'callproc'): return cursor.callproc(statement, parameters) else: return cursor.execute(statement, parameters) else: return execute(cursor, statement, parameters, context) engine = create_engine('sqlite://', proxy=ProcedureProxy(), echo=True) print engine.execute(procedure(pragma table_info, foo)) I will give this a try. Would this be thread-safe under Pylons? Kevin -- You received this message because you are subscribed to the
[sqlalchemy] Re: @comparable_using
I got around this by switching to declarative and declaring my property like this: _id = column_property(Column(id,Integer,primary_key=True), comparator_factory=some_comparator_factory()) And then used @synonym_for for the getter: @synonym_for(_id) @property def id(self): return some_function(self._id) I'm still not sure what was wrong with my original code, so would be interested in any comments, for academic reasons. Thank you! Matthew -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: @comparable_using
On Mar 21, 2010, at 6:20 PM, Matthew wrote: I got around this by switching to declarative and declaring my property like this: _id = column_property(Column(id,Integer,primary_key=True), comparator_factory=some_comparator_factory()) And then used @synonym_for for the getter: @synonym_for(_id) @property def id(self): return some_function(self._id) I'm still not sure what was wrong with my original code, so would be interested in any comments, for academic reasons. your original test appeared to have a class descending from object. comparable_using applies to declarative classes. If you were using mapper(), you'd take the output from comparable_using and pass it to the properties dictionary of mapper() - but more likely you'd use comparable_property() directly pass your comparator straight in to column_property(), as introduced in http://www.sqlalchemy.org/docs/mappers.html#custom-comparators and linking to an example at http://www.sqlalchemy.org/docs/reference/orm/mapping.html#sqlalchemy.orm.comparable_property . Thank you! Matthew -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: @comparable_using
After fixing my cutpaste problem I just get DB2 sql errors from the sample code. I'm going to turn on some more logging in db2 and see what is actually reaching it. Kevin Matthew wrote: I got around this by switching to declarative and declaring my property like this: _id = column_property(Column(id,Integer,primary_key=True), comparator_factory=some_comparator_factory()) And then used @synonym_for for the getter: @synonym_for(_id) @property def id(self): return some_function(self._id) I'm still not sure what was wrong with my original code, so would be interested in any comments, for academic reasons. Thank you! Matthew -- 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] callproc support
From DB2 logs it appears that the following is what's getting sent to DB2 and resulting in the error: CALL BILLING.subscriber_balance(1000,?)( ) This is using: print engine.execute(procedure(BILLING.subscriber_balance, subid, ?)) Any idea how I can get rid of the extra set of ()'s? Thanks, Kevin Michael Bayer wrote: On Mar 21, 2010, at 12:50 PM, Kevin Wormington wrote: Hi, I am using SQLAlchemy 0.5.8 with the ibm_db_sa (DB2) adapter and I am wanting to add simple session.callproc support so that I can get results from stored procedures that don't use a select or table format. I haven't done any development on SA before but at first glance it appears that I would just need to add the method in the ibm dialect class and into engines/base.py to raise an exception for other dialects that don't support it. Can it really be that easy? Also, if I implement this will it be a possibility to get it integrated into future releases? So here's the things i ask/state about this: 1. IBM's dialect works with 0.5 ? it says its only for 0.4. 2. the callproc requirement here assumes that there's absolutely no way to make this work using plain execute(). For example with cx_oracle you can use outparams with plain execute(). I don't know the details here for DB2, but if callproc() really provides functionality that is impossible otherwise, then yes we need to examine ways to call it. 3. the way this feature would work would be: from sqlalchemy import procedure result = engine.execute(procedure.name_of_my_procedure(arg1, arg2, ...)) or with plain text or such, you can do this: result = engine.connect().execution_options(callproc=True).execute(my procedure...) 4. execution_options is only availble in 0.6. Similarly, any built in version of this feature would be for 0.6. 5. I'd like to have IBM's dialect on 0.6. Two ways to do this would either be to write a new dialect from scratch using their DBAPI, or to just port their 0.4/0.5 dialect to 0.6. I'd prefer the latter, but I don't know the licensing details. If IBM were to give me the green light I'd just wrap their dialect into SQLAlchemy core where all the other ones are. 6. in 0.5 or any, the functionality of #3 can be achieved using public API points, here's a demo: from sqlalchemy.ext.compiler import compiles from sqlalchemy.interfaces import ConnectionProxy from sqlalchemy.sql.expression import ClauseElement from sqlalchemy import create_engine class procedure(ClauseElement): supports_execution = True _autocommit = False _execution_options = {} def __init__(self, name, *args): self.name = name self.args = args @compiles(procedure) def compile_procedure(element, compiler, **kw): return %s(%s) % (element.name, ,.join(str(expr) for expr in element.args)) class ProcedureProxy(ConnectionProxy): def cursor_execute(self, execute, cursor, statement, parameters, context, executemany): Intercept low-level cursor execute() events. if context and isinstance(context.compiled.statement, procedure): engine.logger.info(calling stored procedure: %s, statement) if hasattr(cursor, 'callproc'): return cursor.callproc(statement, parameters) else: return cursor.execute(statement, parameters) else: return execute(cursor, statement, parameters, context) engine = create_engine('sqlite://', proxy=ProcedureProxy(), echo=True) print engine.execute(procedure(pragma table_info, foo)) Kevin -- 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] callproc support
On Mar 21, 2010, at 5:44 PM, Kevin Wormington wrote: I have tried several different ways of getting plain execute to work including via the low-level ibm_db.execute interface with no luck. I have also tried changing the DB2 side to be a function instead of a stored procedure so that something like select myfunctionname(parameter) from SYSIBM.SYSDUMMY1 would work; this crashes under SA but works with the low-level ibm_db. well that would imply a way of doing it with execute(). if the SA dialect crashes but not the DBAPI then the SA dialect has a bug. The ibm_db.callproc() function works as expected at the low level and appears to be AFAIK the only way to get a stored procedure to return results for DB2. I'm using DB2 Express-C 9.7. its likely behaving the same as your execute scenario above and still would have problems. I will give this a try. Would this be thread-safe under Pylons? yeah there is no shared state there. -- 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] callproc support
On Mar 21, 2010, at 7:34 PM, Kevin Wormington wrote: From DB2 logs it appears that the following is what's getting sent to DB2 and resulting in the error: CALL BILLING.subscriber_balance(1000,?)( ) This is using: print engine.execute(procedure(BILLING.subscriber_balance, subid, ?)) Any idea how I can get rid of the extra set of ()'s? what does the logging at the Python level say ? whatever goes into callproc() is the most you can control. that recipe is allowing you to control the string output exactly. Thanks, Kevin Michael Bayer wrote: On Mar 21, 2010, at 12:50 PM, Kevin Wormington wrote: Hi, I am using SQLAlchemy 0.5.8 with the ibm_db_sa (DB2) adapter and I am wanting to add simple session.callproc support so that I can get results from stored procedures that don't use a select or table format. I haven't done any development on SA before but at first glance it appears that I would just need to add the method in the ibm dialect class and into engines/base.py to raise an exception for other dialects that don't support it. Can it really be that easy? Also, if I implement this will it be a possibility to get it integrated into future releases? So here's the things i ask/state about this: 1. IBM's dialect works with 0.5 ? it says its only for 0.4. 2. the callproc requirement here assumes that there's absolutely no way to make this work using plain execute(). For example with cx_oracle you can use outparams with plain execute(). I don't know the details here for DB2, but if callproc() really provides functionality that is impossible otherwise, then yes we need to examine ways to call it. 3. the way this feature would work would be: from sqlalchemy import procedure result = engine.execute(procedure.name_of_my_procedure(arg1, arg2, ...)) or with plain text or such, you can do this: result = engine.connect().execution_options(callproc=True).execute(my procedure...) 4. execution_options is only availble in 0.6. Similarly, any built in version of this feature would be for 0.6. 5. I'd like to have IBM's dialect on 0.6. Two ways to do this would either be to write a new dialect from scratch using their DBAPI, or to just port their 0.4/0.5 dialect to 0.6. I'd prefer the latter, but I don't know the licensing details. If IBM were to give me the green light I'd just wrap their dialect into SQLAlchemy core where all the other ones are. 6. in 0.5 or any, the functionality of #3 can be achieved using public API points, here's a demo: from sqlalchemy.ext.compiler import compiles from sqlalchemy.interfaces import ConnectionProxy from sqlalchemy.sql.expression import ClauseElement from sqlalchemy import create_engine class procedure(ClauseElement): supports_execution = True _autocommit = False _execution_options = {} def __init__(self, name, *args): self.name = name self.args = args @compiles(procedure) def compile_procedure(element, compiler, **kw): return %s(%s) % (element.name, ,.join(str(expr) for expr in element.args)) class ProcedureProxy(ConnectionProxy): def cursor_execute(self, execute, cursor, statement, parameters, context, executemany): Intercept low-level cursor execute() events. if context and isinstance(context.compiled.statement, procedure): engine.logger.info(calling stored procedure: %s, statement) if hasattr(cursor, 'callproc'): return cursor.callproc(statement, parameters) else: return cursor.execute(statement, parameters) else: return execute(cursor, statement, parameters, context) engine = create_engine('sqlite://', proxy=ProcedureProxy(), echo=True) print engine.execute(procedure(pragma table_info, foo)) Kevin -- 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.
Re: [sqlalchemy] callproc support
Michael Bayer wrote: On Mar 21, 2010, at 5:44 PM, Kevin Wormington wrote: I have tried several different ways of getting plain execute to work including via the low-level ibm_db.execute interface with no luck. I have also tried changing the DB2 side to be a function instead of a stored procedure so that something like select myfunctionname(parameter) from SYSIBM.SYSDUMMY1 would work; this crashes under SA but works with the low-level ibm_db. well that would imply a way of doing it with execute(). if the SA dialect crashes but not the DBAPI then the SA dialect has a bug. Sort of: the DB2 side had to be changed from CREATE PROCEDURE to CREATE FUNCTION so they aren't quite equivalent functionality especially if it were a complex SP which mine is not. Also, I was successful with the function using ibm_db not ibm_db_dbi ... but ibm_db_dbi used ibm_db so one would think it should work. The ibm_db.callproc() function works as expected at the low level and appears to be AFAIK the only way to get a stored procedure to return results for DB2. I'm using DB2 Express-C 9.7. its likely behaving the same as your execute scenario above and still would have problems. I will give this a try. Would this be thread-safe under Pylons? yeah there is no shared state there. -- 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] callproc support
2010-03-21 18:40:49,656 INFO sqlalchemy.engine.base.Engine.0x...cb0c calling stored procedure: BILLING.subscriber_balance(1000,?) INFO:sqlalchemy.engine.base.Engine.0x...cb0c:calling stored procedure: BILLING.subscriber_balance(1000,?) It looks correct there but all that is getting logged is the statement which has had all the arguments combined together by the compile. I think that the 1000,? should remain as parameters to cursor.callproc instead of everything going into statement. ibm_db_dbi.callproc is probably adding the second set of () since it doesn't think there are any arguments. Just not sure how to fix it. Michael Bayer wrote: On Mar 21, 2010, at 7:34 PM, Kevin Wormington wrote: From DB2 logs it appears that the following is what's getting sent to DB2 and resulting in the error: CALL BILLING.subscriber_balance(1000,?)( ) This is using: print engine.execute(procedure(BILLING.subscriber_balance, subid, ?)) Any idea how I can get rid of the extra set of ()'s? what does the logging at the Python level say ? whatever goes into callproc() is the most you can control. that recipe is allowing you to control the string output exactly. Thanks, Kevin Michael Bayer wrote: On Mar 21, 2010, at 12:50 PM, Kevin Wormington wrote: Hi, I am using SQLAlchemy 0.5.8 with the ibm_db_sa (DB2) adapter and I am wanting to add simple session.callproc support so that I can get results from stored procedures that don't use a select or table format. I haven't done any development on SA before but at first glance it appears that I would just need to add the method in the ibm dialect class and into engines/base.py to raise an exception for other dialects that don't support it. Can it really be that easy? Also, if I implement this will it be a possibility to get it integrated into future releases? So here's the things i ask/state about this: 1. IBM's dialect works with 0.5 ? it says its only for 0.4. 2. the callproc requirement here assumes that there's absolutely no way to make this work using plain execute(). For example with cx_oracle you can use outparams with plain execute(). I don't know the details here for DB2, but if callproc() really provides functionality that is impossible otherwise, then yes we need to examine ways to call it. 3. the way this feature would work would be: from sqlalchemy import procedure result = engine.execute(procedure.name_of_my_procedure(arg1, arg2, ...)) or with plain text or such, you can do this: result = engine.connect().execution_options(callproc=True).execute(my procedure...) 4. execution_options is only availble in 0.6. Similarly, any built in version of this feature would be for 0.6. 5. I'd like to have IBM's dialect on 0.6. Two ways to do this would either be to write a new dialect from scratch using their DBAPI, or to just port their 0.4/0.5 dialect to 0.6. I'd prefer the latter, but I don't know the licensing details. If IBM were to give me the green light I'd just wrap their dialect into SQLAlchemy core where all the other ones are. 6. in 0.5 or any, the functionality of #3 can be achieved using public API points, here's a demo: from sqlalchemy.ext.compiler import compiles from sqlalchemy.interfaces import ConnectionProxy from sqlalchemy.sql.expression import ClauseElement from sqlalchemy import create_engine class procedure(ClauseElement): supports_execution = True _autocommit = False _execution_options = {} def __init__(self, name, *args): self.name = name self.args = args @compiles(procedure) def compile_procedure(element, compiler, **kw): return %s(%s) % (element.name, ,.join(str(expr) for expr in element.args)) class ProcedureProxy(ConnectionProxy): def cursor_execute(self, execute, cursor, statement, parameters, context, executemany): Intercept low-level cursor execute() events. if context and isinstance(context.compiled.statement, procedure): engine.logger.info(calling stored procedure: %s, statement) if hasattr(cursor, 'callproc'): return cursor.callproc(statement, parameters) else: return cursor.execute(statement, parameters) else: return execute(cursor, statement, parameters, context) engine = create_engine('sqlite://', proxy=ProcedureProxy(), echo=True) print engine.execute(procedure(pragma table_info, foo)) Kevin -- 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,
Re: [sqlalchemy] callproc support
I just modified the compile to return just the procedure name and the cursor.callproc to send the statement and the two parameters as a tuple and the DB2 receives the correct SQL: CALL BILLING.subscriber_balance(?,?) But I get the following back from ibm_db_dbi: return cursor.callproc(statement, (1000,0)) File /usr/local/lib/python2.6/dist-packages/ibm_db-1.0-py2.6-linux-i686.egg/ibm_db_dbi.py, line 973, in callproc result = self._callproc_helper(procname, parameters) File /usr/local/lib/python2.6/dist-packages/ibm_db-1.0-py2.6-linux-i686.egg/ibm_db_dbi.py, line 951, in _callproc_helper raise _get_exception(inst) ibm_db_dbi.DatabaseError: ibm_db_dbi::DatabaseError: Describe Param Failed: [IBM][CLI Driver] CLI0150E Driver not capable. SQLSTATE=HYC00 SQLCODE=-9 Looks like it doesn't like my values perhaps not being actual variables to bind. Kevin Wormington wrote: 2010-03-21 18:40:49,656 INFO sqlalchemy.engine.base.Engine.0x...cb0c calling stored procedure: BILLING.subscriber_balance(1000,?) INFO:sqlalchemy.engine.base.Engine.0x...cb0c:calling stored procedure: BILLING.subscriber_balance(1000,?) It looks correct there but all that is getting logged is the statement which has had all the arguments combined together by the compile. I think that the 1000,? should remain as parameters to cursor.callproc instead of everything going into statement. ibm_db_dbi.callproc is probably adding the second set of () since it doesn't think there are any arguments. Just not sure how to fix it. Michael Bayer wrote: On Mar 21, 2010, at 7:34 PM, Kevin Wormington wrote: From DB2 logs it appears that the following is what's getting sent to DB2 and resulting in the error: CALL BILLING.subscriber_balance(1000,?)( ) This is using: print engine.execute(procedure(BILLING.subscriber_balance, subid, ?)) Any idea how I can get rid of the extra set of ()'s? what does the logging at the Python level say ? whatever goes into callproc() is the most you can control. that recipe is allowing you to control the string output exactly. Thanks, Kevin Michael Bayer wrote: On Mar 21, 2010, at 12:50 PM, Kevin Wormington wrote: Hi, I am using SQLAlchemy 0.5.8 with the ibm_db_sa (DB2) adapter and I am wanting to add simple session.callproc support so that I can get results from stored procedures that don't use a select or table format. I haven't done any development on SA before but at first glance it appears that I would just need to add the method in the ibm dialect class and into engines/base.py to raise an exception for other dialects that don't support it. Can it really be that easy? Also, if I implement this will it be a possibility to get it integrated into future releases? So here's the things i ask/state about this: 1. IBM's dialect works with 0.5 ? it says its only for 0.4. 2. the callproc requirement here assumes that there's absolutely no way to make this work using plain execute(). For example with cx_oracle you can use outparams with plain execute(). I don't know the details here for DB2, but if callproc() really provides functionality that is impossible otherwise, then yes we need to examine ways to call it. 3. the way this feature would work would be: from sqlalchemy import procedure result = engine.execute(procedure.name_of_my_procedure(arg1, arg2, ...)) or with plain text or such, you can do this: result = engine.connect().execution_options(callproc=True).execute(my procedure...) 4. execution_options is only availble in 0.6. Similarly, any built in version of this feature would be for 0.6. 5. I'd like to have IBM's dialect on 0.6. Two ways to do this would either be to write a new dialect from scratch using their DBAPI, or to just port their 0.4/0.5 dialect to 0.6. I'd prefer the latter, but I don't know the licensing details. If IBM were to give me the green light I'd just wrap their dialect into SQLAlchemy core where all the other ones are. 6. in 0.5 or any, the functionality of #3 can be achieved using public API points, here's a demo: from sqlalchemy.ext.compiler import compiles from sqlalchemy.interfaces import ConnectionProxy from sqlalchemy.sql.expression import ClauseElement from sqlalchemy import create_engine class procedure(ClauseElement): supports_execution = True _autocommit = False _execution_options = {} def __init__(self, name, *args): self.name = name self.args = args @compiles(procedure) def compile_procedure(element, compiler, **kw): return %s(%s) % (element.name, ,.join(str(expr) for expr in element.args)) class ProcedureProxy(ConnectionProxy): def cursor_execute(self, execute, cursor, statement, parameters, context, executemany): Intercept low-level cursor execute() events. if context and isinstance(context.compiled.statement, procedure): engine.logger.info(calling stored procedure: %s, statement)
Re: [sqlalchemy] callproc support
It appears this is some issue with ibm_db_dbi. I tried it using just the ibm_db_dbi calls and no SA and get the same error msg: CLI0150E Driver not capable. Explanation: The operation is valid but not supported by either the driver or the data source. User response: Specify a valid operation. Kevin Wormington wrote: I just modified the compile to return just the procedure name and the cursor.callproc to send the statement and the two parameters as a tuple and the DB2 receives the correct SQL: CALL BILLING.subscriber_balance(?,?) But I get the following back from ibm_db_dbi: return cursor.callproc(statement, (1000,0)) File /usr/local/lib/python2.6/dist-packages/ibm_db-1.0-py2.6-linux-i686.egg/ibm_db_dbi.py, line 973, in callproc result = self._callproc_helper(procname, parameters) File /usr/local/lib/python2.6/dist-packages/ibm_db-1.0-py2.6-linux-i686.egg/ibm_db_dbi.py, line 951, in _callproc_helper raise _get_exception(inst) ibm_db_dbi.DatabaseError: ibm_db_dbi::DatabaseError: Describe Param Failed: [IBM][CLI Driver] CLI0150E Driver not capable. SQLSTATE=HYC00 SQLCODE=-9 Looks like it doesn't like my values perhaps not being actual variables to bind. Kevin Wormington wrote: 2010-03-21 18:40:49,656 INFO sqlalchemy.engine.base.Engine.0x...cb0c calling stored procedure: BILLING.subscriber_balance(1000,?) INFO:sqlalchemy.engine.base.Engine.0x...cb0c:calling stored procedure: BILLING.subscriber_balance(1000,?) It looks correct there but all that is getting logged is the statement which has had all the arguments combined together by the compile. I think that the 1000,? should remain as parameters to cursor.callproc instead of everything going into statement. ibm_db_dbi.callproc is probably adding the second set of () since it doesn't think there are any arguments. Just not sure how to fix it. Michael Bayer wrote: On Mar 21, 2010, at 7:34 PM, Kevin Wormington wrote: From DB2 logs it appears that the following is what's getting sent to DB2 and resulting in the error: CALL BILLING.subscriber_balance(1000,?)( ) This is using: print engine.execute(procedure(BILLING.subscriber_balance, subid, ?)) Any idea how I can get rid of the extra set of ()'s? what does the logging at the Python level say ? whatever goes into callproc() is the most you can control. that recipe is allowing you to control the string output exactly. Thanks, Kevin Michael Bayer wrote: On Mar 21, 2010, at 12:50 PM, Kevin Wormington wrote: Hi, I am using SQLAlchemy 0.5.8 with the ibm_db_sa (DB2) adapter and I am wanting to add simple session.callproc support so that I can get results from stored procedures that don't use a select or table format. I haven't done any development on SA before but at first glance it appears that I would just need to add the method in the ibm dialect class and into engines/base.py to raise an exception for other dialects that don't support it. Can it really be that easy? Also, if I implement this will it be a possibility to get it integrated into future releases? So here's the things i ask/state about this: 1. IBM's dialect works with 0.5 ? it says its only for 0.4. 2. the callproc requirement here assumes that there's absolutely no way to make this work using plain execute(). For example with cx_oracle you can use outparams with plain execute(). I don't know the details here for DB2, but if callproc() really provides functionality that is impossible otherwise, then yes we need to examine ways to call it. 3. the way this feature would work would be: from sqlalchemy import procedure result = engine.execute(procedure.name_of_my_procedure(arg1, arg2, ...)) or with plain text or such, you can do this: result = engine.connect().execution_options(callproc=True).execute(my procedure...) 4. execution_options is only availble in 0.6. Similarly, any built in version of this feature would be for 0.6. 5. I'd like to have IBM's dialect on 0.6. Two ways to do this would either be to write a new dialect from scratch using their DBAPI, or to just port their 0.4/0.5 dialect to 0.6. I'd prefer the latter, but I don't know the licensing details. If IBM were to give me the green light I'd just wrap their dialect into SQLAlchemy core where all the other ones are. 6. in 0.5 or any, the functionality of #3 can be achieved using public API points, here's a demo: from sqlalchemy.ext.compiler import compiles from sqlalchemy.interfaces import ConnectionProxy from sqlalchemy.sql.expression import ClauseElement from sqlalchemy import create_engine class procedure(ClauseElement): supports_execution = True _autocommit = False _execution_options = {} def __init__(self, name, *args): self.name = name self.args = args @compiles(procedure) def compile_procedure(element, compiler, **kw): return %s(%s) % (element.name, ,.join(str(expr) for expr in element.args)) class
Re: [sqlalchemy] callproc support
On Mar 21, 2010, at 8:07 PM, Kevin Wormington wrote: I just modified the compile to return just the procedure name and the cursor.callproc to send the statement and the two parameters as a tuple and the DB2 receives the correct SQL: CALL BILLING.subscriber_balance(?,?) But I get the following back from ibm_db_dbi: return cursor.callproc(statement, (1000,0)) File /usr/local/lib/python2.6/dist-packages/ibm_db-1.0-py2.6-linux-i686.egg/ibm_db_dbi.py, line 973, in callproc result = self._callproc_helper(procname, parameters) File /usr/local/lib/python2.6/dist-packages/ibm_db-1.0-py2.6-linux-i686.egg/ibm_db_dbi.py, line 951, in _callproc_helper raise _get_exception(inst) ibm_db_dbi.DatabaseError: ibm_db_dbi::DatabaseError: Describe Param Failed: [IBM][CLI Driver] CLI0150E Driver not capable. SQLSTATE=HYC00 SQLCODE=-9 Looks like it doesn't like my values perhaps not being actual variables to bind. heres how to get the binds to come through, in case thats the problem: from sqlalchemy import literal @compiles(procedure) def compile_procedure(element, compiler, **kw): return %s(%s) % (element.name, ,.join(compiler.process(literal(expr)) for expr in element.args)) literal is basically the same as bindparam. when you send it to the compiler the value becomes part of the compiled's params, and the generated string is ? or whatever paramstyle is. usually in SQLA core we do the conversion to bindparam in the constructor of the clause construct. Kevin Wormington wrote: 2010-03-21 18:40:49,656 INFO sqlalchemy.engine.base.Engine.0x...cb0c calling stored procedure: BILLING.subscriber_balance(1000,?) INFO:sqlalchemy.engine.base.Engine.0x...cb0c:calling stored procedure: BILLING.subscriber_balance(1000,?) It looks correct there but all that is getting logged is the statement which has had all the arguments combined together by the compile. I think that the 1000,? should remain as parameters to cursor.callproc instead of everything going into statement. ibm_db_dbi.callproc is probably adding the second set of () since it doesn't think there are any arguments. Just not sure how to fix it. Michael Bayer wrote: On Mar 21, 2010, at 7:34 PM, Kevin Wormington wrote: From DB2 logs it appears that the following is what's getting sent to DB2 and resulting in the error: CALL BILLING.subscriber_balance(1000,?)( ) This is using: print engine.execute(procedure(BILLING.subscriber_balance, subid, ?)) Any idea how I can get rid of the extra set of ()'s? what does the logging at the Python level say ? whatever goes into callproc() is the most you can control. that recipe is allowing you to control the string output exactly. Thanks, Kevin Michael Bayer wrote: On Mar 21, 2010, at 12:50 PM, Kevin Wormington wrote: Hi, I am using SQLAlchemy 0.5.8 with the ibm_db_sa (DB2) adapter and I am wanting to add simple session.callproc support so that I can get results from stored procedures that don't use a select or table format. I haven't done any development on SA before but at first glance it appears that I would just need to add the method in the ibm dialect class and into engines/base.py to raise an exception for other dialects that don't support it. Can it really be that easy? Also, if I implement this will it be a possibility to get it integrated into future releases? So here's the things i ask/state about this: 1. IBM's dialect works with 0.5 ? it says its only for 0.4. 2. the callproc requirement here assumes that there's absolutely no way to make this work using plain execute(). For example with cx_oracle you can use outparams with plain execute(). I don't know the details here for DB2, but if callproc() really provides functionality that is impossible otherwise, then yes we need to examine ways to call it. 3. the way this feature would work would be: from sqlalchemy import procedure result = engine.execute(procedure.name_of_my_procedure(arg1, arg2, ...)) or with plain text or such, you can do this: result = engine.connect().execution_options(callproc=True).execute(my procedure...) 4. execution_options is only availble in 0.6. Similarly, any built in version of this feature would be for 0.6. 5. I'd like to have IBM's dialect on 0.6. Two ways to do this would either be to write a new dialect from scratch using their DBAPI, or to just port their 0.4/0.5 dialect to 0.6. I'd prefer the latter, but I don't know the licensing details. If IBM were to give me the green light I'd just wrap their dialect into SQLAlchemy core where all the other ones are. 6. in 0.5 or any, the functionality of #3 can be achieved using public API points, here's a demo: from sqlalchemy.ext.compiler import compiles from sqlalchemy.interfaces import ConnectionProxy from sqlalchemy.sql.expression import ClauseElement from sqlalchemy import create_engine class
Re: [sqlalchemy] callproc support
I was able to get it working from just the ibm_db_dbi interface - the actual call has to be: cursor.callproc('BILLING.subscriber_balance',(subid,balance)) these both cause the sql errors: cursor.callproc('BILLING.subscriber_balance',(1000,0)) cursor.callproc('BILLING.subscriber_balance',(1000,balance)) The compiler.process(literal you suggested results in CALL BILLING.subscriber_balance(?,?)(?,?). I tried changing it to just return element.name and get just CALL BILLING.subscriber_balance( ) sent to the DB. How can I get the return cursor.callproc(statement, parameters) to actually have the (subid,balance) in the parameters? Kevin Michael Bayer wrote: On Mar 21, 2010, at 8:07 PM, Kevin Wormington wrote: I just modified the compile to return just the procedure name and the cursor.callproc to send the statement and the two parameters as a tuple and the DB2 receives the correct SQL: CALL BILLING.subscriber_balance(?,?) But I get the following back from ibm_db_dbi: return cursor.callproc(statement, (1000,0)) File /usr/local/lib/python2.6/dist-packages/ibm_db-1.0-py2.6-linux-i686.egg/ibm_db_dbi.py, line 973, in callproc result = self._callproc_helper(procname, parameters) File /usr/local/lib/python2.6/dist-packages/ibm_db-1.0-py2.6-linux-i686.egg/ibm_db_dbi.py, line 951, in _callproc_helper raise _get_exception(inst) ibm_db_dbi.DatabaseError: ibm_db_dbi::DatabaseError: Describe Param Failed: [IBM][CLI Driver] CLI0150E Driver not capable. SQLSTATE=HYC00 SQLCODE=-9 Looks like it doesn't like my values perhaps not being actual variables to bind. heres how to get the binds to come through, in case thats the problem: from sqlalchemy import literal @compiles(procedure) def compile_procedure(element, compiler, **kw): return %s(%s) % (element.name, ,.join(compiler.process(literal(expr)) for expr in element.args)) literal is basically the same as bindparam. when you send it to the compiler the value becomes part of the compiled's params, and the generated string is ? or whatever paramstyle is. usually in SQLA core we do the conversion to bindparam in the constructor of the clause construct. Kevin Wormington wrote: 2010-03-21 18:40:49,656 INFO sqlalchemy.engine.base.Engine.0x...cb0c calling stored procedure: BILLING.subscriber_balance(1000,?) INFO:sqlalchemy.engine.base.Engine.0x...cb0c:calling stored procedure: BILLING.subscriber_balance(1000,?) It looks correct there but all that is getting logged is the statement which has had all the arguments combined together by the compile. I think that the 1000,? should remain as parameters to cursor.callproc instead of everything going into statement. ibm_db_dbi.callproc is probably adding the second set of () since it doesn't think there are any arguments. Just not sure how to fix it. Michael Bayer wrote: On Mar 21, 2010, at 7:34 PM, Kevin Wormington wrote: From DB2 logs it appears that the following is what's getting sent to DB2 and resulting in the error: CALL BILLING.subscriber_balance(1000,?)( ) This is using: print engine.execute(procedure(BILLING.subscriber_balance, subid, ?)) Any idea how I can get rid of the extra set of ()'s? what does the logging at the Python level say ? whatever goes into callproc() is the most you can control. that recipe is allowing you to control the string output exactly. Thanks, Kevin Michael Bayer wrote: On Mar 21, 2010, at 12:50 PM, Kevin Wormington wrote: Hi, I am using SQLAlchemy 0.5.8 with the ibm_db_sa (DB2) adapter and I am wanting to add simple session.callproc support so that I can get results from stored procedures that don't use a select or table format. I haven't done any development on SA before but at first glance it appears that I would just need to add the method in the ibm dialect class and into engines/base.py to raise an exception for other dialects that don't support it. Can it really be that easy? Also, if I implement this will it be a possibility to get it integrated into future releases? So here's the things i ask/state about this: 1. IBM's dialect works with 0.5 ? it says its only for 0.4. 2. the callproc requirement here assumes that there's absolutely no way to make this work using plain execute(). For example with cx_oracle you can use outparams with plain execute(). I don't know the details here for DB2, but if callproc() really provides functionality that is impossible otherwise, then yes we need to examine ways to call it. 3. the way this feature would work would be: from sqlalchemy import procedure result = engine.execute(procedure.name_of_my_procedure(arg1, arg2, ...)) or with plain text or such, you can do this: result = engine.connect().execution_options(callproc=True).execute(my procedure...) 4. execution_options is only availble in 0.6. Similarly, any built in version of this feature would be for 0.6. 5. I'd like to have IBM's dialect on 0.6. Two ways to do this would either be
Re: [sqlalchemy] callproc support
As a test I altered the compile_procedure and the call to cursor.callproc and do get the values back from the stored procedure from the print res before the return. I get exceptions about the cursor being close when I try to access the ResultProxy object returned though. I think it's because the callproc returns just the results and not a cursor/set. def compile_procedure(element, compiler, **kw): return %s % (element.name) class ProcedureProxy(ConnectionProxy): def cursor_execute(self, execute, cursor, statement, parameters, context, executemany): Intercept low-level cursor execute() events. if context and isinstance(context.compiled.statement, procedure): engine.logger.info(calling stored procedure: %s, statement) if hasattr(cursor, 'callproc'): subid = 1000 balance = 0 res = cursor.callproc(statement, (subid,balance)) print res return res #return cursor.callproc(statement, parameters) else: return cursor.execute(statement, parameters) else: return execute(cursor, statement, parameters, context) Kevin Wormington wrote: I was able to get it working from just the ibm_db_dbi interface - the actual call has to be: cursor.callproc('BILLING.subscriber_balance',(subid,balance)) these both cause the sql errors: cursor.callproc('BILLING.subscriber_balance',(1000,0)) cursor.callproc('BILLING.subscriber_balance',(1000,balance)) The compiler.process(literal you suggested results in CALL BILLING.subscriber_balance(?,?)(?,?). I tried changing it to just return element.name and get just CALL BILLING.subscriber_balance( ) sent to the DB. How can I get the return cursor.callproc(statement, parameters) to actually have the (subid,balance) in the parameters? Kevin Michael Bayer wrote: On Mar 21, 2010, at 8:07 PM, Kevin Wormington wrote: I just modified the compile to return just the procedure name and the cursor.callproc to send the statement and the two parameters as a tuple and the DB2 receives the correct SQL: CALL BILLING.subscriber_balance(?,?) But I get the following back from ibm_db_dbi: return cursor.callproc(statement, (1000,0)) File /usr/local/lib/python2.6/dist-packages/ibm_db-1.0-py2.6-linux-i686.egg/ibm_db_dbi.py, line 973, in callproc result = self._callproc_helper(procname, parameters) File /usr/local/lib/python2.6/dist-packages/ibm_db-1.0-py2.6-linux-i686.egg/ibm_db_dbi.py, line 951, in _callproc_helper raise _get_exception(inst) ibm_db_dbi.DatabaseError: ibm_db_dbi::DatabaseError: Describe Param Failed: [IBM][CLI Driver] CLI0150E Driver not capable. SQLSTATE=HYC00 SQLCODE=-9 Looks like it doesn't like my values perhaps not being actual variables to bind. heres how to get the binds to come through, in case thats the problem: from sqlalchemy import literal @compiles(procedure) def compile_procedure(element, compiler, **kw): return %s(%s) % (element.name, ,.join(compiler.process(literal(expr)) for expr in element.args)) literal is basically the same as bindparam. when you send it to the compiler the value becomes part of the compiled's params, and the generated string is ? or whatever paramstyle is. usually in SQLA core we do the conversion to bindparam in the constructor of the clause construct. Kevin Wormington wrote: 2010-03-21 18:40:49,656 INFO sqlalchemy.engine.base.Engine.0x...cb0c calling stored procedure: BILLING.subscriber_balance(1000,?) INFO:sqlalchemy.engine.base.Engine.0x...cb0c:calling stored procedure: BILLING.subscriber_balance(1000,?) It looks correct there but all that is getting logged is the statement which has had all the arguments combined together by the compile. I think that the 1000,? should remain as parameters to cursor.callproc instead of everything going into statement. ibm_db_dbi.callproc is probably adding the second set of () since it doesn't think there are any arguments. Just not sure how to fix it. Michael Bayer wrote: On Mar 21, 2010, at 7:34 PM, Kevin Wormington wrote: From DB2 logs it appears that the following is what's getting sent to DB2 and resulting in the error: CALL BILLING.subscriber_balance(1000,?)( ) This is using: print engine.execute(procedure(BILLING.subscriber_balance, subid, ?)) Any idea how I can get rid of the extra set of ()'s? what does the logging at the Python level say ? whatever goes into callproc() is the most you can control. that recipe is allowing you to control the string output exactly. Thanks, Kevin Michael Bayer wrote: On Mar 21, 2010, at 12:50 PM, Kevin Wormington wrote: Hi, I am using SQLAlchemy 0.5.8 with the ibm_db_sa (DB2) adapter and I am wanting to add simple session.callproc support so that I can get results from stored procedures that don't use a select or table format. I haven't done any development on SA before
Re: [sqlalchemy] callproc support
On Mar 21, 2010, at 9:33 PM, Kevin Wormington wrote: As a test I altered the compile_procedure and the call to cursor.callproc and do get the values back from the stored procedure from the print res before the return. I get exceptions about the cursor being close when I try to access the ResultProxy object returned though. I think it's because the callproc returns just the results and not a cursor/set. ah well, the result proxy closes the cursor when there's no cursor.description, i.e. that theres no results to fetch. The workarounds are getting uglier here, but you can do this: result = cursor.callproc(stuff) context.callproc_result = result then on the outside when you get your result, context is there: result = conn.execute(myprocedure...) print result.context.callproc_result def compile_procedure(element, compiler, **kw): return %s % (element.name) class ProcedureProxy(ConnectionProxy): def cursor_execute(self, execute, cursor, statement, parameters, context, executemany): Intercept low-level cursor execute() events. if context and isinstance(context.compiled.statement, procedure): engine.logger.info(calling stored procedure: %s, statement) if hasattr(cursor, 'callproc'): subid = 1000 balance = 0 res = cursor.callproc(statement, (subid,balance)) print res return res #return cursor.callproc(statement, parameters) else: return cursor.execute(statement, parameters) else: return execute(cursor, statement, parameters, context) Kevin Wormington wrote: I was able to get it working from just the ibm_db_dbi interface - the actual call has to be: cursor.callproc('BILLING.subscriber_balance',(subid,balance)) these both cause the sql errors: cursor.callproc('BILLING.subscriber_balance',(1000,0)) cursor.callproc('BILLING.subscriber_balance',(1000,balance)) The compiler.process(literal you suggested results in CALL BILLING.subscriber_balance(?,?)(?,?). I tried changing it to just return element.name and get just CALL BILLING.subscriber_balance( ) sent to the DB. How can I get the return cursor.callproc(statement, parameters) to actually have the (subid,balance) in the parameters? Kevin Michael Bayer wrote: On Mar 21, 2010, at 8:07 PM, Kevin Wormington wrote: I just modified the compile to return just the procedure name and the cursor.callproc to send the statement and the two parameters as a tuple and the DB2 receives the correct SQL: CALL BILLING.subscriber_balance(?,?) But I get the following back from ibm_db_dbi: return cursor.callproc(statement, (1000,0)) File /usr/local/lib/python2.6/dist-packages/ibm_db-1.0-py2.6-linux-i686.egg/ibm_db_dbi.py, line 973, in callproc result = self._callproc_helper(procname, parameters) File /usr/local/lib/python2.6/dist-packages/ibm_db-1.0-py2.6-linux-i686.egg/ibm_db_dbi.py, line 951, in _callproc_helper raise _get_exception(inst) ibm_db_dbi.DatabaseError: ibm_db_dbi::DatabaseError: Describe Param Failed: [IBM][CLI Driver] CLI0150E Driver not capable. SQLSTATE=HYC00 SQLCODE=-9 Looks like it doesn't like my values perhaps not being actual variables to bind. heres how to get the binds to come through, in case thats the problem: from sqlalchemy import literal @compiles(procedure) def compile_procedure(element, compiler, **kw): return %s(%s) % (element.name, ,.join(compiler.process(literal(expr)) for expr in element.args)) literal is basically the same as bindparam. when you send it to the compiler the value becomes part of the compiled's params, and the generated string is ? or whatever paramstyle is. usually in SQLA core we do the conversion to bindparam in the constructor of the clause construct. Kevin Wormington wrote: 2010-03-21 18:40:49,656 INFO sqlalchemy.engine.base.Engine.0x...cb0c calling stored procedure: BILLING.subscriber_balance(1000,?) INFO:sqlalchemy.engine.base.Engine.0x...cb0c:calling stored procedure: BILLING.subscriber_balance(1000,?) It looks correct there but all that is getting logged is the statement which has had all the arguments combined together by the compile. I think that the 1000,? should remain as parameters to cursor.callproc instead of everything going into statement. ibm_db_dbi.callproc is probably adding the second set of () since it doesn't think there are any arguments. Just not sure how to fix it. Michael Bayer wrote: On Mar 21, 2010, at 7:34 PM, Kevin Wormington wrote: From DB2 logs it appears that the following is what's getting sent to DB2 and resulting in the error: CALL BILLING.subscriber_balance(1000,?)( ) This is using: print engine.execute(procedure(BILLING.subscriber_balance, subid, ?)) Any idea how I can get rid of the extra set of ()'s? what does
[sqlalchemy] recommended declarative method design pattern for sessions
Hi All, One of the things that doesn't seem to be covered in the docs, and that I'm currently trying to figure out, is the recommended design pattern to use for managing sessions from declarative methods calls. Consider a declarative class User, where I want to implement a FindFriends() method: class User(Base): # declarative fields defined here def FindFriends(self): session = Session() # it's handy to use the self reference in query methods: friends = session.query(Friends).filter_by(friend=self).all() session.close() return friends Certainly, these types of methods would seem to be useful, but here's a dilemma - the above code doesn't work. Because the method uses a new session, which is guaranteed to not be the same session that was used to retrieve the original User object, the following code will fail: session = session() me = session.query(User).filter_by(name=Daniel).first() me.FindFriends() It would seem to be handy if SQLAlchemy placed a reference in each declarative object of the session from which it originated when query was called, so then my code could do something like this: class User(Base): # declarative fields defined here def FindFriends(self): # note the self.session.query - the idea is that sqlalchemy's query() would initialize this for us return self.session.query(Friends).filter_by(friend=self).all() Then this would allow the following code to work: session = session() me = session.query(User).filter_by(name=Daniel).first() me.FindFriends() This would work because me.FindFriends() would now have easy access to the same session that was used to retrieve me -- so the objects would be compatible and could be easily combined in queries. This would allow many methods to be added to the User class that could all do various kinds of db queries without having to pass a session variable around manually. My question is - what is the recommended design pattern to do what I am trying to do above? Passing the current session as an argument to FindFriends() seems cumbersome - is that the recommended approach or is there a more elegant way to handle it? Is my handy suggestion above something that would actually be useful or is there a better way to do what I am wanting to do? (I'm trying to be a good SQLAlchemy coder and not use a global session=Session() for everything, as explained here: http://www.sqlalchemy.org/docs/session.html#frequently-asked-questions . But to do this, I need to find a good design pattern to use in place of a global :) Thanks and Regards, Daniel -- 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.