On Tue, Mar 22, 2022, at 2:46 PM, mkmo...@gmail.com wrote: > 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?
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+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/3ef987dd-cd2b-4326-bdf7-b75045265114n%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/3ef987dd-cd2b-4326-bdf7-b75045265114n%40googlegroups.com?utm_medium=email&utm_source=footer>. -- 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+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/cfaab9d6-d141-4526-a89e-8feee909fa32%40www.fastmail.com.