Re: [sqlalchemy] Test query seems to spuriously give sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'")

2023-03-21 Thread 'Dan Stromberg [External]' via sqlalchemy 
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'")

2023-03-21 Thread 'Dan Stromberg [External]' via sqlalchemy 

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'")

2023-03-21 Thread 'Dan Stromberg [External]' via sqlalchemy 

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'")

2023-03-20 Thread 'Dan Stromberg [External]' via sqlalchemy 
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'")

2023-03-20 Thread 'Dan Stromberg [External]' via sqlalchemy 

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'")

2023-03-20 Thread 'Dan Stromberg [External]' via sqlalchemy 
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):
__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(

Re: [sqlalchemy] Test query seems to spuriously give sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'")

2023-03-17 Thread 'Dan Stromberg [External]' via sqlalchemy 

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.