Inserting a JSON object into a JSON field in Postgresql with Python

Asked

Viewed 593 times

0

Good morning, everyone,

I have the following problem, I made a Crawler and it receives the information, but at the time of formatting it in json format and insert in the database presents error. Follows the Code:

     valor_coleta = "3,5%" 
     info_crawler = { "valor_coleta": valor_coleta}
     info_crawler_json = json.dumps(info_crawler)

     sql = """INSERT INTO crawler(info) VALUES(info_crawler_json);"""

     connection.run(sql)

     return True

That is the error:

ERROR - column "info_crawler_json" does not exist
LINE 1: INSERT INTO crawler(info) VALUES(info_crawler_json);

Any suggestions on how I can solve this problem?

python json postgresql

  • Vc is passing only a string as parameter to the function connection.run() and not the value of info_crawler_json.

  • In this passage sql = """INSERT INTO crawler(info) VALUES(info_crawler_json);""" info_crawler_json is simply a piece of the variable string sql, I believe you would have to link the value of the variable info_crawler_jsonwith the valuesof your query.

1 answer

1

Python is a language that has no magic in the syntax - you cropped a fixed string when writing:

 sql = """INSERT INTO crawler(info) VALUES(info_crawler_json);"""

What is inside of VALUES there is the variable info_crawler_json , and yes, simply the text "info_crawler_json" - if this text were a variable within postgresql, it would be used there.

But to put the value that is in the Python variable inside the string, this is done with the f-strings (from Python 3.6) - put the example at the end. In fact, just this point of placing values from various sources within SQL commands is one of the most popular attack etores to aqualquersoftware, because it is where the attacks of "SQL Injection are possible".

For this reason, in Python, when inserting data into SQL (anyone - this is standardized in the language), we do not use the forms of interpolation of data in strings available in the language itself, but let the SQL driver create the interpolation for us. The driver code checks the data, and makes a basic "escape" of spatials like ' and ;, avoiding almost all possible forms of SQL Injection.

In the case of the Postgresql driver, we should mark the points where we will insert data from the program with %s (or %d and %f for numerical data). The psycopg2 driver doesn’t know anything about JSON, otherwise you could already do the conversion - because of this you need to pass JSON as string - and this conversion you are already doing correctly.

summing up

Change these lines in your code:

sql = """INSERT INTO crawler (info) VALUES (%s)"""
connection.run(sql, (info_crawler_json,))

(note the extra comma after the variable name - it is needed to indicate that we are passing a tuple of a single element, not an expression in parentheses).

Nothing stops you putting everything in one line:

connection.run("""INSERT INTO crawler (info) VALUES (%s)""", (json.dumps(info_crawler),)

Interpolation of text in Python:

In cases where the data will not be inserted in the database, but displayed in the terminal or in a text file, Python has several ways to interpolate values within strings. recommends was introduced with Python 3.6, and works by prefixing f in strings, and then, variable names and Python expressions can be used within { } inside the string:

print(f"Os dados lidos foram: { info_crawler } ")

Browser other questions tagged python json postgresql

You are not signed in. Login or sign up in order to post.