1

I am trying to write a python function to run PostGIS query:

import sys, os

#set up psycopg2 environment
import psycopg2

def river_dist_matrix(table_name):

    #driving_distance module
    query = """
        select *
        from pgr_drivingDistance ($$
            select
                id as id,
                source::int4 as source,
                target::int4 as target,
                shape_leng::double precision as cost
            from public.'table_name'
            $$, %s, %s, %s, %s
        )
    """

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

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

    #run loops
    rs = []
    i = 1
    while i <= k:
        cur.execute(query, (i, 100000000000, False, False))
        rs.append(cur.fetchall())
        i = i + 1
    # print rs
    #import csv module
    import csv
    import tempfile
    import shutil

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

    # concerning about flow-connection
    with open(filename, 'rb') as f, tempfile.NamedTemporaryFile(mode='wb', delete=False) as g:
        writer = csv.writer(g, delimiter = ',')
        for row in csv.reader(f):
            row = [element if float(element) < 10**6 else 'nan' for element in row]
            writer.writerow(row)

    shutil.move(g.name, filename)


    conn.close()



if __name__ == "__main__":

    table_name = 'tc_500wa'
    river_dist_matrix(table_name)

But as I ran code above, I got this error:

Traceback (most recent call last):
  File "C:\Users\Heinz\Desktop\river_dist_matrix.py", line 95, in <module>
    river_dist_matrix(table_name)
  File "C:\Users\Heinz\Desktop\river_dist_matrix.py", line 40, in river_dist_matrix
    cur.execute("select count(*) from 'table_name'")
psycopg2.ProgrammingError: syntax error at or near "'table_name'"
LINE 1: select count(*) from 'table_name'
                             ^

[Finished in 0.2s]

I think the error is in relation to the input arg table_name because it was placed in the query and other places in the code. As in:

query = """
    select *
    from pgr_drivingDistance ($$
        select
            id as id,
            source::int4 as source,
            target::int4 as target,
            shape_leng::double precision as cost
        from public.'table_name'
        $$, %s, %s, %s, %s
    )
"""

and

cur.execute("select count(*) from 'table_name'")

How to edit this code to make it run smoothly?

2

As I say in comment in previous answer, you have to replace 'table_name' in query string with real table name, which is provided as function parameter. So you code schould be:

query = """
select *
from pgr_drivingDistance ($$
    select
        id as id,
        source::int4 as source,
        target::int4 as target,
        shape_leng::double precision as cost
    from public.{0}
    $$, %s, %s, %s, %s
)
""".format(table_name)

and

cur.execute("select count(*) from {0}".format(table_name))
  • 1
    You should also consider removing schema name (public.) from first query... Then you can provide fully qualified table name as function parameter (eg. "public.tc_500wa"). Or you can provide table name without schema, but this suppose correctly set schema search path in PG. This is more flexible than hard coded schema name in query. – DavidP Oct 23 '16 at 11:53
  • Thank you for the effective advice, I think to remove public from query is a good choice. How to edit code to achieve this?( I tried .format(scheme.table_name) in the last line of the query but error showed up. – Heinz Oct 23 '16 at 12:49
  • 1
    @Heinz just replace public.{0} with only {0} Then you can pass schema.table_name in function call in main section of your script as follows: table_name = 'public.tc_500wa' – DavidP Oct 23 '16 at 13:06
0

I hing the source of problem is single quoted table name. Single quotes in PG means string literal... so you try to select count from string, which yields error. You should remove quotes around table name or you should use double quotes. Correct sql query should look like this:

"select count(*) from table_name"

or

"select count(*) from \"table_name\""

And remember - double quoted object names are case sensitive. So "table_name" is different form "Table_Name", etc.

  • Thank you for reply, I tried your way but still got error, how to edit code in the post where "table_name" exist in the line "from public.'table_name'"? – Heinz Oct 23 '16 at 9:25
  • That's because you don't use table_name parameter passed to function. So you have to replace 'table_name' in each query with real name from function parameter. You can do than in many different ways, but simplest one is use string.format() function. Replace 'table_name' with {0} and after each query string place .format(table_name) eg.: 'select count(*) from {0}'.format(table_name) – DavidP Oct 23 '16 at 9:35

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.