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 <sqlalchemy@googlegroups.com> on behalf of
Mike Bayer <mike_not_on_goo...@zzzcomputing.com>
Date: Saturday, March 18, 2023 at 8:01 AM
To: noreply-spamdigest via sqlalchemy <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'")
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(
"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():
"""
Connect to Staging for testing.
This is based on
https://medium.com/analytics-vidhya/translating-sql-queries-to-sqlalchemy-orm-a8603085762b<https://urldefense.com/v3/__https:/medium.com/analytics-vidhya/translating-sql-queries-to-sqlalchemy-orm-a8603085762b__;!!Ci6f514n9QsL8ck!mipJCD36HORYotWmlDnDv1170cjBPHMcqqZMRM98_rQyzBVOXW7Idnb6DeRYSVpe1XEhYulxQ9eExFg78XlivGY89hFAXM-qqlVMnQ$>
...and ./game-publishing/services/api/deploy/celery/config/staging-base.j2
"""
conn_str = "mysql://{}:{}@{}/{}".format(
os.environ["DBU"],
os.environ["DBP"],
os.environ["DBH"],
os.environ["IDB"],
)
engine = create_engine(conn_str)
# Added 2023-03-17
Base.metadata.create_all(engine)
session = sessionmaker(bind=engine)
sess = session()
return (engine, sess)
def main():
"""A minimal query that exhibits the problem."""
(engine, session) = connect()
Base.metadata.create_all(engine)
v_2 = aliased(Vers, name="v_2")
bs = aliased(St, name="bs")
bs_2 = aliased(St, name="bs_2")
bs_3 = aliased(St, name="bs_3")
# sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054,
"Unknown column 'tb_br.id' in 'on clause'")
# [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
# INNER JOIN tb_brst AS bs_2 ON tb_br.id = bs_2.branch_id]
query = (
select(NV.id, func.min(bs_3.build_id))
.select_from(bs)
.join(v_2, onclause=(bs.version_id == v_2.id))
.join(bs_2, onclause=(Br.id == bs_2.branch_id))
)
result = session.execute(query)
display(result.scalar().all())
main()
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 mysqlclient 2.1.1
/data/home/dstromberg/.local/lib/python3.10/site-packages pip PyMySQL 0.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
Any hints?
Thanks!
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/<https://urldefense.com/v3/__http:/www.sqlalchemy.org/__;!!Ci6f514n9QsL8ck!mipJCD36HORYotWmlDnDv1170cjBPHMcqqZMRM98_rQyzBVOXW7Idnb6DeRYSVpe1XEhYulxQ9eExFg78XlivGY89hFAXM_W4SmO7w$>
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See
http://stackoverflow.com/help/mcve<https://urldefense.com/v3/__http:/stackoverflow.com/help/mcve__;!!Ci6f514n9QsL8ck!mipJCD36HORYotWmlDnDv1170cjBPHMcqqZMRM98_rQyzBVOXW7Idnb6DeRYSVpe1XEhYulxQ9eExFg78XlivGY89hFAXM-1bML7Uw$>
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<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/1576c1a1-e108-4bb0-83eb-57ab4125c457n%40googlegroups.com<https://urldefense.com/v3/__https:/groups.google.com/d/msgid/sqlalchemy/1576c1a1-e108-4bb0-83eb-57ab4125c457n*40googlegroups.com?utm_medium=email&utm_source=footer__;JQ!!Ci6f514n9QsL8ck!mipJCD36HORYotWmlDnDv1170cjBPHMcqqZMRM98_rQyzBVOXW7Idnb6DeRYSVpe1XEhYulxQ9eExFg78XlivGY89hFAXM-hAwikVw$>.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/<https://urldefense.com/v3/__http:/www.sqlalchemy.org/__;!!Ci6f514n9QsL8ck!mipJCD36HORYotWmlDnDv1170cjBPHMcqqZMRM98_rQyzBVOXW7Idnb6DeRYSVpe1XEhYulxQ9eExFg78XlivGY89hFAXM_W4SmO7w$>
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See
http://stackoverflow.com/help/mcve<https://urldefense.com/v3/__http:/stackoverflow.com/help/mcve__;!!Ci6f514n9QsL8ck!mipJCD36HORYotWmlDnDv1170cjBPHMcqqZMRM98_rQyzBVOXW7Idnb6DeRYSVpe1XEhYulxQ9eExFg78XlivGY89hFAXM-1bML7Uw$>
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<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/1896c52f-a997-4bbc-b78f-47a13077e797%40app.fastmail.com<https://urldefense.com/v3/__https:/groups.google.com/d/msgid/sqlalchemy/1896c52f-a997-4bbc-b78f-47a13077e797*40app.fastmail.com?utm_medium=email&utm_source=footer__;JQ!!Ci6f514n9QsL8ck!mipJCD36HORYotWmlDnDv1170cjBPHMcqqZMRM98_rQyzBVOXW7Idnb6DeRYSVpe1XEhYulxQ9eExFg78XlivGY89hFAXM8ALetfTw$>.
--
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/DM5PR12MB25034195B351C4A186BD7B99C5809%40DM5PR12MB2503.namprd12.prod.outlook.com.
