How to write 3 lists to 6 columns with Python3 and SQlite3

Question

I need to write 3 lists containing value pairs into 6 columns with sqlite3.

my_list = ['a','1','b','2','c','3','d','4']
my_list2 = ['e','5','f','6','g','7','h','8']
my_list3 = ['i','9','j','10','k','11','l','12']

like so:

| a | 1 | e | 5 | i | 9 |
| b | 2 | f | 6 | j | 10|
| c | 3 | g | 7 | k | 11|
| d | 4 | h | 8 | l | 12|

I need the each pair to be inserted into the .db next to each other. I can do this utilizing a pairwise function and execute many for a single list.

The pairwise function:

def pairwise(iterable):
    iterable = iter(iterable)
    return zip(iterable, iterable)

The execute many code that works for one list:

cursor.executemany('INSERT INTO mytable(column1, column2) VALUES (?,?)', pairwise(my_list))
connection.commit()

Whenever I try to pass the other lists at the same time:

cursor.executemany('INSERT INTO mytable(column1, column2, column3, column4, column4, column6) VALUES (?,?,?,?,?,?)',pairwise(my_list),pairwise(my_list2),pairwise(my_list3))
conn.commit()

I get an error that says:

TypeError: function takes exactly 2 arguments (4 given)

Answer 1

executemany() can take an iterator of sequences (such as tuples) for the arguments, but when you write

pairwise(my_list),pairwise(my_list2),pairwise(my_list3)

that gives you three iterators of tuples, not one combined iterator of tuples. It doesn't combine the columns.

Here's one way to combine the columns:

def concat_columns(*row_lists):
    return (tuple(chain(*r)) for r in zip(*row_lists)

This uses zip() to create an iterator of tuples of tuples, and itertools.chain() to flatten each row. Your final code could then look something like this:

cursor.executemany(
    'INSERT INTO mytable(column1, column2, column3, column4, column4, column6) VALUES (?,?,?,?,?,?)',
    concat_columns(pairwise(my_list),pairwise(my_list2),pairwise(my_list3)))