How to define an unsigned integer in SQLAlchemy

Question

I am migrating a portal to Flask with Flask-SQLAlchemy (MySQL). Below is the code I used to create my DB for my existing portal:

 Users = """CREATE TABLE Users(
           id INT UNSIGNED AUTO_INCREMENT NOT NULL,
           UserName VARCHAR(40) NOT NULL,
           FirstName VARCHAR(40) NOT NULL,
           LastName VARCHAR(40) NOT NULL,
           EmailAddress VARCHAR(255) NOT NULL,      
           Password VARCHAR(40) NOT NULL,    
           PRIMARY KEY (id)
           ) """

Here is how I am trying to use it in SQLAlchemy:

 class Users(db.Model):
      id           = db.Column(db.Integer, 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))

My question is, how can I make the SQLAlchemy model be specified as an unsigned integer?

Solution 1

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 INTEGER

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

Solution 2

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)

Comments

[Mark Hildreth]

I'm modifying your question to limit its scope to a single question (How do you specify an integer as unsigned). If you wish, make a second question for your second question. I would recommend "Here's my code please make suggestions" be asked on codereview.stackexchange.com.

[Mark Hildreth]

Note that I've never actually tried using an unsigned integer as a primary key with SQLAlchemy, but I see no reason why it shouldn't work.