Does BIGINT auto increment work for SQLAlchemy with sqlite?

Question

I am trying to declare a table using SQLAlchemy. I'd like to include a BIGINT auto incrementing primary key in the table. This does not seem to work with sqlite as the DB backend. On the other hand, having INTEGER auto incrementing primary key works just fine.

I read that sqlite has ROWID that is a signed bigint. But is there a way to have a BIGINT auto increment field? This way I can swap backends without worrying about db specific issues (assuming MySQL and Postgres support bigint auto incrementing fields).

Thanks.

honestduane · Accepted Answer · 2014-04-19 21:01:59Z

For others who get here via Google and just need a solution I have written the following code:

# SQLAlchemy does not map BigInt to Int by default on the sqlite dialect. # It should, but it doesnt. from sqlalchemy import BigInteger from sqlalchemy.dialects import postgresql, mysql, sqlite BigIntegerType = BigInteger() BigIntegerType = BigIntegerType.with_variant(postgresql.BIGINT(), 'postgresql') BigIntegerType = BigIntegerType.with_variant(mysql.BIGINT(), 'mysql') BigIntegerType = BigIntegerType.with_variant(sqlite.INTEGER(), 'sqlite')

This will allow you to use BIGINT on a database, and INT for when you run unit tests.

Specifying postgresql and mysql seems to be redundant, i got this working with just BigInt = BigInteger().with_variant(sqlite.INTEGER(), 'sqlite')
But it already is explicitly a BigInteger() that you start with. Anyway whatever. I like my way ;-)

Community · Accepted Answer · 2017-05-23 12:08:47Z

Sqlite doesn't allow BIGINT used as an primary key with autoincrement.

But, due to dynamic nature of sqlite column types, you can make a backend-specific column type and use INTEGER type in case of sqlite backend, see SQLAlchemy: How to conditionally choose type for column by depending on its backend.

Hope that helps.

Collectives™ on Stack Overflow

Does BIGINT auto increment work for SQLAlchemy with sqlite?

2 Answers 2

3 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Linked

Related