SQLAlchemy 1.4 overiding system value

user_14740191 0
Viewed 323

I currently am using SQL Alchemy Core specifically with the SQL Expression Language. I have a table that is currently using the GENERATED ALWAYS AS IDENTITY parameter.

CREATE TABLE mytable(id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, 
col1 VARCHAR(100),col2 VARCHAR(100));

Everytime I try insert in the table, i'm getting the error:

DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.

I know that if I just to use postgres I could:

INSERT INTO mytable (id,col1,col2) OVERRIDING SYSTEM VALUE 
VALUES (%s,%s,%s) ON CONFLICT (id) DO NOTHING;

But how would do this using the sql expression language that sqlalchemy provides? I am currently upserting like this:

insert_stmt = postgresql.insert(target).values(vals)
primary_keys = [key.name for key in inspect(target).primary_key]

stmt = insert_stmt.on_conflict_do_nothing(index_elements=primary_keys)
conn.execute(stmt)
sqlalchemy postgresql python
1 Answers

I wanted OVERRIDING SYSTEM VALUE to use fixed IDs in my tests. As far as I can see, SQLAlchemy doesn't support this at the moment.

I hacked it in this way:

@compiles(Insert)
def set_inserts_overriding_system_value(the_insert, compiler, **kw):
    text = compiler.visit_insert(the_insert, **kw)
    text = text.replace(") VALUES (", ") OVERRIDING SYSTEM VALUE VALUES (")
    return text

You can probably create some weird tables or insert queries on purpose, that will be messed up by this text replace. But it won't ever happen by accident.

user_4044950
Related