PyMySQL executemany INSERT List from variable

Question

I am trying to insert some data in a mysql table using pymysql but failing. The data is held in variables already so I need to pass them to the INSERT statement.

this is what I am currently trying...

con = pymysql.connect(host='*.*.*.*', port=***, user='****', 
passwd='***', db='****')
with con:
    cur = con.cursor()
    sql = ("INSERT INTO groupMembers (groupID, members) VALUES (%s, %s)")
    data = (groupID, (x for x in membersList))
    cur.executemany(sql, data)
    con.commit()
    con.close()

the data I am trying to pass looks like the following....

groupID = G9gh472

membersList = [Dave,Bob,Mike,Bill,Colin]

the length of the list is unknown and can vary the resulting table I want to look like this...

| groupID | members |
+---------+---------+
| G9gh472 | Dave    |
| G9gh472 | Bob     |
| G9gh472 | Mike    |
| G9gh472 | Bill    |
| G9gh472 | Colin   |

I have tried a few variations of this based on reading others answers, but nothing I have tried so far has worked. Thanks all

Answer 1

According to the pymysql docs the executemany function expects a Sequence of sequences or mappings for the data.

You can do

data = list([(groupID, x) for x in membersList]) # Create a list of tuples

which should solve the issue. Here is the updated code snippet-

con = pymysql.connect(host='*.*.*.*', port=***, user='****', 
passwd='***', db='****')
with con:
    cur = con.cursor()
    sql = ("INSERT INTO groupMembers (groupID, members) VALUES (%s, %s)")
    data = list([(groupID, x) for x in membersList]) # Create a list of tuples
    cur.executemany(sql, data)
    con.commit()
    con.close()