Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to define an unsigned integer in SQLAlchemy

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?

like image 708
Ethan Avatar asked Oct 31 '13 22:10

Ethan


People also ask

Does SQLAlchemy require primary key?

The SQLAlchemy ORM, in order to map to a particular table, needs there to be at least one column denoted as a primary key column; multiple-column, i.e. composite, primary keys are of course entirely feasible as well.

What is nullable SQLAlchemy?

From SQLAlchemy docs: nullable – If set to the default of True, indicates the column will be rendered as allowing NULL, else it's rendered as NOT NULL. This parameter is only used when issuing CREATE TABLE statements.

What is PickleType?

PickleType. Holds Python objects, which are serialized using pickle.

Can SQLAlchemy be used without Flask?

One of the most sought after helpers being the handling of a database connection across the app. However, ensuring your database connection session is available throughout your app can be accomplished with base SQLAlchemy and does not require Flask-SQLAlchemy.


2 Answers

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))
like image 84
Mark Hildreth Avatar answered Oct 20 '22 15:10

Mark Hildreth


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)
like image 44
marcoooo Avatar answered Oct 20 '22 15:10

marcoooo