Join the Stack Overflow Community
Stack Overflow is a community of 6.7 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 5 down vote favorite
2

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 the table is 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 example. 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?

share|improve this question
up vote 9 down vote accepted

You need to import Enum from a 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))
);
share|improve this answer
1  
For this to work with postgres for me, I had to from sqlalchemy.dialects.postgresql import ENUM – miah Dec 14 '16 at 22:55
up vote 11 down vote

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

share|improve this answer
    
can you re-use same enum definion in many tables this way? – filiprem Aug 18 '16 at 19:54
    
That is correct, you can define the enum once and use it wherever you need to. – m1yag1 Aug 18 '16 at 22:15

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.