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: (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
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` varchar(45) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
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 from flask_sqlalchemy import SQLAlchemy db = SQLAlchemy() Base = declarative_base() class NV(Base):
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()
>> 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__ = (
>> {
>>
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):
__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(
