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?

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
– Mark Hildreth, Commented Oct 31, 2013 at 23:19

Mark Hildreth · Accepted Answer · 2017-08-02 21:53:16Z

58

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

edited Aug 2, 2017 at 21:53

answered Oct 31, 2013 at 23:31

Mark Hildreth

43.3k12 gold badges125 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mark Hildreth Over a year ago

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.

marcoooo · Accepted Answer · 2018-12-18 16:25:49Z

14

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)

answered Dec 18, 2018 at 16:25

marcoooo

4274 silver badges11 bronze badges

2 Comments

chrisinmtown Over a year ago

Why use both Integer and INTEGER? It sure is not intuitive that I can just write code that assigns to UnsignedInt twice as shown and it will work fine when the engine is MySQL (when deployed for real) or SQLite (when running tox tests).

Sean Champ Over a year ago

This variant syntax really simplifies the definition, I almost wish they'd mentioned this somewhere in the ORM docs ... LoL. Using this with uint64 (mariadb uint32?) storage for numpy datetime64[ns] with translation via TypeDecorator

Collectives™ on Stack Overflow

How to define an unsigned integer in SQLAlchemy

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related