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
>> <[email protected]> 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 [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
