psycopg2 craziness

Discussion in 'Python' started by andydtaylor@gmail.com, Feb 21, 2013.

  1. Guest

    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
     
    , Feb 21, 2013
    #1
    1. Advertising

  2. MRAB Guest

    On 2013-02-21 23:27, 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
    >
     
    MRAB, Feb 22, 2013
    #2
    1. Advertising

  3. Guest

    I'd actually forgotten about commit, thanks!

    I'll have another go with this in mind.
     
    , Feb 22, 2013
    #3
  4. Guest

    I'd actually forgotten about commit, thanks!

    I'll have another go with this in mind.
     
    , Feb 22, 2013
    #4
  5. On Fri, Feb 22, 2013 at 10:27 AM, <> 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
     
    Chris Angelico, Feb 22, 2013
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Jeff Trotman

    Re: Session Object Craziness

    Jeff Trotman, Jul 17, 2003, in forum: ASP .Net
    Replies:
    2
    Views:
    463
    Jeff Trotman
    Jul 18, 2003
  2. Martin c

    Namespace Craziness

    Martin c, Feb 10, 2004, in forum: ASP .Net
    Replies:
    3
    Views:
    594
    Steven Cheng[MSFT]
    Feb 13, 2004
  3. Mike Kamzyuk

    managed and native craziness

    Mike Kamzyuk, Apr 26, 2005, in forum: C++
    Replies:
    2
    Views:
    346
    Mike Kamzyuk
    Apr 26, 2005
  4. ASh
    Replies:
    10
    Views:
    2,573
    Anton Shishkov
    Mar 31, 2010
  5. mrdrew
    Replies:
    5
    Views:
    2,916
    Dennis Lee Bieber
    Apr 5, 2010
Loading...

Share This Page