Python MySQLdb 'not all arguments converted during string formatting'

Question

I'm doing a task for uni, where we take a text file with a list of student IDs and their favourite movies, games, tv shows, et cetera, and populating a MySQL database with it. The text files are formatted like so:

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

and the database table (popularity) is this:

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

My code for doing all this is as follows:

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'])

The problem I'm having is that no matter what I do, I keep getting the following error:

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

I have no idea how to fix this (even after extensive googling), and I can't change from MySQLdb because it's a uni task (personally, i'd be using sqlalchemy). If anyone can help it's be greatly appreciated!

Thanks, Tom

I am really sure that if you check connections.py", line 35 you will find the cause. At least one of the arguments is None and it is not supposed to be (or the list has too few elements). — sorin
– sorin, Commented May 28, 2011 at 12:43
Try to use an ORM, e.g. sqlalchemy.org. It's much easier than writing raw SQLs. — kakarukeys
– kakarukeys, Commented May 28, 2011 at 13:29
I would be using sqlalchemy if I could, but it's a uni task and we have to use mysqldb (which annoyingly, also doesn't work on mac) — Tom Brunoli
– Tom Brunoli, Commented May 28, 2011 at 13:42
@TomBrunoli Don't ever, ever use sqlalchemy! I've run into severe memory leaks and performance problems because of that beast! Nothing helped until I've just rewritten everything to omit ORM altogether. Always do everything in low-level SQL and you will be fine. — Megan Caithlyn
– Megan Caithlyn, Commented Dec 8, 2016 at 9:48

ssapkota · Accepted Answer · 2011-05-28 12:44:17Z

11

Its must be the typo, remove the $ and replace it with %

Error code:

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

Corrected code:

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

answered May 28, 2011 at 12:44

ssapkota

3,30022 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tom Brunoli Over a year ago

Wow, I feel like an idiot. I was sure it was correct :/. Thanks though!

Collectives™ on Stack Overflow

Python MySQLdb 'not all arguments converted during string formatting'

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related