Invalid parameter type.

[AthreyVinay]

Please first make sure you have looked at:

• Documentation: https://github.com/mkleehammer/pyodbc/wiki
• Other issues

Environment

To diagnose, we usually need to know the following, including version numbers. On Windows, be sure to specify 32-bit Python or 64-bit:

• Python: 3.10
• pyodbc: 4.0.32
• OS: Windows
• DB: MSSQL
• driver: pyodbc

Issue

class AdvancedProject(SQLModel, table=True):
    __tablename__: str  = 'ADVANCEDPROJECT'
    PROJECT: Optional[str] = Field(default=None, primary_key=True)
    PROJECTTYPE: Optional[str]
    SERIALLENGTH: Optional[str]
    MASTERDESIGN: Optional[Design] = Field(default=None, foreign_key="DESIGN.DESIGN")

So while reading the MASTERDESIGN value from the database, the datatyps is String. But I need to re-populate the column with a type Design. When I try to do that, I get the following: pyodbc.ProgrammingError: ('Invalid parameter type. param-index=0 param-type=Design', 'HY105')

Any help will be highly appreciated. Do I have to convert the type Design to String?

Please see this for what I am trying to achieve and also the workaround: https://github.com/tiangolo/sqlmodel/issues/284

[v-chojas]

https://github.com/mkleehammer/pyodbc/wiki/Data-Types

[AthreyVinay]

https://github.com/mkleehammer/pyodbc/wiki/Data-Types

@v-chojas Thanks for the quick reply. While I try to understand the above link, the data-type that I am gicing back in not a generic one but a custom type i.e. Design.

[AthreyVinay]

I don't know if my thought process is correct or not. So in a nutshell, from the db the datatype of MASTERDESIGN is string. I try to do a eager join and set the joined table as the new value of the MASTERDESIGN but in the process I am pushing a different datatype than the datatype while I read from the db(String). Please let me know if Im unclear, thanks.

[v-chojas]

I am saying that those are all the datatypes that pyODBC supports. You'll need to do your own conversion for anything else.

[AthreyVinay]

You'll need to do your own conversion for anything e

Ah - okay, do you have an example that could show me? Thanks

[gordthompson]

I am unable to reproduce your issue. This code works fine for me:

from typing import Optional

from sqlmodel import create_engine
from sqlmodel import Field
from sqlmodel import Relationship
from sqlmodel import select
from sqlmodel import Session
from sqlmodel import SQLModel

class Design(SQLModel, table=True):
    __tablename__: str = "DESIGN"
    DESIGN: Optional[str] = Field(default=None, primary_key=True)
    project: Optional["AdvancedProject"] = Relationship(
        sa_relationship_kwargs={"uselist": False}
    )

class AdvancedProject(SQLModel, table=True):
    __tablename__: str = "ADVANCEDPROJECT"
    PROJECT: Optional[str] = Field(default=None, primary_key=True)
    MASTERDESIGN: Optional[str] = Field(
        default=None, foreign_key="DESIGN.DESIGN"
    )
    MASTERDESIGN_MODEL: Optional[Design] = Relationship(
        sa_relationship_kwargs={"uselist": False}, back_populates="project"
    )

engine = create_engine("mssql+pyodbc://scott:tiger^5HHH@mssql_199", echo=True)

with Session(engine) as session:
    proj = session.get(AdvancedProject, "project_1")
    if not proj:
        session.add(
            AdvancedProject(
                PROJECT="project_1",
                MASTERDESIGN_MODEL=Design(DESIGN="design_1"),
            )
        )
        session.commit()

    query = select(AdvancedProject)
    results = session.scalars(query).all()
    print(results)
    # [AdvancedProject(MASTERDESIGN='design_1', PROJECT='project_1')]

[AthreyVinay]

Thank you so much for the response @gordthompson Will work with this :)