Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Inserting a list holding multiple values in MySQL using pymysql

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!

like image 498
Alice Jarmusch Avatar asked Feb 04 '17 11:02

Alice Jarmusch


People also ask

How do I insert multiple records in MySQL?

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.

Can I insert list in MySQL?

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.

What does Pymysql fetchAll return?

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.


1 Answers

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

like image 136
havlock Avatar answered Oct 23 '22 03:10

havlock