时间戳

首页 > 解决方案 > INSERT INTO 语句 - Python 正则表达式和 Sqlite

python - INSERT INTO 语句 - Python 正则表达式和 Sqlite

问题描述

我正在使用 python regex 构建一个程序来解析来自文本文件的电子邮件，然后将每封电子邮件添加到我数据库中的“电子邮件”列中。

import sqlite3 #imports the module

conn= sqlite3.connect('testdatabase.db') #creates a connection to the specified file. if the file does exist, it creates the file

c=conn.cursor() #allows me to create commands in this new file

#the following code creates a table with four columns
c.execute("""CREATE TABLE employees (
    'first' text,
    'last' text,
    'pay' integer,
    'email' text
    )""")

c.execute("INSERT INTO employees VALUES('Corey', 'Schafer','50000','cshafer@gmail.com')") #sample data
conn.commit() #this commits the current transaction

import re

#gets emails from a text
pattern = re.compile(r'[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+')
'''#this is the regular expression that locates the emails in my fife'''
with open('sample_data.txt') as file_handler: #this is the file I'm opening. it contains the emails I want to extract
    for line in file_handler:
        matches = pattern.findall(line)
        for match in matches:
            c.execute("""INSERT INTO employees (email) VALUES ('match')""") #this is the issue
            conn.commit()  # this commits current transaction

conn.close()  # this closes the connection to database

对于我的文本文件中的每封电子邮件，“匹配”一词出现在我数据库的电子邮件列中。我的代码不区分匹配的实际电子邮件值和字符串“匹配”：
https ://i.stack.imgur.com/ROMzV.png

如何更改我的代码，以便将来自正则表达式的电子邮件填充到数据库中？

标签： pythondatabasesqlitesql-insert

解决方案

您将文字字符串'match'而不是变量的值传递给查询。你想要一个参数化的插入：

for match in matches:
    c.execute("""INSERT INTO employees (email) VALUES (?)""", (match,))
    conn.commit()

推荐阅读