CODEWITHSUNDEEP
pythonmysqlregex
adarshram
adarshram

Reputation: 181

regex parse store in mysql

I am coding to find names and hobby from text file and store it in Details(mysql Table).The details table consists of 'Names' and 'Hobbies'.I cant able to store to my database.

import MySQLdb
import re
db = MySQLdb.connect(host="localhost", # your host, usually localhost
                     user="root", # your username
                      passwd="mysql", # your password
                      db="sakila") # name of the data base
cursor = db.cursor()
with open('qwer2.txt','r') as file:
    for line in file:


        patterns = [
         a,b= re.compile('My name is (\w+) and my hobby is (\w+)\.', re.IGNORECASE),
         a,b= re.compile('Me (\w+) and my interest is (\w+)\.', re.IGNORECASE),
        ]

        match_result = patterns[0].match(line) or patterns[1].match(line)
        name, hobby = match_result.groups()             
        cursor.execute('''INSERT into Details (Names, Hobby)
                          values (? , ?)'''%(a,b)

My text file is a paragraph:

My Name is Casssandra and my Hobby is Cooking.
My name is Archana and my hobby is Playing.Me Adarsh and my interest is Programming.
Me Leela and my interest is Baking.My name is John and my interest is Gaming.

Output:

Names      |  Hobby

Cassandra   Cooking  
Archana     Playing
Adarsh      Programming
Leela       Baking
John        Gaming

Please help me rectify my program to store into the table.

Upvotes: 0

Views: 746

Answers (3)

Braj
Braj

Reputation: 46871

Get the matched group from index 2 and 4.

/(?:(My Name is|Me) )(.*?) and (?:my (Hobby|interest) is )([^\\.]*)./i

Online demo

sample code:

import re
p = re.compile(ur'(?:(My Name is|Me) )(.*?) and (?:my (Hobby|interest) is )([^\\.]*).', re.IGNORECASE)
test_str = u"..."

re.findall(p, test_str)

Upvotes: 2

Martijn Pieters
Martijn Pieters

Reputation: 1124208

You are mixing SQL parameters with string formatting, and that doesn't work. Pass in the parameters as a separate argument:

cursor.execute('''INSERT into Details (Names, Hobby)
                  values (%s, %s)''', (name, hobby))
db.commit()

You need to use %s for the placeholders when using the MySQLdb database adapter, and you need to commit the transaction as well.

Your patterns setup is not valid Python; if you wanted to match multiple patterns, then make that a proper list:

patterns = (
    re.compile('My name is (\w+) and my hobby is (\w+)\.', re.IGNORECASE),
    re.compile('Me (\w+) and my interest is (\w+)\.', re.IGNORECASE),
)

and just loop over these patterns until one matches:

for pattern in patterns:
     match_result = pattern.match(line)
     if match_result:
         name, hobby = match_result.groups()

Demo:

>>> import re
>>> patterns = (
...     re.compile('My name is (\w+) and my hobby is (\w+)\.', re.IGNORECASE),
...     re.compile('Me (\w+) and my interest is (\w+)\.', re.IGNORECASE),
... )
>>> lines = '''\
... My Name is Casssandra and my Hobby is Cooking.
... My name is Archana and my hobby is Playing.Me Adarsh and my interest is Programming.
... Me Leela and my interest is Baking.My name is John and my interest is Gaming.
... '''.splitlines()
>>> for line in lines:
...     for pattern in patterns:
...         match_result = pattern.match(line)
...         if match_result:
...             name, hobby = match_result.groups()
...             print(name, hobby)
... 
('Casssandra', 'Cooking')
('Archana', 'Playing')
('Leela', 'Baking')

All put together that becomes:

import MySQLdb
import re

patterns = (
    re.compile('My name is (\w+) and my hobby is (\w+)\.', re.IGNORECASE),
    re.compile('Me (\w+) and my interest is (\w+)\.', re.IGNORECASE),
)
db = MySQLdb.connect(host="localhost", # your host, usually localhost
                     user="root", # your username
                      passwd="mysql", # your password
                      db="sakila") # name of the data base

with open('qwer2.txt','r') as file, db as cursor:
    for line in file:
        for pattern in patterns:
             match_result = pattern.match(line)
             if match_result:
                 name, hobby = match_result.groups()
                 cursor.execute(
                     '''INSERT INTO Details (Names, Hobby)
                        VALUES (%s, %s)''',
                     (name, hobby))
                 break

This also uses the database connection as a context manager (which gives you a cursor), this auto-commits the changes when the with block is done without errors.

Upvotes: 2

Ch0c0late
Ch0c0late

Reputation: 19

You've defined the cursor correctly and the only thing you need to do is to execute SQL statements which you can do it by calling execute on cursor object. Also, I recommend you to checkout the MySQLDB documentation.

Upvotes: 0

Related Questions