How to copy a variable number of elements in a list to a uniform list and store in MySQL using Python the most efficient way?

Question

This is a sample list (each line has variable elements) :

['1', 'Tech', 'Code']
['2', 'Edu']
['3', 'Money', 'Sum', '176']

I have to insert this into a MySQL table which has 4 columns (max num. of elements in a value in a list).

How to do this efficiently? I have a feeling my solution is the least efficient!

Here is my solution :

for eachval in mylistings:       #mylistings has the sample list elements described above
     mylen = len(eachval)
     defaultlist = [None]*4   #reset defaultlist to 'None' to handle variable no. of columns
     ctr = 0
     for myoneval in mylistingline:
          defaultlist[ctr] = myoneval
          ctr += 1

for finalval in defaultlist:     #finally inserting them into a MySQL table
            cursor.execute("INSERT INTO LoadData VALUES (%s, %s, %s, %s)", (finalval[0], finalval[1], finalval[2], finalval[3]))

Answer 1

How about this?

for line in mylistings:
    out = line + [None] * (4 - len(line)) # pad the list with None to 4 elements
    cursor.execute("INSERT INTO LoadData VALUES (%s, %s, %s, %s)", out)

Answer 2

You should use executemany to speed things up on the database side. Also, you can cache the padding list so you don't have to create it every time:

values = []
padding = [None] * 4
for line in mylistings:
    values.append((line + padding)[:4])
cursor.executemany("INSERT INTO LoadData VALUES (%s, %s, %s, %s)", values)

You can also use itertools so that you don't construct the padded list at all:

from itertools import chain, islice

values = []
padding = [None] * 4
for line in mylistings:
    values.append(tuple(islice(chain(line, padding), 4)))
cursor.executemany("INSERT INTO LoadData VALUES (%s, %s, %s, %s)", values)

From my testing, using tuple(islice(chain(line, padding), 4)) is about 5% faster 50% slower than (line + padding)[:4].

EDIT: I forgot that for string substitution you need tuples or lists, and adding the tuple wrapper slowed the second solution down significantly. But the first one still works!