SQLAlchemy 确定是否存在唯一约束 [英] SQLAlchemy Determine If Unique Constraint Exists
问题描述
我有一个要在其上运行验证的 SQLAlchemy 模型.验证的一部分是确保在(少数)列上存在 UniqueConstraint.我知道列是什么.有没有用 SQLAlchemy 做到这一点的好方法?我使用的底层数据库是 MySQL.
I have a SQLAlchemy model on which I want to run validation. Part of the validation is to ensure a UniqueConstraint exists on a (few) column(s). I know what the columns are. Is there a good way to do this with SQLAlchemy? The underlying database I am using is MySQL.
推荐答案
你可以使用 SQLalchemy 反射 API.
为了获得唯一约束,发出 get_unique_constraints.
In order to get the unique constraints, issue a get_unique_constraints.
主键是唯一的,因此您必须发出 get_pk_constraint 也是.
Primary keys are unique, so you must issue a get_pk_constraint too.
表格创建方式:
CREATE TABLE user (
id INTEGER NOT NULL,
name VARCHAR(255),
email VARCHAR(255),
login VARCHAR(255),
PRIMARY KEY (id),
UNIQUE (email),
UNIQUE (login)
)
示例:
from sqlalchemy import create_engine
from sqlalchemy.engine.reflection import Inspector
# engine = create_engine(...)
insp = Inspector.from_engine(engine)
print "PK: %r" % insp.get_pk_constraint("user")
print "UNIQUE: %r" % insp.get_unique_constraints("user")
输出:
PK: {'name': None, 'constrained_columns': [u'login']}
UNIQUE: [{'column_names': [u'email'], 'name': None}, {'column_names': [u'login'], 'name': None}]
您可以通过以下方式验证唯一约束:
You can verify the unique constraints by:
pk = insp.get_pk_constraint("user")['constrained_columns']
unique = map(lambda x: x['column_names'], insp.get_unique_constraints("user"))
for column in ['name', 'id', 'email', 'login']:
print "Column %r has an unique constraint: %s" %(column, [column] in [pk]+unique)
输出:
Column 'name' has an unique constraint: False
Column 'id' has an unique constraint: True
Column 'email' has an unique constraint: True
Column 'login' has an unique constraint: True
更新 01
上面的代码只检查已经创建的表的列的约束,如果要在创建前检查列更简单:
Update 01
The code above only check constraint for columns of an already created table, if you want to inspect the columns before the creation is more simple:
from sqlalchemy import create_engine, Column, types
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, scoped_session
Base = declarative_base()
class User(Base):
__tablename__ = "user"
id = Column(types.Integer, primary_key=True)
name = Column(types.String(255))
email = Column(types.String(255), unique=True)
login = Column(types.String(255), unique=True)
# do not create any table
#engine = create_engine('sqlite:///:memory:', echo=True)
#session = scoped_session(sessionmaker(bind=engine))
#Base.metadata.create_all(engine)
# check if column is (any) a primary_key or has unique constraint
# Note1: You can use User.__table__.c too, it is a alias to columns
# Note2: If you don't want to use __table__, you could use the reflection API like:
# >>> from sqlalchemy.inspection import inspect
# >>> columns = inspect(User).columns
result = dict([(c.name, any([c.primary_key, c.unique])) for c in User.__table__.columns])
print(result)
输出:
{'email': True, 'login': True, 'id': True, 'name': False}
如果你只想检查一些列,你只能这样做:
If you want to check only some columns, you could only do:
for column_name in ['name', 'id', 'email', 'login']:
c = User.__table__.columns.get(column_name)
print("Column %r has an unique constraint: %s" %(column_name, any([c.primary_key, c.unique])))
输出:
Column 'name' has an unique constraint: False
Column 'id' has an unique constraint: True
Column 'email' has an unique constraint: True
Column 'login' has an unique constraint: True
这篇关于SQLAlchemy 确定是否存在唯一约束的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!