sqlalchemy.schema.Column defaulting to autoincrement=True

[Bryan Small]

Toying around with sqlalchemy, and introspection, I'm having a hard time accurately identifying if a column is autoincrement. For example:

bsmall@localhost:~/$ cat alchemist.py
from sqlalchemy import create_engine, Column, Integer, String, MetaData
from sqlalchemy.ext.declarative import declarative_base

base = declarative_base()

class Alchemist(base):

    __tablename__ = 'Charlatans'

    id = Column(Integer(unsigned = True), primary_key = True)
    counter = Column(Integer(unsigned = True))


bsmall@localhost:~/$ python
Python 2.7.3 (default, Jan  2 2013, 13:56:14) 
[4.7.2](GCC) on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlalchemy
>>> sqlalchemy.__version__
'0.7.8'
>>> from alchemist import Alchemist
>>> Alchemist.counter.property.columns
[Integer(), table=<Charlatans>)](Column('counter',)
>>> Alchemist.counter.property.columns[0](0).autoincrement
True

At the end of the day, my goal is to look through the class attributes, identifying table columns. I'll create a {{{ MyMixin.init }}} that automatically knows which columns are required and which aren't; atleast for my purposes.

When plugging in an engine, like mysql, the resulting SQL does NOT describe the column as AUTO_INCREMENT, so this isn't critical. Though it's misleading for someone working with the API.

Can someone confirm that this attribute is meant to be set to autoincrement?

-Ben

[Mike Bayer]

"autoincrement" by itself does not mean "AUTO_INCREMENT" will be applied. It's default is True for all columns and only has meaning in some situations.

"autoincrement" may or may not be set during the reflection process, though is not supported by all backends. In this case it doesn't seem reflection is being used, and I don't see the bug here, reopen if there's something actually unexpected going on, given the documentation below:

http://docs.sqlalchemy.org/en/rel_0_8/core/schema.html#sqlalchemy.schema.Column.__init__

autoincrement –

This flag may be set to False to indicate an integer primary key column that should not be considered to be the “autoincrement” column, that is the integer primary key column which generates values implicitly upon INSERT and whose value is usually returned via the DBAPI cursor.lastrowid attribute. It defaults to True to satisfy the common use case of a table with a single integer primary key column. If the table has a composite primary key consisting of more than one integer column, set this flag to True only on the column that should be considered “autoincrement”.

The setting only has an effect for columns which are:

    * Integer derived (i.e. INT, SMALLINT, BIGINT).

        * Part of the primary key

    * Are not referenced by any foreign keys, unless the value is specified as 'ignore_fk'


The setting has these two effects on columns that meet the above criteria:

    * DDL issued for the column will include database-specific keywords intended to signify this column as an “autoincrement” column, such as AUTO INCREMENT on MySQL, SERIAL on Postgresql, and IDENTITY on MS-SQL. It does not issue AUTOINCREMENT for SQLite since this is a special SQLite flag that is not required for autoincrementing behavior. See the SQLite dialect documentation for information on SQLite’s AUTOINCREMENT.

    * The column will be considered to be available as cursor.lastrowid or equivalent, for those dialects which “post fetch” newly inserted identifiers after a row has been inserted (SQLite, MySQL, MS-SQL). It does not have any effect in this regard for databases that use sequences to generate primary key identifiers (i.e. Firebird, Postgresql, Oracle).