来自带有"prefix-namespaces"的mssql数据库的sqlalchemy映射表； [英] sqlalchemy map table from mssql database with "prefix-namespaces"

问题描述

我已经在这个问题上苦苦挣扎了一段时间，还没有找到答案，或者也许我已经看到了答案，但是没有得到答案-但是，我希望我能够描述我的问题.

I have been struggling on this for a while now and did not find an answer yet, or maybe I already have seen the answer and just didnt get it - however, I hope I am able to describe my problem.

我有一个MS SQL数据库，其中的表按Prefix.Tablename(带点)表示的名称空间(或任何名称)分组.因此，用于请求某些内容的本机sql语句如下所示:

I have a MS SQL database in which the tables are grouped in namespaces (or whatever it is called), denoted by Prefix.Tablename (with a dot). So a native sql statement to request some content looks like this:

    SELECT TOP 100 
     [Value], [ValueDate]
    FROM [FinancialDataBase].[Reporting].[IndexedElements]

如何将此映射到sqlalchemy? 如果"Reporting"前缀不存在，则解决方案(或一种实现方式)如下所示:

How to map this to sqlalchemy? If the "Reporting" prefix would not be there, the solution (or one way to do it) looks like this:

from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base, declared_attr
from sqlalchemy.orm import sessionmaker

def get_session():
    from urllib.parse import quote_plus as urllib_quote_plus

    server = "FinancialDataBase.sql.local"
    connstr = "DRIVER={SQL Server};SERVER=%s;DATABASE=FinancialDataBase" % server
    params = urllib_quote_plus(connstr)
    base_url = "mssql+pyodbc:///?odbc_connect=%s" % params

    engine  = create_engine(base_url,echo=True)
    Session = sessionmaker(bind=engine)
    session = Session()

    return engine, session

Base = declarative_base()

class IndexedElements(Base):
    __tablename__ = "IndexedElements"

    UniqueID = Column(String,primary_key=True)
    ValueDate = Column(DateTime)
    Value = Column(Float)

然后可以完成请求并将其包装在Pandas数据框中，例如:

And then requests can be done and wrapped in a Pandas dataframe for example like this:

import pandas as pd

engine, session = get_session()

query = session.query(IndexedElements.Value,IndexedElements.ValueDate)

data = pd.read_sql(query.statement,query.session.bind)

但是在其中编译并实际执行的SQL语句包含以下错误的FROM部分:

But the SQL statement that is compiled and actually executed in this, includes this wrong FROM part:

FROM [FinancialDataBase].[IndexedElements]

由于命名空间前缀，它必须是

Due to the namespace-prefix it would have to be

FROM [FinancialDataBase].[Reporting].[IndexedElements]

只需将表名扩展为

__tablename__ = "Reporting.IndexedElements"

不对其进行修复，因为它将已编译的sql语句更改为

doesnt fix it, because it changes the compiled sql statement to

FROM [FinancialDataBase].[Reporting.IndexedElements]

无法正常工作.

那如何解决呢?

推荐答案

答案在上面Ilja的评论中给出:

The answer is given in the comment by Ilja above:

命名空间"是所谓的架构，必须在映射的对象中声明.给定开头文章中的示例，必须按以下方式定义映射表:

The "namespace" is a so called schema and has to be declarated in the mapped object. Given the example from the opening post, the mapped table has to be defined like this:

class IndexedElements(Base):
    __tablename__ = "IndexedElements"
    __table_args__ = {"schema": "Reporting"}

    UniqueID = Column(String,primary_key=True)
    ValueDate = Column(DateTime)
    Value = Column(Float)

或定义一个基类，其中包含针对不同模式的这些信息.还要在sqlalchemy文档中检查增强基础": http://docs.sqlalchemy. org/en/latest/orm/extensions/declarative/mixins.html#augmenting-the-base

Or define a base class containing these informations for different schemata. Check also "Augmenting the base" in sqlalchemy docs: http://docs.sqlalchemy.org/en/latest/orm/extensions/declarative/mixins.html#augmenting-the-base

这篇关于来自带有"prefix-namespaces"的mssql数据库的sqlalchemy映射表；的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！