python - mysqldb .. 'NoneType' object is not subscriptable

Question

This code works fine when the cur.execute() and db.commit() lines are commented out; i.e. if all I do is print the query, this program runs for n number of rows. The problem seems to occur here:

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

When I try to insert the result, I get:

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

The code:

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

The reason for your error is:

player_categories_statistics = cur.fetchone()

This sets player_categories_statistics to None. None[0] raises the exception.

The only reason this would happen is your query returns no rows, which means your table is empty. Your table is most likely is empty because you never put any rows in it, or less likely you removed them somehow.

I culprit may be the following, you are inserting into sometable and selecting from players:

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

vs

SELECT %s FROM players

The only reason this is possible is because your forcing it to loop even if nothing was returned with the line:

rowcount = 2 #hard-coded for debugging

---

Additional Info:

Here's a working query I ran on an sqlite3 database with a single table with a single row with nearly identical statements as yours, just to show that yours should be working if the data is indeed there.

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

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

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

Here's another working query on a sqlite3 database with another table and no rows.

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

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

As you can see, identical to the behavior above.

Also make sure rowcount works they way you want with your DB. It doesn't with sqlite3, for example. See rowcount spec in http://www.python.org/dev/peps/pep-0249/#cursor_objects and consulte MySQLdb docs.