CODEWITHSUNDEEP
pythonsqlalchemy
dwanderson
dwanderson

Reputation: 2860

Render INSERT INTO SELECT RETURNING using SQLAlchemy

I'm really trying to grok SQLAlchemy, but it's going slowly and confusingly. I have the following query:

INSERT INTO
    user (name, email)
SELECT
    :name, :email
WHERE NOT EXISTS (
    SELECT * FROM other WHERE other.id > :some_id
)
RETURNING id

and I want to run this through SQLAlchemy in a more pythonic way than just supplying raw SQL to execute.

I can't figure out which functions exist on which objects - filter, where, insert, add... the documentation is so verbose and overwhelming, I get lost.

I've got user objects that get generated and are waiting to be added to the database - this is the query that should be doing it, if someone can help me figure out how to construct this.

I'd like this to all be in one query because there is a race condition, where sometimes I get a new other object, which invalidates the current user objects and so I no longer want to add them.

Upvotes: 3

Views: 3328

Answers (1)

pi.
pi.

Reputation: 21582

I managed to render your query with SQLAlchemy-Core. I'll provide examples for simple Table objects and for models using declarative_base.

Given this demostration code:

from sqlalchemy import table, column, Unicode, String, Integer

user = table(
    'user',
    column('id', Integer),
    column('name', Unicode),
    column('email', String),
)

other = table(
    'other',
    column('id', Integer),
    column('name', Unicode),
    column('email', String),
)

If you use the ORM then your definitions probably look like this:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, Unicode, String


Base = declarative_base()

class User(Base):
    __tablename__ = 'user'

    id = Column(Integer, primary_key=True)
    name = Column('name', Unicode)
    email = Column('email', String)


class Other(Base):
    __tablename__ = 'other'

    id = Column(Integer, primary_key=True)
    name = Column('name', Unicode)
    email = Column('email', String)

# Pull out the `Table` objects from the mapped models.
user = User.__table__
other = Other.__table__

Given this, you can render the query like such:

from sqlalchemy import select, literal, exists, text

# Your inputs.
name = 'foo'
email = '[email protected]'
other_id = 1

# Compile the query for demonstration purposes
print(
    user
    .insert()
    .from_select(
        ['name', 'email'],
        select(
            [literal(name),
             literal(email)])
        .where(~exists([other.c.id])
               .where(other.c.id > other_id))
    )
    .returning(text('id'))
)

Compiling the query will give you the following SQL string:

INSERT INTO "user" (name, email) SELECT :param_1 AS anon_1, :param_2 AS anon_2 
WHERE NOT (EXISTS (SELECT other.id 
FROM other 
WHERE other.id > :id_1)) RETURNING id

Upvotes: 5

Related Questions