SQLAlchemy - INSERT OR REPLACE equivalent

does anybody know what is the equivalent to SQL "INSERT OR REPLACE" clause in SQLAlchemy and its SQL expression language?

Many thanks -- honzas

Asked by: Carlos408 | Posted: 06-12-2021

Answer 1

What about Session.merge?

Session.merge(instance, load=True, **kw)

Copy the state an instance onto the persistent instance with the same identifier.

If there is no persistent instance currently associated with the session, it will be loaded. Return the persistent instance. If the given instance is unsaved, save a copy of and return it as a newly persistent instance. The given instance does not become associated with the session. This operation cascades to associated instances if the association is mapped with cascade="merge".

from http://www.sqlalchemy.org/docs/reference/orm/sessions.html

Answered by: Kate511 | Posted: 07-01-2022

Answer 2


Answered by: Kelvin362 | Posted: 07-01-2022

Answer 3

I don't think (correct me if I'm wrong) INSERT OR REPLACE is in any of the SQL standards; it's an SQLite-specific thing. There is MERGE, but that isn't supported by all dialects either. So it's not available in SQLAlchemy's general dialect.

The cleanest solution is to use Session, as suggested by M. Utku. You could also use SAVEPOINTs to save, try: an insert, except IntegrityError: then rollback and do an update instead. A third solution is to write your INSERT with an OUTER JOIN and a WHERE clause that filters on the rows with nulls.

Answered by: Sophia855 | Posted: 07-01-2022

Answer 4

You can use OR REPLACE as a so-called prefix in your SQLAlchemy Insert -- the documentation for how to place OR REPLACE between INSERT and INTO in your SQL statement is here

Answered by: Aldus110 | Posted: 07-01-2022

Similar questions

python - What is the sqlalchemy equivalent column type for 'money' and 'OID' in Postgres?

What is the sqlalchemy equivalent column type for 'money' and 'OID' column types in Postgres?

python - SQLAlchemy equivalent to Django's annotate() method

I'm doing a join like this in SQLAlchemy: items = Item.query\ .outerjoin((ItemInfo, ItemInfo.item_id==Item.id)) items.add_columns(ItemInfo.count) This causes SQLAlchemy to return tuples: >>> items.first() (<Item ...>, 2) I'd much prefer it if the "count" value would instead be returned as an attribute of the item, i.e. I want to do:

python - SqlAlchemy equivalent of pyodbc connect string using FreeTDS

The following works: import pyodbc pyodbc.connect('DRIVER={FreeTDS};Server=my.db.server;Database=mydb;UID=myuser;PWD=mypwd;TDS_Version=8.0;Port=1433;') The following fails: import sqlalchemy sqlalchemy.create_engine("mssql://myuser:mypwd@my.db.server:1433/mydb?driver=FreeTDS& odbc_options='TDS_Version=8.0'").connect() The error message for above is:

python - SqlAlchemy equivalent of pyodbc connect string fails

The following works: import pyodbc import sqlalchemy as sa import pandas as pd cnxn = pyodbc.connect('Driver=ODBC Driver 17 for SQL Server;Server=MyServer;Database=MyDB;Trusted_Connection=yes;') sql = ("SELECT * FROM MyTable") data_df = pd.read_sql(sql,cnxn) The following do not work: params = urllib.parse.quote_plus('Drive...

python - Add GROUPBY COUNT(*) to UNION ALL Query in SQLAlchemy - EXCEPT Equivalent

I have a query which performs a UNION ALL operation on two SELECT statements in SQLAlchemy. It looks like this, union_query = query1.union_all(query2) What I want to do now is to perform a GROUPBY using several attributes and then get only the rows where COUNT(*) is equal to 1. How can I do this? I know I can do a GROUPBY like this, group_query = union_quer...

python - How can I use UUIDs in SQLAlchemy?

Is there a way to define a column (primary key) as a UUID in SQLAlchemy if using PostgreSQL (Postgres)?

python - Getting random row through SQLAlchemy

How do I select one or more random rows from a table using SQLAlchemy?

python - How to add an automatic filter to a relation with SQLAlchemy?

I'm using SQLAlchemy 0.5rc, and I'd like to add an automatic filter to a relation, so that every time it tries to fetch records for that relation, it ignores the "remote" ones if they're flagged as "logically_deleted" (a boolean field of the child table) For example, if an object "parent" has a "children" relation that has 3 records, but one of them is logically deleted, when I query for "Parent" I'd like SQLA to f...

python - What is the sqlalchemy equivalent column type for 'money' and 'OID' in Postgres?

What is the sqlalchemy equivalent column type for 'money' and 'OID' column types in Postgres?

python - SQLAlchemy and empty columns

When I try to insert a new record into the database using SQLAlchemy and I don't fill out all values, it tries to insert them as "None" (instead of omitting them). It then complains about "can't be null" errors. Is there a way to have it just omit columns from the sql query if I also omitted them when declaring the instance?

python - SQLAlchemy DateTime timezone

SQLAlchemy's DateTime type allows for a timezone=True argument to save a non-naive datetime object to the database, and to return it as such. Is there any way to modify the timezone of the tzinfo that SQLAlchemy passes in so it could be, for instance, UTC? I realize that I could just use default=datetime.datetime.utcnow; however, this is a naive time that would happily ac...

python - How can I get all rows with keys provided in a list using SQLalchemy?

I have sequence of IDs I want to retrieve. It's simple: session.query(Record).filter(Record.id.in_(seq)).all() Is there a better way to do it?

python - How can I order objects according to some attribute of the child in sqlalchemy?

Here is the situation: I have a parent model say BlogPost. It has many Comments. What I want is the list of BlogPosts ordered by the creation date of its' Comments. I.e. the blog post which has the most newest comment should be on top of the list. Is this possible with SQLAlchemy?

python - Defining a table with sqlalchemy with a mysql unix timestamp

Background, there are several ways to store dates in MySQ. As a string e.g. "09/09/2009". As integer using the function UNIX_TIMESTAMP() this is supposedly the traditional unix time representation (you know seconds since the epoch plus/minus leap seconds). As a MySQL TIMESTAMP, a mysql specific data type not the same than unix timestamps. As a MySQL Date field, another mysql spec...

python - How to generate a file with DDL in the engine's SQL dialect in SQLAlchemy?

Suppose I have an engine pointing at MySQL database: engine = create_engine('mysql://arthurdent:answer42@localhost/dtdb', echo=True) I can populate dtdb with tables, FKs, etc by: metadata.create_all(engine) Is there an easy way to generate the SQL file that contains all the DDL statements instead of actually applying these DDL sta...

Still can't find your answer? Check out these communities...

PySlackers | Full Stack Python | NHS Python | Pythonist Cafe | Hacker Earth | Discord Python