Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I use postgresql 8.4 to route a river network, the following is my code typed in pgAdmin and the result is fine,

select * from driving_distance
('select gid as id,
 start_id::int4 as source, end_id::int4 as target,
 shape_leng::double precision as cost from network',
 10, 1000000, false, false);

enter image description here

I have installed psycopg2 and it successfully connect python and postgresql,

#set up python and postgresql connection
import psycopg2

try:
    conn = psycopg2.connect("dbname = 'routing_template' user = 'postgres' host = 'localhost' password = '****'")
except:
    print 'I am unable to connect the database'

Now I need to directly execute my sql code on the top in pyscripter, how should I change these codes to python code?

I am working with postgresql 8.4, python 2.7.6 under Windows 8.1 x64.

share|improve this question
1  
I have not used psycopg2, but have you considered using pyodbc instead? code.google.com/p/pyodbc/wiki/ConnectionStrings –  Alex Tereshenkov Mar 31 at 10:08
    
@AlexTereshenkov thank you, In the very beginning I thought I need to use postgresql to handle shapefile data, so I choose psycopg2. Now I am reading Getting Started of pyodbc, but I really don't know which one has merit connecting to postgresql. –  Heinz Mar 31 at 11:26
2  
No, seriously, do not use PyODBC if psycopg2 is an option. psycopg2 is a lot better. –  Craig Ringer Mar 31 at 12:28
add comment

1 Answer 1

up vote 3 down vote accepted

First, never swallow an exception, or catch all exceptions. Your connection code should be more like:

import sys
import psycopg2

conn = None
try:
    conn = psycopg2.connect("dbname = 'routing_template' user = 'postgres' host = 'localhost' password = '****'")
except psycopg2.DatabaseError, ex:
    print 'I am unable to connect the database: " + ex
    sys.exit(1)

Now, you have a connection. Running SQL is trivial ... as the psycopg2 tutorial would show you.

curs = conn.cursor()
curs.execute("SELECT ...")
for row in curs:
   print row

You can be much more sophisticated about how you output row data, etc. The manual has lots of useful examples.

share|improve this answer
    
thank you for your answer. I have read the tutorial before, but I am too new to solve the sql problem, anyway, I will spend much more time to learn sql, thanks a lot! –  Heinz Mar 31 at 12:54
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.