On Fri, 2006-12-01 at 03:28 +0000, Lee McFadden wrote: > On 12/1/06, iain duncan <[EMAIL PROTECTED]> wrote: > > > > Does sqlalchemy not do enums? Are enums bad? > > > > They aren't *bad* per se, but they're not (afaik) ANSI SQL and are > (again, afaik) a MySQL feature. I would just emulate an enum but > modifying the object in SA (or SO for that matter) and raise a > ValueError if you try to set the attribute to anything other than what > you have in your 'enum' list. >
> example_table = Table("examples", metadata,
> Column('id', Integer, primary_key=True),
> Column('data', String(50))
> )
> class Example(object):
> def _set_data(self, data):
> enum_vals['valid', 'data', 'for', 'enum']
> if data not in enum_vals:
> raise ValueError("'%s' is not a valid data value" % data)
> self._data = data
>
> def _get_data(self):
> return self._data
>
> data = property(_get_data, _set_data)
>
> assign_mapper(session.context, Example, example_table,
> properties={
> '_data': example_table.c.data
> }
> )
>
I've used two other methods to emulate enums:
check constraints and foreign keys.
Note that I know that the foreign key method works in postgresql and
fails in sqlite. I assume this is because postgresql itself checks
foreign keys while sqlite doesn't. CheckConstraints work in both. I
haven't tested against other db backends.
Attaching a short example.
-Toshio
#!/usr/bin/python -tt
from sqlalchemy import *
#db = create_engine('postgres://%s:[EMAIL PROTECTED]/%s' % (user, pass, host, dbName))
db = create_engine('sqlite://')
metadata = BoundMetaData(db)
checkTable = Table('cexample', metadata,
Column('id', Integer, primary_key=True),
Column('status', String),
CheckConstraint("status = 'active' or status = 'passive'")
)
enumTable = Table('enumTable', metadata,
Column('name', String, primary_key=True)
)
foreignTable = Table('fexample', metadata,
Column('id', Integer, primary_key=True),
Column('status', String, ForeignKey('enumTable.name'))
)
checkTable.create(checkfirst=True)
enumTable.create(checkfirst=True)
foreignTable.create(checkfirst=True)
enumTable.insert().execute(name='useful for')
enumTable.insert().execute(name='user defined')
enumTable.insert().execute(name='enumerations')
print 'checkTable should succeed:',
try:
checkTable.insert().execute(status='active')
except:
print 'Fail'
else:
print 'Pass'
print 'checkTable should raise:',
try:
checkTable.insert().execute(status='gobbledygook')
except Exception, e:
print 'Pass'
print 'Exception was:', e
else:
print 'Fail'
print 'foreignTable should succeed:',
try:
foreignTable.insert().execute(status='enumerations')
except:
print 'Fail'
else:
print 'Pass'
print 'foreignTable should raise:',
try:
foreignTable.insert().execute(status='gobbledygook')
except Exception, e:
print 'Pass'
print 'Exectpion was:', e
else:
print 'Fail'
signature.asc
Description: This is a digitally signed message part
