CODEWITHSUNDEEP
pythonjsonpsycopg2
Grymjack
Grymjack

Reputation: 539

psycopg2 dictionary dump to json

Sorry if this is a noob question, but I am trying to dump a psycopg2 dictionary directly into a json string. I do get a return value in the browser, but it isn't formatted like most of the other json examples I see. The idea being to dump the result of a select statement into a json string and unbundle it on the other end to add into a database on the client side. The code is below and a sample of the return. Is there a better way to do this operation with json and 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"\"}]"

Upvotes: 0

Views: 607

Answers (1)

Maurice Meyer
Maurice Meyer

Reputation: 18106

You could create the JSON string directly in Postgres using row_to_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()

Upvotes: 1

Related Questions