在SQLi中输入Python列表

Question

我试图添加在它解析每一行之后生成的列表。当我检查每一个代码时，我会得到不同的错误

(C:\Users\myname\Desktop\pythonCourse>dblesson2
Enter file name: mbox.txt
['uct.ac.za']
Traceback (most recent call last):
  File "C:\Users\myname\Desktop\pythonCourse\dblesson2.py", line 25, in      
    <module>
    #VALUES ( ?, 1 )''', ( email, ) )
sqlite3.OperationalError: near "#VALUES": syntax error) 

我知道这是因为我没有将正确的数据传递给数据库，但我自己无法解决这个问题。在

^{pr2}$

Answer 1

你的程序中有很多小错误。让我试着列出它们：

• re.findall返回一个列表，但您似乎将其视为单个字符串。尝试email = email[0]只考虑列表的第一个元素。在
• 您的第一个SELECT语句有(email)。将一个项目放在括号内不会使其成为元组。请尝试(email,)或{}。在
• 在for循环之后的if将在for循环的每次迭代中出现，因此它必须缩进一个句点。在
• if的正文不能为空。取消该操作的注释，或将其更改为pass。在
• 最后一个for循环的主体需要缩进一个句点。在
• 出于对堆栈溢出阅读器的礼貌，请复制粘贴整个独立程序，而不仅仅是代码片段。在

以下是我解决问题后的程序：

import sqlite3
import re

conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute('''
DROP TABLE IF EXISTS Counts''')

cur.execute('''
CREATE TABLE Counts (email TEXT, count INTEGER)''')

fname = raw_input('Enter file name: ')
if ( len(fname) < 1 ) : fname = 'mbox-short.txt'
fh = open(fname)
for line in fh:
    if not line.startswith('From: ') : continue
    line = line.rstrip()
    email = re.findall('@(\S+[a-zA-Z]+)', line)
    email = email[0]
    cur.execute('SELECT count FROM Counts WHERE email = ? ', (email,))
    row = cur.fetchone()
    if row is None:
        cur.execute('''INSERT INTO Counts (email, count) 
            VALUES ( ?, 1 )''', ( email, ) )
    else : 
        cur.execute('UPDATE Counts SET count=count+1 WHERE email = ?', 
            (email, ))
# This statement commits outstanding changes to disk each 
# time through the loop - the program can be made faster 
# by moving the commit so it runs only after the loop completes
conn.commit()

# https://www.sqlite.org/lang_select.html
sqlstr = 'SELECT email, count FROM Counts ORDER BY count DESC LIMIT 10'

print
print "Counts:"
for row in cur.execute(sqlstr) :
    print str(row[0]), row[1]

cur.close()

Answer 2

import sqlite3
conn=sqlite3.connect('emaildb.sqlite')
cur=conn.cursor()

cur.execute('''DROP TABLE IF EXISTS counts''')
cur.execute('''CREATE TABLE counts (org TEXT, count INTEGER)''')

f_name=raw_input('Enter file name: ')
if len(f_name)<1   :   f_name='mbox.txt'
fn=open(f_name)
for line in fn:
   if not line.startswith('From: ') : continue 
   words = line.split()
   email=words[1]
   domain=email.split('@')
   organiz=domain[1]
   print organiz
   cur.execute('SELECT count FROM Counts WHERE org=?',(organiz, ))
   row=cur.fetchone()
   if row==None:
     cur.execute('''INSERT INTO counts (org, count) VALUES (?,1)''',
     (organiz, ))
   else:
     cur.execute('''UPDATE counts SET count=count+1 WHERE org=?''',(organiz, 
     ))
conn.commit()