psycopg2 craziness

[andydtaylor at gmail.com]

Hi,

I'm trying to use psycopg2 but having some issues. Would it be possible to get some pointers? I seem unable to execute any SQL statements.


So here are my database tables before (and after) I try to do anything:

postgres=# \c
You are now connected to database "postgres" as user "postgres".
postgres=# \d
                    List of relations
 Schema |          Name           |   Type   |   Owner    
--------+-------------------------+----------+------------
 public | loc_all                 | table    | django_dev
 public | loc_all_unique          | table    | django_dev
 public | loc_dlr                 | table    | django_dev
 public | loc_londonbuses         | table    | django_dev
 public | loc_londonriverservices | table    | django_dev
 public | loc_londonunderground   | table    | django_dev
 public | loc_tramlink            | table    | django_dev
 public | lu_stations_id_seq      | sequence | postgres
 public | postcode_input          | table    | postgres
 public | postcode_lat_long       | table    | django_dev
 public | test_foo                | table    | django_dev
 public | tubecross               | table    | django_dev


Here's what I've been trying to execute in the python shell:

>>> import psycopg2
>>> import psycopg2.extras
>>> db = psycopg2.connect(
...     host = 'localhost',
...     database = 'postgres',
...     user = 'postgres',
...     password = 'password'
...     )
>>> cursor_to = db.cursor()
>>> cursor_to.execute("CREATE TABLE foo (id serial PRIMARY KEY);")
>>> cursor_to.execute("CREATE TABLE foo (id serial PRIMARY KEY);")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
psycopg2.ProgrammingError: relation "foo" already exists

>>> 
>>> 
>>> cursor_to.execute("CREATE TABLE foo (id serial PRIMARY KEY);")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
psycopg2.InternalError: current transaction is aborted, commands ignored until end of transaction block


Here's the postgres log:

2013-02-21 22:22:20 GMT LOG:  database system was shut down at 2013-02-21 22:21:41 GMT
2013-02-21 22:22:20 GMT LOG:  autovacuum launcher started
2013-02-21 22:22:20 GMT LOG:  database system is ready to accept connections
2013-02-21 22:22:20 GMT LOG:  incomplete startup packet
2013-02-21 22:22:21 GMT FATAL:  password authentication failed for user "postgres"
2013-02-21 22:22:22 GMT FATAL:  password authentication failed for user "postgres"
2013-02-21 22:22:22 GMT FATAL:  password authentication failed for user "postgres"
2013-02-21 22:22:23 GMT FATAL:  password authentication failed for user "postgres"
2013-02-21 22:22:23 GMT FATAL:  password authentication failed for user "postgres"
2013-02-21 22:22:24 GMT FATAL:  password authentication failed for user "postgres"
2013-02-21 22:22:24 GMT FATAL:  password authentication failed for user "postgres"
2013-02-21 22:22:25 GMT FATAL:  password authentication failed for user "postgres"
2013-02-21 22:22:25 GMT FATAL:  password authentication failed for user "postgres"
2013-02-21 22:22:26 GMT FATAL:  password authentication failed for user "postgres"
2013-02-21 22:22:26 GMT FATAL:  password authentication failed for user "postgres"
2013-02-21 22:22:26 GMT LOG:  incomplete startup packet
2013-02-21 22:28:52 GMT ERROR:  relation "foo" already exists
2013-02-21 22:28:52 GMT STATEMENT:  CREATE TABLE foo (id serial PRIMARY KEY);
2013-02-21 22:34:53 GMT ERROR:  current transaction is aborted, commands ignored until end of transaction block
2013-02-21 22:34:53 GMT STATEMENT:  CREATE TABLE foo (id serial PRIMARY KEY);




Further example from python shell:

andyt at andyt-ThinkPad-X61:~$ python
Python 2.7.3 (default, Sep 26 2012, 21:51:14) 
[GCC 4.7.2] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import psycopg2
>>> import psycopg2.extras
>>> db = psycopg2.connect(
...     host = 'localhost',
...     database = 'postgres',
...     user = 'postgres',
...     password = 'password'
...     )
>>> cursor_to = db.cursor()
>>> cursor_to.execute("DROP TABLE IF EXISTS tubecross")
>>> cursor_to.execute("DROP TABLE tubecross")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
psycopg2.ProgrammingError: table "tubecross" does not exist

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


Postgres log:

2013-02-21 23:08:18 GMT LOG:  database system was shut down at 2013-02-21 23:07:40 GMT
2013-02-21 23:08:18 GMT LOG:  autovacuum launcher started
2013-02-21 23:08:18 GMT LOG:  database system is ready to accept connections
2013-02-21 23:08:18 GMT LOG:  incomplete startup packet
2013-02-21 23:08:19 GMT FATAL:  password authentication failed for user "postgres"
2013-02-21 23:08:19 GMT FATAL:  password authentication failed for user "postgres"
2013-02-21 23:08:20 GMT FATAL:  password authentication failed for user "postgres"
2013-02-21 23:08:20 GMT FATAL:  password authentication failed for user "postgres"
2013-02-21 23:08:21 GMT FATAL:  password authentication failed for user "postgres"
2013-02-21 23:08:21 GMT FATAL:  password authentication failed for user "postgres"
2013-02-21 23:08:22 GMT FATAL:  password authentication failed for user "postgres"
2013-02-21 23:08:22 GMT FATAL:  password authentication failed for user "postgres"
2013-02-21 23:08:23 GMT FATAL:  password authentication failed for user "postgres"
2013-02-21 23:08:23 GMT FATAL:  password authentication failed for user "postgres"
2013-02-21 23:08:24 GMT FATAL:  password authentication failed for user "postgres"
2013-02-21 23:08:24 GMT LOG:  incomplete startup packet
2013-02-21 23:19:23 GMT ERROR:  table "tubecross" does not exist
2013-02-21 23:19:23 GMT STATEMENT:  DROP TABLE tubecross
2013-02-21 23:22:05 GMT ERROR:  current transaction is aborted, commands ignored until end of transaction block
2013-02-21 23:22:05 GMT STATEMENT:  SELECT * FROM loc_tramlink


Thanks,



Andy