How to define an unsigned integer in SQLAlchemy
SQLAlchemy types (such as Integer) seem to try to abide by the standard SQL data types. Since an "unsigned integer" is not a standard data type, you won't see something like an UnsignedInteger
or Integer(unsigned=True)
.
In cases such as these (where a database such as MySQL has a data type that is itself not a standard data type or has options that are not standard) you can access these types/options by getting dialect-specific types. For MySQL, you can access these types through the sqlalchemy.dialects.mysql
module, like so...
from sqlalchemy.dialects.mysql import INTEGERclass Users(db.Model): id = db.Column(INTEGER(unsigned=True), primary_key=True) UserName = db.Column(db.String(40)) FirstName = db.Column(db.String(40)) LastName = db.Column(db.String(40)) EmailAddress = db.Column(db.String(255)) Password = db.Column(db.String(40))
May be very late but if you want your model classes to be able to manage multiple database engine such as MySQL and SqlLite (for instance during unit testing). What you can do is :
UnsignedInt = Integer()UnsignedInt = UnsignedInt.with_variant(INTEGER(unsigned=True), 'mysql')
And in your Declarative models:
class Meta(Base): __tablename__ = 'meta' meta_id = Column(UnsignedInt, primary_key=True) meta_key = Column(String(64), nullable=False, ) meta_value = Column(String(128)) species_id = Column(UnsignedInt)