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 <sqlalchemy@googlegroups.com> on behalf > of Mike Bayer <mike_not_on_goo...@zzzcomputing.com> > *Date: *Monday, March 20, 2023 at 1:11 PM > *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'") > 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 <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. >>> >>> 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. >> 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/ >> <https://urldefense.com/v3/__http:/www.sqlalchemy.org/__;!!Ci6f514n9QsL8ck!iTWJxGeae8XRrGbXIsCDHkmL9aKlS-H0TO-tI9iKlpv-9zhfDqcctO8QfmbnX2Qz7Yu3bT5Q268iq1KhcgP1aJJeUaWdUlD9sOqvmA$> >> >> 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!iTWJxGeae8XRrGbXIsCDHkmL9aKlS-H0TO-tI9iKlpv-9zhfDqcctO8QfmbnX2Qz7Yu3bT5Q268iq1KhcgP1aJJeUaWdUlAbp5Kb7g$> >> 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 >> >> <https://urldefense.com/v3/__https:/groups.google.com/d/msgid/sqlalchemy/DM5PR12MB25034195B351C4A186BD7B99C5809*40DM5PR12MB2503.namprd12.prod.outlook.com?utm_medium=email&utm_source=footer__;JQ!!Ci6f514n9QsL8ck!iTWJxGeae8XRrGbXIsCDHkmL9aKlS-H0TO-tI9iKlpv-9zhfDqcctO8QfmbnX2Qz7Yu3bT5Q268iq1KhcgP1aJJeUaWdUlD1zzg8TA$>. > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > <https://urldefense.com/v3/__http:/www.sqlalchemy.org/__;!!Ci6f514n9QsL8ck!iTWJxGeae8XRrGbXIsCDHkmL9aKlS-H0TO-tI9iKlpv-9zhfDqcctO8QfmbnX2Qz7Yu3bT5Q268iq1KhcgP1aJJeUaWdUlD9sOqvmA$> > > 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!iTWJxGeae8XRrGbXIsCDHkmL9aKlS-H0TO-tI9iKlpv-9zhfDqcctO8QfmbnX2Qz7Yu3bT5Q268iq1KhcgP1aJJeUaWdUlAbp5Kb7g$> > 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/282a420a-46a6-4fc0-96f2-d4ced697dc13%40app.fastmail.com > > <https://urldefense.com/v3/__https:/groups.google.com/d/msgid/sqlalchemy/282a420a-46a6-4fc0-96f2-d4ced697dc13*40app.fastmail.com?utm_medium=email&utm_source=footer__;JQ!!Ci6f514n9QsL8ck!iTWJxGeae8XRrGbXIsCDHkmL9aKlS-H0TO-tI9iKlpv-9zhfDqcctO8QfmbnX2Qz7Yu3bT5Q268iq1KhcgP1aJJeUaWdUlDuHXt1DA$>. > > > > -- > 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/DM5PR12MB25037CCEB4E02BC36F40D831C5809%40DM5PR12MB2503.namprd12.prod.outlook.com > > <https://groups.google.com/d/msgid/sqlalchemy/DM5PR12MB25037CCEB4E02BC36F40D831C5809%40DM5PR12MB2503.namprd12.prod.outlook.com?utm_medium=email&utm_source=footer>. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/1550511f-e7b1-4160-99ef-8b63191a2ee2%40app.fastmail.com.
