問題描述

我在設計一個使用 psycopg2 庫規范的好算法時遇到了一些問題 這里

I have some problem to design a good algorithm which use specification of psycopg2 library described here

我想建立一個與這個字符串相等的動態查詢:

I want to build a dynamic query equal to this string :

SELECT ST_GeomFromText('POLYGON((0.0 0.0,20.0 0.0,20.0 20.0,0.0 20.0,0.0 0.0))');

如您所見，我的 POLYGON 對象包含多個點，讀取一個簡單的 csv 文件 some.csv，其中包含:

As you can see, my POLYGON object contain multiple point, read in a simple csv file some.csv which contain :

0.0;0.0
20.0;0.0
20.0;20.0
0.0;20.0
0.0;0.0

所以我動態構建查詢，csv 中行/數據數的函數.

So i build the query dynamically, function of the number of line/data in the csv.

這里我的程序生成要執行的 SQL Query 字符串:

Here my program to generate the SQL Query string to execute :

import psycopg2
import csv 

# list of points
lXy = []

DSN= "dbname='testS' user='postgres' password='postgres' host='localhost'"
conn = psycopg2.connect(DSN)

curs = conn.cursor()

def genPointText(curs,x,y):
    generatedPoint = "%s %s" % (x,y)
    return generatedPoint

#Lecture fichier csv
polygonFile = open('some.csv', 'rb')
readerCSV = csv.reader(polygonFile,delimiter = ';')

for coordinates in readerCSV:
    lXy.append(genPointText(curs,float(coordinates[0]),float(coordinates[1])))

# function of list concatenation by separator
def convert(myList,separator):
    return separator.join([str(i) for i in myList])

# construct simple query with psycopg
def genPolygonText(curs,l):
    # http://initd.org/psycopg/docs/usage.html#python-types-adaptation
    generatedPolygon = "POLYGON((%s))" % convert(l, ",")
    return generatedPolygon

def executeWKT(curs,geomObject,srid):
    try:
            # geometry ST_GeomFromText(text WKT, integer srid);
        finalWKT = "SELECT ST_GeomFromText('%s');" % (geomObject) 
        print finalWKT
        curs.execute(finalWKT)
    except psycopg2.ProgrammingError,err:
        print "ERROR = " , err

polygonQuery = genPolygonText(curs,lXy)
executeWKT(curs,polygonQuery,4326)

如您所見，這是可行的，但是由于python對象和sql postgresql對象之間的轉換問題，這種方式不正確.

As you can see, that's works, but this way is not correct because of conversion problem between python object and sql postgresql object.

在文檔中，我只看到了為靜態查詢提供和轉換數據的示例.您是否知道在查詢的動態構建中創建具有正確類型的正確字符串的優雅"方式?

In the documentation, i see only example to feed and convert data for static query. Do you know an "elegant" way to create correct string with correct type in a dynamic build for query ?

更新 1:

如你所見，當我在這個簡單的例子中使用 psycopg 類型轉換函數時，我有這樣的錯誤:

As you can see, when i use psycopg type transformation function on this simple example, i have error like this :

<代碼>查詢= ST_GeomFromText( 'POLYGON((52.146542 19.050557，52.148430 19.045527，52.149525 19.045831，52.147400 19.050780，52.147400 19.050780，52.146542 19.050557))'，4326)"名稱 = 我的表"嘗試:curs.execute('INSERT INTO %s(name, url, id, point_geom, poly_geom) VALUES (%s);', (name,query))除了 psycopg2.ProgrammingError，錯誤:打印錯誤="，錯誤

錯誤相等:

