mysql 中的正则表达式解析存储 [英] regex parse store in mysql
问题描述
我正在编码以从文本文件中查找姓名和爱好并将其存储在详细信息(mysql 表)中.详细信息表由姓名"和爱好"组成.我无法存储到我的数据库中.
导入 MySQLdb进口重新db = MySQLdb.connect(host="localhost", # 你的主机，通常是 localhostuser="root", # 你的用户名passwd="mysql", # 你的密码db="sakila") # 数据库名称游标 = db.cursor()使用 open('qwer2.txt','r') 作为文件:对于文件中的行:模式 = [a,b= re.compile('我的名字是(\w+)，我的爱好是(\w+)\.', re.IGNORECASE),a,b= re.compile('Me (\w+) 而我的兴趣是 (\w+)\.', re.IGNORECASE),]match_result = patterns[0].match(line) 或 patterns[1].match(line)姓名，爱好 = match_result.groups()cursor.execute('''INSERT into Details (Names, Hobby)值 (? , ?)'''%(a,b)我的文本文件是一个段落:
我的名字是 Casssandra，我的爱好是烹饪.我的名字是 Archana，我的爱好是 Playing.Me Adarsh，我的兴趣是编程.我是 Leela，我的兴趣是烘焙.我的名字是 John，我的兴趣是游戏.输出:
名称 |爱好卡桑德拉烹饪阿卡纳演奏阿达什编程里拉烘焙约翰游戏请帮我改正我的程序以存储到表中.
您将 SQL 参数与字符串格式混合使用，但这是行不通的.将参数作为单独的参数传入:
cursor.execute('''INSERT into Details (Names, Hobby)值 (%s, %s)''', (name, hobby))数据库提交()在使用 MySQLdb 数据库适配器时，您需要使用 %s 作为占位符，并且您还需要提交事务.
您的 patterns 设置不是有效的 Python；如果你想匹配多个模式，那么把它作为一个合适的列表:
patterns = (re.compile('我的名字是(\w+)，我的爱好是(\w+)\.', re.IGNORECASE),re.compile('Me (\w+) 而我的兴趣是 (\w+)\.', re.IGNORECASE),)然后循环遍历这些模式直到匹配:
用于模式中的模式:match_result = pattern.match(line)如果匹配结果:姓名，爱好 = match_result.groups()演示:
<预><代码>>>>进口重新>>>模式 = (... re.compile('我的名字是 (\w+) 而我的爱好是 (\w+)\.', re.IGNORECASE),... re.compile('Me (\w+) 而我的兴趣是 (\w+)\.', re.IGNORECASE),……)>>>行 = '''\... 我的名字是 Casssandra，我的爱好是烹饪.... 我的名字是 Archana，我的爱好是 Playing.Me Adarsh，我的兴趣是编程.... Me Leela，我的兴趣是烘焙.我的名字是 John，我的兴趣是游戏.... '''.splitlines()>>>对于线中线:...对于模式中的模式:... match_result = pattern.match(line)...如果匹配结果:... 姓名，爱好 = match_result.groups()...打印(姓名，爱好)...('卡桑德拉'，'烹饪')('Archana', '播放')('Leela', '烘焙')放在一起变成:
导入 MySQLdb进口重新模式 = (re.compile('我的名字是(\w+)，我的爱好是(\w+)\.', re.IGNORECASE),re.compile('Me (\w+) 而我的兴趣是 (\w+)\.', re.IGNORECASE),)db = MySQLdb.connect(host="localhost", # 你的主机，通常是 localhostuser="root", # 你的用户名passwd="mysql", # 你的密码db="sakila") # 数据库名称以 open('qwer2.txt','r') 作为文件，db 作为光标:对于文件中的行:对于模式中的模式:match_result = pattern.match(line)如果匹配结果:姓名，爱好 = match_result.groups()游标.执行('''INSERT INTO 详细信息(姓名、爱好)值 (%s, %s)''',(姓名，爱好))休息这也使用数据库连接作为上下文管理器(它给你一个游标)，当 with 块没有错误地完成时，它会自动提交更改.
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.
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.
这篇关于mysql 中的正则表达式解析存储的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
