python regex,dictionary not inserting into mysql

Question

I obtaines a dictionary 'p' from the following code,but cannot able to insert into the mysql database.please help me to insert the datas into database.

dictionary is :[('Casssandraw', 'Cooking'), ('Archanea', 'Playing'), ('Adarshan', 'Programming'), ('Leelal', 'Baking')] should be stored to Names and Hobby fields.

Name       Hobby
Cassandraw Cooking
Archanea   Playing
...        ...

Program:

import MySQLdb
import re
db = MySQLdb.connect(host="localhost", # your host, usually localhost
                     user="root", # your username
                      passwd="mysql", # your password
                      db="sakila") # n

with open('qwer2.txt','r') as file, db as cursor:
    f = open('qwer2.txt', 'r')

    lines = f.readlines()

    for x in lines:
       p=re.findall(r'(?:name is|me)\s+(\w+).*?(?:interest|hobby)\s+is\s+(\w+)',x, re.I)
       print p

       cursor.execute(
       '''INSERT INTO Details (Names, Hobby)
          VALUES (%s, %s)''',
         (name, hobby))#<-donot know what to provide
db.commit()

possible duplicate of python regex re.findall() insert data to db — l'L'l
– l'L'l, Commented Jul 13, 2014 at 17:36
Then you might want to try the other question you asked already: stackoverflow.com/questions/24722586/regex-parse-store-in-mysql — l'L'l
– l'L'l, Commented Jul 13, 2014 at 17:38
this question infact doesnt have over the entire stackoverflow for sure. — adarshram
– adarshram, Commented Jul 13, 2014 at 17:39
I'm not convinced, how about this example: stackoverflow.com/questions/24405875/… — l'L'l
– l'L'l, Commented Jul 13, 2014 at 17:44

ZZY · Accepted Answer · 2014-07-14 06:32:53Z

0

What's the value of 'p'? I assume 'p' is something like:

print(p)
>>> [('Casssandraw', 'Cooking'), ('Archanea', 'Playing'), ('Adarshan', 'Programming'), ('Leelal', 'Baking')]

Then:

value_parm = ','.join(['(%s,%s)'] * len(p))
sql = 'INSERT INTO Details (Names, Hobby) VALUES %s' % value_parm
# print(sql)
# >>> INSERT INTO Details (Names, Hobby) VALUES (%s,%s),(%s,%s),(%s,%s),(%s,%s)
import itertools
flat_p = itertools.chain.from_iterable(p)
# flat_p is an iterable like ['Casssandraw', 'Cooking', 'Archanea', ...]
db.cursor().execute(sql, flat_p)
db.commit()

answered Jul 14, 2014 at 6:32

ZZY

3,9871 gold badge22 silver badges22 bronze badges

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

Collectives™ on Stack Overflow

python regex,dictionary not inserting into mysql

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related