Re: [sqlalchemy] Running an externally supplied SQL statement with special characters
Actually, using the session.connection().execute did help! Also, I think there is an option of creating a TextClause subclass with a different search regex that, for example, matches nothing. But it's a bit of an overkill, IMO. On Fri, Nov 29, 2013 at 10:41 PM, Michael Bayer mike...@zzzcomputing.comwrote: On Nov 22, 2013, at 2:08 PM, Michael Bayer mike...@zzzcomputing.com wrote: On Nov 22, 2013, at 1:11 PM, Ivan Kalinin pupss...@gmail.com wrote: Hello there, fellow developers! We've recently run into a terrible problem. A small tool uses SQLAlchemy to execute statements read from a text file against a database. The trouble comes when that pre-defined statement has a colon symbol in the field value of a, say, INSERT statement. Like as follows: INSERT INTO my_test_table values (123, ':bar') Running this statement with a plain session.execute(stmt) (where stmt contains a unicode string with full statement) causes a StatementError with a message like A value is required for bind parameter u'bar' However, I'm certain that parameter placeholders should not be parsed from within string literals. Is there a way to tell SA that this statement should not be analyzed for placeholders? Thanks in advance for help and advice! the string passed to session.execute() is wrapped with a text() construct, which does parse for bound parameters so that they may be type-processed and converted to the representation expected by the DBAPI (which is usually not the colon style).This parsing is pretty simplistic and does not expect that a quoted value would be directly embedded in the statement. there’s no escaping for those at the moment, so you have to skip the text() part here. To send a raw statement to the DBAPI layer without any processing, use the Connection object directly, that is, send session.connection().execute(stmt). sorry, I’m partially incorrect here, you should escape out that colon with a backslash: from sqlalchemy import text print text(INSERT INTO my_test_table values (123, '\\:bar')) INSERT INTO my_test_table values (123, ':bar') -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Running an externally supplied SQL statement with special characters
The point is we get those SQL statements from an external source and we'd prefer not to modify them. I do understand its a rare use-case of SA, but having a DumbTextClause or an option regex parameter in TextClause constructor could help. On Fri, Nov 29, 2013 at 11:06 PM, Michael Bayer mike...@zzzcomputing.comwrote: yes, that workaround works, but much more simply, using a backslash in text() should work as well On Nov 29, 2013, at 2:01 PM, Ivan Kalinin pupss...@gmail.com wrote: Actually, using the session.connection().execute did help! Also, I think there is an option of creating a TextClause subclass with a different search regex that, for example, matches nothing. But it's a bit of an overkill, IMO. On Fri, Nov 29, 2013 at 10:41 PM, Michael Bayer mike...@zzzcomputing.comwrote: On Nov 22, 2013, at 2:08 PM, Michael Bayer mike...@zzzcomputing.com wrote: On Nov 22, 2013, at 1:11 PM, Ivan Kalinin pupss...@gmail.com wrote: Hello there, fellow developers! We've recently run into a terrible problem. A small tool uses SQLAlchemy to execute statements read from a text file against a database. The trouble comes when that pre-defined statement has a colon symbol in the field value of a, say, INSERT statement. Like as follows: INSERT INTO my_test_table values (123, ':bar') Running this statement with a plain session.execute(stmt) (where stmt contains a unicode string with full statement) causes a StatementError with a message like A value is required for bind parameter u'bar' However, I'm certain that parameter placeholders should not be parsed from within string literals. Is there a way to tell SA that this statement should not be analyzed for placeholders? Thanks in advance for help and advice! the string passed to session.execute() is wrapped with a text() construct, which does parse for bound parameters so that they may be type-processed and converted to the representation expected by the DBAPI (which is usually not the colon style).This parsing is pretty simplistic and does not expect that a quoted value would be directly embedded in the statement. there’s no escaping for those at the moment, so you have to skip the text() part here. To send a raw statement to the DBAPI layer without any processing, use the Connection object directly, that is, send session.connection().execute(stmt). sorry, I’m partially incorrect here, you should escape out that colon with a backslash: from sqlalchemy import text print text(INSERT INTO my_test_table values (123, '\\:bar')) INSERT INTO my_test_table values (123, ':bar') -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Running an externally supplied SQL statement with special characters
Hello there, fellow developers! We've recently run into a terrible problem. A small tool uses SQLAlchemy to execute statements read from a text file against a database. The trouble comes when that pre-defined statement has a colon symbol in the field value of a, say, INSERT statement. Like as follows: INSERT INTO my_test_table values (123, ':bar') Running this statement with a plain session.execute(stmt) (where stmt contains a unicode string with full statement) causes a StatementError with a message like A value is required for bind parameter u'bar' However, I'm certain that parameter placeholders should not be parsed from within string literals. Is there a way to tell SA that this statement should not be analyzed for placeholders? Thanks in advance for help and advice! Best regards, Ivan. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Re: pymssql and uuid.UUID convertion
Well, anyone? On Thursday, November 22, 2012 1:42:07 AM UTC+4, Ivan Kalinin wrote: Hellow, fellow developers! I have run into and issue trying to use SQLAlchemy (0.7.4) and recent pymssql (2.0.0). Precisely, pymssql can not handle uuid.UUID objects as parameters (see related post on their ML herehttps://groups.google.com/forum/?fromgroups=#!topic/pymssql/ah6f8cl2Va0), but SA uses them in generated queries for the object querying/deletion. Folks from the pymssql suggest that the convertion of uuid.UUID to the string objects should be done in the corresponding SA dialect. If so, I would be glad to fix that with a patch if someone could give me couple of pointers to writing these dialects. Best regards, Ivan Kalinin. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/2UKN1ibSPMkJ. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] pymssql and uuid.UUID convertion
Thanks for the reply! However, I see that MSSQL dialect contains a UNIQUEIDENTIFIER type that is to represent similarly named column type. Looks like it should handle issues of marshalling the values, not some custom type. On Friday, November 23, 2012 7:39:50 PM UTC+4, Michael Bayer wrote: On Nov 21, 2012, at 4:42 PM, Ivan Kalinin wrote: Hellow, fellow developers! I have run into and issue trying to use SQLAlchemy (0.7.4) and recent pymssql (2.0.0). Precisely, pymssql can not handle uuid.UUID objects as parameters (see related post on their ML herehttps://groups.google.com/forum/?fromgroups=#!topic/pymssql/ah6f8cl2Va0), but SA uses them in generated queries for the object querying/deletion. Folks from the pymssql suggest that the convertion of uuid.UUID to the string objects should be done in the corresponding SA dialect. If so, I would be glad to fix that with a patch if someone could give me couple of pointers to writing these dialects. Take a look at the UUID recipe at http://docs.sqlalchemy.org/en/rel_0_8/core/types.html#backend-agnostic-guid-typewhich is provided for this purpose. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/_76lRkLKwboJ. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] pymssql and uuid.UUID convertion
Wait. I mean that SA _already_ has implemented UNIQUEIDENTIFIER over here: https://bitbucket.org/sqlalchemy/sqlalchemy/src/0c8e0b613da3b2bf6e965e1e5c15b6b3e540368a/lib/sqlalchemy/dialects/mssql/base.py?at=default#cl-478 I believe that thing should be responsible for valid marshalling/unmarshalling of the stuff. Please correct me if I'm wrong. On the other hand, could you please elaborate a bit concerning your problems with UNIQUEID's? From my point of view, they appear to work pretty much out of the box on pydobc+freetds, but have mentioned issue on pymssql. 2012/11/24 Michael Bayer mike...@zzzcomputing.com In my own MSSQL work, we use character fields for UUIDs as the DBAs were not in favor of trying to get UNIQUEIDENTIFIER to work. However, feel free to use UserDefinedType to implement UNIQUEIDENTIFIER including whatever coercion pymssql requires ( http://docs.sqlalchemy.org/en/rel_0_8/core/types.html#sqlalchemy.types.UserDefinedType). SQLAlchemy's type system is fully extensible here. On Nov 23, 2012, at 2:41 PM, Ivan Kalinin wrote: Thanks for the reply! However, I see that MSSQL dialect contains a UNIQUEIDENTIFIER type that is to represent similarly named column type. Looks like it should handle issues of marshalling the values, not some custom type. On Friday, November 23, 2012 7:39:50 PM UTC+4, Michael Bayer wrote: On Nov 21, 2012, at 4:42 PM, Ivan Kalinin wrote: Hellow, fellow developers! I have run into and issue trying to use SQLAlchemy (0.7.4) and recent pymssql (2.0.0). Precisely, pymssql can not handle uuid.UUID objects as parameters (see related post on their ML herehttps://groups.google.com/forum/?fromgroups=#!topic/pymssql/ah6f8cl2Va0), but SA uses them in generated queries for the object querying/deletion. Folks from the pymssql suggest that the convertion of uuid.UUID to the string objects should be done in the corresponding SA dialect. If so, I would be glad to fix that with a patch if someone could give me couple of pointers to writing these dialects. Take a look at the UUID recipe at http://docs.sqlalchemy.org/** en/rel_0_8/core/types.html#**backend-agnostic-guid-typehttp://docs.sqlalchemy.org/en/rel_0_8/core/types.html#backend-agnostic-guid-typewhich is provided for this purpose. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/_76lRkLKwboJ. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] pymssql and uuid.UUID convertion
Wow, thanks! But that looks pretty complex. I believe a more valid way of fixing the issue is a patch for pymssql itself - since it's relatively simple and already available: https://bitbucket.org/PuPSSMaN/pymssql/changeset/ed48708effdb481a9695e58876e223eb I'll try to contact pymssql mantainer to get that merged to the trunk. Thanks a lot =) 2012/11/24 Michael Bayer mike...@zzzcomputing.com ideally pymssql would know how (and are you saying that pyodbc does already?), but sure, SQLA does all kinds of coercion. For now, your options are to use the TypeDecorator recipe mentioned earlier around this UNIQUEIDENTIFIER type, or send me a pull request with a pymssql-specific type (assuming pyodbc does not need this coercion). An example of a DBAPI-specific type would be such as this one: https://bitbucket.org/sqlalchemy/sqlalchemy/src/0c8e0b613da3/lib/sqlalchemy/dialects/mssql/pyodbc.py?at=default#cl-120. On Nov 23, 2012, at 5:45 PM, Ivan Kalinin wrote: Wait. I mean that SA _already_ has implemented UNIQUEIDENTIFIER over here: https://bitbucket.org/sqlalchemy/sqlalchemy/src/0c8e0b613da3b2bf6e965e1e5c15b6b3e540368a/lib/sqlalchemy/dialects/mssql/base.py?at=default#cl-478 I believe that thing should be responsible for valid marshalling/unmarshalling of the stuff. Please correct me if I'm wrong. On the other hand, could you please elaborate a bit concerning your problems with UNIQUEID's? From my point of view, they appear to work pretty much out of the box on pydobc+freetds, but have mentioned issue on pymssql. 2012/11/24 Michael Bayer mike...@zzzcomputing.com In my own MSSQL work, we use character fields for UUIDs as the DBAs were not in favor of trying to get UNIQUEIDENTIFIER to work. However, feel free to use UserDefinedType to implement UNIQUEIDENTIFIER including whatever coercion pymssql requires ( http://docs.sqlalchemy.org/en/rel_0_8/core/types.html#sqlalchemy.types.UserDefinedType). SQLAlchemy's type system is fully extensible here. On Nov 23, 2012, at 2:41 PM, Ivan Kalinin wrote: Thanks for the reply! However, I see that MSSQL dialect contains a UNIQUEIDENTIFIER type that is to represent similarly named column type. Looks like it should handle issues of marshalling the values, not some custom type. On Friday, November 23, 2012 7:39:50 PM UTC+4, Michael Bayer wrote: On Nov 21, 2012, at 4:42 PM, Ivan Kalinin wrote: Hellow, fellow developers! I have run into and issue trying to use SQLAlchemy (0.7.4) and recent pymssql (2.0.0). Precisely, pymssql can not handle uuid.UUID objects as parameters (see related post on their ML herehttps://groups.google.com/forum/?fromgroups=#!topic/pymssql/ah6f8cl2Va0), but SA uses them in generated queries for the object querying/deletion. Folks from the pymssql suggest that the convertion of uuid.UUID to the string objects should be done in the corresponding SA dialect. If so, I would be glad to fix that with a patch if someone could give me couple of pointers to writing these dialects. Take a look at the UUID recipe at http://docs.sqlalchemy.org/** en/rel_0_8/core/types.html#**backend-agnostic-guid-typehttp://docs.sqlalchemy.org/en/rel_0_8/core/types.html#backend-agnostic-guid-typewhich is provided for this purpose. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/_76lRkLKwboJ. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy
[sqlalchemy] pymssql and uuid.UUID convertion
Hellow, fellow developers! I have run into and issue trying to use SQLAlchemy (0.7.4) and recent pymssql (2.0.0). Precisely, pymssql can not handle uuid.UUID objects as parameters (see related post on their ML herehttps://groups.google.com/forum/?fromgroups=#!topic/pymssql/ah6f8cl2Va0), but SA uses them in generated queries for the object querying/deletion. Folks from the pymssql suggest that the convertion of uuid.UUID to the string objects should be done in the corresponding SA dialect. If so, I would be glad to fix that with a patch if someone could give me couple of pointers to writing these dialects. Best regards, Ivan Kalinin. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/C7_dDgzrWg4J. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
