Inserting dictionary rows to database

Question

example_table has id (int) auto increment, json_col (text)

This is the example records from my example_table that I want to insert the dictionary rows to

+----+----------------------------------------------+
| id |                   json_col                   |
+----+----------------------------------------------+
|  1 | {"NO":"1","NIM":"4313010001","NAME":"BENJI"} |
|  2 | {"NO":"2","NIM":"4313010002","NAME":"KENJI"} |
|  3 | {"NO":"3","NIM":"4313010003","NAME":"RYUJI"} |
+----+----------------------------------------------+

This is the array of dictionaries that I have in my python script

dict_result =
[{'NIM': '4313010013','NAME': 'YUKA','STATUS': 'PASSED'},
 {'NIM': '4313010008','NAME': 'YUKO','STATUS': 'PASSED'},
 {'NIM': '4313010017','NAME': 'YUKI','STATUS': 'PASSED'}]

I'm trying to insert each of them into the example_table like the example records above. This is what I do, I tried to cast it into a string to perhaps it could pass the row as it is just like the example records above but it raised error saying ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''NIM': '4313010013', 'NAME': 'YUKA', 'STATUS': 'PASSED'})' at line 1")

for i in dict_result:
    x = str(i)
    cursor.execute("INSERT INTO example_table (json_col) values ({x})".format(x=x))

I have read other questions but in other cases it makes the dictionaries into columns which in my case I want to insert it as it is just like a json. What should I do?

SOLVED With help from comments and added information from Danya02 this line solved the problem

for i in dict_hasil:
    x = str(i)
    cursor.execute("""INSERT INTO example_table (json_col) values ("{x}")""".format(x=x))

conn.commit()

Try add double quotes between `{x}`? this might just add that in, not 100% sure. The reason it's not working is because you are needing quotations around the values. — Eduards, May 23 '21 at 12:06
Hi, writing "{x}" instead of {x} caused `SyntaxError: invalid syntax` in `"{x}"`. Nevermind, I made a mistake, it should also use `"""` or the sql statement. but the rows didnt get into the database tho.. — user15653864, May 23 '21 at 12:18
Do keep in mind that you aren't using JSON here. You're taking the string representation of a `dict`, and that is not necessarily something that can be parsed back into a Python object without using `eval` or similar, and [`eval` is `evil`](https://stackoverflow.com/a/1832957/5936187). If you use `json.dumps` and `json.loads` you'll save yourself many problems with reading the data you put into the database back. — Danya02, May 23 '21 at 12:46

score 2 · Accepted Answer · answered May 23 '21 at 12:05

2

This happens because you are trying to put your data into the SQL command without quotation marks around it. The immediate issue can be solved by writing "{x}" instead of {x}.

However, doing it this way is a terrible idea, because it's an SQL injection waiting to happen. To solve this, you don't put the data that you're trying to insert into your database right in the query string, instead you use query parameters:

cursor.execute("INSERT INTO mydata (fieldA, fieldB) VALUES (?, ?)", ("evil", "data") )

You are correct that in order to put the dictionary into a database row, you'll need to encode it using JSON or something similar. You can look at other answers to see how to do or, but once you get that you get a string which you can insert into the database as shown above.

answered May 23 '21 at 12:05

Danya02

1,006
1
9
24

Hi, writing "{x}" instead of {x} caused `SyntaxError: invalid syntax` in `"{x}"`. Nevermind, I made a mistake, it should also use `"""` or the sql statement. but the rows didnt get into the database tho.. – user15653864 May 23 '21 at 12:18
That'll be because your SQL line is itself enclosed in "double quotes". Anyway, you shouldn't even try to make it work this way, because it is a world of pain and insecurity. At the very least, if you decide to use JSON, that uses double quotes too, so it'll cause the same error you're seeing now, except in the SQL code. – Danya02 May 23 '21 at 12:19
oh I see, but writing "{x}" instead of {x} caused SyntaxError: invalid syntax in "{x}". Why I choose json is because the columns is always changing and by json I don't need to create ever changing columns, this is a final project from my school and I don't really need to make it secure – user15653864 May 23 '21 at 12:32
You might be interested in [MongoDB](https://www.mongodb.com/1) then. It is a database that stores its data as JSON documents, so you can have many different objects with different fields in it, so you don't have to decide on a schema beforehand. – Danya02 May 23 '21 at 12:41
I have solved the problem, I just found out I need to call commit function. Also, It's right to use `"""` for the sql statement plus the "{x}" – user15653864 May 23 '21 at 12:42
Okay, I will definitely check it out. Thanks! – user15653864 May 23 '21 at 12:43

Inserting dictionary rows to database

1 Answers1