psycopg2 craziness
MRAB
python at mrabarnett.plus.com
Thu Feb 21 19:36:21 EST 2013
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
>
More information about the Python-list
mailing list