带有 PostgreSQL 的 SQLAlchemy 中的 ENUM 类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28894257/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ENUM type in SQLAlchemy with PostgreSQL
提问by Ander
I'm using SQLAlchemy corewith a postgresql database and I would like to add the ENUMtype to my table definition. According to the postgresql documentation, the ENUM type must be defined prior the table is created:
我正在使用带有 postgresql 数据库的SQLAlchemy 核心，并且我想将ENUM类型添加到我的表定义中。根据postgresql 文档，必须在创建表之前定义 ENUM 类型：
CREATE TYPE gender_enum AS ENUM ('female', 'male');
CREATE TABLE person (
name VARCHAR(20),
gender gender_enum
);
The problem is when I'm creating the table definition. After reading the SQLAlchemy documentationI couldn't find any implementation example. I've tried something like this but it didn't work:
问题是当我创建表定义时。阅读SQLAlchemy 文档后，我找不到任何实现示例。我试过这样的事情，但没有奏效：
from sqlalchemy.dialects.postgresql import ENUM
person = Table('user_profile', metadata,
Column('name', String(20)),
Column('gender', ENUM('female','male'))
);
How it must be done?
必须怎么做？
回答by m1yag1
@Tim's answer is definitely correct but I wanted to offer the way I setup my ENUMs.
@Tim 的回答绝对正确，但我想提供我设置 ENUM 的方式。
In my models.py I will create the values as tuples
在我的 models.py 中，我将值创建为元组
skill_levels = ('Zero', 'A little', 'Some', 'A lot')
skill_levels = ('Zero', 'A little', 'Some', 'A lot')
Then I will create a skill_level_enum variable and assign it an ENUM class with the skill level values as args.
然后我将创建一个 Skill_level_enum 变量，并为其分配一个 ENUM 类，其中技能级别值为 args。
skill_level_enum = ENUM(*skill_levels, name="skill_level")
skill_level_enum = ENUM(*skill_levels, name="skill_level")
In my table model then I pass in the skill_level_enum
在我的表模型中，然后我传入了 Skill_level_enum
class User(db.Model):
id = db.Column(db.Integer(), primary_key=True)
skill_level = db.Column(skill_level_enum)
I have found this makes my code a lot cleaner and I'm able to make updates to the skill_levels at the top of my file rather than scanning my models for the right thing to update.
我发现这使我的代码更清晰，并且我能够更新文件顶部的 Skill_levels，而不是扫描我的模型以获取正确的更新内容。
回答by Tim Osadchiy
You need to import Enum from sqlalchemy and add a name to it. It should work like this:
您需要从 sqlalchemy 导入 Enum 并为其添加名称。它应该像这样工作：
from sqlalchemy import Enum
person = Table("user_profile", metadata,
Column("name", String(20)),
Column("gender", Enum("female", "male", name="gender_enum", create_type=False))
);
回答by Marcelo
The code below worked for me on SQLAlchemy 1.3.11 and Postgres 12.0.
下面的代码在 SQLAlchemy 1.3.11 和 Postgres 12.0 上对我有用。
You must first create the Enum type in postgres before creating the user table. This can be done directly through sql statement.
在创建用户表之前，您必须首先在 postgres 中创建 Enum 类型。这可以直接通过sql语句完成。
CREATE TYPE permission AS ENUM ('READ_ONLY', 'READ_WRITE', 'ADMIN', 'OWNER');
Then set up the project model
然后设置项目模型
from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()
class User(db.Model):
__tablename__ = 'user'
user_id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(120), unique=True, nullable=False)
password = db.Column(db.String(200), nullable=False)
access = db.Column(db.Enum('READ_ONLY', 'READ_WRITE', 'ADMIN', 'OWNER', name="permission"))
回答by Nick
You can use Python's native enumsas the column type too:
您也可以使用Python 的本机枚举作为列类型：
from flask_sqlalchemy import SQLAlchemy
sqlalchemy.dialects.postgresql import ENUM as pgEnum
from enum import Enum, unique
@unique
class errorTypeEnum(Enum):
videoValidation = 'videoValidation'
audioValidation = 'audioValidation'
subtitleValidation = 'subtitleValidation'
db = SQLAlchemy()
class Error(db.Model):
serviceID = db.Column(db.String(20), primary_key=True)
timestamp = db.Column(db.DateTime, unique=False, nullable=False)
category = db.Column(pgEnum(errorTypeEnum), unique=False, nullable=False)
