sqlalchemy joined alias doesn't have columns from both tables

All I want is the count from TableA grouped by a column from TableB, but of course I need the item from TableB each count is associated with. Better explained with code:

TableA and B are Model objects.

I'm trying to follow this syntax as best I can.

Trying to run this query:

sq = session.query(TableA).join(TableB).\ group_by(TableB.attrB).subquery() countA = func.count(sq.c.attrA) groupB = func.first(sq.c.attrB) print session.query(countA, groupB).all()

But it gives me an AttributeError (sq does not have attrB)

I'm new to SA and I find it difficult to learn. (links to recommended educational resources welcome!)

--------------Solutions-------------

When you make a subquery out of a select statement, the columns that can be accessed from it must be in the columns clause. Take for example a statement like:

select x, y from mytable where z=5

If we wanted to make a subquery, then GROUP BY 'z', this would not be legal SQL:

select * from (select x, y from mytable where z=5) as mysubquery group by mysubquery.z

Because 'z' is not in the columns clause of "mysubquery" (it's also illegal since 'x' and 'y' should be in the GROUP BY as well, but that's a different issue).

SQLAlchemy works the same exact way. When you say query(..).subquery(), or use the alias() function on a core selectable construct, it means you're wrapping your SELECT statement in parenthesis, giving it a (usually generated) name, and giving it a new .c. collection that has only those columns that are in the "columns" clause, just like real SQL.

So here you'd need to ensure that TableB, at least the column you're dealing with externally, is available. You can also limit the columns clause to just those columns you need:

sq = session.query(TableA.attrA, TableB.attrB).join(TableB).\
group_by(TableB.attrB).subquery()

countA = func.count(sq.c.attrA)
groupB = func.first(sq.c.attrB)

print session.query(countA, groupB).all()

Note that the above query probably only works on MySQL, as in general SQL it's illegal to reference any columns that aren't part of an aggregate function, or part of the GROUP BY, when grouping is used. MySQL has a more relaxed (and sloppy) system in this regard.

edit: if you want the results without the zeros:

import collections

letter_count = collections.defaultdict(int)
for count, letter in session.query(func.count(MyClass.id), MyClass.attr).group_by(MyClass.attr):
letter_count[letter] = count

for letter in ["A", "B", "C", "D", "E", ...]:
print "Letter %s has %d elements" % letter_count[letter]

note letter_count[someletter] defaults to zero if otherwise not populated.

Category:python Time:2011-09-29 Views:2

