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?
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.
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.
PickleType. Holds Python objects, which are serialized using pickle.
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.
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))
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With