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

Bhakta Raghavan · Accepted Answer · 2017-11-09 10:20:19Z

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)

answered Nov 9, 2017 at 10:20

Bhakta Raghavan

7259 silver badges18 bronze badges

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

1 Comment

Andreas Over a year ago

Thanks for helping me again! Sadly your approach only works for Columns of lengths below 255 characters, as BINARY does not allow more, while my IDs are 256 chars long, requiring BLOB :(

Bhakta Raghavan · Accepted Answer · 2017-11-09 08:33:29Z

0

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.

answered Nov 9, 2017 at 8:33

Bhakta Raghavan

7259 silver badges18 bronze badges

1 Comment

Andreas Over a year ago

I also check the validity in the application, but the primary key constraint is supposed to act as a safety net, and was specified so I will probably have to run with it.

Collectives™ on Stack Overflow

Using a blob as primary key in SQLAlchemy with MySQL Database

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related