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-16T11:11:33.123

45

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 '18 at 11:11

answered Mar 06 '15 at 07:54

Timur Osadchiy

5,699
2
26
28

17

For this to work with postgres for me, I had to `from sqlalchemy.dialects.postgresql import ENUM` – miah Dec 14 '16 at 22:55
1

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? – dwanderson Jul 20 '18 at 22:52
7

Why: `create_type=False`? – Hans Bouwmeester Oct 05 '19 at 19:18
9

`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` – kevlarr Jan 17 '20 at 18:10
1

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=` to `Enum`. `Enum` type documentation: https://docs.sqlalchemy.org/en/13/core/type_basics.html#sqlalchemy.types.Enum.__init__ – lv10 Jul 06 '20 at 20:36
thank you @lv10 - adding `schema=` to the Enum fixes it for me: also for table creation, it now properly issues a CREATE TYPE. – Robert Muil Aug 17 '20 at 17:46

score 38 · Answer 2 · edited Jan 02 '17 at 18:51

@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.

That is correct, you can define the enum once and use it wherever you need to. — m1yag1, Aug 18 '16 at 22:15

score 12 · Answer 3 · edited Oct 20 '21 at 20:55

You can use Python's native enums as the column type as well:

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)

score 3 · Answer 4 · answered Dec 26 '19 at 18:57

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

ENUM type in SQLAlchemy with PostgreSQL

4 Answers4

Linked