Querying UNIQUEIDENTIFIER with mssql

[stevelewis]

Version: 0.7
Driver: pyodbc

I am able to select GUIDs out of the SQLServer DB with my columns set
up like so:

Id = Column("SiteID", UNIQUEIDENTIFIER, primary_key=True)

I'm unable to query this field when I try this:

site_id = UNIQUEIDENTIFIER(GUID_STR)
q = q.filter(BatchRecordEvent.BatchRecord.has(BatchRecord.SiteId !=
site_id))

I'm getting a warning:
SADeprecationWarning: Passing arguments to type object constructor
<class 'sqlalchemy.dialects.mssql.base.UNIQUEIDENTIFIER'> is
deprecated

And then an error:

('Invalid parameter type. param-index=2 param-type=UNIQUEIDENTIFIER',
'HY105')

So I've googled a good bit, read the docs, and still am unable to
figure something out. Thanks for any help in advance!

Steve

[Michael Bayer]

On Jun 13, 2012, at 3:13 PM, stevelewis wrote:

> Version: 0.7
> Driver: pyodbc
>
> I am able to select GUIDs out of the SQLServer DB with my columns set
> up like so:
>
> Id = Column("SiteID", UNIQUEIDENTIFIER, primary_key=True)

thats fine

>
> I'm unable to query this field when I try this:
>
> site_id = UNIQUEIDENTIFIER(GUID_STR)

Not sure what this is supposed to do. Seems like you're attempting to call the type as a function. I've googled around and cannot find an example of what this would represent on SQL server ( i.e. http://msdn.microsoft.com/en-us/library/ms190215%28v=sql.105%29.aspx, http://msdn.microsoft.com/en-us/library/ms187942.aspx). From what I can see, "uniqueidentifier" is a type only, not a function.

Types, like String, VARCHAR, UNIQUEIDENTIFIER here, are meant to be called in two places - in CREATE TABLE statements and in CAST statements. If there's some data-level procedure or something, that would be something else, such as a function you'd invoke in SQLAlchemy as func.uniqueidentifier(value). But I can't find documentation for such a function, if you can point me to the SQL you want then your issue is solved.

[Michael Bayer]

On Jun 13, 2012, at 3:40 PM, Michael Bayer wrote:

>
>>
>> I'm unable to query this field when I try this:
>>
>> site_id = UNIQUEIDENTIFIER(GUID_STR)
>
> Not sure what this is supposed to do. Seems like you're attempting to call the type as a function. I've googled around and cannot find an example of what this would represent on SQL server ( i.e. http://msdn.microsoft.com/en-us/library/ms190215%28v=sql.105%29.aspx, http://msdn.microsoft.com/en-us/library/ms187942.aspx). From what I can see, "uniqueidentifier" is a type only, not a function.
>
> Types, like String, VARCHAR, UNIQUEIDENTIFIER here, are meant to be called in two places - in CREATE TABLE statements and in CAST statements. If there's some data-level procedure or something, that would be something else, such as a function you'd invoke in SQLAlchemy as func.uniqueidentifier(value). But I can't find documentation for such a function, if you can point me to the SQL you want then your issue is solved.

oh, you just want to query against a string guid. Just pass it in as a string, sorry.

from sqlalchemy import MetaData, Table, Column, create_engine
from sqlalchemy.dialects.mssql import UNIQUEIDENTIFIER
import uuid

m = MetaData()
t1 = Table('t', m, Column('id', UNIQUEIDENTIFIER))

e = create_engine("mssql://scott:tiger@ms_2005")
with e.begin() as conn:
m.create_all(conn)

id_str = str(uuid.uuid4())
conn.execute(t1.insert(), id=id_str)
print conn.execute(t1.select().where(t1.c.id==id_str)).first()

[stevelewis]

Oh, duh! I just had the wrong string format. I knew I was doing
something dumb but I couldn't place my finger on it.
Thanks a ton!!!

On Jun 13, 4:06 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
> On Jun 13, 2012, at 3:40 PM, Michael Bayer wrote:
>
>
>
> >> I'm unable to query this field when I try this:
>
> >> site_id = UNIQUEIDENTIFIER(GUID_STR)
>

> > Not sure what this is supposed to do.  Seems like you're attempting to call the type as a function.  I've googled around and cannot find an example of what this would represent on SQL server ( i.e.http://msdn.microsoft.com/en-us/library/ms190215%28v=sql.105%29.aspx,http://msdn.microsoft.com/en-us/library/ms187942.aspx).    From what I can see, "uniqueidentifier" is a type only, not a function.