存储ENUM值的PostgreSQL ARRAY [英] Storing a PostgreSQL ARRAY of ENUM values
问题描述
我有一个表,其状态可以为:
I have a table that can have a status:
statuses = ['unmoderated', 'nominee', 'finalist', 'winner']
status = db.Enum(
*statuses, name='enum_nomination_status', metadata=db.metadata)
class Nomination(db.Model):
status = db.Column(status, default='unmoderated')
我现在想要一个表,该表的列可以包含多个状态:
I would now like to have a table that has a column that can contain multiple statuses:
class Judge(db.Model):
statuses = db.Column(ARRAY(status, dimensions=1))
但是,上述方法将我引向该错误:
However the above approach leads me to this error:
ProgrammingError: (psycopg2.ProgrammingError) column "statuses" is of type enum_nomination_status[] but expression is of type text[]
LINE 1: ...4, 'Name', ARRAY['unm...
^
HINT: You will need to rewrite or cast the expression.
所以我试图创建一个自定义类型,将其强制转换为枚举类型:
So I tried to create a custom type that did the cast to the enum type:
class STATUS_ARRAY(TypeDecorator):
impl = ARRAY(status, dimensions=1)
def process_bind_param(self, value, dialect):
if value is None:
return value
else:
return cast(array(value), ARRAY(status, dimensions=1))
但这会导致段错误。
我也尝试过投射单个项目:
I've also tried casting the individual items:
class STATUS_ARRAY(TypeDecorator):
impl = ARRAY(status, dimensions=1)
def process_bind_param(self, value, dialect):
if value is None:
return value
else:
return array(cast(s, status) for s in value)
但我得到:
ProgrammingError: (psycopg2.ProgrammingError) can't adapt type 'Cast' [SQL: 'INSERT INTO judge (statuses) VALUES (%(statuses)s)'] [parameters: {'statuses': [<sqlalchemy.sql.elements.Cast object at 0x7fc8bb69c710>]}]
我承认我主要是在尝试各种不同的投射组合,而并不真正了解引擎盖下的情况。我尝试查看底层的ENUM实现,以查看是否可以在不进行强制转换的情况下获得某种本机枚举类型,但看不到任何东西。
I admit that I'm mostly trying different combinations of casting things without really knowing what's going on underneath the hood. I tried looking at the underlying ENUM implementation to see if I could get at some kind of native enum type without casting but I couldn't see anything. I'm grasping at straws.
感谢您的帮助:)
推荐答案
我查看了问题3467 由Wichert Akkerman发布,并且此替代方法已发布。感谢Mike Bayer。在代码中声明以下类(当然,带有必要的导入):
I looked at Issue 3467 posted by Wichert Akkerman, and this work-around was posted. Credit to Mike Bayer. Declare the following class in your code (with the necessary imports, of course):
from sqlalchemy.dialects.postgresql import ARRAY
from sqlalchemy import cast
class ArrayOfEnum(ARRAY):
def bind_expression(self, bindvalue):
return cast(bindvalue, self)
def result_processor(self, dialect, coltype):
super_rp = super(ArrayOfEnum, self).result_processor(dialect, coltype)
def handle_raw_string(value):
if value==None:
return []
inner = re.match(r"^{(.*)}$", value).group(1)
return inner.split(",")
def process(value):
return super_rp(handle_raw_string(value))
return process
ArrayOfEnum
现在是在模型定义中使用的特殊列类型。
ArrayOfEnum
is now a special column type that gets used in the model definition.
所以代替
class Judge(db.Model):
statuses = db.Column(ARRAY(status))
现在您可以执行以下操作:
Now you can do:
class Judge(db.Model):
statuses = db.Column(ArrayOfEnum(status))
现在,您可以在代码中为状态分配值
和一个列表,保存后将进行正确的转换:
Now in your code you can assign values to statuses
with a list and it will do the proper casting upon saving:
my_judge_object.status = ['unmoderated', 'nominee']
这篇关于存储ENUM值的PostgreSQL ARRAY的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!