Hi Simon- I have update my ORM query this way
result = session.query(models.Instance.hostname.distinct(),models.FixedIp.address,models.VirtualInterface.address).\ join((models.InstanceMetadata, models.InstanceMetadata.instance_id == models.Instance.id)).\ join ((models.FixedIp, models.FixedIp.instance_id == models.InstanceMetadata.instance_id)).\ join ((models.VirtualInterface, models.VirtualInterface.instance_id == models.FixedIp.instance_id)).\ filter(and_(models.Instance.project_id == search_opts['project_id'], models.InstanceMetadata.key == search_opts['key'], models.InstanceMetadata.value == search_opts['value'])).\ all() I have received an Programming error ProgrammingError: (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 \') AND instance_metadata.value = ("\'DOM1\'",)\' at line 3') 'SELECT DISTINCT instances.hostname AS anon_1, fixed_ips.address AS fixed_ips_address, virtual_interfaces.address AS virtual_interfaces_address \nFROM instances INNER JOIN instance_metadata ON instance_metadata.instance_id = instances.id INNER JOIN fixed_ips ON fixed_ips.instance_id = instance_metadata.instance_id INNER JOIN virtual_interfaces ON virtual_interfaces.instance_id = fixed_ips.instance_id \nWHERE instances.project_id = %s AND instance_metadata.`key` = %s AND instance_metadata.value = %s' ('e216fcb54dc944a8ab16e4e325299643', ['Server Group'], ['DOM1']) Can you help me troubleshoot the issue. Thanks a lot for the reply. - Trinath On Wednesday, 3 October 2012 21:47:23 UTC+5:30, Simon King wrote: > > On Wed, Oct 3, 2012 at 5:02 PM, Trinath Somanchi > <trinath....@gmail.com <javascript:>> wrote: > > Hi- > > > > I have a sql query which is returning 2 rows. But when is transformed to > ORM > > query, its not returning any rows. > > > > My SQL Statement: > > > > select distinct(inst.hostname) as server_name, > > fip.address as fixed_ip_address, > > vif.address as fixed_ip_mac_address > > from instances inst, instance_metadata mtd, virtual_interfaces vif, > > fixed_ips fip > > where inst.id = mtd.instance_id and > > mtd.instance_id = vif.instance_id and > > vif.instance_id = fip.instance_id and > > inst.project_id = 'e216fcb54dc944a8ab16e4e325299643' and > > mtd.key = 'Server Group' and > > mtd.value = 'DOM1' > > group by mtd.key,mtd.value; > > > > SQL> > > +-------------+------------------+----------------------+ > > | server_name | fixed_ip_address | fixed_ip_mac_address | > > +-------------+------------------+----------------------+ > > | serverpoc | 172.15.1.2 | fa:16:3e:56:47:71 | > > | serverpoc2 | 172.15.1.3 | fa:16:3e:4f:3c:9b | > > +-------------+------------------+----------------------+ > > > > > > > > I have written the ORM query as > > > > result = > > > session.query(models.Instance.hostname.distinct(),models.FixedIp.address,models.VirtualInterface.address).\ > > > > join((models.InstanceMetadata, > > models.InstanceMetadata.instance_id == > > models.Instance.id)).\ > > join ((models.FixedIp, > > models.FixedIp.instance_id == > > models.InstanceMetadata.instance_id)).\ > > join ((models.VirtualInterface, > > models.VirtualInterface.instance_id == > > models.FixedIp.instance_id)).\ > > filter(and_(models.Instance.project_id == > > search_opts['project_id'])).\ > > filter(and_(models.InstanceMetadata.key == > > str(search_opts['key']) )).\ > > filter(and_(models.InstanceMetadata.value == > > str(search_opts['value']))).\ > > all() > > > > Can any one help me find the fault in the ORM query. > > > > Thanks in advance. > > > > Have you tried turning on SQL logging (eg. by passing echo=True to > create_engine), and comparing the query with your original? If the > query looks ok, maybe the parameters you are passing aren't exactly > what you think they should be. > > (Also, I'm not sure if it makes any difference, but those "and_()" > calls inside filter() are unnecessary - filtering a query already > implies that you are AND-ing the condition with all the previous > conditions) > > Simon > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/jGkraGmdWzQJ. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.