ERROR =  ERREUR:  erreur de syntaxe sur ou près de ? E'my_table' ?
LINE 1: INSERT INTO E'my_table'(name, poly_geom) VALUES (E'ST_GeomFr...

更新 2:

感謝stackoverflow用戶的最終代碼！

Final code which work thanks to stackoverflow users !

#info lib : http://www.initd.org/psycopg/docs/
import psycopg2
# info lib : http://docs.python.org/2/library/csv.html
import csv 

# list of points
lXy = []

DSN= "dbname='testS' user='postgres' password='postgres' host='localhost'"

print "Opening connection using dns:", DSN
conn = psycopg2.connect(DSN)

curs = conn.cursor()

def genPointText(curs,x,y):
    generatedPoint = "%s %s" % (x,y)
    return generatedPoint

#Lecture fichier csv
polygonFile = open('some.csv', 'rb')
readerCSV = csv.reader(polygonFile,delimiter = ';')

for coordinates in readerCSV:
    lXy.append(genPointText(curs,float(coordinates[0]),float(coordinates[1])))

# function of list concatenation by separator
def convert(myList,separator):
    return separator.join([str(i) for i in myList])

# construct simple query with psycopg
def genPolygonText(l):
    # http://initd.org/psycopg/docs/usage.html#python-types-adaptation
    generatedPolygon = "POLYGON((%s))" % convert(l, ",")
    return generatedPolygon

def generateInsert(curs,tableName,name,geomObject):
    curs.execute('INSERT INTO binome1(name,geom) VALUES (%s, %s);' , (name,geomObject))


def create_db_binome(conn,name):

    curs = conn.cursor()

    SQL = (
        "CREATE TABLE %s"
        " ("
        " polyname character varying(15),"
        " geom geometry,"
        " id serial NOT NULL,"
        " CONSTRAINT id_key PRIMARY KEY (id)"
        " )" 
        " WITH ("
        " OIDS=FALSE"
        " );"
        " ALTER TABLE %s OWNER TO postgres;"
        ) %(name,name)
    try:
      #print SQL
      curs.execute(SQL)

    except psycopg2.ProgrammingError,err:
      conn.rollback()
      dropQuery = "ALTER TABLE %s DROP CONSTRAINT id_key; DROP TABLE %s;" % (name,name)
      curs.execute(dropQuery)
      curs.execute(SQL)

    conn.commit()

def insert_geometry(polyname,tablename,geometry):

    escaped_name = tablename.replace('""','""')

    try:
        test = 'INSERT INTO %s(polyname, geom) VALUES(%%s, ST_GeomFromText(%%s,%%s))' % (escaped_name)
        curs.execute(test, (tablename, geometry, 4326))
        conn.commit()
    except psycopg2.ProgrammingError,err:
        print "ERROR = " , err

################
# PROGRAM MAIN #
################

polygonQuery = genPolygonText(lXy)
srid = 4326
table = "binome1"

create_db_binome(conn,table)
insert_geometry("Berlin",table,polygonQuery)
insert_geometry("Paris",table,polygonQuery)

polygonFile.close()
conn.close()

推薦答案

您正在嘗試將表名作為參數傳遞.如果您只是查看 PostgreSQL 錯誤日志，您可能會立即看到這一點.

You are trying to pass a table name as a parameter. You probably could've seen this immediately if you'd just looked at the PostgreSQL error log.

您試圖通過 psycopg2 作為參數傳遞的表名被轉義，產生如下查詢:

The table name you're trying to pass through psycopg2 as a parameter is being escaped, producing a query like:

INSERT INTO E'my_table'(name, url, id, point_geom, poly_geom) VALUES (E'ST_GeomFromText(''POLYGON(( 52.146542 19.050557, 52.148430 19.045527, 52.149525 19.045831, 52.147400 19.050780, 52.147400 19.050780, 52.146542 19.050557))'',4326)');'

這不是您想要的，也不會起作用；你不能像文字一樣轉義表名.必須使用普通的 Python 字符串插值來構造動態 SQL，對于實際字面值，只能使用參數化語句占位符.

This isn't what you intended and won't work; you can't escape a table name like a literal. You must use normal Python string interpolation to construct dynamic SQL, you can only use parameterized statement placeholders for actual literal values.

params = ('POLYGON(( 52.146542 19.050557, 52.148430 19.045527, 52.149525 19.045831, 52.147400 19.050780, 52.147400 19.050780, 52.146542 19.050557))',4326)
escaped_name = name.replace('"",'""')
curs.execute('INSERT INTO "%s"(name, url, id, point_geom, poly_geom) VALUES (ST_GeomFromText(%%s,%%s));' % escaped_name, params)

看看我是如何直接插入名稱來生成查詢字符串的:

See how I've interpolated the name directly to produce the query string:

INSERT INTO my_table(name, url, id, point_geom, poly_geom) VALUES (ST_GeomFromText(%s,%s));

(%% 通過 % 替換轉換為純 %).然后我將該查詢與定義 POLYGON 的字符串和 ST_GeomFromText 的另一個參數作為查詢參數一起使用.

(%% gets converted to plain % by % substitution). Then I'm using that query with the string defining the POLYGON and the other argument to ST_GeomFromText as query parameters.

我沒有對此進行測試，但它應該可以為您提供正確的想法并幫助您解釋問題所在.

I haven't tested this, but it should give you the right idea and help explain what's wrong.

非常小心在進行這樣的字符串插值時，這是 的簡單途徑SQL 注入.我在上面顯示的代碼中做了非常粗略的引用，但是如果您的客戶端庫提供了一個正確的標識符引用函數，我想使用它.

BE EXTEMELY CAREFUL when doing string interpolation like this, it's an easy avenue for SQL injection. I've done very crude quoting in the code shown above, but I'd want to use a proper identifier quoting function if your client library offers one.

這篇關于使用 psycopg2 python 庫并使用良好的轉換類型工具構建 SQL 動態查詢的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！