psycopg2字典转储到json

Question

如果这是一个noob问题，很抱歉，但我正在尝试将psycopg2字典直接转储到json字符串中。我确实在浏览器中得到了一个返回值，但它的格式与我看到的大多数其他json示例不同。其思想是将select语句的结果转储到json字符串中，并在另一端将其解压以添加到客户端的数据库中。下面是代码和返回的示例。有没有更好的方法使用json和psycopg2执行此操作

    # initializing variables
    location_connection = location_cursor = 0
    sql_string = coordinate_return = data = ""

    # opening connection and setting cursor
    location_connection = psycopg2.connect("dbname='' user='' password=''")
    location_cursor = location_connection.cursor(cursor_factory=psycopg2.extras.RealDictCursor)

    # setting sql string and executing query
    sql_string = "select * from " + tablename + " where left(alphacoordinate," + str(len(coordinate)) + ") = '" + coordinate + "' order by alphacoordinate;"
    location_cursor.execute(sql_string)
    data = json.dumps(location_cursor.fetchall())

    # closing database connection
    location_connection.close()

    # returning coordinate string
    return data

sample return

"[{\"alphacoordinate\": \"nmaan-001-01\", \"xcoordinate\": 3072951151886, \"planetarydiameter\": 288499, \"planetarymass\": 2.020936938e+27, \"planetarydescription\": \"PCCGQAAA\", \"planetarydescriptionsecondary\": 0, \"moons\": 1"\"}]"

Answer 1

您可以使用row_to_json直接在Postgres中创建JSON字符串：

# setting sql string and executing query
sql_string = "select row_to_json(" + tablename + ") from " + tablename + " where left(alphacoordinate," + str(len(coordinate)) + ") = '" + coordinate + "' order by alphacoordinate;"
location_cursor.execute(sql_string)
data = location_cursor.fetchall()