pymssql and uuid.UUID convertion

[Ivan Kalinin]

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 here), 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.

[Ivan Kalinin]

Well, anyone?

[Michael Bayer]

Take a look at the UUID recipe at http://docs.sqlalchemy.org/en/rel_0_8/core/types.html#backend-agnostic-guid-type which is provided for this purpose.

[Ivan Kalinin]

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.

[Michael Bayer]

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.

--
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 sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[Ivan Kalinin]

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 <mik...@zzzcomputing.com>

[Michael Bayer]

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 .

[Ivan Kalinin]

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 <mik...@zzzcomputing.com>