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] <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 [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.
