Insert list into my database using Python [duplicate]

Question

I want to insert a list in my database but I can't.

Here is an example of what I need:

variable_1 = "HELLO"
variable_2 = "ADIOS"
list = [variable_1,variable_2]

INSERT INTO table VALUES ('%s') % list

Can something like this be done? Can I insert a list as a value? When I try it, an error says that is because of an error in MySQL syntax

Answer 1

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.