Does BIGINT auto-increment work for SQLAlchemy using sqlite?

Question

I am trying to declare a table using SQLAlchemy. I would like to include in the table BIGINT auto incrementing primary key. This does not seem to work with sqlite as a database database. On the other hand, having INTEGER primary key auto-increment works fine.

I read that sqlite has a ROWID, which is a signed bigint. But is there a way to get the BIGINT auto-zoom field? This way, I can swap places without worrying about specific problems with db (assuming MySQL and Postgres support bigint automatically incrementing fields).

Thanks.

Answer 1

Sqlite does not allow using BIGINT as a primary key with auto-increments.

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

Hope this helps.

Answer 2

For others who get here through Google and just need a solution, I wrote 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 in the database and INT to run unit tests.