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
--
http://mail.python.org/mailman/listinfo/python-list
