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.

  • 1
    I have not used psycopg2, but have you considered using pyodbc instead? code.google.com/p/pyodbc/wiki/ConnectionStrings – 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
  • 2
    No, seriously, do not use PyODBC if psycopg2 is an option. psycopg2 is a lot better. – Craig Ringer Mar 31 '14 at 12:28

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.

  • 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 clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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