CODEWITHSUNDEEP
pythonsqlitesqlalchemy
Vlad Vladimir Hercules
Vlad Vladimir Hercules

Reputation: 1859

Why SQLAlchemy not adding auto increment?

Introduction

I am running pytest to perform some database integration tests. I have a fixture which is run for scope='session' to setup connection and run migrations for models.

Issue

Create statements are being run, but, create table statement is missing autoincrement condition.

I have the following model:

class MyTable(Base):
    __tablename__ = 'my_table'
    id = Column("id", SmallInteger, primary_key=True, autoincrement=True)
    name = Column(String(255))

I have defined autoincrement yet I've read that there is no need to do that...

However, when I run Base.metadata.create_all(engine), the following SQL is executed when I run pytest -vv:

CREATE TABLE my_table (
        id SMALLINT NOT NULL, 
        name VARCHAR(255), 
        PRIMARY KEY (id)
)

Upvotes: 1

Views: 299

Answers (1)

snakecharmerb
snakecharmerb

Reputation: 55600

Sqlite's autoincrementing behaviour only works for the INTEGER type - it doesn't work with other integer types (such as SMALLINT).

To get the autoincrementing behaviour using SQLAlchemy you must either:

  • use the Integer type
    id = sa.Column(sa.Integer, primary_key)
  • use TypeEngine.with_variant()
id = sa.Column(sa.SmallInteger().with_variant(sa.Integer, 'sqlite'), primary_key=True)
  • subclass SmallInteger to override the DDL output
from sqlalchemy import SmallInteger
from sqlalchemy.ext.compiler import compiles

class SLSmallInteger(SmallInteger):
    pass

@compiles(SLSmallInteger, 'sqlite')
def bi_c(element, compiler, **kw):
    return "INTEGER"

@compiles(SLSmallInteger)
def bi_c(element, compiler, **kw):
    return compiler.visit_SMALLINT(element, **kw)

The final example is untested, but based on the example code in the docs for the Sqlite dialect, which discuss Sqlite autoincrementing behaviour in detail.

Upvotes: 2

Related Questions