CODEWITHSUNDEEP
postgresqlenumssqlalchemy
Ander
Ander

Reputation: 5644

ENUM type in SQLAlchemy with PostgreSQL

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?

Upvotes: 45

Views: 40873

Answers (4)

Nick
Nick

Reputation: 3374

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)

Upvotes: 13

Marcelo
Marcelo

Reputation: 377

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

Upvotes: 4

Timur Osadchiy
Timur Osadchiy

Reputation: 6209

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

Upvotes: 48

m1yag1
m1yag1

Reputation: 829

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

Upvotes: 39

Related Questions