Related post

  • MySQL complex join - Can't select multiple columns in second table 2011-05-20

    I can't figure out why I can't select multiple columns in the second table. Here are my tables: Computers: ---------- id, cond, type Images: ------- id, foreignid, name, width, height, def Here is my select statement: SELECT id, cond, type, (SELECT n

  • JPQL / QueryDSL: join subquery and get aliased column 2011-08-04

    I'm trying to get an average for a count on a groupBy by joining with a subquery. Don't know if that the right way to go at all but I couldn't anything about subqueries other than the mysema doc. Scenario: How many orders per product did a customer d

  • Reference column in a table on another server in an UPDATE/JOIN (SQL Server) 2012-03-13

    I'm familiar with 4-part naming, but I get an error every time I try to reference a column. For example: UPDATE my_table SET my_table.column1 = otherserver.otherdatabase.dbo.othertable.column1 FROM my_table INNER JOIN otherserver.otherdatabase.dbo.ot

  • Select only some columns from a table on a LEFT JOIN 2009-08-25

    Is it possible to select only some columns from a table on a LEFT JOIN? --------------Solutions------------- Of course. Just list the columns you want to select as you would in any query: SELECT table1.column1, table1.column2, table2.column3 FROM tab

  • Nhibernate: join tables and get single column from other table 2009-12-13

    I have the following tables: create table Users( Id uniqueidentifier primary key, InfoId uniqueidentifier not null unique, Password nvarchar(255) not null ) Create table UserInfo( Id uniqueidentifier primary key, Company nvarchar(255) not null, Conta

  • Update with inner join, update 2 columns from both tables 2010-01-27

    This is my query in sql server 2008 - UPDATE a SET a.col2 = 'new', a.col3 = 'www.google.com', b.col1 = '10' FROM table a INNER JOIN table b ON a.col1 = b.col1 WHERE a.col1 = 7 It crashes stating "Invalid column name b.col1." How do I make this work?

  • Fluent NHibernate join single column from another table 2010-02-10

    I'm using Fluent NHibernate and have two tables; Customer [ID, Name, LanguageID] Languages [ID, Description] I have a Customer entity with the following properties; ID, Name, LanguageID, Language What I would like to do is to join to the Languages ta

  • Is there a way to rename a similarly named column from two tables when performing a join? 2010-03-10

    I have two tables that I am joining with the following query... select * from Partners p inner join OrganizationMembers om on p.ParID = om.OrganizationId where om.EmailAddress = '[email protected]' and om.deleted = 0 Which works great but some of

  • MySQL join multiple columns from same table 2010-09-17

    I am trying to write a query that returns a users profile information, along with a count of occurrences of the user's ID in 2 columns from another table. An example below: TableA userID userName 1 UserA 2 UserB TableB LinkID leadID followID 1 1 2 2

  • How do I join on two non-unique columns in django? 2010-11-19

    I would like to be able to join two models by a column which is not unique in either one. class Ctystate(models.Model): ctystate_id = models.IntegerField(primary_key=True) zip5 = models.IntegerField() [...] class Addr2zip(models.Model): addr2zip_id =

  • LINQ to Entities, join two tables, then group and take sums of columns from both tables 2011-02-22

    As the title says, my goal is to JOIN two tables (target and transaction) on several columns, then group the result of that join and sum the values of columns from BOTH tables. The following query only allows access to columns from the FIRST table in

  • joining 2 columns in 1 table 2011-04-16

    Im not sure of the right way to do this so help needed if possible please :) I have a table of football fixtures with the following fields: id, hteam, ateam, hscore, ascore, date, time I want to return all the fixtures for one team so where hteam OR

  • Pythons SQLAlchemy does not recognize Primary key column and complains, why? 2011-08-20

    I have this MySQL Table: CREATE TABLE `affiliations` ( `affiliation_id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(333) COLLATE utf8_bin NOT NULL, PRIMARY KEY (`affiliation_id`), UNIQUE KEY `name_UNIQUE` (`name`) ) ENGINE=MyISAM DEFAULT CHARSET=

  • SQL inner join query returns two identical columns 2011-08-23

    Let's say I have the following SQL query: SELECT * FROM employee INNER JOIN department ON employee.EmpID = department.EmpID I wanted to ask, why I am getting two EmpID columns, and how can I get only one of those, preferably the first. I'm using SQL

  • Oracle ANSI left outer join with more than 1050 columns in 11gR2 2011-11-23

    I have an issue on Oracle (11.2.0.1.0) where too many left outer joins (or rather too many columns in the joined tables) results in the following error: ORA-03113: end-of-file on communication channel The alert.log file shows the underlying error as:

  • Join multiple columns from one table to single column from another table 2012-02-02

    I'm trying to learn how to join multiple columns from one table to a single column from another table. This is my table structure in its simplest form: teams id | team_name | 1 | teamA | 2 | teamB | 3 | teamC | 4 | teamD | trades id | team_1 (FK to t

  • Getting Sum of multiple columns of multiple tables through group by and joins 2012-03-09

    I am using following query to get sum of multiple columns from two tables using left join. But mysql shows an error of "Error Code: 1111. Invalid use of group function" Please point out where I am on mistake? SELECT url as Domain, A.advertiserid as a

  • In sqlite3 reference(foreign key) a column of a table by join of two columns in other two tables 2012-04-07

    I am using sqlite3 in python. I have two tables with id column in each. These id columns in the tables can have different values. I want to create a new table with column id which can contain only the values by combining the values from the id column

  • Sum one table where columns match another table and join the results 2012-04-28

    I have been trying to figure this out for a while now and decided I'm not going to figure it out any time soon! Thanks for the help in advance. Love this place. I have 2 tables date: date_id, user_id, cost, title, description date_rating: date_id, us

Copyright (C) pcaskme.com, All Rights Reserved.

processed in 0.641 (s). 13 q(s)