Python NLP: How to store a python list to MySQL database?

Question

I am learning natural language processing. I need to store a list e.g.["We","aren't","friends","."] to MySQL table's column, so that when i want to analyse it, i could read it from the database. Part of my code is :

tokenized_text = nltk.word_tokenize(line)
sql2 = 'update training_data set tokenized_essay = "{0}" where filename = "{1}"'.format(tokenized_text,res)
cursor.execute(sql2)

_mysql_exceptions.ProgrammingError: (1064, 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version.

I have set the column tokenized_text as longtext. I have tried json,pickle library,but it seems that the problem is how to escape the single quote.e.g."aren't" Please help me!

Answer 1

Yop can use MySQL-python 1.2.5 Python interface to MySQL

MySQLdb is an interface to the popular MySQL database server for Python. Here is sample example

import MySQLdb
db=MySQLdb.connect(passwd="moonpie",db="thangs")
c=db.cursor()
c.executemany(
 """INSERT INTO breakfast (name, spam, eggs, sausage, price)
 VALUES (%s, %s, %s, %s, %s)""",
 [
 ("Spam and Sausage Lover's Plate", 5, 1, 8, 7.95 ),
 ("Not So Much Spam Plate", 3, 2, 0, 3.95 ),
 ("Don't Wany ANY SPAM! Plate", 0, 4, 3, 5.95 )
 ] )

More details : http://mysql-python.sourceforge.net/MySQLdb.html

Update:

Oh okay, for storing list items, this example might be helpful

def encoding(val):
 if isinstance(val, unicode):
 return val.encode('utf-8')
 else:
 return str(val)
for id, val in mydict.items():
 data = dict(reduce(lambda x, y: x+y, [v.items() for v in val]) + [('id', id)])
 sorted_keys = sorted(map(str, data.keys()))
 sorted_vals = map(encoding, [v[k] for k in sorted_keys]) # sorted by keys
 format = ', '.join(["'%s'"] * len(sorted_vals))
 c.execute("insert into deldictmysql
 (%s) values (%s)" % (', '.join(sorted_keys), format), sorted_vals)

• allenwang: Hey, thanks for your reply. I am using MysQLdb.However,please note my question. How can I store a python list to MySQL, not a string. the list is filled with tokenized words.
• user123: @wanglan8498: please check update, if it helps close the thread with apprppriat action
• allenwang: Thank you for your earliest reply. But it seems that you don't understand what i am trying to solve. I want to store a word tokenized list to a column, such as ["We","aren't","friends","."].When I put this list into an update SQL sentence, a single quote error happened. I don't know how to solve it .

Answer 2

I think you may use join and split to wrap or parse for example

foo = ["hola", "mundo"]
bar = ' '.join(foo)
print bar
: "hola mundo"
foo = bar.split(' ')
print foo
: ["hola", "mundo"]