sqlalchemy - 用空格反映表和列 [英] sqlalchemy - reflecting tables and columns with spaces

问题描述

如何在列名(和表名)中有空格的数据库上使用 sqlalchemy?

How can I use sqlalchemy on a database where the column names (and table names) have spaces in them?

db.auth_stuff.filter("db.auth_stuff.first name"=='Joe') 显然不行.我想在从数据库读取的现有表名之间放置类似 lambda x: x.replace(' ','_') 之类的东西，而不是在进行反射时手动定义所有内容，并用于我的模型.(创建一个通用函数来重命名所有不适用于 python 的表名 - 保留字等也可能有用)

db.auth_stuff.filter("db.auth_stuff.first name"=='Joe') obviously can't work. Rather than manually define everything when doing the reflections I want to put something like lambda x: x.replace(' ','_') between existing table names being read from the db, and being used in my models. (It might also be useful to create a general function to rename all table names that won't work well with python - reserved words etc.)

有没有简单/干净的方法来做到这一点?

Is there an easy/clean way of doing this?

我认为我需要定义自己的映射器类?

I think I need to define my own mapper class?

https://groups.google.com/forum/#!msg/sqlalchemy/pE1ZfBlq56w/ErPcn1YYSJgJ

或者使用某种 __mapper_args__ 参数 -http://docs.sqlalchemy.org/en/rel_0_8/orm/mapper_config.html#naming-all-columns-with-a-prefix

Or use some sort of __mapper_args__ parameter - http://docs.sqlalchemy.org/en/rel_0_8/orm/mapper_config.html#naming-all-columns-with-a-prefix

理想情况下:

class NewBase(Base):
    __mapper_args__ = {
        'column_rename_function' : lambda x: x.replace(' ','_')
    }

class User(NewBase):
    __table__ = "user table"
    }

推荐答案

您可以使用 reflection event 给列一个.key，但是当涉及到主键列时，完整的配方有一个错误，这在仍然- 未发布的 0.8.3 版本(以及主版本).如果您在 https://bitbucket.org/zzzeek/sqlalchemy/get 查看 0.8.3/rel_0_8.zip 这个秘籍即使使用主键列也能工作:

you can do this using a reflection event to give the columns a .key, however the full recipe has a bug when primary key columns are involved, which was fixed in the still-unreleased 0.8.3 version (as well as master). If you check out 0.8.3 at https://bitbucket.org/zzzeek/sqlalchemy/get/rel_0_8.zip this recipe will work even with primary key cols:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base, DeferredReflection

Base = declarative_base(cls=DeferredReflection)


e = create_engine("sqlite://", echo=True)
e.execute("""
    create table "user table" (
            "id col" integer primary key,
            "data col" varchar(30)
    )
""")

from sqlalchemy import event

@event.listens_for(Table, "column_reflect")
def reflect_col(inspector, table, column_info):
    column_info['key'] = column_info['name'].replace(' ', '_')

class User(Base):
    __tablename__ = "user table"

Base.prepare(e)

s = Session(e)
print s.query(User).filter(User.data_col == "some data")

DeferredReflection是一个可选的辅助函数，用于声明式 + 反射.

DeferredReflection is an optional helper to use with declarative + reflection.

这篇关于sqlalchemy - 用空格反映表和列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！