New join 'on' context

[Chris Matta]

I'm trying to join two tables that don't share a ForeignKey using the new join on context:

>>> hosts = InvManage.select(InvHost.name, InvIp.fqdn).join(InvHost).join(InvIp, on=(InvIp.device == InvHost.id)).group_by(InvHost.name).switch(InvManage).join(InvApplication).where(InvApplication.name == 'Solution Enabler')

But I'm getting this error:

>>> [h.name for h in hosts]                                                                                                                       Traceback (most recent call last):

  File "<stdin>", line 1, in <module>

  File ".../.pythonbrew/pythons/Python-2.7.2/lib/python2.7/site-packages/peewee-2.0.7-py2.7.egg/peewee.py", line 1018, in next

    instance = self.iterate()

  File ".../.pythonbrew/pythons/Python-2.7.2/lib/python2.7/site-packages/peewee-2.0.7-py2.7.egg/peewee.py", line 1006, in iterate

    return self.construct_instance(row)

  File ".../.pythonbrew/pythons/Python-2.7.2/lib/python2.7/site-packages/peewee-2.0.7-py2.7.egg/peewee.py", line 965, in construct_instance

    return self.follow_joins(self.join_meta, collected_models, self.model)

  File ".../.pythonbrew/pythons/Python-2.7.2/lib/python2.7/site-packages/peewee-2.0.7-py2.7.egg/peewee.py", line 968, in follow_joins

    inst = collected_models[current]

KeyError: <class 'ssp.db.InvManage'>

It's odd because the SQL produced from hosts.sql works fine.

Any help would be appreciated.

[Charles Leifer]

Ah, I think I know what is causing this.  The join meta returned to the QueryResultWrapper is not complete since there are no foreign keys.  I will look into a fix.

--
You received this message because you are subscribed to the Google Groups "peewee-orm" group.
To unsubscribe from this group and stop receiving emails from it, send an email to peewee-orm+...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Charles Leifer]

OK actually, the issue is due to the fact that you are selecting InvManage but not grabbing any columns off of it.  When the query result wrapper goes to construct the instance graph after reading off the cursor, it has no 'starting point' because nothing was selected from InvManage.

To fix, try doing:

InvManage.select(InvManage, InvHost.name, InvIp.fqdn).join(InvHost)....etc

                  ^^^^^^^

I hope this helps!

On Mon, Feb 25, 2013 at 10:57 AM, Chris Matta <cma...@gmail.com> wrote:

[Chris Matta]

Ok, that got rid of the first error, but raised another!

Here's the new expression and error:

se_hosts = InvManage.select(InvManage, InvHost.name, InvIp.fqdn).join(InvHost).join(InvIp, on=(InvIp.device == InvHost.id)).group_by(InvHost.name).switch(InvManage).join(InvApplication).where(InvApplication.name == 'Solution Enabler')

>>> se_hosts.count()

1L

>>> [h.name for h in se_hosts]

Traceback (most recent call last):

  File "<stdin>", line 1, in <module>

AttributeError: 'InvManage' object has no attribute 'name'

>>>

Which I tried to correct like so:

>>> se_hosts = InvManage.select(InvManage, InvHost.name.alias('hostname'), InvIp.fqdn).join(InvHost).join(InvIp, on=(InvIp.device == InvHost.id)).group_by(InvHost.name).switch(InvManage).join(InvApplication).where(InvApplication.name == 'Solution Enabler')

>>> [h.name for h in se_hosts]

Traceback (most recent call last):

  File "<stdin>", line 1, in <module>

  File ".../.pythonbrew/pythons/Python-2.7.2/lib/python2.7/site-packages/peewee-2.0.7-py2.7.egg/peewee.py", line 1387, in __iter__

    return iter(self.execute())

  File ".../.pythonbrew/pythons/Python-2.7.2/lib/python2.7/site-packages/peewee-2.0.7-py2.7.egg/peewee.py", line 1380, in execute

    self._qr = QueryResultWrapper(self.model_class, self._execute(), query_meta)

  File ".../.pythonbrew/pythons/Python-2.7.2/lib/python2.7/site-packages/peewee-2.0.7-py2.7.egg/peewee.py", line 1177, in _execute

    return self.database.execute_sql(sql, params, self.require_commit)

  File ".../.pythonbrew/pythons/Python-2.7.2/lib/python2.7/site-packages/peewee-2.0.7-py2.7.egg/peewee.py", line 1545, in execute_sql

    res = cursor.execute(sql, params or ())

  File ".../.pythonbrew/pythons/Python-2.7.2/lib/python2.7/site-packages/MySQLdb/cursors.py", line 201, in execute

    self.errorhandler(self, exc, value)

  File ".../.pythonbrew/pythons/Python-2.7.2/lib/python2.7/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler

    raise errorclass, errorvalue

_mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS hostname' at line 1")

It seems like setting an alias in the select also sets it in the group_by statement:

>>> se_hosts.sql

<bound method SelectQuery.sql of <class 'ssp.db.InvManage'> SELECT t1.`id`, t1.`app_status`, t1.`application_id`, t1.`comment`, t1.`device_class`, t1.`device_id`, t1.`host_id`, t1.`preference`, t2.`name` AS hostname, t3.`fqdn` FROM `inv_manage` AS t1 INNER JOIN `inv_host` AS t2 ON (t1.`host_id` = t2.`id`) INNER JOIN `inv_application` AS t4 ON (t1.`application_id` = t4.`id`) INNER JOIN `inv_ip` AS t3 ON (t2.`id` = t3.`device_id`) WHERE (t4.`name` = %s) GROUP BY t2.`name` AS hostname [u'Solution Enabler']>

[Chris Matta]

So is what I'm seeing a bug or something on my part?

[Charles]

In your list comprehension try [h.host.name for h in se_hosts] and let me know.

[Chris Matta]

Charles,

Yes it gives the same error. The error in the SQL is here:

>>> se_hosts.sql

<bound method SelectQuery.sql of <class 'ssp.db.InvManage'> SELECT t1.`id`, t1.`app_status`, t1.`application_id`, t1.`comment`, t1.`device_class`, t1.`device_id`, t1.`host_id`, t1.`preference`, t2.`name` AS hostname, t3.`fqdn` FROM `inv_manage` AS t1 INNER JOIN `inv_host` AS t2 ON (t1.`host_id` = t2.`id`) INNER JOIN `inv_application` AS t4 ON (t1.`application_id` = t4.`id`) INNER JOIN `inv_ip` AS t3 ON (t2.`id` = t3.`device_id`) WHERE (t4.`name` = %s) GROUP BY t2.`name` AS hostname [u'Solution Enabler']>

GROUP BY xxx AS yyyy is not legal SQL on MySQL as far as I know.

[Chris Matta]

Sorry, I just upgraded to 2.0.8 and it's working.

Thanks.