How to handle MSSQL UNIQUEIDENTIFIER in SQLModel in Python ? (Conversion failed)

Question

# Model(shortened):
from pydantic import UUID4

class Customer_RoleBase(SQLModel):
    roles_id: Optional[UUID4] = Field(primary_key=True, nullable=False)

# the Function to update the DB(shortened):
def update_customer_role(role: Customer_Role) -> Customer_Role:
    with Session(createDBEngine()) as session:        
        print(role.roles_id)
        print(type(role.roles_id))
       
        target = session.exec(select(Customer_Role).where(Customer_Role.roles_id == role.roles_id)).one()
        
        if not target:
            raise HTTPException(status_code=404, detail="Customer-Role not found")
        new_data = role.dict(exclude_unset=True)
        for key, value in new_data.items():
            setattr(target, key, value)
        Customer_Role.from_orm(target)        
        session.add(target)
        session.commit() 
        session.refresh(target)
        return target

In FastApi with SQLModel i try to get a matching role by its roles_id (UNIQUEIDENTIFIER) but it end´s in an error:

Console Output: (see the print commands are first)

120ba15d-61ea-40fa-8a39-dd0145d7ec7d
<class 'uuid.UUID'>

sqlalchemy.exc.OperationalError: (pymssql._pymssql.OperationalError) (8169, b'Conversion failed when converting from a character string to uniqueidentifier.DB-Lib error message 20018

[SQL: SELECT customer_roles.roles_id, customer_roles.customer_id, customer_roles.system_id, customer_roles.salesorg_id, customer_roles.customer_role, customer_roles.role_number, customer_roles.full_name, customer_roles.first_name, customer_roles.last_name, customer_roles.partner_desc, customer_roles.default_partner, customer_roles.street, customer_roles.zip, customer_roles.city, customer_roles.country_code, customer_roles.email, customer_roles.phone, customer_roles.currency_code, customer_roles.payment_code, customer_roles.inco_terms_1, customer_roles.inco_terms_2, customer_roles.dist_channel, customer_roles.division
FROM customer_roles
WHERE customer_roles.roles_id = %(roles_id_1)s]
[parameters: {'roles_id_1': '120ba15d61ea40fa8a39dd0145d7ec7d'}]

The Column roles_id is of Type UUID4 and in the print command we can see its a real UUID and type of uuid.UUID but something after will convert it to something like a string without the hyphens and that will not be accepted by the SQL Server.

If i copy the SQL Statement to Azure Data Studio and run the query, it results in same error. If i include the hyphens to the query it works.

I was searching and testing alot but everthing ended in more problems and I could not find a solution.

• MSSQL Server
• DB Driver pymssql: 2.2.11
• SQLModel: 0.0.11
• pydantic: 1.10.13
• fastapi: 0.104.1
• SQLAlchemy: 1.4.50
• Python 3.11.0

Update:

If I CAST the roles_id to a UNIQUEIDENTIFIER then the Where-Clause will be ok. But that dont really help because later in the code on the UPDATE Command by session.commit() it will fail again!

Any Ideas please ?

from sqlalchemy.dialects.mssql import UNIQUEIDENTIFIER

target = session.exec(select(Customer_Role).where(
            Customer_Role.roles_id == sql.func.CAST(
                role.roles_id, UNIQUEIDENTIFIER
                )
            )).one()