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!

user123 · Accepted Answer · 2014-09-23 07:41:09Z

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)

edited Sep 23, 2014 at 7:41

answered Sep 22, 2014 at 12:09

user123

5,42516 gold badges79 silver badges126 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

allenwang Over a year ago

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 Over a year ago

@wanglan8498: please check update, if it helps close the thread with apprppriat action

allenwang Over a year ago

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 .

koxmoz · Accepted Answer · 2015-02-28 23:41:57Z

1

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"]

answered Feb 28, 2015 at 23:41

koxmoz

213 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related