在PostgreSQL中使用rcost路由时出现Python错误

Question

我使用pyscripter从外部调用PostgreSQL并路由我的网络，这是我的代码

import sys, os

#set up psycopg2 environment
import psycopg2

#driving_distance module
query = """
    select *
    from driving_distance ($$
        select
            gid as id,
            source::int4 as source,
            target::int4 as target,
            cost::double precision as cost
        from network
        $$, %s, %s, %s, %s
    )
"""

#make connection between python and postgresql
conn = psycopg2.connect("dbname = 'TC_area' user = 'postgres' host = 'localhost' password = 'xxxx'")
cur = conn.cursor()

#count rows in the table
cur.execute("select count(*) from network")
result = cur.fetchone()
k = result[0] + 1                #number of points = number of segments + 1

#run loops
rs = []
i = 1
while i <= k:
    cur.execute(query, (i, 1000000, False, True))
    rs.append(cur.fetchall())
    i = i + 1

#import csv module
import csv

j = 0
h = 0
ars = []
element = list(rs)

#export data to every row
with open('distMatrix.csv', 'wb') as f:
    writer = csv.writer(f, delimiter = ',')
    while j <= k - 1:
        while h <= k - 1:
            rp = element[j][h][1]
            ars.append(rp)
            h = h + 1
        else:
            h = 0
            writer.writerow(ars)
            ars = []
        j = j + 1

conn.close()

结果是好的，但是如果我想在PostgreSQL中的行驶距离函数中使用反向成本函数，我只需在“cost:：double precision as cost”下面加一行

rcost::double precision as reverse_cost

我加了这行之后，这个错误框弹出了

以及python IDLE中的错误消息

Traceback (most recent call last):


File "C:\Users\Heinz\Documents\pyscript\postgresql\distMatrix.py.py", line 47, in <module>
    cur.execute(query, (i, 1000000, False, True))
ProgrammingError: 錯誤:  在"語法錯誤"附近發生 rcost
LINE 7:             rcost::double precision as reverse_cost
                    ^
QUERY:  
        select
            gid as id,
            source::int4 as source,
            target::int4 as target,
            cost::double precision as cost
            rcost::double precision as reverse_cost
        from network

PS.我修改了这个网络的表，因此它有一个“rcost”列，下面是视图的一部分，

如果我在pgAdmin中执行代码，我可以成功地得到正确的结果

SELECT * FROM driving_distance('
SELECT gid as id,
    source::int4 AS source, 
    target::int4 AS target,
    cost::double precision as cost,
    rcost::double precision as reverse_cost
    FROM network',
3, 10000000, false, True);

但是我需要python来做循环，如何解决这个问题

PS.如果我将函数中的两个布尔值都设置为FALSE，理论上程序将忽略rcost并返回仅根据成本计算的答案，但我仍然得到相同的错误，

这个问题似乎是由成本造成的。你知道吗

我正在Windows8.1x64下使用PostgreSQL8.4和Python2.7.6。你知道吗

---

更新#1

我在剧本里改了两行然后就成功了

cost::double precision as cost,                    #need to add a trailing comma if followed by rcost

cur.execute(query, (i, 100000000000, False, True)) #the range must be greater than max of rcost

Answer 1

您的行“cost：：double precision as cost”需要一个尾随逗号。你知道吗