Python MySQLdb 'not all arguments converted during string formatting'

Question

我正在为 uni 做一个任务，我们在其中获取一个包含学生 ID 列表和他们最喜欢的电影、游戏、电视节目等的文本文件，并用它填充 MySQL 数据库。文本文件的格式如下:

1   Fight Club
1   Pootie Tang
3   The Lord Of The Rings Trilogy
3   Ocean's Eleven
3   The Italian Job
4   Apocalypse Now
4   Black Hawk Down
4   Full Metal Jacket
4   Platoon
4   Star Wars Series
4   Stargate
4   The Crow

数据库表(流行度)是这样的:

studentnumber (int)
category      (varchar)
value         (varchar)

我做这一切的代码如下:

import MySQLdb

def open_connection():
    '''Returns the database object'''
    connection = MySQLdb.connect(host='localhost',
                                 user='root',
                                 passwd='inb104',
                                 db='inb104')
    return connection


def process_file(category, cursor):
    '''opens the relavent file, then for each entry, runs a query to insert it
    into the database'''
    words = open(category + '.txt', 'rU')
    entries_list = []

    for line in words:
        split_line = line.split()
        number = str(split_line[0])
        value = str(split_line[1])
        entries_list.append((number, category, value))

    cursor.executemany('''INSERT INTO popularity
                          VALUES (%s, %s, $s)''', entries_list)


def data_entry(categories):
    '''
    Adds data from each category in a list of categories into a MySQL
    database.  A text file exists for each category in the list.

    categories is a list of category strings.
    '''
    connection = open_connection()
    cursor = connection.cursor()

    #process the file
    for item in categories:
        process_file(item, cursor)

if __name__ == '__main__':
data_entry(['movies', 'sports', 'actors', 'tv', 'games', \
            'activities', 'musicians', 'books'])

我遇到的问题是，无论我做什么，我都会不断收到以下错误:

Traceback (most recent call last):
  File "\\vmware-host\Shared Folders\Uni\INB104\portfolio2\data_entry\data_entry_q.py", line 96, in <module>
    'activities', 'musicians', 'books'])
  File "\\vmware-host\Shared Folders\Uni\INB104\portfolio2\data_entry\data_entry_q.py", line 78, in data_entry
    process_file(item, cursor)
  File "\\vmware-host\Shared Folders\Uni\INB104\portfolio2\data_entry\data_entry_q.py", line 63, in process_file
    VALUES (%s, %s, $s)''', entries_list)
  File "C:\Python25\Lib\site-packages\MySQLdb\cursors.py", line 212, in executemany
    self.errorhandler(self, TypeError, msg)
  File "C:\Python25\Lib\site-packages\MySQLdb\connections.py", line 35, in defaulterrorhandler
    raise errorclass, errorvalue
TypeError: not all arguments converted during string formatting

我不知道如何解决这个问题(即使在大量谷歌搜索之后)，而且我无法从 MySQLdb 更改，因为它是一个单一任务(就个人而言，我会使用 sqlalchemy)。如果有人可以提供帮助，我们将不胜感激!

谢谢，汤姆

Answer 1

一定是打错了，去掉$换成%

错误代码:

cursor.executemany('''INSERT INTO popularity
      VALUES (%s, %s, $s)''', entries_list)

更正后的代码:

cursor.executemany('''INSERT INTO popularity
      VALUES (%s, %s, %s)''', entries_list)