psycopg2: database connectivity to PostgreSQL

[PoovenM]

Hi there, I'm trying to connect to a PostGIS database (which is basically an enriched PostgreSQL database) and I'm having trouble with the INSERT statement. Here's my code:

import psycopg2

try:
    conn = psycopg2.connect("dbname='postgis' user='fdi' host='localhost' password='fdi'");
    print "Database connection established"
except:
    print "Critical Error: Unable to connect to the database"
cur = conn.cursor()
cur.execute("""INSERT INTO fdi.fdiresults (grid, index) VALUES (POINT(1, 1), 1)""")

It does sucessfully connect to the database (as per the program output) but when I query the database using the pgAdmin III SQL Query feature, I get no returned rows. I use the following SQL code to query:

SELECT * FROM fdi.fdiresults;

I even refresh the database but still nothing is returned. There is not traceback error given by Python either.
The following SQL statement working in the Query Editor from pgAdmin:

INSERT INTO fdi.fdiresults (grid, index) VALUES (POINT(1, 1), 1);

So since my SQL seems to be working, I don't see why my code isn't working. Can someone please give me a few suggestions?

[jlm699]

When using psycopg2 your operations are only transactions. In order to get your transaction to commit to the database you'll need to issue a conn.commit() command.

[woooee]

I get no returned rows. I use the following SQL code to query:
SELECT * FROM fdi.fdiresults;

fdi.fdiresults seems odd for a table name. Try "SELECT tablename FROM pg_tables where tablename not like 'pg_%'" and see what it gives you for a table name. (Note that semicolons are not used in Python.) I am not familiar with PostGIS, but generally a select statement is more like 'cur.execute( "SELECT * FROM tablename")'.

[jlm699]

(Note that semicolons are not used in Python.)

When I was writing a data collecting application that interfaced with pgSQL via psycopg2 I tried out both ways (a query with and without a semicolon), neither of which threw any errors.

And that line ...SELECT etc, is what he typed into his Postgres GUI, not python.

[PoovenM]

Hi guys, thank you so much. Jlm699 was correct, I needed to commit the changes to the database. I knew that there was a commit call that has to be used but I was calling it on the cursor (cur) :icon_redface:
And I agree, I haven't really seen tables being used the way I have (that is: fdi.fdiresults) but it does work. The fdi is the schema and fdiresults is the table. Clever use of SQL there mate :icon_cool:

Once again, thanks guys!

[jlm699]

Hi guys, thank you so much. Jlm699 was correct, I needed to commit the changes to the database. I knew that there was a commit call that has to be used but I was calling it on the cursor (cur) :icon_redface:
And I agree, I haven't really seen tables being used the way I have (that is: fdi.fdiresults) but it does work. The fdi is the schema and fdiresults is the table. Clever use of SQL there mate :icon_cool:

Once again, thanks guys!

It's understandable that you'd try to commit the cursor instead of 'conn', mainly because you've tricked yourself by using that nomenclature! The return of psycopg2.connect() is actually a database object! Maybe it'd help your thinking if you changed all instances of conn to db or my_db? That's what I did!

I'm glad we could help.