Is it possible to use session.insert for one to main relationships in SQLAlchemy?

Question

I have read in the following link: Sqlalchemy adding multiple records and potential constraint violation

That using SQLAlchemy core library to perform the inserts is much faster option, rather than the ORM's session.add() method:

i.e:

session.add()

should be replaced with:

session.execute(Entry.__table__.insert(), params=inserts)

In the following code I have tried to replace .add with .insert:

from sqlalchemy import Column, DateTime, String, Integer, ForeignKey, func
from sqlalchemy.orm import relationship, backref
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class Department(Base):
    __tablename__ = 'department'
    id = Column(Integer, primary_key=True)
    name = Column(String)


class Employee(Base):
    __tablename__ = 'employee'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    # Use default=func.now() to set the default hiring time
    # of an Employee to be the current time when an
    # Employee record was created
    hired_on = Column(DateTime, default=func.now())
    department_id = Column(Integer, ForeignKey('department.id'))
    # Use cascade='delete,all' to propagate the deletion of a Department onto its Employees
    department = relationship(
        Department,
        backref=backref('employees',
                        uselist=True,
                        cascade='delete,all'))


from sqlalchemy import create_engine

engine = create_engine('postgres://blah:blah@blah:blah/blah')

from sqlalchemy.orm import sessionmaker

session = sessionmaker()
session.configure(bind=engine)
Base.metadata.create_all(engine)

d = Department(name="IT")
emp1 = Employee(name="John", department=d)
s = session()
s.add(d)
s.add(emp1)
s.commit() 


s.delete(d)  # Deleting the department also deletes all of its employees.
s.commit()
s.query(Employee).all()

# Insert Option Attempt

from sqlalchemy.dialects.postgresql import insert
d = insert(Department).values(name="IT")
d1 = d.on_conflict_do_nothing()
s.execute(d1)
emp1 = insert(Employee).values(name="John", department=d1)
emp1 = emp1.on_conflict_do_nothing()
s.execute(emp1)

The error I receive:

sqlalchemy.exc.CompileError: Unconsumed column names: department

I can't quite understand the syntax and how to do it in the right way, I'm new to the SQLAlchemy.

It looks my question is similar to How to get primary key columns in pd.DataFrame.to_sql insertion method for PostgreSQL "upsert" , so potentially by answering either of our questions, you could help two people at the same time ;-)

score 1 · Accepted Answer · answered Aug 03 '19 at 01:49

I am new to SQLAlchemy as well, but this is what I found :

Using your exact code, adding department only didn't work using "s.execute(d1)", so I changed it to the below and it does work :

with engine.connect() as conn:
        d = insert(Department).values(name="IT")
        d1 = d.on_conflict_do_nothing()
        conn.execute(d1)

I found on SQLAlchemy documentation that in the past it was just a warning when you try to use a virtual column that doesn't really exist. But from version 0.8, it has been changed to an exception.

As a result, I am not sure if you can do that using the insert. I think that SQLAlchemy does it behind the scene in some other way when using session.add(). Maybe some experts can elaborate here.

I hope that will help.

Is it possible to use session.insert for one to main relationships in SQLAlchemy?

1 Answers1