如何使用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

^{pr2}$

现在我需要在pyscripter中直接执行sql代码，如何将这些代码更改为python代码

我在Windows8.1x64下使用postgresql 8.4、Python2.7.6。在

Answer 1

使用^{}类的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代码中使用了Dollar-quoted String Constants