How to specify schema when using SQLServer

[chuymtz]

First Check

• I added a very descriptive title to this issue.
• I used the GitHub search to find a similar issue 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

# SELECT * FROM XYZ.test_sqlmodel
# My table doesnt exit in dbo. It exists in XYZ schema

from typing import Optional
from sqlmodel import Field, Session, SQLModel, create_engine, select
from sqlalchemy import event

class test_sqlmodel(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str

path = 'mssql+pymssql://uid:owd@server/db'
engine = create_engine(path)


def select_tbl():
    with Session(engine) as session:
        statement = select(test_sqlmodel)
        results = session.exec(statement)
        for row in results:
            print(row)
            
select_tbl()

Description

When I run the example code, it works as long as my table test_sqlmodel exists in the default schema. But, how can I create a sql model that points to a different one?

Operating System

Linux

Operating System Details

Red Hat 7

SQLModel Version

0.0.4

Python Version

Python 3.8.10

Additional Context

No response

[sarvan24]

Adding metadata with schema information should fix it.

from typing import Optional
from sqlmodel import Field, Session, SQLModel, create_engine, select, MetaData


xyz_metadata = MetaData(schema="XYZ")
class test_sqlmodel(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    metadata = xyz_metadata


path = 'mssql+pymssql://uid:owd@server/db'
engine = create_engine(path)


def select_tbl():
    with Session(engine) as session:
        statement = select(test_sqlmodel)
        results = session.exec(statement)
        for row in results:
            print(row)


select_tbl()

[YuriiMotov]

This seems to work:

from typing import Optional
from sqlmodel import Field, SQLModel, select
from sqlalchemy.dialects import mssql


class test_sqlmodel(SQLModel, table=True):
    __table_args__ = {"schema": "XYZ"}

    id: Optional[int] = Field(default=None, primary_key=True)
    name: str


statement = select(test_sqlmodel)
print(statement.compile(dialect=mssql.dialect()))

# SELECT [XYZ].test_sqlmodel.id, [XYZ].test_sqlmodel.name 
# FROM [XYZ].test_sqlmodel