如何在Python中执行PostgreSQL的SQL语句？

Question

我在使用postgresql 8.4来处理一个河流网络，下面是我在pgAdmin里输入的代码，结果很好，

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);

我已经安装了psycopg2，它成功地将python和postgresql连接起来，

#set up python and postgresql connection
import psycopg2

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

现在我想在pyscripter里直接执行我上面的sql代码，我应该怎么把这些代码改成python代码呢？

我在Windows 8.1 x64上使用postgresql 8.4和python 2.7.6。

Answer 1

使用 cursor 类的 execute 方法来传递参数。

import psycopg2

query = """
    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
        $$, %s, %s, %s, %s
    )
;"""


conn = psycopg2.connect("dbname=cpn")
cur = conn.cursor()
cur.execute(query, (10, 1000000, False, False))
rs = cur.fetchall()
conn.close()
print rs

注意在 Python 中使用三重引号，以及在 SQL 代码中使用的 美元引号字符串常量。