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

The data variable you are passing to the executemany function is a tuple but function expects a sequence/mapping. cursor.executemany(operation, seq_of_params) is the function signature. This is why your code is not working.

One way to produce a sequence is as follows.

product(x,y) returns ((x,y) for x in A for y in B)

product([groupId], members) returns a tuple of tuples (a sequence).

You can refer to the code below -

import itertools

    with con.cursor() as cur: # a good practice to follow
        sql = ("INSERT INTO test (id, memb) VALUES (%s, %s)")
        cur.executemany(sql, itertools.product([groupId], members)) # the change needed
    con.commit()

Answer 2

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()