Pylons, SQlite and autoincrementing fields

Question

Hey! Just started working with Pylons in conjunction with SQLAlchemy, and my model looks something like this:

from sqlalchemy import Column
from sqlalchemy.types import Integer, String

from helloworld.model.meta import Base

class Person(Base):
    __tablename__ = "person"

    id = Column(Integer, primary_key=True)
    name = Column(String(100))
    email = Column(String(100))

    def __init__(self, name='', email=''):
        self.name = name
        self.email = email

    def __repr__(self):
        return "<Person('%s')" % self.name

To avoid sqlite reusing id's that might have been deleted, I want to add AUTOINCREMENT to the column "id". I've looked through the documentation for sqlalchemy and saw that the sqlite_autoincrement can be issued. An example where this attribute is given can be found here.

sqlite_autoincrement seems though to be issued when creating the table itself, and I just wondered how it can be supplied when using a declarative style of the model such as mine.

Answer 1

Try including a __table_args__ attribute with the arguments you would pass to Table constructors in the traditional (non-declarative) data definition style, e.g.:

class Person(Base):
    __tablename__ = "person"
    __table_args__ = {'sqlite_autoincrement': True}

If you have to include several arguments, use this form instead (dict has to be last):

__table_args__ = (
    Unique('foo'),
    # ...
    {'sqlite_autoincrement': True}
)

From the Table configuration section of the Declarative SQLAlchemy documentation:

Table arguments other than the name, metadata, and mapped Column arguments are specified using the __table_args__ class attribute. This attribute accommodates both positional as well as keyword arguments that are normally sent to the Table constructor.