Skip to main content

SQLMoDEL

MSSQL - (pyodbc)​

通过链接URL链接​

from functools import lru_cache

DATABASE_URL_TEMPLATE = (
    "mssql+pyodbc://{username}:{password}@{server_name}/{database_name}?driver=ODBC Driver 17 for SQL Server"
    # 其他常用param 
    # ?charset=utf8
)

@lru_cache
def get_db_url(database_name: str, charset: str = None) -> str:
    """
    @Description {description}

    - param database_name :{str} 数据库名称
    - param charset       :{str} utf8|

    @returns `{str}` 数据库的连接url

    """
    # 从fastapi中快速获取配置
    config = get_settings()

    res = DATABASE_URL_TEMPLATE.format(
        username=config.db_user_name,
        password=config.db_pass_word,
        server_name=f"{config.db_host}:{config.db_port}",
        database_name=database_name,
    )

    if charset:
        res = res + f"?charset={charset}"

    return res

获取实例

connection_str = get_db_url('db_name')
engine = create_engine(connection_str, echo=False)

基础使用​

SELECT​

# 等价于 SELECT dbo.NpcDataParams WHERE NpcId=1000;
with Session(engine) as session:
    statement = select(NpcDataParams).where(NpcDataParams.NpcId == 1000)
    npcData = session.exec(statement).first()
    print(npcData)