Adding a list into a single MySQL table field

Question

I have a MySQL table that i have created in Python

+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| consensus    | char(40)     | NO   |     | NULL    |       |
| identityb32  | char(40)     | NO   |     | NULL    |       |
| pubdate      | char(40)     | NO   |     | NULL    |       |
| dirport      | char(6)      | NO   |     | NULL    |       |
| ip           | char(40)     | NO   |     | NULL    |       |
| orport       | char(40)     | NO   |     | NULL    |       |
| identityhash | char(40)     | NO   |     | NULL    |       |
| nick         | char(40)     | NO   |     | NULL    |       |
| version      | char(40)     | NO   |     | NULL    |       |
| flags        | varchar(500) | NO   |     | NULL    |       |
| identity     | char(40)     | NO   |     | NULL    |       |
| digest       | char(40)     | NO   |     | NULL    |       |
| pubtime      | char(40)     | NO   |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
13 rows in set (0.00 sec)

Everything is working well accept for one small issue.

For my flags field the values i need to add are in the format:

['Exit', 'Fast', 'Guard', 'HSDir', 'Named', 'Running', 'Stable', 'V2Dir', 'Valid']

and I can not add these values into the table. What do i need to do in order to add all the flag values for a field to the flag field?

PYTHON code :

format = """','"""

c = 0
# Prepare SQL query to INSERT a record into the database.
sql = """INSERT INTO """ + onion_Add +"""(consensus, identityb32, pubdate, dirport, ip, orport, identityhash, nick, version, flags, identity, digest, pubtime)
         VALUES ('""" +consensus.consensus_name + format + identityb32[c] + format + pubdate[c] + format + dirport[c] + format + ip[c] + format + orport[c] + format + identityhash[c] + format + nick[c] + format + version[c] + format + flags + format + identity[c] + format + digest[c] + format + pubtime[c] + """')"""
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()
   print "adding to db error"
# disconnect from server
db.close()

Why not add the list as a string with `str_flags = ','.join(flags)`? — xnx, Mar 13 '15 at 13:13
Show us the Python code you use to `INSERT` such a list as the value of `flag`. — , Mar 13 '15 at 13:14
Either remove the ' so that there is only one before Exit and after Valid, or paramaterize the query [example](http://stackoverflow.com/questions/1633332/how-to-put-parameterized-sql-query-into-variable-and-then-execute-in-python) so you can pass in the entire value with the quotes. The issue is the multiple '' values can't be used when inserting into one line. SQL is seeing those as different values to insert which do not align to the columns you're trying to insert. — xQbert, Mar 13 '15 at 13:16

score 0 · Accepted Answer · answered Mar 13 '15 at 13:26

You'll probably find it easier and safer to let MySQLdb to build your statement for you:

sql_fmt = 'INSERT INTO %s (consensus, identityb32, pubdate, dirport, ip, orport, identityhash, nick, version, flags, identity, digest, pubtime) VALUES (' + ', '.join(['%s']*14) + ')'

...

cursor.execute(sql_fmt, (onion_Add, consensus.consensus_name,
                         identityb32[c], pubdate[c], dirport[c],
                         ip[c], orport[c], identityhash[c], nick[c],
                         version[c], ','.join(flags), identity[c],
                         digest[c], pubtime[c])
              )

Adding a list into a single MySQL table field

1 Answers1