如何将整数列表插入到数据库查询的字符串中python3

Question

我试图使用cursor execute在django应用程序中进行查询，但是当我将列表传递到查询字符串时，它会带有引号并返回以下错误：

psycopg2.DataError: invalid input syntax for integer: "139,212,933,2303,2613" LINE 1: ...ados_ibge.sociodemografia_rmc sd WHERE sd.gid IN ('139,212,9...

这是我到目前为止一直在写的代码

gids = self.request.GET.getlist('gid')

if gids:
    with connection.cursor() as cursor:

        g = ','.join(gids)
        cursor.execute("SELECT * FROM poi.farmacias_rmc f, dados_ibge.sociodemografia_rmc sd WHERE sd.gid IN (%s) AND ST_Intersects(sd.geom, f.geom) = true", [g])

        rows = cursor.fetchall()

Answer 1

不应将列表gids转换为字符串，而应转换为元组：

cursor.execute(""" 
    SELECT * FROM poi.farmacias_rmc f, dados_ibge.sociodemografia_rmc sd 
      WHERE sd.gid IN %s 
      AND ST_Intersects(sd.geom, f.geom) = true
    """, [tuple(gids)])

Python列表自动转换为SQL数组，元组转换为SQL列表