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.
Please start posting anonymously - your entry will be published after you log in or create a new account. This space is reserved only for answers. If you would like to engage in a discussion, please instead post a comment under the question or an answer that you would like to discuss
Asked: 2022-05-31 11:00:00 +0000
Seen: 16 times
Last updated: Nov 17 '22
In PySpark, how does the lead function work when the value of a column changes?
How can SQL output be structured in a column-based XML format instead of row-based?
How can pgcrypto be used to secure data on Postgres?
What is the SQL grammar used for addSql in Doctrine?
How can Django Admin accommodate a variety of formats and locales for its input fields?
How can an array be passed using typo3 flexform xml and itemsProcConfig?