ENUM type in SQLAlchemy with PostgreSQL

Question

I'm using SQLAlchemy core with a postgresql database and I would like to add the ENUM type to my table definition. According to the postgresql documentation, the ENUM type must be defined prior to the table being created:

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 documentation I couldn't find any implementation examples. I've tried something like this but it didn't work:

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?

Timur Osadchiy · Accepted Answer · 2018-03-16 11:11:33Z

49

You need to import Enum from sqlalchemy and add a name to it. It should work like this:

from sqlalchemy import Enum

person = Table("user_profile", metadata,
    Column("name", String(20)),
    Column("gender", Enum("female", "male", name="gender_enum", create_type=False))
);

edited Mar 16, 2018 at 11:11

answered Mar 6, 2015 at 7:54

Timur Osadchiy

6,2994 gold badges28 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

miah Over a year ago

For this to work with postgres for me, I had to from sqlalchemy.dialects.postgresql import ENUM

dwanderson Over a year ago

Well which is it? OP said postgreql.ENUM didn't work, comment says it does work. Is there a version or driver distinction that causes the discrepancy?

Hans Bouwmeester Over a year ago

Why: create_type=False?

kevlarr Over a year ago

create_type isn't actually useful here - that's for sqlalchemy.dialects.postgresql.ENUM. In a nutshell - use sqlalchemy.Enum if you want to define the enum type and create it during a create_table command. If you want to use an existing type for a new table during creation, use the dialects.postgresql.ENUM one with create_type = False

lv10 Over a year ago

In case anyone is using specific schemas, I had the same issue and I was able to solve it using from sqlalchemy import Enum and for the schema I was able to pass schema=<schema_name> to Enum. Enum type documentation: docs.sqlalchemy.org/en/13/core/…

|

Slater Victoroff · Accepted Answer · 2017-01-02 18:51:30Z

40

@Tim's answer is definitely correct but I wanted to offer the way I setup my ENUMs.

In my models.py I will create the values as tuples

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(*skill_levels, name="skill_level")

In my table model then I pass in the 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.

edited Jan 2, 2017 at 18:51

Slater Victoroff

22k23 gold badges92 silver badges149 bronze badges

answered Oct 9, 2015 at 15:41

m1yag1

8397 silver badges11 bronze badges

1 Comment

m1yag1 Over a year ago

That is correct, you can define the enum once and use it wherever you need to.

Nick · Accepted Answer · 2024-02-21 01:04:09Z

14

You can use Python's native enums as the input for a column type:

from flask_sqlalchemy import SQLAlchemy
from 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)

edited Feb 21, 2024 at 1:04

answered May 8, 2020 at 23:27

Nick

3,4343 gold badges41 silver badges54 bronze badges

Comments

Marcelo · Accepted Answer · 2019-12-26 18:57:58Z

4

The code below worked for me on SQLAlchemy 1.3.11 and 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.

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"))

answered Dec 26, 2019 at 18:57

Marcelo

3873 silver badges7 bronze badges

Collectives™ on Stack Overflow

ENUM type in SQLAlchemy with PostgreSQL

4 Answers 4

6 Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related