Paste the list into my database using Python

Question

I want to insert a list into my database, but I cannot.

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 

Is it possible to do something like this? Can I insert a list as a value? When I try, the error says it is due to an error in the MySQL syntax

Answer 1

The answer to your original question: No, you cannot insert such a list.

However, with some tweaking, you can make this code 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, this style of variable insertion leaves your code vulnerable to SQL injection attacks .

Instead, we recommend using the Python DB API and creating a custom query string with several question marks for the inserted data:

 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 documentation shows how to pass arguments using question marks, and explains why they are necessary (in fact, it ensures that your variables are correctly quoted).

Answer 2

Your question is not clear.

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

Insert a comma delimited list into one column:

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

Paste in separate columns:

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

both assume that you have made a name conn connection.

A few other suggestions:

• Try to avoid INSERT statements that do not display the names and order of the columns you are inserting into. Such a statement leads to very fragile code; it breaks if you add, delete or move columns in your table.

• If you insert a comma-separated list into one field, which usually violates the principles of database design, and you should use a separate table with one value for each record.

• If you insert into separate fields and they have names like Word1 and Word2 , this also means that you should use a separate table instead.

• Never use direct row replacements to create SQL statements. It will break if one of the values, for example o'clock . It also opens up attacks against people using SQL injection methods.

Answer 3

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

For instance:

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