psycopg2 craziness

[MRAB]

On 2013-02-21 23:27, andydtaylor at gmail.com wrote:
>
>
> 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()

You ask it to create a table "foo":

>>>> cursor_to.execute("CREATE TABLE foo (id serial PRIMARY KEY);")

Done. The table "foo" has been created.

You ask it to create a table "foo" again:

>>>> cursor_to.execute("CREATE TABLE foo (id serial PRIMARY KEY);")

It complains because it already exists. No surprise there, you've
already created it.

> 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
>

Maybe you need to commit the change?

>
> 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()

You ask it to drop the table "tubecross" if it exists.

>>>> cursor_to.execute("DROP TABLE IF EXISTS tubecross")

Done. The table "done", if it ever existed, has been dropped.

You ask it to drop the table "tubecross" again.

>>>> cursor_to.execute("DROP TABLE tubecross")

It complains because it doesn't exist. No surprise there, you've
already dropped it.

> 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
>

Maybe you need to commit the change?

>
> 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
>