Received: by 10.43.46.137 with SMTP id uo9mr1597092icb.18.1349282459312;
Wed, 03 Oct 2012 09:40:59 -0700 (PDT)
X-BeenThere: sqlalchemy@googlegroups.com
Received: by 10.43.7.131 with SMTP id oo3ls4004586icb.8.gmail; Wed, 03 Oct
2012 09:40:54 -0700 (PDT)
Received: by 10.50.104.135 with SMTP id ge7mr1416542igb.0.1349282453359;
Wed, 03 Oct 2012 09:40:53 -0700 (PDT)
Date: Wed, 3 Oct 2012 09:40:52 -0700 (PDT)
From: Trinath Somanchi <trinath.soman...@gmail.com>
To: sqlalchemy@googlegroups.com
Message-Id: <c257ae57-a39e-4585-97b1-061d8afbaea4@googlegroups.com>
In-Reply-To: <8a34bae5-d43e-49fb-80f3-74e12a892f6e@googlegroups.com>
References: <cd36b796-da27-47cd-8d15-8a9e261633f2@googlegroups.com>
<CAFHwexc7ifSK19BQ0zoXsXWFxJG5wFtuY4_s-7rkR3uBfm_SPg@mail.gmail.com>
<8a34bae5-d43e-49fb-80f3-74e12a892f6e@googlegroups.com>
Subject: Re: [sqlalchemy] SQLALCHEMY query.
MIME-Version: 1.0
Content-Type: multipart/mixed;
boundary="----=_Part_1784_22110876.1349282452518"
------=_Part_1784_22110876.1349282452518
Content-Type: multipart/alternative;
boundary="----=_Part_1785_2174724.1349282452518"
------=_Part_1785_2174724.1349282452518
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
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
>> <trinath....@gmail.com> 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
>>
>
------=_Part_1785_2174724.1349282452518
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Hi-<div><br></div><div>I have solved the issue mysql.</div><div><br></div><=
div>Just converted the LIST to string for key and value..</div><div><br></d=
iv><div>and the query is working fine.</div><div><br></div><div>Thanks a lo=
t guys for the help..</div><div><br></div><div>Thanks a lot Simon...for the=
precious help./</div><div><br></div><div><br></div><div>-</div><div>Trinat=
h<br><br>On Wednesday, 3 October 2012 21:53:05 UTC+5:30, Trinath Somanchi =
wrote:<blockquote class=3D"gmail_quote" style=3D"margin: 0;margin-left: 0.8=
ex;border-left: 1px #ccc solid;padding-left: 1ex;">Hi Simon-<div><br></div>=
<div>I have update my ORM query this way </div><div><br></div><div><di=
v>result =3D session.query(models.Instance.<wbr>hostname.distinct(),models.=
<wbr>FixedIp.address,models.<wbr>VirtualInterface.address).\</div><div>&nbs=
p; join((mod=
els.InstanceMetadata,</div><div> &=
nbsp; models.InstanceMetadata.<wb=
r>instance_id =3D=3D <a href=3D"http://models.Instance.id" target=3D"_blank=
">models.Instance.id</a>)).\</div><div> &=
nbsp; join ((models.FixedIp,</div><div> &=
nbsp; =
models.FixedIp.instance_id =3D=3D models.InstanceMetadata.<wbr>inst=
ance_id)).\</div><div> &nbs=
p; join ((models.VirtualInterface,</div><div> &n=
bsp; =
models.VirtualInterface.<wbr>instance_id =3D=3D models.FixedIp.instan=
ce_id)).\ &n=
bsp;filter(and_(models.Instance.<wbr>project_id =3D=3D search_opts['project=
_id'],</div><div> &n=
bsp; models.InstanceMetada=
ta.key =3D=3D search_opts['key'],</div><div> &nbs=
p; &n=
bsp; models.InstanceMetadata.value =3D=3D search_opts['value'])).\</=
div><div> &nb=
sp; all()</div><div><br></div><div><br></div><div>I have received an Progra=
mming error</div><div><br></div><div>ProgrammingError: (ProgrammingError) (=
1064, 'You have an error in your SQL syntax; check the manual that correspo=
nds to your MySQL server version for the right syntax to use near \') AND i=
nstance_metadata.value =3D ("\'DOM1\'",)\' at line 3') 'SELECT DISTINCT ins=
tances.hostname AS anon_1, fixed_ips.address AS fixed_ips_address, virtual_=
interfaces.address AS virtual_interfaces_address \nFROM instances INNER JOI=
N instance_metadata ON instance_metadata.instance_id =3D <a href=3D"http://=
instances.id" target=3D"_blank">instances.id</a> INNER JOIN fixed_ips ON fi=
xed_ips.instance_id =3D instance_metadata.instance_id INNER JOIN virtual_in=
terfaces ON virtual_interfaces.instance_id =3D fixed_ips.instance_id \nWHER=
E instances.project_id =3D %s AND instance_metadata.`key` =3D %s AND instan=
ce_metadata.value =3D %s' ('<wbr>e216fcb54dc944a8ab16e4e3252996<wbr>43', ['=
Server Group'], ['DOM1'])<br></div><div><br></div><div>Can you help me trou=
bleshoot the issue.</div><div><br></div><div>Thanks a lot for the reply.</d=
iv><div><br></div><div>-</div><div>Trinath</div><br>On Wednesday, 3 October=
2012 21:47:23 UTC+5:30, Simon King wrote:<blockquote class=3D"gmail_quote=
" style=3D"margin:0;margin-left:0.8ex;border-left:1px #ccc solid;padding-le=
ft:1ex">On Wed, Oct 3, 2012 at 5:02 PM, Trinath Somanchi
<br><<a>trinath....@gmail.com</a>> wrote:
<br>> Hi-
<br>>
<br>> I have a sql query which is returning 2 rows. But when is transfor=
med to ORM
<br>> query, its not returning any rows.
<br>>
<br>> My SQL Statement:
<br>>
<br>> select distinct(inst.hostname) as server_name,
<br>> fip.address as fixed_ip_address,
<br>> vif.address as fixed_ip_mac_address
<br>> from instances inst, instance_metadata mtd, virtual_interfaces vif=
,
<br>> fixed_ips fip
<br>> where <a href=3D"http://inst.id" target=3D"_blank">inst.id</=
a> =3D mtd.instance_id and
<br>> mtd.instance_id =3D vif.instance_id &nb=
sp;and
<br>> vif.instance_id =3D fip.instance_id &nb=
sp;and
<br>> inst.project_id =3D '<wbr>e216fcb54dc944a8ab16e4e3252=
996<wbr>43' and
<br>> mtd.key =3D 'Server Group' and
<br>> mtd.value =3D 'DOM1'
<br>> group by mtd.key,mtd.value;
<br>>
<br>> SQL>
<br>> +-------------+---------------<wbr>---+----------------------+
<br>> | server_name | fixed_ip_address | fixed_ip_mac_address |
<br>> +-------------+---------------<wbr>---+----------------------+
<br>> | serverpoc | 172.15.1.2 | fa:16:3e:56=
:47:71 |
<br>> | serverpoc2 | 172.15.1.3 | fa:16:3e:4f=
:3c:9b |
<br>> +-------------+---------------<wbr>---+----------------------+
<br>>
<br>>
<br>>
<br>> I have written the ORM query as
<br>>
<br>> result =3D
<br>> session.query(models.Instance.<wbr>hostname.distinct(),models.<wbr=
>FixedIp.address,models.<wbr>VirtualInterface.address).\
<br>> &nb=
sp; join((models.InstanceMetadata,
<br>> &nb=
sp; models.InstanceMetadata.<wbr>instance_id =3D=3D
<br>> <a href=3D"http://models.Instance.id" target=3D"_blank">models.Ins=
tance.id</a>)).\
<br>> &nb=
sp; join ((models.FixedIp,
<br>> &nb=
sp; models.FixedIp.instance_id =3D=3D
<br>> models.InstanceMetadata.<wbr>instance_id)).\
<br>> &nb=
sp; join ((models.VirtualInterface,
<br>> &nb=
sp; models.VirtualInterface.<wbr>instance_id =3D=
=3D
<br>> models.FixedIp.instance_id)).\
<br>> &nb=
sp; filter(and_(models.Instance.<wbr>project_id =3D=3D
<br>> search_opts['project_id'])).\
<br>> &nb=
sp; filter(and_(models.<wbr>InstanceMetadata.key =3D=3D
<br>> str(search_opts['key']) )).\
<br>> &nb=
sp; filter(and_(models.<wbr>InstanceMetadata.value =3D=3D
<br>> str(search_opts['value']))).\
<br>> &nb=
sp; all()
<br>>
<br>> Can any one help me find the fault in the ORM query.
<br>>
<br>> Thanks in advance.
<br>>
<br>
<br>Have you tried turning on SQL logging (eg. by passing echo=3DTrue to
<br>create_engine), and comparing the query with your original? If the
<br>query looks ok, maybe the parameters you are passing aren't exactly
<br>what you think they should be.
<br>
<br>(Also, I'm not sure if it makes any difference, but those "and_()"
<br>calls inside filter() are unnecessary - filtering a query already
<br>implies that you are AND-ing the condition with all the previous
<br>conditions)
<br>
<br>Simon
<br></blockquote></div></blockquote></div>
------=_Part_1785_2174724.1349282452518--
------=_Part_1784_22110876.1349282452518--
Message from discussion SQLALCHEMY query.
| Create a group - Google Groups - Google Home - Terms of Service - Privacy Policy |
| ©2013 Google |