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

Question

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

Navigation

#1 by (1 votes)

0

Good morning,

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 inserting into the database it presents error. Follow 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

Here 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

asked by anonymous 05.12.2018 / 14:21

1 answer

How to make an appointment to know the averages of people per day a week Function executing before the response of my request

score 1 · Answer 1

Python is a language that has no magic in syntax - you have written a string when you write:

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

What's inside VALUES there is not the info_crawler_json variable, and yes, just the text "info_crawler_json" - if this text was 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) - I put the example at the end. In fact, just this point of putting values from various sources within SQL commands is one of the most popular attackers to aqualquersoftware, as it is where "SQL injection" attacks are possible.

For this reason, in Python, when entering data into SQL (any one - this is standardized in the language), we do not use the forms of data interpolation in strings available in the language itself, but rather, we leave the SQL driver create interdependence for us. The driver code checks the data, and makes a basic "escape" of specifics like ' and ; , avoiding almost all possible SQL injection forms.

In the case of the Postgresql driver, we must mark the points where we enter program data with %s (or %d and %f for numeric data). The psycopg2 driver does not know anything about JSON, otherwise you might already be doing the conversion - this is why you need to pass the JSON as a string - and that conversion is already done correctly.

summarizing

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 necessary to indicate that we are passing a tuple of a single element, not an expression in parentheses).

Nothing prevents you from putting everything in one line:

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

Text interpolation in Python:

In cases where the data will not be entered in the database, but displayed in the terminal or in a text file, Python has several ways of interpolating values within strings. The recommendation was introduced with Python 3.6, and works by putting the f prefix in the strings, and then Python variable names and expressions can be used within { } within the string:

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