Using a blob as primary key in SQLAlchemy with MySQL Database

Question

I have a database where the primary key is supposed to be 256 bytes of data that is an ID that I get from another application.

My problem is SQLAlchemy does not output a length parameter for a primary key, even if the field it involves has it specified when using create_all.

Model

class Route(db.Model):
    assignmentId = db.Column(db.Binary(256), primary_key=True, nullable=False)

Things I have tried

• Providing mysql_length in the PrimaryKey table options, but that gets rejected and thus does not work. Error:

sqlalchemy.exc.ArgumentError: Argument 'mysql_length' is not accepted by dialect 'mysql' on behalf of <class 'sqlalchemy.sql.schema.PrimaryKeyConstraint'

• Creating Tables via manually outputting a SQL-Statement that contains the required length parameter (Works, but I would really like to avoid sidestepping the ORM like that)

• Other stuff that did not lead to anything

I know, my problem is rather specific, but any help towards a solution is really appreciated. Thanks :)

Answer 1

I tried this and it worked:

class Test(db.Model):
    __tablename__ = 'test'
    assignmentId = db.Column(db.BINARY(64), primary_key=True, nullable=False)
    name = db.Column(db.String(64))

and the result:

MariaDB [ioc_eventdb]> describe test;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| assignmentId | binary(64)  | NO   | PRI | NULL    |       |
| name         | varchar(64) | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

Answer 2

I don't have the answer with sqlalchemy. However:

Are you going to have a scenario where an external application (other than your python app) updating the data. If yes, then you would want to specify constraints in the Database.

If it is your app that will control the DB, you can always make sure you validate the input in the application before saving it to the DB.