Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.python > #39488 > unrolled thread

psycopg2 craziness

Started byandydtaylor@gmail.com
First post2013-02-21 15:27 -0800
Last post2013-02-22 17:22 +1100
Articles 5 — 3 participants

Back to article view | Back to comp.lang.python

Contents

  psycopg2 craziness andydtaylor@gmail.com - 2013-02-21 15:27 -0800
    Re: psycopg2 craziness MRAB <python@mrabarnett.plus.com> - 2013-02-22 00:36 +0000
      Re: psycopg2 craziness andydtaylor@gmail.com - 2013-02-21 16:44 -0800
      Re: psycopg2 craziness andydtaylor@gmail.com - 2013-02-21 16:44 -0800
    Re: psycopg2 craziness Chris Angelico <rosuav@gmail.com> - 2013-02-22 17:22 +1100

#39488 — psycopg2 craziness

Fromandydtaylor@gmail.com
Date2013-02-21 15:27 -0800
Subjectpsycopg2 craziness
Message-ID<2427e351-dd19-4e13-af76-d1c41aa5ad3d@googlegroups.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@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

[toc] | [next] | [standalone]

#39507

FromMRAB <python@mrabarnett.plus.com>
Date2013-02-22 00:36 +0000
Message-ID<mailman.2228.1361493382.2939.python-list@python.org>
In reply to#39488
On 2013-02-21 23:27, andydtaylor@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@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
>

[toc] | [prev] | [next] | [standalone]

#39508

Fromandydtaylor@gmail.com
Date2013-02-21 16:44 -0800
Message-ID<e38f7c39-d748-4601-a107-2eccaae3b792@googlegroups.com>
In reply to#39507
I'd actually forgotten about commit, thanks!

I'll have another go with this in mind.

[toc] | [prev] | [next] | [standalone]

#39509

Fromandydtaylor@gmail.com
Date2013-02-21 16:44 -0800
Message-ID<mailman.2229.1361494454.2939.python-list@python.org>
In reply to#39507
I'd actually forgotten about commit, thanks!

I'll have another go with this in mind.

[toc] | [prev] | [next] | [standalone]

#39524

FromChris Angelico <rosuav@gmail.com>
Date2013-02-22 17:22 +1100
Message-ID<mailman.2240.1361514159.2939.python-list@python.org>
In reply to#39488
On Fri, Feb 22, 2013 at 10:27 AM,  <andydtaylor@gmail.com> wrote:
>>>> cursor_to.execute("CREATE TABLE foo (id serial PRIMARY KEY);")

Like many things, it's silent when everything works. As MRAB
suggested, you probably need to commit before the changes become
visible; unlike certain other database engines, PostgreSQL actually
provides transactional integrity for DDL queries (CREATE/DROP TABLE
etc) as well as DML queries (INSERT/UPDATE etc). This is incredibly
handy, but can be surprising if you're accustomed to them being
immediately visible to other sessions.

But next time you ask for help, don't say that you're "having issues"
- say *what* issues you're having! It makes helping you so much easier
if we know up front what you need help with, rather than being forced
to guess :)

(By the way, does anyone else see irony in there being "craziness" in
a module that's pretty much called "psycho"?)

ChrisA

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.python

csiph-web