On 2013-02-21 23:27, andydtay...@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
--
http://mail.python.org/mailman/listinfo/python-list
