views:

39

answers:

2

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]))
+2  A: 

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)
eumiro
Worked great! Thanks!
ThinkCode
+2  A: 

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!

DzinX
Thanks for pointing me to executemany, saves a loop! I get this though : TypeError: not enough arguments for format string. Should iSlice be slicing 3 values given that it starts from 0?
ThinkCode
You're right: executemany uses string substitution, and it requires its argument to be a tuple. Inside MySQLdb's executemany, lists are converted to tuples, but iterators aren't. I modified the answer and tested thoroughly. Sorry for my mistake.
DzinX