Re: [sqlalchemy] Getting a triple of related id fields
On Tue, Jun 20, 2023 at 3:47 PM Mike Bayer wrote: > > > step 1 is stop using that silly Flask extension that gives you > "Pipeline.query", I can't tell what it is you want to SELECT from either by > reading this query. Wow, that made things a lot easier. Is there any reason to avoid mixing Model.query with direct SQLAlchemy calls? We have a bunch of Model.query right now. I'm wondering if we'd need to rewrite all those upfront, or if we could redo them as time permits. Thanks! -- 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/CAOvKW55pTerGhFw6F7%3D%3DCgbz0fu2N71uUOgk73A91jP3M10AEQ%40mail.gmail.com.
[sqlalchemy] Getting a triple of related id fields
I've been banging on this for hours, but I seem to be getting nowhere. I've tried more things that I can count, but here are two of my attempts: # result = ( #Pipeline.query # .select_from(Storage, NewProduct) # .join(Storage, pipeline_alias1.storage_id == Storage.id) # .join(NewProduct, Storage.product_id == NewProduct.id) # .filter(pipeline_alias2.storage_id == storage_alias1.id) # .filter(storage_alias2.product_id == product_alias1.id) # ) result = ( Pipeline.query .select_from(Pipeline, Storage, NewProduct) .join(Storage, pipeline_alias1.storage_id == storage_alias1.id) .join(NewProduct, storage_alias2.product_id == product_alias1.id) ) I keep getting: sqlalchemy.exc.InvalidRequestError: Can't determine which FROM clause to join from, there are multiple FROMS which can join to this entity. Please use the .select_from() method to establish an explicit left side, as well as providing an explicit ON clause if not present already to help resolve the ambiguity. How can I tell SQLAlchemy which FROM to use? Thanks! -- 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/CAOvKW57frKP1EACaQ4zoRwu7kDAUULAbXx1xFi8eVdw69tZCZg%40mail.gmail.com.
[sqlalchemy] Turning a complex query into a view for SQLAlchemy?
Hi. In https://pajhome.org.uk/blog/10_reasons_to_love_sqlalchemy.html it says: When performing highly complex queries, it is possible to define these with SQLAlchemy syntax. However, I find there's a certain level of complexity where it becomes easier to write SQL directly. In that case, you can define a database view that encompasses the complex query, and SQLAlchemy can map the view to Python objects. I spoke with my team lead about this practice, and he was open to trying it but said he had heard that SQLAlchemy may have problems using views. Does anyone on the list have anything to add here? Thanks! -- 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/CAOvKW54y462yR4m5pOV5ukACA7v0CgCDUcrDztRBogtuE9t_GA%40mail.gmail.com.
[sqlalchemy] subquery relationships?
Hi folks. I have a subquery that is selected from a table with 5 foreign keys, and joined with another table with 3 foreign keys. And then that subquery is used in a join with the table having the 3 foreign keys again. I don't know how to tell what column(s) that join is happening on. What goes into SQLAlchemy's "thought process" when it joins a table with a subquery? Thanks! -- 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/CAOvKW56oZPu4NByXY1qZO99T8BQ_L54w42y6cex9zJyEoPmejw%40mail.gmail.com.
[sqlalchemy] Interpreting a SA traceback
I know, python2 is long dead. We’re almost ready for Python3, but not quite. Anyway, here’s a traceback that I’m not sure what to make of. Is it saying that a transaction got so big that it couldn’t be fully flushed within the timeout window? I’ve elided a sensitive part from the very end of the traceback. I realize this error is coming from mysql, not SA. Traceback (most recent call last): File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1475, in full_dispatch_request rv = self.dispatch_request() File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1461, in dispatch_request return self.view_functions[rule.endpoint](**req.view_args) File "/app/app/common/routing.py", line 117, in __call__ return Wrapper(*args, **kwargs) File "/app/app/common/auth/manager.py", line 340, in AuthDecorator return callback(self, *args, **kwargs) File "/app/app/common/routing.py", line 101, in Wrapper return self.callback(context, *a, **k) File "/app/app/api/service/v3/publish/controllers.py", line 99, in publish_new_build return publish_helper(context, manifestData, pipeline, keyring, token=token) File "/app/app/common/publishing/helpers.py", line 181, in publish_helper build.save(commit=False) File "/app/app/db/mixins.py", line 16, in save _session.flush() File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/scoping.py", line 153, in do return getattr(self.registry(), name)(*args, **kwargs) File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", line 2254, in flush self._flush(objects) File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", line 2380, in _flush transaction.rollback(_capture_exception=True) File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/util/langhelpers.py", line 66, in __exit__ compat.reraise(exc_type, exc_value, exc_tb) File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", line 2344, in _flush flush_context.execute() File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/unitofwork.py", line 391, in execute rec.execute(self) File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/unitofwork.py", line 556, in execute uow File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/persistence.py", line 181, in save_obj mapper, table, insert) File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/persistence.py", line 866, in _emit_insert_statements execute(statement, params) File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 948, in execute return meth(self, multiparams, params)ddd File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/elements.py", line 269, in _execute_on_connection return connection._execute_clauseelement(self, multiparams, params) File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1060, in _execute_clauseelement compiled_sql, distilled_params File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context context) File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception exc_info File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause reraise(type(exception), exception, tb=exc_tb, cause=cause) File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context context) File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 507, in do_execute cursor.execute(statement, parameters) File "/usr/local/lib/python2.7/dist-packages/pymysql/cursors.py", line 165, in execute result = self._query(query) File "/usr/local/lib/python2.7/dist-packages/pymysql/cursors.py", line 321, in _query conn.query(q) File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 860, in query self._affected_rows = self._read_query_result(unbuffered=unbuffered) File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 1061, in _read_query_result result.read() File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 1349, in read first_packet = self.connection._read_packet() File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 1018, in _read_packet packet.check_error() File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 384, in check_error err.raise_mysql_exception(self._data) File "/usr/local/lib/python2.7/dist-packages/pymysql/err.py", line 107, in raise_mysql_exception raise errorclass(errno, errval) InternalError: (pymysql.err.InternalError) (1205, u'Lock wait timeout exceeded; try restarting transaction') [SQL: u'INSERT INTO … (Background on this error at: http://sqlalche.me/e/2j85) We’re running: tact@tact_pub_api:/app$ python2 -m pip list -v | grep -i sqlalchemy Flask-SQLAlchemy 2.5.1 /usr/local/lib/python2.7/dist-packages pip SQLAlchemy1.2.5
Re: [sqlalchemy] Test query seems to spuriously give sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'")
Granted, it’s difficult to read my (admittedly rather blank) mind. Maybe I should restate the question: What are my options? I just want to see the rows from the query below. Why is it telling me I need to aggregate, and if I do truly need to, what might be an aggregate function that won’t eliminate much of what the query is producing? From: 'Dan Stromberg [External]' via sqlalchemy Date: Tuesday, March 21, 2023 at 9:05 AM To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] Test query seems to spuriously give sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'") Alright, using join_from may have led to clearing a hurdle. I’m now using: query = ( select(NV. id, func. min(bs_3. build_id)) .select_from(bs) .join(v_2, onclause=(bs. version_id == v_2. id)) .join_from(bs_2, Br, onclause=(Br. id == bs_2. branch_id)) Alright, using join_from may have led to clearing a hurdle. I’m now using: query = ( select(NV.id, func.min(bs_3.build_id)) .select_from(bs) .join(v_2, onclause=(bs.version_id == v_2.id)) .join_from(bs_2, Br, onclause=(Br.id == bs_2.branch_id)) ) ..and am getting a new error: sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1140, "In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'tact_dev.tb_nv.id'; this is incompatible with sql_mode=only_full_group_by") Do I need to aggregate? Or perhaps change sql_mode? Thanks! From: sqlalchemy@googlegroups.com on behalf of Mike Bayer Date: Monday, March 20, 2023 at 5:33 PM To: noreply-spamdigest via sqlalchemy Subject: Re: [sqlalchemy] Test query seems to spuriously give sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'") OK, not really, you want tables in the FROM clause. use either the select_from() or join_from() method to do that: https: //docs. sqlalchemy. org/en/20/tutorial/data_select. html#explicit-from-clauses-and-joins On Mon, Mar 20, 2023, at 5: 16 PM, OK, not really, you want tables in the FROM clause. use either the select_from() or join_from() method to do that: https://docs.sqlalchemy.org/en/20/tutorial/data_select.html#explicit-from-clauses-and-joins<https://urldefense.com/v3/__https:/docs.sqlalchemy.org/en/20/tutorial/data_select.html*explicit-from-clauses-and-joins__;Iw!!Ci6f514n9QsL8ck!gsSZYRslnIShc80D5SJP9hQv7FJkNL5Bzfvc8dkqobmEg8-ctkAcRyR1sZuv3pRL4eCzLvlJC-VDSf5sXXQNtX0d4POMpzTQh3-QUw$> On Mon, Mar 20, 2023, at 5:16 PM, 'Dan Stromberg [External]' via sqlalchemy wrote: I’m getting some pushback internally, from my team lead – he and I both think it’s probably too much detail to share. It’s 43 lines of SQL with multiple subqueries. Would just the simplest parts of the from clause work? From: sqlalchemy@googlegroups.com on behalf of Mike Bayer Date: Monday, March 20, 2023 at 1:11 PM To: noreply-spamdigest via sqlalchemy Subject: Re: [sqlalchemy] Test query seems to spuriously give sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'") what SQL are you going for ? start with that. On Mon, Mar 20, 2023, at 10: 33 AM, 'Dan Stromberg [External]' via sqlalchemy wrote: That makes sense, but…. I’m afraid I don’t know how to add tb_br to the select. I tried: query = ( select(NV. id, what SQL are you going for ? start with that. On Mon, Mar 20, 2023, at 10:33 AM, 'Dan Stromberg [External]' via sqlalchemy wrote: That makes sense, but…. I’m afraid I don’t know how to add tb_br to the select. I tried: query = ( select(NV.id, func.min(bs_3.build_id)) .select_from(bs, Br) .join(v_2, onclause=(bs.version_id == v_2.id)) .join(bs_2, onclause=(Br.id == bs_2.branch_id)) ) …which gave: 1054, "Unknown column 'tb_br.id' in 'on clause'" …and I tried: query = ( select(NV.id, func.min(bs_3.build_id), Br) .select_from(bs) .join(v_2, onclause=(bs.version_id == v_2.id)) .join(bs_2, onclause=(Br.id == bs_2.branch_id)) ) …which also gave: (1054, "Unknown column 'tb_br.id' in 'on clause'") I’m guessing I’m missing something simple, but I have no idea what. Any (further) suggestions? From: sqlalchemy@googlegroups.com on behalf of Mike Bayer Date: Saturday, March 18, 2023 at 8:01 AM To: noreply-spamdigest via sqlalchemy Subject: Re: [sqlalchemy] Test query seems to spuriously give sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'") the query emitted is: SELECT tb_nv. id, min(bs_3. build_id) AS min_1 FROM tb_nv, tb_brst AS bs_3, tb_brst AS bs INNER JOIN tb_vers AS v_2 ON bs. version_id = v_2. id INNER JOIN tb_brst AS bs_2 ON tb_br. id = bs_2. branch_id the error means that your
Re: [sqlalchemy] Test query seems to spuriously give sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'")
Hoping to save an iteration: the SQL currently looks like: [SQL: SELECT tb_nv.id, min(bs_3.build_id) AS min_1 FROM tb_nv, tb_brst AS bs_3, tb_brst AS bs INNER JOIN tb_vers AS v_2 ON bs.version_id = v_2.id, tb_brst AS bs_2 INNER JOIN tb_br ON tb_br.id = bs_2.branch_id] From: 'Dan Stromberg [External]' via sqlalchemy Date: Tuesday, March 21, 2023 at 9:05 AM To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] Test query seems to spuriously give sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'") Alright, using join_from may have led to clearing a hurdle. I’m now using: query = ( select(NV. id, func. min(bs_3. build_id)) .select_from(bs) .join(v_2, onclause=(bs. version_id == v_2. id)) .join_from(bs_2, Br, onclause=(Br. id == bs_2. branch_id)) Alright, using join_from may have led to clearing a hurdle. I’m now using: query = ( select(NV.id, func.min(bs_3.build_id)) .select_from(bs) .join(v_2, onclause=(bs.version_id == v_2.id)) .join_from(bs_2, Br, onclause=(Br.id == bs_2.branch_id)) ) ..and am getting a new error: sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1140, "In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'tact_dev.tb_nv.id'; this is incompatible with sql_mode=only_full_group_by") Do I need to aggregate? Or perhaps change sql_mode? Thanks! From: sqlalchemy@googlegroups.com on behalf of Mike Bayer Date: Monday, March 20, 2023 at 5:33 PM To: noreply-spamdigest via sqlalchemy Subject: Re: [sqlalchemy] Test query seems to spuriously give sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'") OK, not really, you want tables in the FROM clause. use either the select_from() or join_from() method to do that: https: //docs. sqlalchemy. org/en/20/tutorial/data_select. html#explicit-from-clauses-and-joins On Mon, Mar 20, 2023, at 5: 16 PM, OK, not really, you want tables in the FROM clause. use either the select_from() or join_from() method to do that: https://docs.sqlalchemy.org/en/20/tutorial/data_select.html#explicit-from-clauses-and-joins<https://urldefense.com/v3/__https:/docs.sqlalchemy.org/en/20/tutorial/data_select.html*explicit-from-clauses-and-joins__;Iw!!Ci6f514n9QsL8ck!gsSZYRslnIShc80D5SJP9hQv7FJkNL5Bzfvc8dkqobmEg8-ctkAcRyR1sZuv3pRL4eCzLvlJC-VDSf5sXXQNtX0d4POMpzTQh3-QUw$> On Mon, Mar 20, 2023, at 5:16 PM, 'Dan Stromberg [External]' via sqlalchemy wrote: I’m getting some pushback internally, from my team lead – he and I both think it’s probably too much detail to share. It’s 43 lines of SQL with multiple subqueries. Would just the simplest parts of the from clause work? From: sqlalchemy@googlegroups.com on behalf of Mike Bayer Date: Monday, March 20, 2023 at 1:11 PM To: noreply-spamdigest via sqlalchemy Subject: Re: [sqlalchemy] Test query seems to spuriously give sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'") what SQL are you going for ? start with that. On Mon, Mar 20, 2023, at 10: 33 AM, 'Dan Stromberg [External]' via sqlalchemy wrote: That makes sense, but…. I’m afraid I don’t know how to add tb_br to the select. I tried: query = ( select(NV. id, what SQL are you going for ? start with that. On Mon, Mar 20, 2023, at 10:33 AM, 'Dan Stromberg [External]' via sqlalchemy wrote: That makes sense, but…. I’m afraid I don’t know how to add tb_br to the select. I tried: query = ( select(NV.id, func.min(bs_3.build_id)) .select_from(bs, Br) .join(v_2, onclause=(bs.version_id == v_2.id)) .join(bs_2, onclause=(Br.id == bs_2.branch_id)) ) …which gave: 1054, "Unknown column 'tb_br.id' in 'on clause'" …and I tried: query = ( select(NV.id, func.min(bs_3.build_id), Br) .select_from(bs) .join(v_2, onclause=(bs.version_id == v_2.id)) .join(bs_2, onclause=(Br.id == bs_2.branch_id)) ) …which also gave: (1054, "Unknown column 'tb_br.id' in 'on clause'") I’m guessing I’m missing something simple, but I have no idea what. Any (further) suggestions? From: sqlalchemy@googlegroups.com on behalf of Mike Bayer Date: Saturday, March 18, 2023 at 8:01 AM To: noreply-spamdigest via sqlalchemy Subject: Re: [sqlalchemy] Test query seems to spuriously give sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'") the query emitted is: SELECT tb_nv. id, min(bs_3. build_id) AS min_1 FROM tb_nv, tb_brst AS bs_3, tb_brst AS bs INNER JOIN tb_vers AS v_2 ON bs. version_id = v_2. id INNER JOIN tb_brst AS bs_2 ON tb_br. id = bs_2. branch_id the error means that your the query emitted is: SELECT tb_nv.id, min(bs_3.build_id) AS
Re: [sqlalchemy] Test query seems to spuriously give sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'")
Alright, using join_from may have led to clearing a hurdle. I’m now using: query = ( select(NV.id, func.min(bs_3.build_id)) .select_from(bs) .join(v_2, onclause=(bs.version_id == v_2.id)) .join_from(bs_2, Br, onclause=(Br.id == bs_2.branch_id)) ) ..and am getting a new error: sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1140, "In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'tact_dev.tb_nv.id'; this is incompatible with sql_mode=only_full_group_by") Do I need to aggregate? Or perhaps change sql_mode? Thanks! From: sqlalchemy@googlegroups.com on behalf of Mike Bayer Date: Monday, March 20, 2023 at 5:33 PM To: noreply-spamdigest via sqlalchemy Subject: Re: [sqlalchemy] Test query seems to spuriously give sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'") OK, not really, you want tables in the FROM clause. use either the select_from() or join_from() method to do that: https: //docs. sqlalchemy. org/en/20/tutorial/data_select. html#explicit-from-clauses-and-joins On Mon, Mar 20, 2023, at 5: 16 PM, OK, not really, you want tables in the FROM clause. use either the select_from() or join_from() method to do that: https://docs.sqlalchemy.org/en/20/tutorial/data_select.html#explicit-from-clauses-and-joins<https://urldefense.com/v3/__https:/docs.sqlalchemy.org/en/20/tutorial/data_select.html*explicit-from-clauses-and-joins__;Iw!!Ci6f514n9QsL8ck!gsSZYRslnIShc80D5SJP9hQv7FJkNL5Bzfvc8dkqobmEg8-ctkAcRyR1sZuv3pRL4eCzLvlJC-VDSf5sXXQNtX0d4POMpzTQh3-QUw$> On Mon, Mar 20, 2023, at 5:16 PM, 'Dan Stromberg [External]' via sqlalchemy wrote: I’m getting some pushback internally, from my team lead – he and I both think it’s probably too much detail to share. It’s 43 lines of SQL with multiple subqueries. Would just the simplest parts of the from clause work? From: sqlalchemy@googlegroups.com on behalf of Mike Bayer Date: Monday, March 20, 2023 at 1:11 PM To: noreply-spamdigest via sqlalchemy Subject: Re: [sqlalchemy] Test query seems to spuriously give sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'") what SQL are you going for ? start with that. On Mon, Mar 20, 2023, at 10: 33 AM, 'Dan Stromberg [External]' via sqlalchemy wrote: That makes sense, but…. I’m afraid I don’t know how to add tb_br to the select. I tried: query = ( select(NV. id, what SQL are you going for ? start with that. On Mon, Mar 20, 2023, at 10:33 AM, 'Dan Stromberg [External]' via sqlalchemy wrote: That makes sense, but…. I’m afraid I don’t know how to add tb_br to the select. I tried: query = ( select(NV.id, func.min(bs_3.build_id)) .select_from(bs, Br) .join(v_2, onclause=(bs.version_id == v_2.id)) .join(bs_2, onclause=(Br.id == bs_2.branch_id)) ) …which gave: 1054, "Unknown column 'tb_br.id' in 'on clause'" …and I tried: query = ( select(NV.id, func.min(bs_3.build_id), Br) .select_from(bs) .join(v_2, onclause=(bs.version_id == v_2.id)) .join(bs_2, onclause=(Br.id == bs_2.branch_id)) ) …which also gave: (1054, "Unknown column 'tb_br.id' in 'on clause'") I’m guessing I’m missing something simple, but I have no idea what. Any (further) suggestions? From: sqlalchemy@googlegroups.com on behalf of Mike Bayer Date: Saturday, March 18, 2023 at 8:01 AM To: noreply-spamdigest via sqlalchemy Subject: Re: [sqlalchemy] Test query seems to spuriously give sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'") the query emitted is: SELECT tb_nv. id, min(bs_3. build_id) AS min_1 FROM tb_nv, tb_brst AS bs_3, tb_brst AS bs INNER JOIN tb_vers AS v_2 ON bs. version_id = v_2. id INNER JOIN tb_brst AS bs_2 ON tb_br. id = bs_2. branch_id the error means that your the query emitted is: SELECT tb_nv.id, min(bs_3.build_id) AS min_1 FROM tb_nv, tb_brst AS bs_3, tb_brst AS bs INNER JOIN tb_vers AS v_2 ON bs.version_id = v_2.id INNER JOIN tb_brst AS bs_2 ON tb_br.id = bs_2.branch_id the error means that your ON clause refers to a table "tb_br" which is not otherwise in the FROM clause: "ON tb_br.id = bs_2.branch_id" the ON clause can only refer to columns from tables that are being SELECTed from, such as: SELECT tb_nv.id, min(bs_3.build_id) AS min_1 FROM tb_nv, tb_brst AS bs_3, tb_brst AS bs INNER JOIN tb_vers AS v_2 ON bs.version_id = v_2.id INNER JOIN tb_br ON tb_br.id = bs_2.branch_id INNER JOIN tb_brst AS bs_2 ON tb_br.id = bs_2.branch_id so you'd need to alter your query to include some indication how tb_br is part of what's being joined. On Fri, Mar 17, 2023, at 7:52 PM, 'Dan Stromberg' via sqlalchemy wro
Re: [sqlalchemy] Test query seems to spuriously give sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'")
Here’s the select, and most of the from clause: select nv.id, min(bs.build_id) as min_build_id from tb_v as v, tb_nv as nv, tb_bs as bs, tb_br as br, From: 'Dan Stromberg [External]' via sqlalchemy Date: Monday, March 20, 2023 at 2:16 PM To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] Test query seems to spuriously give sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'") I’m getting some pushback internally, from my team lead – he and I both think it’s probably too much detail to share. It’s 43 lines of SQL with multiple subqueries. Would just the simplest parts of the from clause work? From: sqlalchemy@ googlegroups. com I’m getting some pushback internally, from my team lead – he and I both think it’s probably too much detail to share. It’s 43 lines of SQL with multiple subqueries. Would just the simplest parts of the from clause work? From: sqlalchemy@googlegroups.com on behalf of Mike Bayer Date: Monday, March 20, 2023 at 1:11 PM To: noreply-spamdigest via sqlalchemy Subject: Re: [sqlalchemy] Test query seems to spuriously give sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'") what SQL are you going for ? start with that. On Mon, Mar 20, 2023, at 10: 33 AM, 'Dan Stromberg [External]' via sqlalchemy wrote: That makes sense, but…. I’m afraid I don’t know how to add tb_br to the select. I tried: query = ( select(NV. id, what SQL are you going for ? start with that. On Mon, Mar 20, 2023, at 10:33 AM, 'Dan Stromberg [External]' via sqlalchemy wrote: That makes sense, but…. I’m afraid I don’t know how to add tb_br to the select. I tried: query = ( select(NV.id, func.min(bs_3.build_id)) .select_from(bs, Br) .join(v_2, onclause=(bs.version_id == v_2.id)) .join(bs_2, onclause=(Br.id == bs_2.branch_id)) ) …which gave: 1054, "Unknown column 'tb_br.id' in 'on clause'" …and I tried: query = ( select(NV.id, func.min(bs_3.build_id), Br) .select_from(bs) .join(v_2, onclause=(bs.version_id == v_2.id)) .join(bs_2, onclause=(Br.id == bs_2.branch_id)) ) …which also gave: (1054, "Unknown column 'tb_br.id' in 'on clause'") I’m guessing I’m missing something simple, but I have no idea what. Any (further) suggestions? From: sqlalchemy@googlegroups.com on behalf of Mike Bayer Date: Saturday, March 18, 2023 at 8:01 AM To: noreply-spamdigest via sqlalchemy Subject: Re: [sqlalchemy] Test query seems to spuriously give sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'") the query emitted is: SELECT tb_nv. id, min(bs_3. build_id) AS min_1 FROM tb_nv, tb_brst AS bs_3, tb_brst AS bs INNER JOIN tb_vers AS v_2 ON bs. version_id = v_2. id INNER JOIN tb_brst AS bs_2 ON tb_br. id = bs_2. branch_id the error means that your the query emitted is: SELECT tb_nv.id, min(bs_3.build_id) AS min_1 FROM tb_nv, tb_brst AS bs_3, tb_brst AS bs INNER JOIN tb_vers AS v_2 ON bs.version_id = v_2.id INNER JOIN tb_brst AS bs_2 ON tb_br.id = bs_2.branch_id the error means that your ON clause refers to a table "tb_br" which is not otherwise in the FROM clause: "ON tb_br.id = bs_2.branch_id" the ON clause can only refer to columns from tables that are being SELECTed from, such as: SELECT tb_nv.id, min(bs_3.build_id) AS min_1 FROM tb_nv, tb_brst AS bs_3, tb_brst AS bs INNER JOIN tb_vers AS v_2 ON bs.version_id = v_2.id INNER JOIN tb_br ON tb_br.id = bs_2.branch_id INNER JOIN tb_brst AS bs_2 ON tb_br.id = bs_2.branch_id so you'd need to alter your query to include some indication how tb_br is part of what's being joined. On Fri, Mar 17, 2023, at 7:52 PM, 'Dan Stromberg' via sqlalchemy wrote: Hi people. I'm having trouble with a test query. As the subject line says, I'm getting: sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'") But it seems like tb_br exists, and has an id column - tb_br being an empty table, but still, existent: mysql> show create table tb_br; +---+-+ | Table | Create Table | +---+-+ | tb_br | CREATE TABLE `tb_br` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name
Re: [sqlalchemy] Test query seems to spuriously give sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'")
I’m getting some pushback internally, from my team lead – he and I both think it’s probably too much detail to share. It’s 43 lines of SQL with multiple subqueries. Would just the simplest parts of the from clause work? From: sqlalchemy@googlegroups.com on behalf of Mike Bayer Date: Monday, March 20, 2023 at 1:11 PM To: noreply-spamdigest via sqlalchemy Subject: Re: [sqlalchemy] Test query seems to spuriously give sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'") what SQL are you going for ? start with that. On Mon, Mar 20, 2023, at 10: 33 AM, 'Dan Stromberg [External]' via sqlalchemy wrote: That makes sense, but…. I’m afraid I don’t know how to add tb_br to the select. I tried: query = ( select(NV. id, what SQL are you going for ? start with that. On Mon, Mar 20, 2023, at 10:33 AM, 'Dan Stromberg [External]' via sqlalchemy wrote: That makes sense, but…. I’m afraid I don’t know how to add tb_br to the select. I tried: query = ( select(NV.id, func.min(bs_3.build_id)) .select_from(bs, Br) .join(v_2, onclause=(bs.version_id == v_2.id)) .join(bs_2, onclause=(Br.id == bs_2.branch_id)) ) …which gave: 1054, "Unknown column 'tb_br.id' in 'on clause'" …and I tried: query = ( select(NV.id, func.min(bs_3.build_id), Br) .select_from(bs) .join(v_2, onclause=(bs.version_id == v_2.id)) .join(bs_2, onclause=(Br.id == bs_2.branch_id)) ) …which also gave: (1054, "Unknown column 'tb_br.id' in 'on clause'") I’m guessing I’m missing something simple, but I have no idea what. Any (further) suggestions? From: sqlalchemy@googlegroups.com on behalf of Mike Bayer Date: Saturday, March 18, 2023 at 8:01 AM To: noreply-spamdigest via sqlalchemy Subject: Re: [sqlalchemy] Test query seems to spuriously give sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'") the query emitted is: SELECT tb_nv. id, min(bs_3. build_id) AS min_1 FROM tb_nv, tb_brst AS bs_3, tb_brst AS bs INNER JOIN tb_vers AS v_2 ON bs. version_id = v_2. id INNER JOIN tb_brst AS bs_2 ON tb_br. id = bs_2. branch_id the error means that your the query emitted is: SELECT tb_nv.id, min(bs_3.build_id) AS min_1 FROM tb_nv, tb_brst AS bs_3, tb_brst AS bs INNER JOIN tb_vers AS v_2 ON bs.version_id = v_2.id INNER JOIN tb_brst AS bs_2 ON tb_br.id = bs_2.branch_id the error means that your ON clause refers to a table "tb_br" which is not otherwise in the FROM clause: "ON tb_br.id = bs_2.branch_id" the ON clause can only refer to columns from tables that are being SELECTed from, such as: SELECT tb_nv.id, min(bs_3.build_id) AS min_1 FROM tb_nv, tb_brst AS bs_3, tb_brst AS bs INNER JOIN tb_vers AS v_2 ON bs.version_id = v_2.id INNER JOIN tb_br ON tb_br.id = bs_2.branch_id INNER JOIN tb_brst AS bs_2 ON tb_br.id = bs_2.branch_id so you'd need to alter your query to include some indication how tb_br is part of what's being joined. On Fri, Mar 17, 2023, at 7:52 PM, 'Dan Stromberg' via sqlalchemy wrote: Hi people. I'm having trouble with a test query. As the subject line says, I'm getting: sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'") But it seems like tb_br exists, and has an id column - tb_br being an empty table, but still, existent: mysql> show create table tb_br; +---+-+ | Table | Create Table | +---+-+ | tb_br | CREATE TABLE `tb_br` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(45) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +---+-+ 1 row in set (0.04 sec) mysql> select * from tb_br; Empty set (0.03 sec) The query, along with sample models, looks like: #!/usr/bin/env python3 """ A little test program. Environment variables: DBU Your database user DBP Your database password DBH Your database host IDB Your initial database """ import os import pprint from sqlalchemy import create_engine, select from sqlalchemy.orm import aliased, sessionmaker, declarative_base from sqlalchemy.sql.expression import func
Re: [sqlalchemy] Test query seems to spuriously give sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'")
That makes sense, but…. I’m afraid I don’t know how to add tb_br to the select.
I tried:
query = (
select(NV.id, func.min(bs_3.build_id))
.select_from(bs, Br)
.join(v_2, onclause=(bs.version_id == v_2.id))
.join(bs_2, onclause=(Br.id == bs_2.branch_id))
)
…which gave:
1054, "Unknown column 'tb_br.id' in 'on clause'"
…and I tried:
query = (
select(NV.id, func.min(bs_3.build_id), Br)
.select_from(bs)
.join(v_2, onclause=(bs.version_id == v_2.id))
.join(bs_2, onclause=(Br.id == bs_2.branch_id))
)
…which also gave:
(1054, "Unknown column 'tb_br.id' in 'on clause'")
I’m guessing I’m missing something simple, but I have no idea what.
Any (further) suggestions?
From: sqlalchemy@googlegroups.com on behalf of
Mike Bayer
Date: Saturday, March 18, 2023 at 8:01 AM
To: noreply-spamdigest via sqlalchemy
Subject: Re: [sqlalchemy] Test query seems to spuriously give
sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown
column 'tb_br.id' in 'on clause'")
the query emitted is: SELECT tb_nv. id, min(bs_3. build_id) AS min_1 FROM
tb_nv, tb_brst AS bs_3, tb_brst AS bs INNER JOIN tb_vers AS v_2 ON bs.
version_id = v_2. id INNER JOIN tb_brst AS bs_2 ON tb_br. id = bs_2. branch_id
the error means that your
the query emitted is:
SELECT tb_nv.id, min(bs_3.build_id) AS min_1
FROM tb_nv, tb_brst AS bs_3, tb_brst AS bs
INNER JOIN tb_vers AS v_2 ON bs.version_id = v_2.id
INNER JOIN tb_brst AS bs_2 ON tb_br.id = bs_2.branch_id
the error means that your ON clause refers to a table "tb_br" which is not
otherwise in the FROM clause: "ON tb_br.id = bs_2.branch_id"
the ON clause can only refer to columns from tables that are being SELECTed
from, such as:
SELECT tb_nv.id, min(bs_3.build_id) AS min_1
FROM tb_nv, tb_brst AS bs_3, tb_brst AS bs
INNER JOIN tb_vers AS v_2 ON bs.version_id = v_2.id
INNER JOIN tb_br ON tb_br.id = bs_2.branch_id
INNER JOIN tb_brst AS bs_2 ON tb_br.id = bs_2.branch_id
so you'd need to alter your query to include some indication how tb_br is part
of what's being joined.
On Fri, Mar 17, 2023, at 7:52 PM, 'Dan Stromberg' via sqlalchemy wrote:
Hi people.
I'm having trouble with a test query.
As the subject line says, I'm getting:
sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown
column 'tb_br.id' in 'on clause'")
But it seems like tb_br exists, and has an id column - tb_br being an empty
table, but still, existent:
mysql> show create table tb_br;
+---+-+
| Table | Create Table
|
+---+-+
| tb_br | CREATE TABLE `tb_br` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---+-+
1 row in set (0.04 sec)
mysql> select * from tb_br;
Empty set (0.03 sec)
The query, along with sample models, looks like:
#!/usr/bin/env python3
"""
A little test program.
Environment variables:
DBU Your database user
DBP Your database password
DBH Your database host
IDB Your initial database
"""
import os
import pprint
from sqlalchemy import create_engine, select
from sqlalchemy.orm import aliased, sessionmaker, declarative_base
from sqlalchemy.sql.expression import func
from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()
Base = declarative_base()
class NV(Base):
__tablename__ = "tb_nv"
__bind_key__ = "testdb"
__table_args__ = (
{
"mysql_engine": "InnoDB",
"mysql_charset": "utf8",
"mysql_collate": "utf8_general_ci",
},
)
id = db.Column("id", db.Integer, primary_key=True, autoincrement=True)
builds = db.relationship("Bld", primaryjoin="(NV.id == Bld.variant_id)")
class Vers(Base):
__tablename__ = "tb_vers"
__bind_key__ = "testdb"
__table_args__ = (
{
"mysql_engine": "InnoDB",
"mysql_charset": "utf8",
"mysql_collate": "utf8_general_ci",
},
)
id = db.Column("id", db.Integer, primary_key=True, autoincrement=True)
class St(Base):
_
Re: [sqlalchemy] Test query seems to spuriously give sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'")
Sorry, I don’t know why Google Groups decided to aggregate a few lines into 2 large lines. Here’s that list of versions again. Hopefully GG will be appeased this time. I'm using: $ python3 -m pip list -v | grep -i sqlalchemy Flask-SQLAlchemy 2.5.1 /data/home/dstromberg/.local/lib/python3.10/site-packages pip SQLAlchemy 1.4.36 /data/home/dstromberg/.local/lib/python3.10/site-packages pip $ python3 -m pip list -v | grep -i mysql mysqlclient2.1.1 /data/home/dstromberg/.local/lib/python3.10/site-packages pip PyMySQL0.8.0 /data/home/dstromberg/.local/lib/python3.10/site-packages pip bash-4.2# mysql --version mysql Ver 14.14 Distrib 5.7.41, for Linux (x86_64) using EditLine wrapper -- 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/DM5PR12MB2503CB97085F7BF2AE76D952C5829%40DM5PR12MB2503.namprd12.prod.outlook.com.
[sqlalchemy] Test query seems to spuriously give sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'")
Hi people.
I'm having trouble with a test query.
As the subject line says, I'm getting:
sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown
column 'tb_br.id' in 'on clause'")
But it seems like tb_br exists, and has an id column - tb_br being an empty
table, but still, existent:
mysql> show create table tb_br;
+---+-+
| Table | Create Table
|
+---+-+
| tb_br | CREATE TABLE `tb_br` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---+-+
1 row in set (0.04 sec)
mysql> select * from tb_br;
Empty set (0.03 sec)
The query, along with sample models, looks like:
#!/usr/bin/env python3
"""
A little test program.
Environment variables:
DBU Your database user
DBP Your database password
DBH Your database host
IDB Your initial database
"""
import os
import pprint
from sqlalchemy import create_engine, select
from sqlalchemy.orm import aliased, sessionmaker, declarative_base
from sqlalchemy.sql.expression import func
from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()
Base = declarative_base()
class NV(Base):
__tablename__ = "tb_nv"
__bind_key__ = "testdb"
__table_args__ = (
{
"mysql_engine": "InnoDB",
"mysql_charset": "utf8",
"mysql_collate": "utf8_general_ci",
},
)
id = db.Column("id", db.Integer, primary_key=True, autoincrement=True)
builds = db.relationship("Bld", primaryjoin="(NV.id == Bld.variant_id)")
class Vers(Base):
__tablename__ = "tb_vers"
__bind_key__ = "testdb"
__table_args__ = (
{
"mysql_engine": "InnoDB",
"mysql_charset": "utf8",
"mysql_collate": "utf8_general_ci",
},
)
id = db.Column("id", db.Integer, primary_key=True, autoincrement=True)
class St(Base):
__tablename__ = "tb_brst"
__bind_key__ = "testdb"
__table_args__ = ({"mysql_engine": "InnoDB", "mysql_charset": "utf8"},)
id = db.Column("id", db.Integer, primary_key=True, autoincrement=True)
version_id = db.Column(
"version_id",
db.Integer,
db.ForeignKey(
"tb_vers.id",
name="fk_tb_brst_version_id",
onupdate="CASCADE",
ondelete="RESTRICT",
),
nullable=False,
)
branch_id = db.Column(
"branch_id",
db.Integer,
db.ForeignKey(
"tb_br.id",
name="fk_tb_brst_branch_id",
onupdate="CASCADE",
ondelete="RESTRICT",
),
nullable=False,
)
build_id = db.Column(
"build_id",
db.Integer,
db.ForeignKey(
"tb_bld.id",
name="fk_tb_brst_build_id",
onupdate="CASCADE",
ondelete="RESTRICT",
),
nullable=False,
)
version = db.relationship(
"Vers", innerjoin=True, primaryjoin="(St.version_id == Vers.id)"
)
branch = db.relationship(
"Br", innerjoin=True, primaryjoin="(St.branch_id == Br.id)"
)
build = db.relationship(
"Bld", innerjoin=True, primaryjoin="(St.build_id == Bld.id)"
)
class Br(Base):
__tablename__ = "tb_br"
__bind_key__ = "testdb"
__table_args__ = (
{
"mysql_engine": "InnoDB",
"mysql_charset": "utf8",
"mysql_collate": "utf8_general_ci",
},
)
id = db.Column("id", db.Integer, primary_key=True, autoincrement=True)
name = db.Column("name", db.String(45), nullable=False)
class Bld(Base):
__tablename__ = "tb_bld"
__bind_key__ = "testdb"
__table_args__ = (
{
"mysql_engine": "InnoDB",
"mysql_charset": "utf8",
"mysql_collate": "utf8_general_ci",
},
)
id = db.Column("id", db.Integer, primary_key=True, autoincrement=True)
name = db.Column("name", db.String(100), nullable=False)
variant_id = db.Column(
"variant_id",
db.Integer,
db.ForeignKey(
"tb_nv.id",
name="fk_tb_bld_variant_id",
onupdate="CASCADE",
ondelete="RESTRICT",
),
nullable=False,
)
variant = db.relationship("NV")
def display(values):
"""Display values in a decent way."""
pprint.pprint(values)
def connect():
