How to know is reflected column has autoincrement

[Kotofos online]

Hi, i'm having a troubles detecting auto-increment on reflected tables across different db types.

There is same manually created table in multiple databases of different type:

sqlite3 test.db 'create table project (id int primary key not null)'

Then reflect it. After reflection, I can check for column attribute autoincrement.

But sometimes column.autoincrement is 'auto' instead of true/false.

from sqlalchemy import create_engine

from sqlalchemy.ext.automap import automap_base

engine = create_engine('sqlite:///../test.db')
Base = automap_base()
Base.prepare(engine, reflect=True)

cls = Base.classes['project']
print cls.id.prop.columns[0].autoincrement

In my case for db2, oracle, sqlite it is 'auto'. For mysql, mssql, postglesql it is false.

As I saw in documentation, this 'auto' is useful when defining tables manually. But what it does on reflected tables?.

The default value is the string "auto" which indicates that a single-column primary key that is of an INTEGER type with no stated client-side or python-side defaults should receive auto increment semantics automatically; all other varieties of primary key columns will not. This includes that DDL such as PostgreSQL SERIAL or MySQL AUTO_INCREMENT will be emitted for this column during a table create, as well as that the column is assumed to generate new integer primary key values when an INSERT statement invokes which will be retrieved by the dialect.
https://docs.sqlalchemy.org/en/13/core/metadata.html#sqlalchemy.schema.Column.params.autoincrement

So, how to properly check is column auto incremented or not?

[Mike Bayer]

"autoincrement" is a concept that exists at multiple levels for some backends so depending on what you're trying to do, it is not necessarily straightforward.  For example, Postgresql has only a partial concept of "autoincrement" and Oracle has none.   At the database level, some backends have an actual "autoincrement" keyword, such as MySQL and SQLite, but it means different things.

SQLAlchemy's ".autoincrement" flag therefore has to accommodate for all of these concepts generically and the best it can do is represent what we know to be the intent of this setting for a particular table, or not.

What is likely producing ambiguity here is that autoincrement="auto" means that the "autoincrement" of this column cannot be determined without taking into account the backend where an INSERT statement for the table is used.

For example, if I have the table:

 CREATE TABLE foo (

    id INTEGER PRIMARY KEY,

   value VARCHAR(20)

)

on SQLite, the above table will behave in an autoincrementing fashion, implicitly.   However, if we create the identical table on Oracle, it will not.    So when this table is reflected on SQlite, autoincrement is "auto", meaning, "it could be the table creator's intent that this column was autoincrement, but we don't really know".       On Oracle, there is no "autoincrement" concept at all so no intent can be determined.

OTOH, it we reflect the above "CREATE TABLE" from PostgreSQL, the SERIAL keyword isn't used, so we know that the *intent* of the column is to *not* be autoincrement.  If we reflected it from MySQL, there's no "AUTO INCREMENT" keyword, so again we know the *intent* is that this table is not supposed to have autoincrement.  So it comes out as False.   That is, on both of these backends, we know that the "autoincrement" keyword *had* to be False in order for this CREATE TABLE to have been emitted by SQLAlchemy.

SQLAlchemy considers "autoincrement" to therefore be an "emergent" property of a table based on the state of the table and the backend in use at the time that an INSERT statement is compiled, including:

1. is the column integer derived

2. is the column part of the primary key constraint

3. is the PK constraint *only* this column, or if it is composite, is autoincrement explicitly True

4. is the column free of any FOREIGN KEY constraints to other columns / tables

5. will SQLAlchemy be able to get the backend to generate a new value for this column

To get the answers to #1 - #4, SQLAlchemy uses the table._autoincrement_column attribute to determine if the answer to all four is "yes".  If this attribute refers to a Column, the answer is "yes", and if not, the answer is "no".

Then to get the answer to #5, that's based on a larger series of questions, which are expressed in code in  sqlalchemy/sql/crud.py and are by this point very complicated.    Here's a quick paraphrase:

5a. If the dialect.postfetch_lastrowid flag is True, that generally means it's SQLite / MySQL style autoincrement , the column will be autoincrementing, and we can get the new value via cursor.lastrowid.

5b. if the database supports sequences and the dialect.sequences_optional flag is True,  and dialect.postfetch_lastrowid is False, that means PostgreSQL style where we can make use of SERIAL, therefore "implicit" autoincrement, but we need to use RETURNING to get the value back.

5c. if the DB dialect.supports_sequences is True and dialect.sequences_optional is False then it's an Oracle / DB2 situation where there needs to be a Sequence() object on the column for the compiler and we need to either execute the sequence ahead of time to get the value or embed it inline into the INSERT and use RETURNING to get the new value back.

All of the above and much more are how a Table() object in SQLAlchemy when configured correctly never fails to get an autoincrementing primary key on any backend in the most efficient way possible.   To that end, the column.autoincrement flag is very much a flag that is designed to allow the user to tell SQLAlchemy a very specific thing about a user-defined Table.  It is not intended  for SQLAlchemy to tell the *user* much at all.   It can do so in those cases where it can, e.g. for a backend that has an explicit "autoincrement" concept like MySQL where the presence or the absense of it implies user intent, but it can't derive this from a schema-reflected table generically.

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/b3dbc610-bf23-415a-b130-31b41b190420%40googlegroups.com.

[Kotofos online]

Hi, thanks for the detailed reply.

My task was to detect the required fields to generate the schema. It looks like it is better to not require field when autoincrement is 'auto'. (And of course, take into account client/server-side defaults)