[GIS] How to execute sql lines in postgresql using python？

postgresqlpsycopg2python-2.7windows 8

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.

Best Answer

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.

Related Question