SQLALCHEMY query.

[Trinath Somanchi]

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.

-

Trinath

[Simon King]

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

[Trinath Somanchi]

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

[Simon King]

The parameters you are passing to the query are:

('e216fcb54dc944a8ab16e4e325299643', ['Server Group'], ['DOM1'])

but I suspect they should be

('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:

models.InstanceMetadata.key.in_(search_opts['key'])

However, you should only add that condition if search_opts['key'] is
not empty. (I *think* SA warns you if you pass an empty list to "in_")

Simon

[Trinath Somanchi]

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