[sqlalchemy] Postgresq Execute Many with Textual SQL Convenience Issue
Hi Mike, When using Core, we can do a bulk insert and bulk return with Postgresql trivially: from sqlalchemy import table, column t = table('foo', column('id'), column('bar') ins = t.insert().values([{'bar': 'a'}, {'bar': 'b'}]).returning(foo.id) results = conn.execute(ins) ids = results.fetchall() However, with raw textual SQL, it is a bit more inconvenient. The following doesn't work: results = conn.execute(text( 'insert into foo values (:bar) returning id ), [{'bar': 'a'}, {'bar': 'b'}]) # raises sqlalchemy.exc.ResourceClosedError: This result object does not return rows. It has been closed automatically. results.fetchall() To get it working, we have to do it this way: results = conn.execute(text( 'insert into foo values (:bar0), (:bar1) ), {'bar0': 'x', 'bar1': 'y'}) assert results.fetchall() This isn't convenient. For example you would have to convert a list of bars like [{'bar': 'a'}, {'bar': 'b'}] into a single dict with uniquely name keys {'bar0': 'a', 'bar1': 'b'}. I imagine sqlalchemy is doing that under the hood when using core. Is there some convenience function available in sqlalchemy core that I can use to simplify this? -- 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/ee82dcc7-7ff5-43e6-a405-1e5aedaaaeban%40googlegroups.com.
Re: [sqlalchemy] Issuing Raw SQL and Returning a List of Objects
Hi Mike, If I understand correctly, you want to work with raw sql and don't want any ORM getting in your way. I'm the same way, and it is trivial to use SQLAlchemy Core for this purpose. results = conn.execute(text('select foo, bar from baz')).mappings().fetchall() # mappings().fetchall() returns a list of dict like objects for row in results: print(row['foo'], row['bar']) result = conn.execute(text('select foo, bar from baz')).fetchall() # fetchall() without mappings() returns a list of named tuple like objects for row in results: print(row.foo, row.bar) print(row[0], row[1]) On Thursday, August 24, 2023 at 5:06:11 AM UTC-7 Mike Graziano wrote: > Hi Simon, > > Thanks for responding to my post. It turns out that MyBatis can do > exactly what you are saying which essentially sounds like a bulk ETL > process. Again, the key difference is that MyBatis doesn’t require that > the mapping be done with all the DB-specific definitions which I frankly > prefer. There is a tool, the MyBatis generator, that does exactly this and > I have used it when I didn’t want to write my own mapping files since the > tables had hundreds of fields. > > In many cases, you are correct in that I was only loading data. The data > was retrieved by raw SQL and could involve joins with other tables much as > in a view. I just needed a data transfer mechanism to translate the SQL > results to a POJO. Your experience differed in that you did need to create > the tables with your Python code. I agree that SQLAlchemy is perfect for > that. I created the tables ahead of time usually with command-line psql > or, as you said, the tables already existed. In fact, I’d sometimes create > temp tables with the schema of existing tables and I also did that with > command-line psql in a Bash script. > > Thanks for your insights. > > Rgds > > mjg > > On Wednesday, August 23, 2023 at 12:49:23 PM UTC-4 Simon King wrote: > >> My perspective: the SQLAlchemy ORM really comes into its own when you are >> making use of its Unit of Work system to load a batch of objects from the >> database, manipulate those objects, and then flush your changes back to the >> database. If you are only *loading* data then you don't need a lot of the >> functionality of the ORM, and you might consider using SQLAlchemy Core >> instead. >> >> Using SQLAlchemy Core to execute SQL strings is very simple: >> >> https://docs.sqlalchemy.org/en/20/core/connections.html#basic-usage >> >> You can use the objects that come back from those calls directly (they >> have attributes named after the columns from the query), or you could >> trivially convert them into instances of some class that you've defined. >> >> It sounds like the sort of work you do involves writing code to access >> pre-existing databases, in which case writing SQL directly makes a lot of >> sense, and you have no need for the schema-definition parts of SQLAlchemy. >> But there are other classes of application for which the schema-definition >> tools are very useful. I have written many applications for which the >> database didn't already exist, so allowing SQLAlchemy to create the tables >> was the obvious way to go (with Alembic for migrations as the schema >> changed over time). SQLAlchemy also gives a certain amount of independence >> from the underlying database, meaning that I can run most of my tests using >> SQLite despite using Postgres or MySQL in production. >> >> In summary: use the right tool for the job :-) >> >> Simon >> >> >> On Mon, Aug 21, 2023 at 6:48 PM Mike Graziano wrote: >> >>> Hi Mike, >>> >>> >>> >>> Thanks for that info. It was just what I needed. I also want to thank >>> you for your YouTube tutorials on SQLAlchemy. They are fantastic. >>> >>> >>> >>> I don’t want to make this a huge post, but I have a real pet peeve >>> concerning ORMs. I come from a Java background where I used MyBatis as my >>> ORM. What I love about MyBatis was >>> >>> >>> >>> - I could use raw SQL which I personally feel is superior. My >>> argument here is simple: Why learn another “language” for issuing SQL >>> statements when we have already spent a fair amount of time learning SQL. >>> Also, raw SQL is easily testable with either command line or GUI tools? >>> >>> - The ORM should just use the mapped models in order to execute SQL >>> using mapping that in and of themselves doesn’t/shouldn’t care about the >>> tables. Unless you are creating a table with the ORM which I have found to >>> be rare, the ORM shouldn’t care about the table structure other than field >>> names with the possibility of aliases and data types. Why define more than >>> what we need in order to populate a plain old object (POO – language >>> agnostic). Why include characteristics like primary key, nullability, >>> etc? Some Pydantic-like validation is handy, but can be table agnostic. >>> Let’s extract the data via SQL and return POOs. In
Re: [sqlalchemy] row.Row and dict behavior in 2.0
Hi Mike, That's much more concise, thank you. Previously I have always accessed the `_asdict()` private member. Thanks and best regards, Matthew On Thursday, January 12, 2023 at 11:00:58 AM UTC-8 Mike Bayer wrote: > > > On Thu, Jan 12, 2023, at 1:46 PM, mkmo...@gmail.com wrote: > > Hi Mike, > > Thanks. I have a few cases where it is easiest if I have a plain dict > instead of a RowMapping. For example RowMapping is immutable, and isn't > playing nicely with my json encoder. > > What is your preferred method to convert to a plain dict? > > > for row in result.mappings(): > d = dict(row) > > should do it > > the row was not mutable in 1.4, 1.3, etc. either, what did you do then ? > > > Row._asdict() > dict(RowMapping.items()) > something else? > > Thanks and best regards, > > Matthew > > > On Monday, January 9, 2023 at 6:00:08 PM UTC-8 Mike Bayer wrote: > > > > On Mon, Jan 9, 2023, at 8:50 PM, mkmo...@gmail.com wrote: > > Hello, > > It looks like in 2.0 we can no longer treat a row.Row as a dict. I have a > few cases where I want to do this, such as when I need to get a list of > columns, or when I don't know the column name in advance. > > rows = conn.execute(select(t.c.foo)).fetchall() > > rows[0].keys() # Not Allowed > > rows[0][some_unknown_column] # not allowed > > If we need to treat it as a dict, are we supposed to be calling: > > rows[0]._asdict() > > This works, but the only issue is that our IDEs flag this as accessing a > protected member of a class. Is there any alternative? > > Thanks and best regards, > > > you have more options here than previously on how to treat rows, as tuples > or mappings, either up front or on a per-row basis. the new API has been > available as of version 1.4. Relevant links include: > > 1. announcement of change and rationale > > > https://docs.sqlalchemy.org/en/14/changelog/migration_14.html#rowproxy-is-no-longer-a-proxy-is-now-called-row-and-behaves-like-an-enhanced-named-tuple > > 2. migration guide > > > https://docs.sqlalchemy.org/en/14/changelog/migration_20.html#result-rows-act-like-named-tuples > > 3. new tutorial coverage > > > https://docs.sqlalchemy.org/en/14/tutorial/dbapi_transactions.html#fetching-rows > > included is background on how to get mappings from a Row or how to get > RowMapping objects from a result up front using result.mappings(). > > > > > > > > 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/10721070-ecf9-4438-87dc-a9ff6c13c0dan%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/10721070-ecf9-4438-87dc-a9ff6c13c0dan%40googlegroups.com?utm_medium=email_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+...@googlegroups.com. > > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/624b2fdc-1c9d-4c3b-9424-f091b4be529an%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/624b2fdc-1c9d-4c3b-9424-f091b4be529an%40googlegroups.com?utm_medium=email_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/3dc91ef7-585d-4085-8825-31bc39ccd2edn%40googlegroups.com.
Re: [sqlalchemy] row.Row and dict behavior in 2.0
Hi Mike, Thanks. I have a few cases where it is easiest if I have a plain dict instead of a RowMapping. For example RowMapping is immutable, and isn't playing nicely with my json encoder. What is your preferred method to convert to a plain dict? Row._asdict() dict(RowMapping.items()) something else? Thanks and best regards, Matthew On Monday, January 9, 2023 at 6:00:08 PM UTC-8 Mike Bayer wrote: > > > On Mon, Jan 9, 2023, at 8:50 PM, mkmo...@gmail.com wrote: > > Hello, > > It looks like in 2.0 we can no longer treat a row.Row as a dict. I have a > few cases where I want to do this, such as when I need to get a list of > columns, or when I don't know the column name in advance. > > rows = conn.execute(select(t.c.foo)).fetchall() > > rows[0].keys() # Not Allowed > > rows[0][some_unknown_column] # not allowed > > If we need to treat it as a dict, are we supposed to be calling: > > rows[0]._asdict() > > This works, but the only issue is that our IDEs flag this as accessing a > protected member of a class. Is there any alternative? > > Thanks and best regards, > > > you have more options here than previously on how to treat rows, as tuples > or mappings, either up front or on a per-row basis. the new API has been > available as of version 1.4. Relevant links include: > > 1. announcement of change and rationale > > > https://docs.sqlalchemy.org/en/14/changelog/migration_14.html#rowproxy-is-no-longer-a-proxy-is-now-called-row-and-behaves-like-an-enhanced-named-tuple > > 2. migration guide > > > https://docs.sqlalchemy.org/en/14/changelog/migration_20.html#result-rows-act-like-named-tuples > > 3. new tutorial coverage > > > https://docs.sqlalchemy.org/en/14/tutorial/dbapi_transactions.html#fetching-rows > > included is background on how to get mappings from a Row or how to get > RowMapping objects from a result up front using result.mappings(). > > > > > > > > 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/10721070-ecf9-4438-87dc-a9ff6c13c0dan%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/10721070-ecf9-4438-87dc-a9ff6c13c0dan%40googlegroups.com?utm_medium=email_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/624b2fdc-1c9d-4c3b-9424-f091b4be529an%40googlegroups.com.
[sqlalchemy] row.Row and dict behavior in 2.0
Hello, It looks like in 2.0 we can no longer treat a row.Row as a dict. I have a few cases where I want to do this, such as when I need to get a list of columns, or when I don't know the column name in advance. rows = conn.execute(select(t.c.foo)).fetchall() rows[0].keys() # Not Allowed rows[0][some_unknown_column] # not allowed If we need to treat it as a dict, are we supposed to be calling: rows[0]._asdict() This works, but the only issue is that our IDEs flag this as accessing a protected member of a class. Is there any alternative? 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/10721070-ecf9-4438-87dc-a9ff6c13c0dan%40googlegroups.com.
[sqlalchemy] Does SQLAlchemy Core support MERGE?
Apologies if this is in the documentation, but I was not able to find it. Does SQLAlchemy Core support the MERGE statement? 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/d09683d5-d642-4592-b80c-40164caf77c9n%40googlegroups.com.
[sqlalchemy] How to Dispose of all Connections in a Pool in a Web Environment?
I'm using the standard connection pool via engine: engine = sqlalchemy.create_engine('oracle://scott:tiger@foo', ...) conn1 = engine.connect() conn2 = engine.connect() I want to dispose and recreate the pool in a web environment. All new threads that connect should get a new connection from the pool. All old threads that are using an old connection may continue to do so until they close these connections out. >From what I understand, this is as simple as executing `engine.dispose()`. Is that correct? 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/def97e71-6d3e-4d28-a001-3aafd583ce5an%40googlegroups.com.
Re: [sqlalchemy] SELECT .. BULK COLLECT INTO ...
Perfect, thank you Mike. Best regards, Matthew On Wednesday, October 5, 2022 at 11:51:39 AM UTC-7 Mike Bayer wrote: > Select does not have a hook for that particular spot in the SQL > compilation, so unless you rewrote all of visit_select, your best bet is to > stick with simple string replacement. > > this does not in any way preclude you from using the compiler extension, > get the text from the compiler then do the process > > import re > from sqlalchemy import column > from sqlalchemy import select > from sqlalchemy import table > from sqlalchemy.ext.compiler import compiles > from sqlalchemy.sql.expression import Select > > > @compiles(Select) > def _compile(element, compiler, **kw): > text = compiler.visit_select(element, **kw) > match = re.search(r"BULK COLLECT INTO (.*)", text) > if match: > text = re.sub(r"BULK COLLECT INTO (.*)", "", text) > text = text.replace("FROM", f"BULK COLLECT INTO {match.group(1)} > FROM") > return text > > baz = table('baz', column('foo'), column('bar')) > > stmt = select(baz).with_statement_hint("BULK COLLECT INTO I_foos") > print(stmt) > > > > > On Wed, Oct 5, 2022, at 2:21 PM, mkmo...@gmail.com wrote: > > Hello, > > I am generating Oracle PLSQL using sqlalchemy core. > > I would like to generate the following output: > > SELECT foo, bar > BULK COLLECT INTO l_foos > FROM baz; > > Is there a mechanism to insert a string after the select column list and > before the FROM? > > In sql/compiler.py:visit_select ( > https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/sql/compiler.py#L3188) > > I do not see a way to hook into this specific spot via compiler > customizations, unless I am missing something. > > I suppose I can simply do `query_str.replace('FROM', 'BULK COLLECT INTO > l_foos FROM', 1) but I was hoping to make a nice API via a compiler > customization. > > 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/8e55ea83-2396-4cda-972a-f011d6000c6an%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/8e55ea83-2396-4cda-972a-f011d6000c6an%40googlegroups.com?utm_medium=email_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/37c1262d-e90f-4dbe-abe9-9448629aab72n%40googlegroups.com.
[sqlalchemy] SELECT .. BULK COLLECT INTO ...
Hello, I am generating Oracle PLSQL using sqlalchemy core. I would like to generate the following output: SELECT foo, bar BULK COLLECT INTO l_foos FROM baz; Is there a mechanism to insert a string after the select column list and before the FROM? In sql/compiler.py:visit_select (https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/sql/compiler.py#L3188) I do not see a way to hook into this specific spot via compiler customizations, unless I am missing something. I suppose I can simply do `query_str.replace('FROM', 'BULK COLLECT INTO l_foos FROM', 1) but I was hoping to make a nice API via a compiler customization. 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/8e55ea83-2396-4cda-972a-f011d6000c6an%40googlegroups.com.
Re: [sqlalchemy] Possible to pass array of (table, conditions) to join() like where()/select() ?
Thanks! On Friday, September 16, 2022 at 10:53:59 AM UTC-7 Mike Bayer wrote: > > > On Fri, Sep 16, 2022, at 12:10 PM, mkmo...@gmail.com wrote: > > I use the following pattern in my REST APIs, building up the select, > joins, where conditions, group bys, order bys, depending on the query > parameters passed in by the user: > > selects = [Foo] > joins = [(Bar, Foo.c.id == Bar.c.foo_id)] > where_conditions = [Foo.c.id == request.args['pk']] > > if request.args.get('include_baz'): > selects.append(Baz) > joins.append((Baz, Bar.c.id == Baz.c.bar_id)) > > What I would like to do is the following: > > sel = select( > *selects > ).join( > *joins # doesn't work > ).where( > *where_conditions > ) > > This works for everything except for `join` and `outerjoin`. So I have to > write it like this: > > sel = select(*selects) > for table, condition in joins: > sel = sel.join(table, condition) > sel = se.where(*where_conditions) > > Is there some way to perform a join by passing an array of (table, > conditions) so I can write the SQL without all of the `sel = sel. ` noise? > > > if you have explicit join conditions like that, you might be able to > make them into join objects: > > from sqlalchemy.orm import join > sel.join(*[join(left, right, onclause) for right, onclause in conditions]) > > IMO that's not really any better, or you can make a def like this: > > def join(stmt, conditions): > for table, condition in conditions: >stmt = stmt.join(table, condition) > return stmt > > then you use it as: > > sel = join(sel, *joins) > > the form where we used to accept multiple join conditions inside of one > join() method is part of legacy Query and is being removed. There are too > many different argument forms for join() as it is for it to be appropriate > for it to accept *args. > > personally I think "stmt = stmt.modifier(thing)" is the cleanest, > including for the WHERE clause too. > > > > What I've been doing is using a function like the following: > > def collection_query(selects, joins, where_conditions, ...) > > But this has other problems and I would like to go back to raw sqlalchemy. > > 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/84058464-5b92-4305-a348-d5a65fba441fn%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/84058464-5b92-4305-a348-d5a65fba441fn%40googlegroups.com?utm_medium=email_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/de7413c5-ddec-419e-8edf-e60e45573f37n%40googlegroups.com.
[sqlalchemy] Possible to pass array of (table, conditions) to join() like where()/select() ?
I use the following pattern in my REST APIs, building up the select, joins, where conditions, group bys, order bys, depending on the query parameters passed in by the user: selects = [Foo] joins = [(Bar, Foo.c.id == Bar.c.foo_id)] where_conditions = [Foo.c.id == request.args['pk']] if request.args.get('include_baz'): selects.append(Baz) joins.append((Baz, Bar.c.id == Baz.c.bar_id)) What I would like to do is the following: sel = select( *selects ).join( *joins # doesn't work ).where( *where_conditions ) This works for everything except for `join` and `outerjoin`. So I have to write it like this: sel = select(*selects) for table, condition in joins: sel = sel.join(table, condition) sel = se.where(*where_conditions) Is there some way to perform a join by passing an array of (table, conditions) so I can write the SQL without all of the `sel = sel. ` noise? What I've been doing is using a function like the following: def collection_query(selects, joins, where_conditions, ...) But this has other problems and I would like to go back to raw sqlalchemy. 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/84058464-5b92-4305-a348-d5a65fba441fn%40googlegroups.com.
[sqlalchemy] How to Warm Up Connection Pool ?
As stated in the docs: > All SQLAlchemy pool implementations have in common that none of them “pre create” connections - all implementations wait until first use before creating a connection Is it possible to warm up, or pre create, the connections in the pool? When I start my server, the connection pool is initially empty. I'm using Oracle and connection initialization has a lot of overhead. I'm seeing as high as 4 seconds sometimes. The only way I can think of is to spawn a lot of threads that all check out a connection and release it after ~10 seconds. Is there a built in solution for this? 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/d64fbfe3-f07b-49d8-a670-6059ed9a4574n%40googlegroups.com.
[sqlalchemy] Re: How to Prevent SQLAlchemy Core from Quoting Name
My apologies, I just saw that I can use the schema key word for this case: t = table('mytable', column('id'), schema='myschema') Best regards, Matthew On Friday, June 17, 2022 at 11:13:44 AM UTC-7 mkmo...@gmail.com wrote: > Using a `.` in a table name will cause the table name to be quoted. > > For example: > > from sqlalchemy import table, column > t = table('myschema.mytable', column('id')) > print(select(t.c.id)) > > Outputs: > > SELECT "myschema.mytable".id > FROM "myschema.mytable" > > This fails in Oracle because the table name within the quotes is not in > all caps. > > How can I remove the quotes and instead have it render: > > SELECT myschema.mytable.id > FROM myschema.mytable > > 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/d68b348b-972b-48da-8eba-616b17972a83n%40googlegroups.com.
[sqlalchemy] How to Prevent SQLAlchemy Core from Quoting Name
Using a `.` in a table name will cause the table name to be quoted. For example: from sqlalchemy import table, column t = table('myschema.mytable', column('id')) print(select(t.c.id)) Outputs: SELECT "myschema.mytable".id FROM "myschema.mytable" This fails in Oracle because the table name within the quotes is not in all caps. How can I remove the quotes and instead have it render: SELECT myschema.mytable.id FROM myschema.mytable 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/2827f031-6f9a-4124-9c85-2f30c6b5e337n%40googlegroups.com.
Re: [sqlalchemy] How to infer the ORM model class from an update()/insert() object?
Hi Mike, Thanks for taking a look into it. Even if only the simple case can be taken care of in 2.0, that would be good as it would set up 2.x for completing the edge cases over time. > select(User).from_statement(insert(User).returning(User, User.my_column_property)) In my particular case, my library function doesn't know anything about the model being passed in. I'm just taking in an insert or update, adding a return, and executing it. I think I'll stick with ` ins.returning(*select(model).selected_columns)` for the time being - it seems to to the trick. Best regards, Matthew On Monday, March 28, 2022 at 11:49:54 AM UTC-7 Mike Bayer wrote: > since it will be very hard to change this after 2.0 is released I will try > to further attempt to get ORM objects to be returned, though this will not > at first support any special features: > > https://github.com/sqlalchemy/sqlalchemy/issues/7865 > > this will allow retunring(User) to send back an instance but there's a lot > of cases to be worked out. > > > > > On Mon, Mar 28, 2022, at 2:05 PM, Mike Bayer wrote: > > > > On Mon, Mar 28, 2022, at 1:31 PM, mkmo...@gmail.com wrote: > > Hi Mike, > > When using `column_property`, this > `select(User).from_statement(ins.returing(User))` construct will not load > in the column property. Instead the ORM will issue a second query when the > column property is accessed. > > I am able to get it working using the following: > `select(User).from_statement(ins.returing(*select(User).selected_columns))` > > I get your point that there may not be much of a demand for this, but I > would argue that it is a bit unexpected for `returning(User)` to return a > Core row, and that the solution is bit unintuitive. > > > as I already agreed, it is unintuitive for now, but it's not clear it can > be made fully automatic. it would be potentially a very large job for > something that can already be achieved right now with a little more API use. > > > > I think it should be as easy as .returning(User) and it should return > the full ORM model with column_properties preloaded. > > > this remains a non-trivial improvement that is not on the timeline right > now, so you will have to work with what we have. > > > > This proposed change is backwards incompatible right? E.g. if people > are depending on `returning(User)` returning a core Row in 2.0, is it OK to > change this to return a Model instance in 2.1? > > > not really sure, this is part of the problem. we reserve the right to > make backwards incompatible changes in a the middle point since we are not > on semver. as returning(User) is not that intuitive when the documented > approach isn't used, we will assume people are not using that form very > much should we decide to implement this feature. > > > By the way, I think I found a bug with insert().values() when the ORM uses > different field names than the Database column names. update().values() > works fine, but not insert().values(). Please check my issue here when you > have a moment. > > > that can likely be improved for 2.0. > > > https://github.com/sqlalchemy/sqlalchemy/issues/7864 > > -- > > Here is how to reproduce the case where column_property results in an > extra query, if you are interested: > > > sure, there's a very complex process that's used to SELECT all columns. > your use case should work right now if you do something like this: > > select(User).from_statement(insert(User).returning(User, > User.my_column_property)) > > > > > > class User(Base): > __tablename__ = 'users' > id = Column(Integer, primary_key=True) > first_name = Column(String(30)) > last_name = Column(String(30)) > full_name = column_property(first_name + " " + last_name) > > # returning(User) triggers extra query on column_property access > res = > session.execute(select(User).from_statement(insert(User).values(first_name='foo', > > last_name='bar').returning(User))) > user = res.scalars().one() > # This triggers a select > print(user.full_name) > > session.expunge(user) > > # normal query does not trigger a select as expected > res = session.execute(select(User).where(User.id == user.id)) > user = res.scalars().one() > print(user.full_name) > > session.expunge(user) > > # use *selected(User).selected_columns to avoid triggering an extra > select > res = > session.execute(select(User).from_statement(update(User).values(first_name='foo2').where(User.id > > == user.id).returning(*select(User).selected_columns)))
Re: [sqlalchemy] How to infer the ORM model class from an update()/insert() object?
Hi Mike, When using `column_property`, this `select(User).from_statement(ins.returing(User))` construct will not load in the column property. Instead the ORM will issue a second query when the column property is accessed. I am able to get it working using the following: `select(User).from_statement(ins.returing(*select(User).selected_columns))` I get your point that there may not be much of a demand for this, but I would argue that it is a bit unexpected for `returning(User)` to return a Core row, and that the solution is bit unintuitive. I think it should be as easy as .returning(User) and it should return the full ORM model with column_properties preloaded. This proposed change is backwards incompatible right? E.g. if people are depending on `returning(User)` returning a core Row in 2.0, is it OK to change this to return a Model instance in 2.1? By the way, I think I found a bug with insert().values() when the ORM uses different field names than the Database column names. update().values() works fine, but not insert().values(). Please check my issue here when you have a moment. https://github.com/sqlalchemy/sqlalchemy/issues/7864 -- Here is how to reproduce the case where column_property results in an extra query, if you are interested: class User(Base): __tablename__ = 'users' id = Column(Integer, primary_key=True) first_name = Column(String(30)) last_name = Column(String(30)) full_name = column_property(first_name + " " + last_name) # returning(User) triggers extra query on column_property access res = session.execute(select(User).from_statement(insert(User).values(first_name='foo', last_name='bar').returning(User))) user = res.scalars().one() # This triggers a select print(user.full_name) session.expunge(user) # normal query does not trigger a select as expected res = session.execute(select(User).where(User.id == user.id)) user = res.scalars().one() print(user.full_name) session.expunge(user) # use *selected(User).selected_columns to avoid triggering an extra select res = session.execute(select(User).from_statement(update(User).values(first_name='foo2').where(User.id == user.id).returning(*select(User).selected_columns))) user = res.scalars().one() # no extra query print(user.full_name) Thanks and best regards, Matthew On Sunday, March 27, 2022 at 7:28:55 PM UTC-7 Mike Bayer wrote: > > > On Sun, Mar 27, 2022, at 2:56 PM, mkmo...@gmail.com wrote: > > Hi Mike, > > I'm writing a library that uses SQLAlchemy. The user will pass the library > an update, and the library will add a RETURNING clause for postgresql > users, and then return the model back to the user. The idea here is to > update and select the row in a single database call, instead of the normal > approach where two calls are made. > > However, `upd.returning(User)` will actually return a Core row, not the > ORM model instance: > > upd = update(User).values(name='foo').where(User.id == > 1).returning(User) > result = session.execute(upd) > row = result.one() > assert isinstance(row, Row) > > The key question I have is how to convert a Core row into an ORM model > instance. > > > use the construct select(User).from_statement(update(User)...returning()) > . See the example at > https://docs.sqlalchemy.org/en/14/orm/persistence_techniques.html#using-insert-update-and-on-conflict-i-e-upsert-to-return-orm-objects > > > > > `model(**row._mapping)` fails in at least these two cases: different > field name in ORM vs database, and column_property. > > I also wonder, should SQLAlchemy return the Model instead of the core row > in this case? > > > unknown at this time. The above link illustrates a very new technique by > which this can work now.if this were to become more implicit without > the extra step, that would at best be a 2.1 thing not expected for at least > 18 months, it would be based on general demand for this kind of thing > (which does seem to be increasing). > > > > > > > > --- > > I've gotten this far: > > model = model_from_dml(upd) > upd = upd.returning(*select(model).selected_columns) # this will > apply the column_property to the RETURNING > row = session.execute(upd).one() > > Now I just need to take this row and convert it to an ORM object. > > Is there a public API I can use to take a Core `row` and convert it to an > ORM model? > > Thanks and best regards, > > Matthew > On Sunday, March 27, 2022 at 11:11:30 AM UTC-7 Mike Bayer wrote: > > > > On Sun, Mar 27, 2022, at 2:08 PM, mkmo...@gmail.com wrote: > > Hi Mike, > > Thanks. Should I use column_descript
Re: [sqlalchemy] How to infer the ORM model class from an update()/insert() object?
Hi Mike, I'm writing a library that uses SQLAlchemy. The user will pass the library an update, and the library will add a RETURNING clause for postgresql users, and then return the model back to the user. The idea here is to update and select the row in a single database call, instead of the normal approach where two calls are made. However, `upd.returning(User)` will actually return a Core row, not the ORM model instance: upd = update(User).values(name='foo').where(User.id == 1).returning(User) result = session.execute(upd) row = result.one() assert isinstance(row, Row) The key question I have is how to convert a Core row into an ORM model instance. `model(**row._mapping)` fails in at least these two cases: different field name in ORM vs database, and column_property. I also wonder, should SQLAlchemy return the Model instead of the core row in this case? --- I've gotten this far: model = model_from_dml(upd) upd = upd.returning(*select(model).selected_columns) # this will apply the column_property to the RETURNING row = session.execute(upd).one() Now I just need to take this row and convert it to an ORM object. Is there a public API I can use to take a Core `row` and convert it to an ORM model? Thanks and best regards, Matthew On Sunday, March 27, 2022 at 11:11:30 AM UTC-7 Mike Bayer wrote: > > > On Sun, Mar 27, 2022, at 2:08 PM, mkmo...@gmail.com wrote: > > Hi Mike, > > Thanks. Should I use column_descriptions[0]['type'] ? > > > yup, that should be pretty consistent in this case. > > I've implemented most of an actual feature for this but isn't committed > yet at https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/3742 > > > In my case, `type`, `expr` and `entity` all return the model class that I > am interested in. > > Thanks and best regards, > > Matthew > On Saturday, March 26, 2022 at 12:02:54 PM UTC-7 Mike Bayer wrote: > > > the Project model is actually in there, but not in a public API place > (this is not the solution, but look inside of table._annotations to see it). > > The closest public API we have for this very new API right now is the > Query equivalent of column_descriptions, which is available on the select() > construct and works when the thing being selected is ORM-enabled, and, > alarmingly, it seems there is no documentation whatsoever for the Select > version of it, that is wrong, but anyway see the 1.x docs for now: > https://docs.sqlalchemy.org/en/14/orm/query.html#sqlalchemy.orm.Query.column_descriptions > > This accessor would ideally be on insert, update and delete also, which it > currently is not. However, here's a quick way to get it right now: > > class A(Base): > __tablename__ = 'a' > > id = Column(Integer, primary_key=True) > data = Column(String) > > > upd = update(A) > > print(select(upd.table).column_descriptions) > > i might take a crack at cleaning this up now but the above will get you > what you need. > > On Sat, Mar 26, 2022, at 1:34 PM, mkmo...@gmail.com wrote: > > Hello, > > How can I infer the ORM model class from an update (or insert, or delete) > function result? > > upd = update(Project).values(name='foo').where( > Project.id == 1 > ) > > def my_library_function(session, upd): > result = session.execute(upd) > # how to get the Project ORM model here, using only session and upd ? > > I saw that the update() object has a `table` attribute, but this returns > the Core table (not the ORM model). In addition I don't have access to the > base/registry from this function (unless it can be derived from session?). > Moreover it seems like searching the registry is O(n) and will not work in > all cases, such as when two ORM models map to the same Core table. > > 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/9fc63126-a36d-4e36-b4df-50701bfcae47n%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/9fc63126-a36d-4e36-b4df-50701bfcae47n%40googlegroups.com?utm_medium=email_source=footer> > . > > > > -- > SQLAlchemy - > The Python SQL Toolkit and O
Re: [sqlalchemy] How to infer the ORM model class from an update()/insert() object?
Hi Mike, Thanks. Should I use column_descriptions[0]['type'] ? In my case, `type`, `expr` and `entity` all return the model class that I am interested in. Thanks and best regards, Matthew On Saturday, March 26, 2022 at 12:02:54 PM UTC-7 Mike Bayer wrote: > the Project model is actually in there, but not in a public API place > (this is not the solution, but look inside of table._annotations to see it). > > The closest public API we have for this very new API right now is the > Query equivalent of column_descriptions, which is available on the select() > construct and works when the thing being selected is ORM-enabled, and, > alarmingly, it seems there is no documentation whatsoever for the Select > version of it, that is wrong, but anyway see the 1.x docs for now: > https://docs.sqlalchemy.org/en/14/orm/query.html#sqlalchemy.orm.Query.column_descriptions > > This accessor would ideally be on insert, update and delete also, which it > currently is not. However, here's a quick way to get it right now: > > class A(Base): > __tablename__ = 'a' > > id = Column(Integer, primary_key=True) > data = Column(String) > > > upd = update(A) > > print(select(upd.table).column_descriptions) > > i might take a crack at cleaning this up now but the above will get you > what you need. > > On Sat, Mar 26, 2022, at 1:34 PM, mkmo...@gmail.com wrote: > > Hello, > > How can I infer the ORM model class from an update (or insert, or delete) > function result? > > upd = update(Project).values(name='foo').where( > Project.id == 1 > ) > > def my_library_function(session, upd): > result = session.execute(upd) > # how to get the Project ORM model here, using only session and upd ? > > I saw that the update() object has a `table` attribute, but this returns > the Core table (not the ORM model). In addition I don't have access to the > base/registry from this function (unless it can be derived from session?). > Moreover it seems like searching the registry is O(n) and will not work in > all cases, such as when two ORM models map to the same Core table. > > 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/9fc63126-a36d-4e36-b4df-50701bfcae47n%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/9fc63126-a36d-4e36-b4df-50701bfcae47n%40googlegroups.com?utm_medium=email_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/abc01658-17a5-451b-aca7-0864998c5af7n%40googlegroups.com.
[sqlalchemy] How to infer the ORM model class from an update()/insert() object?
Hello, How can I infer the ORM model class from an update (or insert, or delete) function result? upd = update(Project).values(name='foo').where( Project.id == 1 ) def my_library_function(session, upd): result = session.execute(upd) # how to get the Project ORM model here, using only session and upd ? I saw that the update() object has a `table` attribute, but this returns the Core table (not the ORM model). In addition I don't have access to the base/registry from this function (unless it can be derived from session?). Moreover it seems like searching the registry is O(n) and will not work in all cases, such as when two ORM models map to the same Core table. 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/9fc63126-a36d-4e36-b4df-50701bfcae47n%40googlegroups.com.
Re: [sqlalchemy] Conditional Insertion of Single Row with Bind Parameters
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 On Wednesday, March 23, 2022 at 6:21:34 AM UTC-7 Mike Bayer wrote: > > > 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+...@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_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/e0d5c0f4-4e9f-4301-9d5b-762786b7fc43n%40googlegroups.com.
[sqlalchemy] Conditional Insertion of Single Row with Bind Parameters
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 -- 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.
[sqlalchemy] How to derive table/table name from a column.label()'d object?
In sqlalchemy Core, I can get the table and table name from a regular column by calling `c.table.name`: from sqlalchemy import table, column t = table('foo', column('bar')) assert hasattr(t.c.bar, 'table') print(t.c.bar.table.name) However, If I label a column, the label no longer has a `table` attribute: lab = t.c.bar.label('baz') assert not hasattr(lab, 'table') Is there any way to go from the label to the column, or from the label to the table? I've found this one, but it seems a bit clunky: lab = t.c.bar.label('baz') c = list(lab.base_columns)[0] print(c.table.name) Will label.base_columns always return a set of at least 1 item, or do I need to check? When will label.base_columns return more than 1 item? If I wanted something generic that handles both columns and labels, will this work safely: def get_table_name(column): return list(column.base_columns)[0].table.name assert get_table_name(t.c.bar) == 'foo' assert get_table_name(t.c.bar.label('baz')) == 'foo' 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/5ee8c50a-4f0d-4d18-92e1-a355e1431a0an%40googlegroups.com.
Re: [sqlalchemy] Automatically add Index on same columns in ForeignKeyConstraint in Postgresql using Custom DDL/Compilation
Hi Mike, Thanks for your feedback. I'll leave your suggestion here if anyone comes across this: from sqlalchemy import event, Table @event.listens_for(Table, 'before_create') def add_index_on_foreign_key_columns(table, connection, **kwargs): for foreign_key in table.foreign_key_constraints: index_name = foreign_key.name.replace('_fk', '_idx') columns = (c <http://c.name> for c in foreign_key.columns) Index(index_name, *columns) Best regards, Matthew Moisen On Friday, June 25, 2021 at 9:58:56 AM UTC-7 Mike Bayer wrote: > > > On Fri, Jun 25, 2021, at 11:58 AM, mkmo...@gmail.com wrote: > > > When using PostgreSQL, creating a foreign key on a column in a table does > not automatically index that column, unlike Oracle or MySQL. > > I would like to get SQLAlchemy to automatically create an index on the > same columns that are specified in a ForeingKeyConstraint. > > For example, if I have a table like this: > > foo = table( > 'foos', metadata, > Column('id', BigInteger), > Column('parent_id', BigInteger), > ForeignKeyConstraint(('parent_id',), refcolumns=('bar.id',), > name='foo_parent_id_fk') > ) > > I would like it to automatically add an Index like: > > Index('foo_parent_id_idx', 'parent_id') > > I've made the following, which appears to work: > > from sqlalchemy import event, Table > > @event.listens_for(Table, 'before_create') > def add_index_on_foreign_key_columns(table, connection, **kwargs): > for foreign_key in table.foreign_key_constraints: > index_name = foreign_key.name.replace('_fk', '_idx') > columns = (c.name for c in foreign_key.columns) > Index(index_name, *columns, _table=table) > > Is there a better way to accomplish this? > > > that's the perfect way to accomplish this and is just what I'd recommend - > the only thing I would change is when creating the Index(), it should > auto-add itself to the Table as you are passing Table-bound Column objects > to it, so i would not use the private "_table" parameter. Otherwise, you > can say "table.append_index(my_index)" to ensure it is added to the Table. > > > > > > > Thanks and best regards, > > Matthew Moisen > > > -- > 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/43dba570-41de-4164-82c8-1cecb3dd9b1cn%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/43dba570-41de-4164-82c8-1cecb3dd9b1cn%40googlegroups.com?utm_medium=email_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/6885dfaf-8021-49d8-be2d-872448ac86fdn%40googlegroups.com.
[sqlalchemy] Automatically add Index on same columns in ForeignKeyConstraint in Postgresql using Custom DDL/Compilation
When using PostgreSQL, creating a foreign key on a column in a table does not automatically index that column, unlike Oracle or MySQL. I would like to get SQLAlchemy to automatically create an index on the same columns that are specified in a ForeingKeyConstraint. For example, if I have a table like this: foo = table( 'foos', metadata, Column('id', BigInteger), Column('parent_id', BigInteger), ForeignKeyConstraint(('parent_id',), refcolumns=('bar.id',), name='foo_parent_id_fk') ) I would like it to automatically add an Index like: Index('foo_parent_id_idx', 'parent_id') I've made the following, which appears to work: from sqlalchemy import event, Table @event.listens_for(Table, 'before_create') def add_index_on_foreign_key_columns(table, connection, **kwargs): for foreign_key in table.foreign_key_constraints: index_name = foreign_key.name.replace('_fk', '_idx') columns = (c.name for c in foreign_key.columns) Index(index_name, *columns, _table=table) Is there a better way to accomplish this? Thanks and best regards, Matthew Moisen -- 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/43dba570-41de-4164-82c8-1cecb3dd9b1cn%40googlegroups.com.
Re: [sqlalchemy] Rationale for why 1.4 Core `select` argument as list is deprecated?
Hi Mike, would you mind elaborating on why a library needs to support typing to remain relevant? For example, does it have some potential impact on downstream users of a library who want to take advantage of typing? I have a (very small) library, and is completely untyped. I don't really like typing but want to keep an open mind. Thanks and best regards, Matthew On Thursday, May 13, 2021 at 8:03:43 AM UTC-7 Mike Bayer wrote: > > Python is going towards using typing, period. Any widely used library > has to support this fully to remain relevant, so even if we didn't like > typing, we still have to support it :) . It has no impact on you as a > user of the library, you can ignore typing completely and there's no issue > with that. > > > On Thu, May 13, 2021, at 9:55 AM, mkmo...@gmail.com wrote: > > Hi Mike. > > Thanks, it makes sense. On another topic, the docs say: > > However, version 2.0 hopes to start embracing *PEP 484* > <https://www.python.org/dev/peps/pep-0484> and other new features to a > great degree > > Would you please explain why SQLAlchemy wants to move to embracing type > hints to a large degree? I'm a bit ambivalent towards type hints and would > love to hear your perspective. > > Thanks and best regards, > > Matthew > > On Thursday, May 13, 2021 at 5:54:21 AM UTC-7 Mike Bayer wrote: > > > SQLAlchemy has a general philosophy of fn(*args) vs fn(list): > > 1. if the sequence of items represents **database data**, we use a > **list** or other inline sequence. E.g. in_(): > > column.in_([1, 2, 3]) > > 2. if the sequence of items represents **SQL structure**, we use a > variable length *Args. E.g. Table: > >Table(name, metadata, *cols_and_other_constraints) > > and_(), or(), etc: > >and_(*sequence_of_expressions( > > ORM query: > >session.query(*sequence_of_entities_expressions_etc) > > select() should work the same as all these other APIs and in particular > it's now largely cross-compatible with ORM query as well. > > The reason select() has always accepted a a list is because the very > ancient and long de-emphasized API for select() looked like this: > >stmt= select({table.c.col1, table.c.ol2], table.c.col1 == > "some_expression", order_by=table.c.col1) > > that is, the "generative" API for select() that is very normal now did not > exist. There was no select().where().order_by(), that was all added years > after SQLAlchemy's first releases.All of those kwargs are also > deprecated as they are very ancient legacy code and we'd like SQLAlchemy's > API to be clean and consistent and not allowing of many variations of the > same thing, as this makes the whole system easier to use and understand. > > So the use of a list in select() is based on long ago deemphasized API > that SQLAlchemy 2.0 seeks to finally remove. As this API is one of the > most complicated to provide a backwards-compatibility path for, it's taken > a very long time for us to get there.But here we are. > > > > > On Thu, May 13, 2021, at 2:10 AM, mkmo...@gmail.com wrote: > > Hello, > > I am on Sqlalchemy 1.2 and looking to upgrade to 1.4. > > I have a lot of Core code that looks like this: > > # returns an array of columns > select_fields = make_select_fields(...) > sel = select(select_fields) > > Passing an array to select is now deprecated, so I'm planning on changing > all my code to: > > sel = select(*select_fields) > > I was curious to know what the rationale for the change in this API is. > > 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/c0de313c-a113-4e8f-9ae4-f30be96a8c9dn%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/c0de313c-a113-4e8f-9ae4-f30be96a8c9dn%40googlegroups.com?utm_medium=email_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 > Verifi
Re: [sqlalchemy] Rationale for why 1.4 Core `select` argument as list is deprecated?
Hi Mike. Thanks, it makes sense. On another topic, the docs say: However, version 2.0 hopes to start embracing *PEP 484* <https://www.python.org/dev/peps/pep-0484> and other new features to a great degree Would you please explain why SQLAlchemy wants to move to embracing type hints to a large degree? I'm a bit ambivalent towards type hints and would love to hear your perspective. Thanks and best regards, Matthew On Thursday, May 13, 2021 at 5:54:21 AM UTC-7 Mike Bayer wrote: > SQLAlchemy has a general philosophy of fn(*args) vs fn(list): > > 1. if the sequence of items represents **database data**, we use a > **list** or other inline sequence. E.g. in_(): > > column.in_([1, 2, 3]) > > 2. if the sequence of items represents **SQL structure**, we use a > variable length *Args. E.g. Table: > >Table(name, metadata, *cols_and_other_constraints) > > and_(), or(), etc: > >and_(*sequence_of_expressions( > > ORM query: > >session.query(*sequence_of_entities_expressions_etc) > > select() should work the same as all these other APIs and in particular > it's now largely cross-compatible with ORM query as well. > > The reason select() has always accepted a a list is because the very > ancient and long de-emphasized API for select() looked like this: > >stmt= select({table.c.col1, table.c.ol2], table.c.col1 == > "some_expression", order_by=table.c.col1) > > that is, the "generative" API for select() that is very normal now did not > exist. There was no select().where().order_by(), that was all added years > after SQLAlchemy's first releases.All of those kwargs are also > deprecated as they are very ancient legacy code and we'd like SQLAlchemy's > API to be clean and consistent and not allowing of many variations of the > same thing, as this makes the whole system easier to use and understand. > > So the use of a list in select() is based on long ago deemphasized API > that SQLAlchemy 2.0 seeks to finally remove.As this API is one of the > most complicated to provide a backwards-compatibility path for, it's taken > a very long time for us to get there.But here we are. > > > > > On Thu, May 13, 2021, at 2:10 AM, mkmo...@gmail.com wrote: > > Hello, > > I am on Sqlalchemy 1.2 and looking to upgrade to 1.4. > > I have a lot of Core code that looks like this: > > # returns an array of columns > select_fields = make_select_fields(...) > sel = select(select_fields) > > Passing an array to select is now deprecated, so I'm planning on changing > all my code to: > > sel = select(*select_fields) > > I was curious to know what the rationale for the change in this API is. > > 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/c0de313c-a113-4e8f-9ae4-f30be96a8c9dn%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/c0de313c-a113-4e8f-9ae4-f30be96a8c9dn%40googlegroups.com?utm_medium=email_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/d6216602-9827-432d-bac1-94f7290d3992n%40googlegroups.com.
[sqlalchemy] Rationale for why 1.4 Core `select` argument as list is deprecated?
Hello, I am on Sqlalchemy 1.2 and looking to upgrade to 1.4. I have a lot of Core code that looks like this: # returns an array of columns select_fields = make_select_fields(...) sel = select(select_fields) Passing an array to select is now deprecated, so I'm planning on changing all my code to: sel = select(*select_fields) I was curious to know what the rationale for the change in this API is. 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/c0de313c-a113-4e8f-9ae4-f30be96a8c9dn%40googlegroups.com.
Re: [sqlalchemy] Using SQLAlchemy Core as Query Builder, not ORM, in CRUD applications
> column based on a select query. > > e.g. (Silly example, but you can see what I mean) > > if DBGetFuncValue(‘students’, ‘MAX’, ‘lastTestResult’, > studentCategoryID=23) < passingGrade: > print “This bunch of students all failed!" > > I use these literally all day every day and they have proven to be a real > asset. Recently, I wanted to support a way of raising notifications (in my > case adding things into a Queue.Queue() when operations happened on some > tables. I simply updated the central method and it worked, even though > there were 1000’s of cases where it was used. :-) > > With all of the above, there are some more subtle use cases that I have > not really covered. e.g. If I pass in a tuple if (‘tableName’, > sessionObject) then the code will do the insert/update inside a transaction > for that session, rather than a direct update etc. > > Hope you gain some insight and inspiration. Happy to chat 1:1 if you > would like more info. > > Cheers > Warwick > > On 23 Apr 2021, at 11:51 AM, mkmo...@gmail.com wrote: > > Hi dAll, > > Some people don't like ORMs and instead use query builders like SQLAlchemy > Core (or even raw SQL text) even for something like a CRUD application. > > For sake of argument, let's put aside whether or not this is a good idea. > I'm just curious how one would go about this without copy and pasting a > large amount of functions for every table in your data model. > > For example if you had two tables, School and Student, you would probably > have functions like insert_school, insert_student, update_school, > update_student, get_school, get_student, delete_school, delete_student, and > etc. where the majority of the implementing code is the same except for the > table and field names. > > Do people who use SQLAlchemy Core as a query builder for CRUD applications > simply write out all these functions? Or do they try to create some kind > of generic function that can introspect the table? Is there another > approach I may not be considering? > > Thanks and best regards, > > Matthew Moisen > > -- > 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/ab775a14-4045-49d4-946a-0adc0ef887ecn%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/ab775a14-4045-49d4-946a-0adc0ef887ecn%40googlegroups.com?utm_medium=email_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/0eff8e69-3018-4e6d-8790-a8137b842869n%40googlegroups.com.
[sqlalchemy] Using SQLAlchemy Core as Query Builder, not ORM, in CRUD applications
Hi All, Some people don't like ORMs and instead use query builders like SQLAlchemy Core (or even raw SQL text) even for something like a CRUD application. For sake of argument, let's put aside whether or not this is a good idea. I'm just curious how one would go about this without copy and pasting a large amount of functions for every table in your data model. For example if you had two tables, School and Student, you would probably have functions like insert_school, insert_student, update_school, update_student, get_school, get_student, delete_school, delete_student, and etc. where the majority of the implementing code is the same except for the table and field names. Do people who use SQLAlchemy Core as a query builder for CRUD applications simply write out all these functions? Or do they try to create some kind of generic function that can introspect the table? Is there another approach I may not be considering? Thanks and best regards, Matthew Moisen -- 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/ab775a14-4045-49d4-946a-0adc0ef887ecn%40googlegroups.com.