I have a database holding names, and I have to create a new list which will hold such values as ID, name, and gender and insert it in the current database. I have to create a list of the names which are not in the database yet. So I simply checked only 3 names and trying to work with them.
I am not sure what sort of list I suppose to create and how I can loop through it to insert all the new values in the proper way.
That's what I have so far:
mylist = [["Betty Beth", "1", "Female"], ["John Cena", "2", "Male"]]
@get("/list_actors")
def list_actors():
with connection.cursor() as cursor:
sql = "INSERT INTO imdb VALUES (mylist)"
cursor.execute(sql)
connection.commit()
return "done"
I am very new to this material so I will appreciate any help. Thanks in advance!
MySQL INSERT multiple rows statement In this syntax: First, specify the name of table that you want to insert after the INSERT INTO keywords. Second, specify a comma-separated column list inside parentheses after the table name. Third, specify a comma-separated list of row data in the VALUES clause.
The answer to your original question is: No, you can't insert a list like that. Unfortunately, that style of variable insertion leaves your code vulnerable to SQL injection attacks.
PyMySQL fetchAll The fetchAll method retrieves all (remaining) rows of a query result, returning them as a sequence of sequences. In the example, we retrieve all cities from the database table. This SQL statement selects all data from the cities table.
vals = [["TEST1", 1], ["TEST2", 2]]
with connection.cursor() as cursor:
cursor.executemany("insert into test(prop, val) values (%s, %s)", vals )
connection.commit()
mysql> select * from test;
+----+-------+------+---------------------+
| id | prop | val | ts |
+----+-------+------+---------------------+
| 1 | TEST1 | 1 | 2017-05-19 09:46:16 |
| 2 | TEST2 | 2 | 2017-05-19 09:46:16 |
+----+-------+------+---------------------+
Adapted from https://groups.google.com/forum/#!searchin/pymysql-users/insert%7Csort:relevance/pymysql-users/4_D8bYusodc/EHFxjRh89XEJ
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With