Hi- I have solved the issue mysql.
Just converted the LIST to string for key and value.. and the query is working fine. Thanks a lot guys for the help.. Thanks a lot Simon...for the precious help./ - Trinath On Wednesday, 3 October 2012 21:53:05 UTC+5:30, Trinath Somanchi wrote: > > 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> 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/-/VkMEQpPl5xsJ. 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.