13

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

Brendan Long
  • 53,280
  • 21
  • 146
  • 188
mauguerra
  • 3,728
  • 5
  • 31
  • 37
  • 3
    In general, if you have an error message, you should include it in the question. – Brendan Long Nov 29 '11 at 19:08
  • why not use redis?if use ast and redis-py module,u can write a list to redis.eg.r.set(a,[1,23,3]).when u need this object ,u can use b = ast.literal_eval(r.get(a)) to get the list – sixsixsix Aug 18 '17 at 02:28

3 Answers3

24

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).

Raymond Hettinger
  • 216,523
  • 63
  • 388
  • 485
  • where do we specify the column name where I want to insert the values? Can you take a look here? https://stackoverflow.com/questions/72641938/add-list-values-to-a-mysql-column – Jnl Jun 16 '22 at 09:21
6

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.

Larry Lustig
  • 49,320
  • 14
  • 110
  • 160
  • HI Larry.... I want to insert two diferent strings in the same column....so when I make the Query to obtain what is inside that colum....I know that there are two different strings.....that's why I am trying to insert the value as a list – mauguerra Nov 29 '11 at 19:17
  • 1
    You cannot do that. A column can hold only one string. You can have a comma in that string to make it look like two different strings, but obviously that doesn't work if one of the strings *already* has a comma. But you should look up the idea of database normalization and redesign your database to use a separate, related table to hold these values. Never, ever, ever put more than one value into a database column. – Larry Lustig Nov 29 '11 at 20:21
  • Can you take a look here? https://stackoverflow.com/questions/72641938/add-list-values-to-a-mysql-column – Jnl Jun 16 '22 at 09:23
1

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))
tsleyson
  • 319
  • 5
  • 18
sixsixsix
  • 1,768
  • 21
  • 19
  • 3
    This example is very confusing. You can't use a Python function like `json.dumps` from inside your SQL string. You would have to call `json.dumps` first, then use one of the parameter substitution methods suggested in other answers to pass the resulting JSON string as a parameter. It's also not usually a good idea to store JSON strings in a database column. – tsleyson Oct 18 '17 at 23:37