python - mysql 中的正则表达式解析存储

Question

我正在编码以从文本文件中查找姓名和爱好并将其存储在详细信息(mysql 表)中。详细信息表由“姓名”和“爱好”组成。我无法存储到我的数据库中。

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 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.

输出:

Names      |  Hobby

Cassandra   Cooking  
Archana     Playing
Adarsh      Programming
Leela       Baking
John        Gaming

请帮我改正程序存入表中。

Answer 1

您将 SQL 参数与字符串格式混合在一起，这是行不通的。将参数作为单独的参数传递:

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

使用MySQLdb数据库适配器时需要使用%s作为占位符，同时需要提交事务。

您的 patterns 设置不是有效的 Python；如果您想匹配多个模式，请将其设为适当的列表:

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),
)

然后循环这些模式直到匹配一个:

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

演示:

>>> 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')

全部放在一起变成:

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

这也使用数据库连接作为上下文管理器(它给你一个游标)，当 with block 没有错误地完成时，这会自动提交更改。