sqlalchemy
sqlalchemy copied to clipboard
sqlalchemy
convert insert() at ORM level to interpret values() in an ORM context
Describe the bug
If the ORM model uses different column names than the Database:
first_name = Column(String(30), name='firstname')
Using the 2.0 style insert() will fail when values() is passed an ORM field name as the keyword. It works when using the DB column names.
Interestingly, update() is the opposite: it is successful when values() is passed an ORM field name as the keyword. It fails when using the DB column names.
update() is correct, the values function should be able to receive the ORM field names, not the DB column names.
To Reproduce
from sqlalchemy.orm import Session, registry, declarative_base, sessionmaker, column_property
from sqlalchemy import create_engine, Integer, String, Column
from sqlalchemy import text, update, select, insert, delete
engine = create_engine("postgresql+psycopg2://test:test@localhost:5432/test", echo=True, future=True)
Base = declarative_base()
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
first_name = Column(String(30), name='firstname')
last_name = Column(String(30), name='lastname')
Base.metadata.create_all(engine, checkfirst=False)
Session = sessionmaker(engine)
session = Session()
# this works fine using ORM field names:
u = User(first_name='foo', last_name='bar')
session.add(u)
session.flush()
# this insert().values() fails with ORM names (CompileError: Unconsumed column names: last_name, first_name):
session.execute(insert(User).values(first_name='foo', last_name='bar'))
# this insert().values() succeeds with DB names:
res = session.execute(insert(User).values(firstname='foo', lastname='bar'))
assert res.rowcount == 1
# this update().values() succeeds with ORM names
res = session.execute(update(User).values(first_name='foo2').where(User.id == u.id))
assert res.rowcount == 1
# this update().values() fails correctly with the DB names (InvalidRequestError: Invalid expression type: 'firstname'):
session.execute(update(User).values(firstname='foo3').where(User.id == u.id))
### Error
Traceback (most recent call last):
File "
### Versions
- OS: Windows 10
- Python: 3.9.5
- SQLAlchemy: 1.4.32
- Database: postgresql
- DBAPI (eg: psycopg, cx_oracle, mysqlclient): 1.4.32
### Additional context
_No response_
insert is not ORM enabled right now, so this behavior is expected.
since we are pushing for insert() to have more ORM awareness, this can be for 2.0
Hi @zzzeek
Do you know a workaround in 1.4 to use insert().values() with different ORM field names vs database column names?
My library function basically receives a model and a **kwargs like:
def foo(model, **kwargs):
ins = insert(model).values(**kwargs)
I am using model.__mapper__.attrs to convert before calling insert, like the following:
def foo(model, **kwargs):
kwargs = {
model.__mapper__.attrs[orm_field_name].columns[0].name: value
for orm_field_name, value in kwargs.items()
if (
orm_field_name in model.__mapper__.attrs
and len(model.__mapper__.attrs[orm_field_name].columns) == 1
and isinstance(model.__mapper__.attrs[orm_field_name].columns[0], Column)
)
}
ins = insert(model).values(**kwargs)
...
Is there a better way to do it?
insert is not ORM enabled right now
I have been using insert and ORM successfully. This is the only issue I have come across so far.
Is there anything else I should be aware of?
Thank you.
Hi @zzzeek
Do you know a workaround in 1.4 to use
insert().values()with different ORM field names vs database column names?My library function basically receives a model and a **kwargs like:
def foo(model, **kwargs): ins = insert(model).values(**kwargs)
use the attributes themselves, which represent column expressions, as keys:
insert(model).values({getattr(model, key): value for key, value in kwargs.items()})
I have been using
insertand ORM successfully. This is the only issue I have come across so far.Is there anything else I should be aware of?
sure, as noted elsewhere, returning(Model) won't return model instances, and if your statement also includes ON CONFLICT types of structures, those UPDATEs wont be reflected in the state of already-loaded objects unless you use the from_statement() workaround in the docs.
insert(model).values({getattr(model, key): value for key, value in kwargs.items()})
Much more simple than mine; thanks.
Mike Bayer has proposed a fix for this issue in the main branch:
ORM bulk insert via execute (WIP, initial reorg) https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/4029
@zzzeek @mkmoisen Following up on this. Is it possible to either
- Get "ORM-enabled" event hooks for session.execute(insert()).
- Use values set by a model, removing the need to use insert.values?
@zzzeek @mkmoisen Following up on this. Is it possible to either
* Get "ORM-enabled" event hooks for session.execute(insert()).
* Use values set by a model, removing the need to use insert.values?
why not just use session.add_all() ? There is also bulk_save_objects but there's not any huge advantage to that method vs. add_all(), hence it's legacy
@zzzeek @mkmoisen Following up on this. Is it possible to either
* Get "ORM-enabled" event hooks for session.execute(insert()).* Use values set by a model, removing the need to use insert.values?why not just use session.add_all() ? There is also bulk_save_objects but there's not any huge advantage to that method vs. add_all(), hence it's legacy
@zzzeek This is to support a single insert (upsert) call. Would do_orm_execute() cover this?