CODEWITHSUNDEEP
pythonsqlalchemyprimary-keyidentity
Andrea Falco
Andrea Falco

Reputation: 59

create column not primary key but identity on sqlalchemy for sql server

I've created this code to create a column with identity on sql server, but where this column isn't a primary key.

I need to have name as primary key and id as identity column but is not primary key. The problem is that I don't know how to do it using sqlalchemy. I tried to put auto_increment on "id" but it doesn't work.

def change_pk(table, sql_table_name, list_pk_column):
    str_pk_column = ""
    for item in list_pk_column:
        str_pk_column += item + ","
    str_pk_column = str_pk_column[:-1]
    event.listen(
        table,
        "after_create",
        DDL(
            """ALTER TABLE {0} DROP CONSTRAINT {0}_id;
                 ALTER TABLE {0} ADD CONSTRAINT {0}_un_id UNIQUE (id);
                 ALTER TABLE {0} ADD CONSTRAINT {0}_pk PRIMARY KEY ({1})""".format(
                sql_table_name, str_pk_column
            )
        ),
    )


msg_users = sa.Table(
    "Msg_Users",
    metadata,
    sa.Column("id", sa.Integer, info={}),
    sa.Column("name", sa.Unicode(50), nullable=False, info={}),
    sa.Column("mobile", sa.Unicode(15), info={}),
    sa.Column("email", sa.Unicode(80), info={}),
    sa.Column(
        "last_update",
        sa.DateTime,
        server_default=sa.func.current_timestamp(),
        server_onupdate=sa.func.current_timestamp(),
        info={},
    ),
    sa.PrimaryKeyConstraint("id", name="Msg_Users_id"),
    info={},
    autoload=aload,
)

change_pk(msg_users, "Msg_Users", ["name"])

Is there a simpler way?

Upvotes: 1

Views: 5752

Answers (2)

badger0053
badger0053

Reputation: 1209

You can create your own user defined type to implement this.

class IdentityType(types.UserDefinedType):
    def get_col_spec(self):
        return 'INT Identity(1,1)'

    def bind_processor(self, dialect):
        def process(value):
            if value is not None:
                if isinstance(value, int):
                    return value
                else:
                    return int(value)
            else:
                return None
        return process

    def result_processor(self, dialect, coltype):
        def process(value):
            if value is not None:
                int(value)
            return value
        return process

Upvotes: 1

Ciaran Liedeman
Ciaran Liedeman

Reputation: 779

After looking through the dialect code and checking the documentationthis does not seem to be possible.

You can try this:

from sqlalchemy import Table, Integer, Sequence, Column, Unicode

Table('test', metadata,
    Column('id', Integer, Sequence('some_sequence')),
    Column('name', Unicode(80), primary_key=True)).create(some_engine)

but from the docs it should not work:

SQL Server provides so-called “auto incrementing” behavior using the IDENTITY construct, which can be placed on an integer primary key. SQLAlchemy considers IDENTITY within its default “autoincrement” behavior, described at Column.autoincrement; this means that by default, the first integer primary key column in a Table will be considered to be the identity column and will generate DDL as such:

http://docs.sqlalchemy.org/en/rel_1_0/dialects/mssql.html#auto-increment-behavior

Upvotes: 0

Related Questions