MYSQLdb/Python - not all arguments converted during string formatting error?

Question

Trying to select some info from a database, I have a database manager class that looks like this:

class DatabaseManager:
    def __init__(self):
        self.connection = MySQLdb.connect(host="localhost", 
                     user="neal", 
                      passwd="hacker123",
                      db="massive") 

        self.cursor = self.connection.cursor()

When trying to do a SELECT like this:

    db = DatabaseManager()
    db.cursor.execute("SELECT password FROM USERS WHERE apikey = %s", str(request.apikey))

I get a

TypeError: not all arguments converted during string formatting

This is odd as I have similar queries elsewhere that worked fine, like this one:

db.cursor.execute('''INSERT into USERS(email, username, password, apikey) values (%s, %s, %s, %s)''',
    (request.email, request.username, request.password, apikey))

Am I doing this wrong?

EDIT: Column confusion, table looks like this:

CREATE TABLE users (
id int NOT NULL AUTO_INCREMENT, 
email varchar(255) NOT NULL,
username varchar(25) NOT NULL,
password varchar(25) NOT NULL,
apikey varchar(45) NOT NULL,
PRIMARY KEY (id),
UNIQUE(email),
UNIQUE(username),
UNIQUE(apikey)
);

Did you try passing the argument as a tu9le with a single string? — Eyal H
– Eyal H, Commented Nov 4, 2014 at 19:20

cwallenpoole · Accepted Answer · 2014-11-04 19:17:09Z

1

That is because the second argument of execute is an iterable. So you would be better off with a list, set, or tuple.

Try this:

db.cursor.execute("SELECT password FROM USERS WHERE apikey = %s", (str(request.apikey),))

answered Nov 4, 2014 at 19:17

cwallenpoole

82.4k26 gold badges132 silver badges174 bronze badges

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

Comments

Burak Yılmaztürk · Accepted Answer · 2014-11-04 19:21:26Z

1

You should give tuple, no need to parse str;

db.cursor.execute("SELECT password FROM USERS WHERE apikey = %s", (request.apikey,))

answered Nov 4, 2014 at 19:21

Burak Yılmaztürk

933 bronze badges

Comments

navy_green · Accepted Answer · 2014-11-04 19:21:21Z

-1

you are much better off with something like this:

db.cursor.execute("SELECT password FROM USERS WHERE apikey = {0}".format(str(request.apikey)))

given that request.apikey can be implicitly converted into a string

answered Nov 4, 2014 at 19:21

navy_green

1111 gold badge2 silver badges7 bronze badges

2 Comments

Lucifer N. Over a year ago

When I do this, I get this error:

File "/Library/Python/2.7/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler OperationalError: (1054, "Unknown column 'a443160d' in 'where clause'")

. The API key is in the format a443160d-66fb-482c-953b-af1349b9c5ba, and the column is named apikey. Why does it think the value I'm passing it is the name of the column?

navy_green Over a year ago

if apikey value is a string, then include quotations('). If its a integer it should be fine

Collectives™ on Stack Overflow

MYSQLdb/Python - not all arguments converted during string formatting error?

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related