python psycopg2 internal error on string formatter

Question

Hi I'm trying the following script

import psycopg2 as pq
import os

# Create the database
os.system('dropdb ptest')
os.system('createdb ptest')
# connect to the database
cn = pq.connect('dbname=ptest user=myname')
cr = cn.cursor()

# the wierd tuple at the EOL below is to preserve the list 
# lookup in case that is where the error is.
cr.execute('CREATE TABLE id1 (%s varchar, %s int PRIMARY KEY, %s int, %s int, %s varchar)' % tuple(['fund', 'sleeve_id', 'sub_id', 'level_id', 'Entity_id'])
cr.execute('INSERT INTO id1 (%s,%s,%s,%s,%s) VALUES ("%s","%s","%s","%s","%s");' % tuple(['fund', 'sleeve_id', 'sub_id', 'level_id', 'Entity_id']+['RHUBARB RHUBARB - RHUBARB RHUBARB', '2', '1', '1', 'CRUMB1']))

and I get the error

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "<string>", line 1, in <module>
psycopg2.InternalError: current transaction is aborted, commands ignored until end of transaction block

What am I doing wrong?

btw the same error occurs with the line

cr.execute('INSERT INTO id1 (%s,%s,%s,%s,%s) VALUES ("%s","%s","%s","%s","%s");' % ('fund', 'sleeve_id', 'sub_id', 'level_id', 'Entity_id','RHUBARB RHUBARB - RHUBARB RHUBARB', '2', '1', '1', 'CRUMB1'))

Talvalin · Accepted Answer · 2013-06-14 16:54:26Z

1

I think you have to commit the first statement that creates the table, before you can run any inserts on it. Try running cn.commit() in between your two SQL statements to see if that resolves the issue.

Alternatively, set autocommit=True when you create your initial connection to the database.

answered Jun 14, 2013 at 16:54

Talvalin

7,8972 gold badges33 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tahnoon Pasha Over a year ago

Thanks @Talvalin this wound up being a little simpler. The second set of "%s" were the wrong kind of quotation for pyscopg2. postgres is looking for single quotes it transpires. Replacing them with %r or changing cr.execute(SQL % data) with cr.execute(SQL,data) did the trick.

Tahnoon Pasha · Accepted Answer · 2013-06-17 08:01:06Z

Okay for completeness the tricky bit was feeding the field names in as string formatters too.

I split the code into two sections.

import psycopg2 as pq
import os

# Create the database
os.system('dropdb ptest')
os.system('createdb ptest')
# connect to the database
cn = pq.connect('dbname=ptest user=myname')
cr = cn.cursor()

# the wierd tuple at the EOL below is to preserve the list 
# lookup in case that is where the error is.
cr.execute('CREATE TABLE id1 (%s varchar, %s int PRIMARY KEY, %s int, %s int, %s varchar)' % tuple(['fund', 'sleeve_id', 'sub_id', 'level_id', 'Entity_id'])
SQL = 'INSERT INTO id1 (%s,%s,%s,%s,%s) VALUES' % tuple(['fund', 'sleeve_id', 'sub_id', 'level_id', 'Entity_id'])
SQL = SQL + ' (%s,%s,%s,%s,%s);'
data = tuple(['RHUBARB RHUBARB - RHUBARB RHUBARB', '2', '1', '1', 'CRUMB1'])
cr.execute(SQL,data)

and that worked fine and (I believe) remains safe from SQL injection type attacks.

Collectives™ on Stack Overflow

python psycopg2 internal error on string formatter

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related