python - mysqldb .. 'NoneType' 对象不可下标

Question

当 cur.execute() 和 db.commit() 行被注释掉时，此代码可以正常工作；即，如果我所做的只是打印查询，则该程序将运行 n 行。问题似乎出现在这里:

player_categories_statistics = cur.fetchone()
player_id = player_categories_statistics[0]

当我尝试插入结果时，我得到:

Traceback (most recent call last):
  File "test2.py", line 72, in <module>
    meat = meatgrind(league_name, categories_measurement_statistics)
  File "test2.py", line 32, in meatgrind
    player_id = int(player_categories_statistics[0])
TypeError: 'NoneType' object is not subscriptable

代码:

import sys 
import MySQLdb
import string 

db = MySQLdb.connect()
cur = db.cursor('localhost','me',XXXXX,'testdb')


def meatgrind(league_name,categories_measurement_statistics):
# a varied range of different categories can be used 

    # 1. list categories
    categories = []
    categories_string = "player_id"
    categories_string_newtable = "meatgrinded_player_id, player_id"
    for category in categories_measurement_statistics:
        categories_string += ", " + category[0]
        categories_string_newtable += ", " + category[0]


    # 2. get players and statistics
    query = "SELECT %s FROM players" % (categories_string)
    cur.execute("%s" % (query))
    # rowcount = int(cur.rowcount)
    rowcount = 2 #hard-coded for debugging

    # 3. meatgrind one player at a time
    meatgrinded_player_id = 1
    for i in range(rowcount):
        player_categories_statistics = cur.fetchone()
        player_id = player_categories_statistics[0]

        #4. grind a category statistic
        meatgrindings_string = "%d, %d" % (meatgrinded_player_id, player_id)

        index = 1
        for category in categories_measurement_statistics:

            # SOME MATH HERE resulting in player_meatgrindings

            meatgrindings_string += ", %0.4f" % player_meatgrindings



query = """INSERT INTO sometable (%s) VALUES (%s)""" % (categories_string_newtable, meatgrindings_string)
cur.execute("%s" % (query))
db.commit()

meatgrinded_player_id += 1


league_name = 'test'
categories_measurement_statistics = (('H', 156.3, 19.643093216474604), ('HR', 21.3, 9.003147597738618), ('SB', 13.25, 16.041179646286754))

meat = meatgrind(league_name, categories_measurement_statistics)

Answer 1

您的错误原因是:

player_categories_statistics = cur.fetchone()

这会将 player_categories_statistics 设置为 None。 None[0] 引发异常。

发生这种情况的唯一原因是您的查询没有返回任何行，这意味着您的表是空的。您的表很可能是空的，因为您从未在其中放入任何行，或者不太可能以某种方式删除它们。

罪魁祸首可能如下，您正在插入sometable并从players中选择:

INSERT INTO sometable (%s) VALUES (%s)

对比

SELECT %s FROM players

这可能的唯一原因是因为您强制它循环，即使该行没有返回任何内容:

rowcount = 2 #hard-coded for debugging

<小时/>

其他信息:

这是我在 sqlite3 数据库上运行的一个工作查询，其中有一个表，其中一行的语句与您的语句几乎相同，只是为了表明如果数据确实存在，您的查询应该可以工作。

query = "SELECT %s FROM customer" % 'first_name, last_name'

row = c.execute("%s" % (query)).fetchone()

row
Out[28]: (u'Derek', u'Litz')

这是对 sqlite3 数据库的另一个有效查询，其中包含另一个表，但没有行。

query = "SELECT %s FROM customer2" % 'first_name, last_name'

print c.execute("%s" % (query)).fetchone()
None

如您所见，与上面的行为相同。

还要确保行计数按照您希望的方式与数据库配合使用。例如，它不适用于 sqlite3。请参阅 http://www.python.org/dev/peps/pep-0249/#cursor_objects 中的行计数规范并查阅 MySQLdb 文档。