2

Using Python 2.7.6, if I want to add a single record to an SQLite database using values (e.g. integers) stored in variables, I can do the following:

import sqlite3
w,x,y,z = 0,1,2,3
conn = sqlite3.connect('mydb.db')
conn.execute('CREATE TABLE IF NOT EXISTS data (v1 INTEGER, v2 INTEGER, v3 INTEGER, v4 INTEGER)')
conn.execute('INSERT INTO data VALUES (?,?,?,?)', (w,x,y,z))
conn.commit()
conn.close()

What if the number of values to be inserted in the single record is large (e.g. 100, 200, arbitrary?), and are stored in a sequence or collection like a list, queue, array, etc? Is there a way to modify the INSERT syntax above so that I can specify only the name of the collection rather than a very long itemized list? And is any particular collection type more convenient to use in this scenario?

Edit: The above code example has four values going into four columns in the table. For the question about the larger data, assume that there are as many columns in the database table as there are values to insert.

Edit2: I want to insert a single record consisting of the values in a large collection. I do not want to insert many records (e.g. executemany).

Mr. P
  • 23
  • 4
  • How would that code know about the colum names? – CL. Apr 23 '14 at 14:13
  • Hmm, would it need to know that? E.g. in the code example above, the column names are not needed for the INSERT statement since the number and order of columns matches the number and order of the data items. – Mr. P Apr 23 '14 at 14:23
  • 1
    Are you trying to insert something in a 100-column table? You should never have a table with that many columns; this seems like a design smell. – Colonel Thirty Two Apr 23 '14 at 14:36
  • See http://stackoverflow.com/questions/3184478/how-many-columns-is-too-many-columns – Mr. P Apr 23 '14 at 14:54
  • Yea, and? The OP even says "I've always heard that this is a horrible practice." I understand that there are instances where you may need a 100-column table, but such instances are very rare and need major justification. – Colonel Thirty Two Apr 23 '14 at 15:03

1 Answers1

2

In your code, (w,x,y,z) is a four-element tuple; that's your variable.

You could just as well use a list instead:

my_little_list = [w, x, y, z]
cursor.execute("...", my_little_list)
CL.
  • 173,858
  • 17
  • 217
  • 259
  • 2
    Indeed. In fact, the solution I settled on was to programmatically build an INSERT string containing the right number of '?'s, pull the data items into a tuple, then pass the string and tuple as arguments to the conn.execute() statement. – Mr. P Apr 23 '14 at 17:26