Python insert via psycopg2 into postgresql failing

Question

I'm a noob so having trouble with inserting into Postgresql via psycopg2. I have a file with lines such as:

42::Dead Presidents (1995)::Action|Crime|Drama
43::Restoration::Drama

I'm trying to change those lines into insert statements via the following code:

import psycopg2

try:
   db = psycopg2.connect("dbname='db' user='postgres' host='localhost' password='password'")
   cur = db.cursor()
except:
   print("Unable to connect to the database.")

for line in open('./movies.dat'):
   (movie_id, movie_name, tags) = line.split('::')[0:3]
   ypos = movie_name.rfind('(')
   ypos2 = movie_name.rfind(')')
   if ypos < 0:
      cur.execute("insert into movies values (%s, %s, %s, null)", (movie_id, movie_name, tags))
   else:
      cur.execute("insert into movies values (%s, %s, %s, %s)", (movie_id, movie_name[0:ypos].strip(), tags, movie_name[ypos+1:ypos2].strip()))

Unfortunately I get the error:

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

I have no idea why, or how to go about debugging the general error of psycopg2. Anybody have any helpful ideas?

This is python 3.2.3 and postgresql 9.2

Check the Postgres' log. What you see is the last error and you need the first one - the one causing the failure of the whole transaction. — Milen A. Radev
– Milen A. Radev, Commented Sep 15, 2012 at 23:03
2012-09-15 22:48:42 CAT STATEMENT: insert into movies values ('29', 'City of Lost Children, The (Cité des enfants perdus, La) (1995', 'Adventure|Drama|Fantasy|Mystery|Sci-Fi', '') — Superdooperhero
– Superdooperhero, Commented Sep 16, 2012 at 5:44
Don't see anything wrong with that log, except for the fact that my year extractions is not working correctly and it might need a semicolon at the end. Do you reckon it's the semicolon? — Superdooperhero
– Superdooperhero, Commented Sep 16, 2012 at 5:45
If I just call it with hardcoded values it gives the same error, even if I use a semicolon — Superdooperhero
– Superdooperhero, Commented Sep 16, 2012 at 5:50
2012-09-16 07:48:10 CAT ERROR: current transaction is aborted, commands ignored until end of transaction block 2012-09-16 07:48:10 CAT STATEMENT: insert into movies values (1, 'Dogma', 'Action', 2010) 2012-09-16 07:48:23 CAT ERROR: current transaction is aborted, commands ignored until end of transaction block 2012-09-16 07:48:23 CAT STATEMENT: insert into movies values (1, 'Dogma', 'Action', 2010); 2012-09-16 07:49:06 CAT ERROR: current transaction is aborted, commands ignored until end of transaction block — Superdooperhero
– Superdooperhero, Commented Sep 16, 2012 at 5:50

Brendan Long · Accepted Answer · 2012-09-15 21:17:48Z

1

except:
    print("Unable to connect to the database.")

You're ignoring a fatal error. You should:

Print a more useful error message (containing the actual error)
Stop execution (either remove this catch statement and let the exception propagate up, or return an error code).

answered Sep 15, 2012 at 21:17

Brendan Long

54.5k21 gold badges154 silver badges194 bronze badges

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

1 Comment

Superdooperhero Over a year ago

That part is only there for the database connection bit and does not relate to my problem.

Collectives™ on Stack Overflow

Python insert via psycopg2 into postgresql failing

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related