Re: [sqlalchemy] Test query seems to spuriously give sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'")
MySQL's "only full group by" option causes MySQL to use the SQL standard for GROUP BY, which states that all columns that are not contained within an aggregate function must be in the GROUP BY clause. That is, this SQL is invalid: SELECT table.x, max(table.y) FROM table The reason is that max(table.y) is going to SELECT only one row, the row that has the highest value for "y". Where does that leave ".x" , does it just indicate the value of "x" that just happens to be at the max(y) row? What if there are 100 "y" rows all with that max value?From a SQL standpoint, the function is wrong. Instead, the correct way to write the above SQL is: SELECT table.x, max(table.y) FROM table GROUP BY table.x Above, the max() function is now applied to individual groups of rows, and we get multiple rows back, one row for each value of "x" along with the max(y) for that group. The MySQL error you are getting is basically asking you to add "NV.id" to the GROUP BY clause. Background on this concept is at https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html . it's critical though that you understand the SQL you want to emit and confirm it gets back the result you are looking for. Just adding elements to the query until MySQL accepts it is not necessarily going to get you the results you want. On Tue, Mar 21, 2023, at 7:04 PM, 'Dan Stromberg [External]' via sqlalchemy wrote: > 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-s
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'")
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, '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 wrote: >> >>> >>> >>> Hi people. >>> >>> >>> >>> I'm having trouble with a test query. >>> >>> >>> >>> As the subject line says, I'm getting: >>> >>> sqlalchemy.exc.OperationalError: (MySQLd
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'")
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 >> from flask_sqlalchemy import SQLAlchemy >> >> db = SQLAlchemy() &g
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'")
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):
> __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__ =
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.
