Insert list into my database using Python [duplicate]

The answer to your original question is: No, you can't insert a list like that.

However, with some tweaking, you could make that code work by using %r and passing in a tuple:

variable_1 = "HELLO"
variable_2 = "ADIOS"
varlist = [variable_1, variable_2]
print "INSERT INTO table VALUES %r;" % (tuple(varlist),)

Unfortunately, that style of variable insertion leaves your code vulnerable to SQL injection attacks.

Instead, we recommend using Python's DB API and building a customized query string with multiple question marks for the data to be inserted:

variable_1 = "HELLO"
variable_2 = "ADIOS"
varlist = [variable_1,variable_2]
var_string = ', '.join('?' * len(varlist))
query_string = 'INSERT INTO table VALUES (%s);' % var_string
cursor.execute(query_string, varlist)

The example at the beginning of the SQLite3 docs shows how to pass arguments using the question marks and it explains why they are necessary (essentially, it assures correct quoting of your variables).

Your question is not clear.

Do you want to insert the list as a comma-delimited text string into a single column in the database? Or do you want to insert each element into a separate column? Either is possible, but the technique is different.

Insert comma-delimited list into one column:

 conn.execute('INSERT INTO table (ColName) VALUES (?);', [','.join(list)])

Insert into separate columns:

  params = ['?' for item in list]
  sql    = 'INSERT INTO table (Col1, Col2. . .) VALUES (%s);' % ','.join(params)
  conn.execute(sql, list)

both assuming you have established a connection name conn.

A few other suggestions:

• Try to avoid INSERT statements that do not list the names and order of the columns you're inserting into. That kind of statement leads to very fragile code; it breaks if you add, delete, or move columns around in your table.

• If you're inserting a comma-separted list into a single-field, that generally violates principals of database design and you should use a separate table with one value per record.

• If you're inserting into separate fields and they have names like Word1 and Word2, that is likewise an indication that you should be using a separate table instead.

• Never use direct string substitution to create SQL statements. It will break if one of the values is, for example o'clock. It also opens you to attacks by people using SQL injection techniques.

You can use json.dumps to convert a list to json and write the json to db.

For example:

insert table example_table(column_name) values(json.dumps(your_list))