Conditional Insertion of Single Row with Bind Parameters

[mkmo...@gmail.com]

I would like to do a conditional insert of a a single row. This is often useful in a CRUD app for checking permissions and inserting in a single database call:

INSERT INTO project (name, user_id)

SELECT :name, :user_id

WHERE EXISTS (

    SELECT 1

    FROM users

    WHERE id = :user_id

        and role = :admin_role

)

In SQLAlchemy I use the following which isn't the most beautiful:

ins = insert(Project).from_select(
    ['name', 'user_id'],

   

    select(
        bindparam('name', project_name),

        bindparam('user_id', user_id),

    ).where(
        exists(
            select(1).select_from(
                User
            ).where(
                User.c.id == 1,

                User.c.role == "ADMIN",

            )
        )
    )
)

I find my use of `bindparam` in the select statement to be rather noisy.

Does anyone know of a way to make it a bit more easier on the eyes?

Would you be open to making a SQLAlchemy conditional insert API?

Project.insert(name=project_name, user_id=user_id).where(

    exists(

        # ...

    )

)

In other words, create a method "where" on Insert that will not use VALUES and instead use a SELECT.

Thanks and best regards,

Matthew

[Mike Bayer]

you don't need to name those parameters, you can use sqlalchemy.literal():

select(literal(project_name), literal(user_id)).where( ... )

Would you be open to making a SQLAlchemy conditional insert API?

Project.insert(name=project_name, user_id=user_id).where(

    exists(

        # ...

    )

)

In other words, create a method "where" on Insert that will not use VALUES and instead use a SELECT.

this is what you should do in your own application.  Make a function called insert_where() and pass along the arguments, then you won't have to see that code everywhere, if this is a common idiom you like to use.

Thanks and best regards,

Matthew

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/3ef987dd-cd2b-4326-bdf7-b75045265114n%40googlegroups.com.

[mkmo...@gmail.com]

Hi Mike,

Thanks, `literal` is good.

Regarding the `insert_where` function, would you mind providing some feedback on the API? I'm writing a library that uses SQLAlchemy so I would like this API to be relatively consistent with the SQLAlchemy approach.

So far I have been doing it like the following:

def insert_where(table, where, **kwargs):

    ....

insert_where(

    Project,

    name=project_name, user_id=user_id,

    where=exists(

       ...

    )

Another option might be:

def insert_where(table, where, *args, **kwargs):

    ...

Where *args could be a dictionary, or tuple, or list of dict/tuple, similar to the insert(Project).values API.

Any thoughts?

Best regards,

Matthew