How to handle UUID in SQLModel for MSSQL ? (Conversion failed)

[Ringostar1982]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

Model(shortened):
from pydantic import UUID4

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

Function to update 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")

Description

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

I just try to get the 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.

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 until now alot but could not find a correct solution.

Operating System

Windows

Operating System Details

No response

SQLModel Version

0.0.11

Python Version

3.11.0

Additional Context

No response

[Ringostar1982]

update:

If I CAST the roles_id to UNIQUEIDENTIFIER the Where Clause will be ok, but later on the UPDATE Command will fail!

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()

The next steps in updating the target will fail:

new_data = role.dict(exclude_unset=True)
        for key, value in new_data.items():
            setattr(target, key, value)
        Customer_Role.from_orm(target)  # above SQLModel v2: Customer.model_validate(target)
        # print(target.json(by_alias=True))
        session.add(target)
        session.commit()  # Commit all the changes tracked in the session. This commits everything in one single batch.
        session.refresh(target)  # update the object!
        return target

Error Message:

[SQL: UPDATE customer_roles SET street=%(street)s WHERE customer_roles.roles_id = %(customer_roles_roles_id)s] [parameters: {'street': 'Straße 13', 'customer_roles_roles_id': '120ba15d61ea40fa8a39dd0145d7ec7d'}]

[joachimhuet]

If the database is responsible for creating that id you don't need the default_factory. In my case it was my code taking care of generating that id. You can use the sqlalchemy server_default parameter instead but it's not necessary if you created your database from sql code directly and not from your python. Because you will already have it defined there.

[Ringostar1982]

So in my case it should be enaught to declare ?
roles_id: Optional[str] = Field(primary_key=True, nullable=False)

[joachimhuet]

Yes I would say so, and to be precise you could add your max_length = 100 that is the size of your column. But as it's an id you won't edit it so it will always be given by the database and should never be more than 100 anyway.
The more precise you are in your sqlmodel the easier it would be for someone else to understand your database without needing to look at your sql so I would still encourage do match your database description in sqlmodel.

For example if one day you want to spawn a test database you could just use your sqlmodel to create it. And having the server_default defined will add it directly on create table statement.
I don't know if my point is clear, but as an answer yes you could do that but you probably should not 🤣

[Ringostar1982]

In your understanding what would you recommend to be best in my case ?
roles_id: Optional[str] = Field(primary_key=True, nullable=False, server_default, max_length = 100)

[joachimhuet]

How did you make your id to be automatically generated in sql ? Using NEWID() on table create statement ? If so I would write something like that (nullable and unique are already suggested by primary_key=True thread):
roles_id: Optional[str] = Field(primary_key=True, sa_column_kwargs={"server_default": "NEWID()"}, max_length=100)

[Ringostar1982]

The DB-Column´s default value = (CONVERT([varchar](100),newid()))

roles_id: Optional[str] = Field(primary_key=True, sa_column_kwargs={"server_default": "(CONVERT([varchar](100),newid()))"}, max_length=100)

The server_default property is just for information?

[Ringostar1982]

I think you forgot to delete the server_default in your upper example without server_default.

So in my case i go on with(i wont do creating tables by my code for now):
roles_id: Optional[str] = Field(primary_key=True, default=None, sa_column_kwargs={"server_default": "(CONVERT([varchar](100),newid()))"}, max_length=100)

Editing Data was succefully. Next is to test if creating new Rows also will work.

[joachimhuet]

You are right sorry about that 💤
Great tell me if you need help for anything else !

[Ringostar1982]

Thanks, I will reply as soon as possible with results ..

[Ringostar1982]

Actually the workaround solution is working for updating and creating new rows in DB-table.
@joachimhuet thanks for support!

class Customer_RoleBase(SQLModel):
    roles_id: Optional[str] = Field(primary_key=True, index=True, default=None, sa_column_kwargs={"server_default": "(CONVERT([varchar](100),newid()))"}, max_length=100)
    ...

[urosjarc]

@joachimhuet & @Ringostar1982 Why we can't use roles_id: UUID ? What is the reason to have column as str type?
I have the same issue but I don't like having str type as column.

[redb0]

@urosjarc How about using TypeDecorator (https://docs.sqlalchemy.org/en/20/core/custom_types.html#sqlalchemy.types.TypeDecorator)? This will convert the UUID type to a database-only string. On the Python side, you will still have a UUID

[michelmetran]

Thank you very much!
I was looking for a solution... I had already tried dozens of things.