Is sqlalchemy.types.Enum supposed to work with SQLite?

[Andrey Petrov]

I noticed a few versions ago that the Enum type has been added. Are
there any notes regarding its compatibility? I'm getting errors with
SQLite on OSX:

sqlalchemy.exc.OperationalError: (OperationalError) no such column:
'in', 'out' u"\nCREATE TABLE graph (\n\trevision_id INTEGER, \n
\ttime_created DATETIME NOT NULL, \n\ttime_updated DATETIME, \n
\tgraph_source_id INTEGER NOT NULL, \n\tdirection VARCHAR(2) NOT NULL,
\n\tremote_id VARCHAR(64) NOT NULL, \n\tPRIMARY KEY (graph_source_id,
revision_id, direction, remote_id), \n\tFOREIGN KEY(graph_source_id)
REFERENCES graph_source (id), \n\tCHECK (direction IN (['in', 'out']))
\n)\n\n" ()

Respective schema:

class Graph(BaseModel):
__tablename__ = 'graph'

revision_id = Column(types.Integer, primary_key=True)
time_created = Column(types.DateTime, default=datetime.now,
nullable=False)
time_updated = Column(types.DateTime, default=datetime.now,
onupdate=datetime.now)

graph_source_id = Column(types.Integer,
ForeignKey(GraphSource.id), nullable=False)
graph_source = orm.relationship(GraphSource)

direction = Column(types.Enum(['in','out']), nullable=False)
remote_id = Column(types.String(64), nullable=False,
primary_key=True)

Versions:

SQLAlchemy 0.6.4
SQLite 3.6.23.1
python-sqlite 2.4.1
Python 2.6.5

[Michael Bayer]

lose the [] around the 'in', 'out'

Post by Andrey Petrov
I noticed a few versions ago that the Enum type has been added. Are
there any notes regarding its compatibility? I'm getting errors with
'in', 'out' u"\nCREATE TABLE graph (\n\trevision_id INTEGER, \n
\ttime_created DATETIME NOT NULL, \n\ttime_updated DATETIME, \n
\tgraph_source_id INTEGER NOT NULL, \n\tdirection VARCHAR(2) NOT NULL,
\n\tremote_id VARCHAR(64) NOT NULL, \n\tPRIMARY KEY (graph_source_id,
revision_id, direction, remote_id), \n\tFOREIGN KEY(graph_source_id)
REFERENCES graph_source (id), \n\tCHECK (direction IN (['in', 'out']))
\n)\n\n" ()
    __tablename__ = 'graph'
    revision_id = Column(types.Integer, primary_key=True)
    time_created = Column(types.DateTime, default=datetime.now,
nullable=False)
    time_updated = Column(types.DateTime, default=datetime.now,
onupdate=datetime.now)
    graph_source_id = Column(types.Integer,
ForeignKey(GraphSource.id), nullable=False)
    graph_source = orm.relationship(GraphSource)
    direction = Column(types.Enum(['in','out']), nullable=False)
    remote_id = Column(types.String(64), nullable=False,
primary_key=True)
SQLAlchemy 0.6.4
SQLite 3.6.23.1
python-sqlite 2.4.1
Python 2.6.5

[Andrey Petrov]

Oh drat, I swear I tried that.

That worked, thanks zzzeek! <3

- shazow

Post by Michael Bayer
lose the [] around the 'in', 'out'

Post by Andrey Petrov
I noticed a few versions ago that the Enum type has been added. Are
there any notes regarding its compatibility? I'm getting errors with
'in', 'out' u"\nCREATE TABLE graph (\n\trevision_id INTEGER, \n
\ttime_created DATETIME NOT NULL, \n\ttime_updated DATETIME, \n
\tgraph_source_id INTEGER NOT NULL, \n\tdirection VARCHAR(2) NOT NULL,
\n\tremote_id VARCHAR(64) NOT NULL, \n\tPRIMARY KEY (graph_source_id,
revision_id, direction, remote_id), \n\tFOREIGN KEY(graph_source_id)
REFERENCES graph_source (id), \n\tCHECK (direction IN (['in', 'out']))
\n)\n\n" ()
    __tablename__ = 'graph'
    revision_id = Column(types.Integer, primary_key=True)
    time_created = Column(types.DateTime, default=datetime.now,
nullable=False)
    time_updated = Column(types.DateTime, default=datetime.now,
onupdate=datetime.now)
    graph_source_id = Column(types.Integer,
ForeignKey(GraphSource.id), nullable=False)
    graph_source = orm.relationship(GraphSource)
    direction = Column(types.Enum(['in','out']), nullable=False)
    remote_id = Column(types.String(64), nullable=False,
primary_key=True)
SQLAlchemy 0.6.4
SQLite 3.6.23.1
python-sqlite 2.4.1
Python 2.6.5