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;
> 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;
> 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)
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 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)
> 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'])
ie. the last 2 elements should be strings, not single-element lists.
If you are getting these from a web form, are they perhaps
multiple-selection fields? In which case you'll probably want to
change the search conditions to be something like:
> 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;
>> > 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)
