16

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.

Overclocked
  • 1,187
  • 1
  • 11
  • 22

2 Answers2

25

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.

honestduane
  • 579
  • 6
  • 13
  • 7
    Specifying postgresql and mysql seems to be redundant, i got this working with just `BigInt = BigInteger().with_variant(sqlite.INTEGER(), 'sqlite')` – robru Oct 09 '15 at 22:19
  • 1
    Its better to be explicit then it is to be implicit. – honestduane Oct 12 '15 at 02:45
  • 2
    But it already is explicitly a `BigInteger()` that you start with. Anyway whatever. I like my way ;-) – robru Oct 14 '15 at 04:08
19

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.

Community
  • 1
  • 1
alecxe
  • 462,703
  • 120
  • 1,088
  • 1,195