Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

    conn = psycopg2.connect("dbname = 'routing_template' user = 'postgres' host = 'localhost' password = '****'")
    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
I have not used psycopg2, but have you considered using pyodbc instead? – Alex Tereshenkov Mar 31 '14 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 '14 at 11:26
No, seriously, do not use PyODBC if psycopg2 is an option. psycopg2 is a lot better. – Craig Ringer Mar 31 '14 at 12:28
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
    conn = psycopg2.connect("dbname = 'routing_template' user = 'postgres' host = 'localhost' password = '****'")
except psycopg2.DatabaseError, ex:
    print 'I am unable to connect the database: " + ex

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 '14 at 12:54

Your Answer


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.