from:"Trinath Somanchi"

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&#x

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'

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

Re: [sqlalchemy] SQLALCHEMY query.

[sqlalchemy] SQLALCHEMY query.

3 matches

Site Navigation

Mail list logo

Footer information