Using Python quick insert many columns into Sqlite\Mysql

Question

If Newdata is list of x columns, How would get the number unique columns--number of members of first tuple. (Len is not important.) Change the number of "?" to match columns and insert using the statement below.

csr = con.cursor()
csr.execute('Truncate table test.data')
csr.executemany('INSERT INTO test.data VALUES (?,?,?,?)', Newdata)
con.commit()

Answer 1

By "Newdata is list of x columns", I imagine you mean x tuples, since then you continue to speak of "the first tuple". If Newdata is a list of tuples, y = len(Newdata[0]) is the number of items in the first one of those tuples.

Assuming that's the number you want (and all tuples had better have the same number of items, otherwise executemany will fail!), the general idea in @Nathan's answer is right: build the string with the appropriate number of comma-separated question marks:

holders = ','.join('?' * y)

then insert it in the rest of the SQL statement. @Nathan's way to insert is right for most Python 2.any versions, but if you have 2.6 or better,

sql = 'INSERT INTO testdata VALUES({0})'.format(holders)

is currently preferred (it also works in Python 3.any).

Finally,

csr.executemany(sql, Newdata)

will do what you desire. Remember to commit the transaction once you're done!-)

Answer 2

If you're looking for the maximum number of items in all elements in Newdata, it's simply:

num_columns = max(len(t) for t in Newdata)

This, of course, assumes python 2.5 or greater.

Not that I'm sure what you're attempting would work, but the insert statement would then become:

sql = "INSERT INTO test.data VALUES (%s)" % ",".join('?' * num_columns)