subject:"\[sqlalchemy\] SQLALCHEMY query."

Re: [sqlalchemy] SQLAlchemy query filter by field value in related table

2018-09-28 Thread Алексанр Платонов

Thank you very much for such a detailed answer.
I am happy that everything works as it should.

пятница, 21 сентября 2018 г., 19:02:43 UTC+3 пользователь Mike Bayer 
написал:
>
> On Fri, Sep 21, 2018 at 10:52 AM Алексанр Платонов 
> > wrote: 
> > 
> > Hi, with all due respect, I copy here the link to the question so as not 
> to duplicate information. 
> > 
> > 
> https://stackoverflow.com/questions/52414329/sqlalchemy-query-filter-by-field-value-in-related-table
>  
>
> the query I see: 
>
> result = Device.query.filter(DeviceGroup.type != 
> ModelType.TYPE_BLADE_SERVER).all() 
>
> does not generate any LEFT OUTER JOIN that you can filter on,  it 
> looks like you have eager loading in there.You can't filter on 
> those: 
>
>
> http://docs.sqlalchemy.org/en/latest/orm/loading_relationships.html?highlight=zen#the-zen-of-joined-eager-loading
>  
>
> you would need to say: 
>
> session.query(Device).outerjoin(Device.model).outerjoin(DeviceModel.group).filter(DeviceGroup.type
>  
>
> != ModelType.TYPE_BLADE_SERVER) 
>
> However, you still have LEFT OUTER JOIN for your eager loads in there 
> so this will create a huge query, unless you add a lazyload('*') 
> option to it, or allow them to be included with contains_eager(): 
>
> session.query(Device).options(contains_eager("model").contains_eager("group")).outerjoin(Device.model).outerjoin(DeviceModel.group).filter(DeviceGroup.type
>  
>
> != ModelType.TYPE_BLADE_SERVER).all() 
>
> The LEFT OUTER JOINS are still inefficient though.  When you want to 
> look for rows where related rows *dont* exist, use EXISTS: 
> session.query(Device).filter(~Device.model.has(DeviceModel.group.has(DeviceGroup.type
>  
>
> == ModelType.TYPE_BLADE_SERVER))).all() 
>
> this gives you, with your eager loads still turned on: 
>
> SELECT device.id AS device_id, device.hostname AS device_hostname, 
> device.model_id AS device_model_id, device_model_1.id AS 
> device_model_1_id, device_model_1.group_id AS device_model_1_group_id, 
> device_group_1.id AS device_group_1_id, device_group_1.name AS 
> device_group_1_name, device_group_1.height AS device_group_1_height, 
> device_group_1.type AS device_group_1_type 
> FROM device LEFT OUTER JOIN device_model AS device_model_1 ON 
> device_model_1.id = device.model_id LEFT OUTER JOIN device_group AS 
> device_group_1 ON device_group_1.id = device_model_1.group_id 
> WHERE NOT (EXISTS (SELECT 1 
> FROM device_model 
> WHERE device_model.id = device.model_id AND (EXISTS (SELECT 1 
> FROM device_group 
> WHERE device_group.id = device_model.group_id AND device_group.type = 
> ? 
>
> you can turn off the eager loads with lazyload('*'): 
>
> session.query(Device).options(lazyload('*')).filter(~Device.model.has(DeviceModel.group.has(DeviceGroup.type
>  
>
> == ModelType.TYPE_BLADE_SERVER))).all() 
>
> SQL: 
>
> SELECT device.id AS device_id, device.hostname AS device_hostname, 
> device.model_id AS device_model_id 
> FROM device 
> WHERE NOT (EXISTS (SELECT 1 
> FROM device_model 
> WHERE device_model.id = device.model_id AND (EXISTS (SELECT 1 
> FROM device_group 
> WHERE device_group.id = device_model.group_id AND device_group.type = 
> ? 
>
>
>
>
>
>
> > 
> > Thank you for your participation and response. 
> > 
> > -- 
> > SQLAlchemy - 
> > The Python SQL Toolkit and Object Relational Mapper 
> > 
> > http://www.sqlalchemy.org/ 
> > 
> > To post example code, please provide an MCVE: Minimal, Complete, and 
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> description. 
> > --- 
> > You received this message because you are subscribed to the Google 
> Groups "sqlalchemy" group. 
> > To unsubscribe from this group and stop receiving emails from it, send 
> an email to sqlalchemy+...@googlegroups.com . 
> > To post to this group, send email to sqlal...@googlegroups.com 
> . 
> > Visit this group at https://groups.google.com/group/sqlalchemy. 
> > For more options, visit https://groups.google.com/d/optout. 
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] SQLAlchemy query filter by field value in related table

2018-09-21 Thread Mike Bayer

On Fri, Sep 21, 2018 at 10:52 AM Алексанр Платонов
 wrote:
>
> Hi, with all due respect, I copy here the link to the question so as not to 
> duplicate information.
>
> https://stackoverflow.com/questions/52414329/sqlalchemy-query-filter-by-field-value-in-related-table

the query I see:

result = Device.query.filter(DeviceGroup.type !=
ModelType.TYPE_BLADE_SERVER).all()

does not generate any LEFT OUTER JOIN that you can filter on,  it
looks like you have eager loading in there.You can't filter on
those:

http://docs.sqlalchemy.org/en/latest/orm/loading_relationships.html?highlight=zen#the-zen-of-joined-eager-loading

you would need to say:

session.query(Device).outerjoin(Device.model).outerjoin(DeviceModel.group).filter(DeviceGroup.type
!= ModelType.TYPE_BLADE_SERVER)

However, you still have LEFT OUTER JOIN for your eager loads in there
so this will create a huge query, unless you add a lazyload('*')
option to it, or allow them to be included with contains_eager():

session.query(Device).options(contains_eager("model").contains_eager("group")).outerjoin(Device.model).outerjoin(DeviceModel.group).filter(DeviceGroup.type
!= ModelType.TYPE_BLADE_SERVER).all()

The LEFT OUTER JOINS are still inefficient though.  When you want to
look for rows where related rows *dont* exist, use EXISTS:
session.query(Device).filter(~Device.model.has(DeviceModel.group.has(DeviceGroup.type
== ModelType.TYPE_BLADE_SERVER))).all()

this gives you, with your eager loads still turned on:

SELECT device.id AS device_id, device.hostname AS device_hostname,
device.model_id AS device_model_id, device_model_1.id AS
device_model_1_id, device_model_1.group_id AS device_model_1_group_id,
device_group_1.id AS device_group_1_id, device_group_1.name AS
device_group_1_name, device_group_1.height AS device_group_1_height,
device_group_1.type AS device_group_1_type
FROM device LEFT OUTER JOIN device_model AS device_model_1 ON
device_model_1.id = device.model_id LEFT OUTER JOIN device_group AS
device_group_1 ON device_group_1.id = device_model_1.group_id
WHERE NOT (EXISTS (SELECT 1
FROM device_model
WHERE device_model.id = device.model_id AND (EXISTS (SELECT 1
FROM device_group
WHERE device_group.id = device_model.group_id AND device_group.type = ?

you can turn off the eager loads with lazyload('*'):

session.query(Device).options(lazyload('*')).filter(~Device.model.has(DeviceModel.group.has(DeviceGroup.type
== ModelType.TYPE_BLADE_SERVER))).all()

SQL:

SELECT device.id AS device_id, device.hostname AS device_hostname,
device.model_id AS device_model_id
FROM device
WHERE NOT (EXISTS (SELECT 1
FROM device_model
WHERE device_model.id = device.model_id AND (EXISTS (SELECT 1
FROM device_group
WHERE device_group.id = device_model.group_id AND device_group.type = ?

>
> Thank you for your participation and response.
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and 
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> description.
> ---
> You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an 
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] SQLAlchemy query filter by field value in related table

2018-09-21 Thread Алексанр Платонов

Hi, with all due respect, I copy here the link to the question so as not to 
duplicate information.

https://stackoverflow.com/questions/52414329/sqlalchemy-query-filter-by-field-value-in-related-table

Thank you for your participation and response.

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] SQLALCHEMY query.

2012-10-03 Thread 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

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 
>>  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 alr

Re: [sqlalchemy] SQLALCHEMY query.

2012-10-03 Thread Simon King

On Wed, Oct 3, 2012 at 5:23 PM, 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.
>

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

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
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.

Re: [sqlalchemy] SQLALCHEMY query.

2012-10-03 Thread 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

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 
> > 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 sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@goo

Re: [sqlalchemy] SQLALCHEMY query.

2012-10-03 Thread Simon King

On Wed, Oct 3, 2012 at 5:02 PM, Trinath Somanchi
 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 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.

[sqlalchemy] SQLALCHEMY query.

2012-10-03 Thread 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

-- 
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/-/a-nPCWf-HTEJ.
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.

Re: [sqlalchemy] SQLAlchemy query filter by field value in related table

Re: [sqlalchemy] SQLAlchemy query filter by field value in related table

[sqlalchemy] SQLAlchemy query filter by field value in related table

Re: [sqlalchemy] SQLALCHEMY query.

Re: [sqlalchemy] SQLALCHEMY query.

Re: [sqlalchemy] SQLALCHEMY query.

Re: [sqlalchemy] SQLALCHEMY query.

[sqlalchemy] SQLALCHEMY query.

8 matches

Site Navigation

Mail list logo

Footer information