Обсуждение: Error in a simple query
Hi, I'm using psycopg2 extensively in different scripts to process spatial data with postgresql 9.1 and postgis. However, in one script I have the following error: psycopg2.internalerror: current transaction is aborted, commands ignored until end of transaction block The command that gave the error is: a = """select idbalizas from nuevo.balizas where %s %s %s %s""" % (emp1, dnid1, mem1, mmsi1) print cur.execute(a) One example of 'a' is: select idbalizas from nuevo.balizas where emp='005' and dnid='08466' and mem='055' and mmsi='701000535' I can run this same query in psql and other postgresql clients and it returns the correct answer. Can you help me debugging this? Many thanks. -- Gustavo Martínez Skype: gmpuljak Linkedin: http://ar.linkedin.com/in/gustavomartínezpuljak “Que la tierra se vaya haciendo camino ante tus pasos, que el viento sople siempre a tus espaldas, que el sol brille cálido sobre tu cara, que la lluvia caiga suavemente sobre tus campos y, hasta tanto volvamos a encontrarnos, que Dios te lleve en la palma de su mano.”
On 06/24/2013 03:55 PM, Gustavo wrote: > Hi, > > I'm using psycopg2 extensively in different scripts to process spatial data > with postgresql 9.1 and postgis. However, in one script I have the following > error: > > psycopg2.internalerror: current transaction is aborted, commands ignored until > end of transaction block > > The command that gave the error is: > > a = """select idbalizas from nuevo.balizas where %s %s %s %s""" % (emp1, > dnid1, mem1, mmsi1) > print > cur.execute(a) > > One example of 'a' is: > > select idbalizas from nuevo.balizas where emp='005' and dnid='08466' and > mem='055' and mmsi='701000535' > > I can run this same query in psql and other postgresql clients and it returns > the correct answer. > > Can you help me debugging this? What shows up in the Postgres log? > Many thanks. > > > -- Adrian Klaver adrian.klaver@gmail.com
the error might be in a line above.. try increasing the log verbosity and look again.. (I also use psycopg2 for PostGIS, not an issue in itself) -- Brian M Hamlin OSGeo California Chapter On Mon, 24 Jun 2013 19:55:41 -0300, Gustavo <gmpuljak@yahoo.com.ar> wrote: Hi, > > I'm using psycopg2 extensively in different scripts to process > spatial data with postgresql 9.1 and postgis. However, in one script > I have the following error: > > psycopg2.internalerror: current transaction is aborted, commands > ignored until end of transaction block > > The command that gave the error is: > > a = """select idbalizas from nuevo.balizas where %s %s %s %s""" % > (emp1, dnid1, mem1, mmsi1) > print cur.execute(a) > > One example of 'a' is: > > select idbalizas from nuevo.balizas where emp='005' and > dnid='08466' and mem='055' and mmsi='701000535' > > I can run this same query in psql and other postgresql clients and it > returns the correct answer. > > Can you help me debugging this? > Many thanks. > > > > -- Gustavo Martínez > Skype: gmpuljak > Linkedin: http://ar.linkedin.com/in/gustavomartínezpuljak > > “Que la tierra se vaya haciendo camino ante tus pasos, > que el viento sople siempre a tus espaldas, > que el sol brille cálido sobre tu cara, > que la lluvia caiga suavemente sobre tus campos y, > hasta tanto volvamos a encontrarnos, > que Dios te lleve en la palma de su mano.” > > > -- Sent via psycopg mailing list (psycopg@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/psycopg > >
Adrian, thanks for the hint. The script was processing data from a txt file with several thousand lines and the problem was in a malformed insert from the bottom of the previous iteration. Thanks. On Lun 24 Jun 2013 20:04:46 Adrian Klaver escribió: > On 06/24/2013 03:55 PM, Gustavo wrote: > > Hi, > > > > I'm using psycopg2 extensively in different scripts to process spatial > > data with postgresql 9.1 and postgis. However, in one script I have the > > following error: > > > > psycopg2.internalerror: current transaction is aborted, commands ignored > > until end of transaction block > > > > The command that gave the error is: > > > > a = """select idbalizas from nuevo.balizas where %s %s %s %s""" % (emp1, > > dnid1, mem1, mmsi1) > > print > > cur.execute(a) > > > > One example of 'a' is: > > > > select idbalizas from nuevo.balizas where emp='005' and dnid='08466' > > and mem='055' and mmsi='701000535' > > > > I can run this same query in psql and other postgresql clients and it > > returns the correct answer. > > > > Can you help me debugging this? > > What shows up in the Postgres log? > > > Many thanks. -- Gustavo Martínez Skype: gmpuljak Linkedin: http://ar.linkedin.com/in/gustavomartínezpuljak “Que la tierra se vaya haciendo camino ante tus pasos, que el viento sople siempre a tus espaldas, que el sol brille cálido sobre tu cara, que la lluvia caiga suavemente sobre tus campos y, hasta tanto volvamos a encontrarnos, que Dios te lleve en la palma de su mano.”
On Tue, Jun 25, 2013 at 12:18 AM, Gustavo <gmpuljak@yahoo.com.ar> wrote: > Adrian, > > thanks for the hint. The script was processing data from a txt file with > several thousand lines and the problem was in a malformed insert from the > bottom of the previous iteration. No: the error is that you are composing the query manually instead of letting psycopg joining the arguments to the query, as cleanly documented at the very beginning of the manual, from the very first example. http://initd.org/psycopg/docs/usage.html The page explains in details why running >> > a = """select idbalizas from nuevo.balizas where %s %s %s %s""" % (emp1, >> > dnid1, mem1, mmsi1) >> > print >> > cur.execute(a) is an amazingly bad idea. -- Daniele