In SQLAlchemy, only primary keys can be set to auto-increment using the autoincrement=True
parameter while defining the column. Non-primary keys cannot be directly set to auto-increment.
However, there are several workarounds that can be used to achieve a similar result:
DDL
object that can be used to execute raw SQL statements. Here's an example:from sqlalchemy import DDL
# Create a sequence in the database
create_seq = DDL("""
CREATE SEQUENCE my_seq START 1;
""")
db.session.execute(create_seq)
# Define the non-primary key column with a default value from the sequence
my_table = Table('my_table', metadata,
Column('id', Integer, primary_key=True),
Column('my_column', Integer, server_default=text('nextval(\'my_seq\'::regclass)')),
)
# Define a trigger function in the database
create_function = DDL("""
CREATE OR REPLACE FUNCTION my_func() RETURNS TRIGGER AS $$
BEGIN
NEW.my_column = (SELECT COALESCE(MAX(my_column), 0) + 1 FROM my_table);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
""")
db.session.execute(create_function)
# Define the non-primary key column with a default value from the trigger function
my_table = Table('my_table', metadata,
Column('id', Integer, primary_key=True),
Column('my_column', Integer),
)
# Create a trigger on insert that calls the trigger function
my_trigger = DDL("""
CREATE TRIGGER my_trigger
BEFORE INSERT ON my_table
FOR EACH ROW
EXECUTE FUNCTION my_func();
""")
db.session.execute(my_trigger)
Note that both of these approaches require executing raw SQL statements and may not be compatible with all database backends. Be sure to test thoroughly and consult the database documentation for details.
Asked: 2022-05-31 11:00:00 +0000
Seen: 16 times
Last updated: Nov 17 '22