为什么这个不工作 (sqlite, python)

Question

我在解释器里试过这个，能成功运行，但在我的函数里却不行。

我想做的事情是：

cursor = dbconnect.cursor()
cursor.execute("""SELECT * FROM credits WHERE phone = ?""",(phone,))
data = cursor.fetchone()
firstname = data[1] #the db is set as firstname in position 1 after the id(primekey)

其实我用这个方法提取所有数据，只是用了不同的变量。

当我在函数里这样做时出现的错误是：

firstname = data[1]  
TypeError: 'NoneType' object is not subscriptable

顺便说一下：我在数据对象后面加了一个打印语句，想看看它返回了什么。在解释器里，它返回了我想要的元组，但在函数里却返回了'None'。

完整代码：

def FindByPhone(self,phone): 
    '''Find Credit by phone number ONLY'''    
    dbconnect = sqlite3.connect(self.dbname)  
    cursor = dbconnect.cursor()  
    cursor.execute("""SELECT * FROM credits WHERE phone = ?""",(phone,))  
    data = cursor.fetchone()  
    first = data[1]  
    last = data[2]  
    phone = data[3]  
    credit = data[4]  
    cid = data[0]
    self.SetVariables(first,last,phone,credit,cid)
    cursor.close()
    dbconnect.close()
    return

Answer 1

我觉得问题在于你的函数没有检查数据库中是否有匹配的行。如果没有返回任何行，你就会遇到这个错误：

#!/usr/bin/python
try:
    import sqlite3
except:
    from pysqlite2 import dbapi2 as sqlite3

#prepare testcase    
db="/tmp/soverflow.sqlite"
dbconnect = sqlite3.connect(db)
c = dbconnect.cursor()
c.execute("""create table credits
(id int not null primary key, firstname varchar(50), phone varchar(30),amount int not null)""")
c.execute("""INSERT INTO credits (id,firstname,phone,amount) VALUES (1,'guybrush','123-456',24)""")
c.execute("""INSERT INTO credits (id,firstname, phone,amount) VALUES (2,'elaine','1337-1337',18)""")
dbconnect.commit()
c.close()


def print_firstname(phone):
    cursor = dbconnect.cursor()
    cursor.execute("""SELECT * FROM credits WHERE phone = ?""",(phone,))
    data = cursor.fetchone()
    firstname = data[1]
    cursor.close() # cleanup
    print firstname

print "testing existing row"
print_firstname('1337-1337')

print "testing missing row"
print_firstname('nothere')

=>

./soverflow_sqlite.py 
testing existing row
elaine
testing missing row
Traceback (most recent call last):
  File "./soverflow_sqlite.py", line 31, in <module>
    print_firstname('not-in-db')
  File "./soverflow_sqlite.py", line 23, in print_firstname
    firstname = data[1]
TypeError: 'NoneType' object is not subscriptable

解决办法：在你的查询中加一个检查，看看是否有行被返回。