psycopg2: database connectivity to PostgreSQL

•

What is DaniWeb IT Discussion Community?

You're currently browsing the Python section within the Software Development category of DaniWeb, a massive community of 402,053 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,420 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.

Join our community!

Please support our Python advertiser: Programming Forums

Thread Tools Display Modes

Views: 443 | Replies: 5 | Solved

•

Join Date: Aug 2006

Location: South Africa, Durban

Posts: 100

Reputation: PoovenM is an unknown quantity at this point

Rep Power: 3

Solved Threads: 8

PoovenM

Offline

Junior Poster

psycopg2: database connectivity to PostgreSQL

Jul 24th, 2008

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:

 Help with Code Tags 
 python Syntax (Toggle Plain Text) 
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)""")
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:

Help with Code Tags

sql Syntax (Toggle Plain Text)

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:

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
INSERT INTO fdi.fdiresults (grid, INDEX) VALUES (POINT(1, 1), 1);
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?

•

Join Date: Jul 2008

Location: Durham, NC

Posts: 139

Reputation: jlm699 is an unknown quantity at this point

Rep Power: 1

Solved Threads: 21

jlm699

Offline

Junior Poster

Re: psycopg2: database connectivity to PostgreSQL

Jul 24th, 2008

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.

Let's Go Pens!

•

Join Date: Dec 2006

Posts: 408

Reputation: woooee is on a distinguished road

Rep Power: 2

Solved Threads: 56

woooee

Offline

Posting Pro in Training

Re: psycopg2: database connectivity to PostgreSQL

Jul 24th, 2008

•

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")'.

•

Join Date: Jul 2008

Location: Durham, NC

Posts: 139

Reputation: jlm699 is an unknown quantity at this point

Rep Power: 1

Solved Threads: 21

jlm699

Offline

Junior Poster

Re: psycopg2: database connectivity to PostgreSQL

Jul 24th, 2008

•

Originally Posted by woooee

(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.

Let's Go Pens!

•

Join Date: Aug 2006

Location: South Africa, Durban

Posts: 100

Reputation: PoovenM is an unknown quantity at this point

Rep Power: 3

Solved Threads: 8

PoovenM

Offline

Junior Poster

Re: psycopg2: database connectivity to PostgreSQL

Jul 25th, 2008

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)

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

Once again, thanks guys!

•

Join Date: Jul 2008

Location: Durham, NC

Posts: 139

Reputation: jlm699 is an unknown quantity at this point

Rep Power: 1

Solved Threads: 21

jlm699

Offline

Junior Poster

Re: psycopg2: database connectivity to PostgreSQL

Jul 25th, 2008

•

Originally Posted by 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)
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

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.

Let's Go Pens!