Python: MySQL-Query fails

Question

In general, I am familiar with MySQL but not with Python. I try to generate .py script which inserts an user with a hashed password into a mysql-database of the radius-server.

That's my script so far:

#!/usr/bin/python2.7

import sys
import MySQLdb
from passlib.hash import django_pbkdf2_sha256 as cryptohandler


def adduser(username, password_plain):
    password = cryptohandler.encrypt(password_plain)

    conn = MySQLdb.connect(host="localhost", port=3306, user="radius", passwd="radpass", db="radius")
    cursor = conn.cursor()
    if cursor.execute("SELECT op FROM radcheck WHERE username = %s", (username)):
        print 'FEHLER: User existiert bereits und kann daher nicht angelegt werden.'
        sys.exit(1)
    else:
        try:
            cursor.execute("INSERT INTO radcheck(username, attribute, op, value) VALUES(%s, 'Password', ':=', %s)", (username, password))
            conn.commit()
            print 'OK user '+ username +' successfully added with hash'+ password
            sys.exit(0)
        except:
            conn.rollback()
            print 'FEHLER query failed'
            sys.exit(1)

def main():
    adduser(sys.argv[1], sys.argv[2])


main()

The output is always "FEHLER query failed", the first query which looks up if an username is existing works fine.

The radcheck table:

id int(11) unsigned Null=No Key=Pri Default=Null Extra=auto_increment
username varchar(64) Null=No Key=Mul
attribute varchar(64) Null=No
op char(2) Null=No Default="=="
value varchar(253) Null=No

Executing the query in the mysql-command-line works fine.

Where is the error in my script? I thought it may be a too long hash, but the hash is only < 100 chars long. (But contains special characters such as the dollar $). Additionally I'd appreciate help how to debug python-sql-scripts in general!

I selected all records from the database and actually, these records are stored. But why do I get an exception? — Drudge
– Drudge, Commented May 12, 2014 at 0:53
To get more information, change your except to except Exception as e:. Then change print 'FEHLER query failed' to print e — huu
– huu, Commented May 12, 2014 at 1:07
Interestingly, my last comment will resolve your issue but does little to reveal what was happening behind the scenes and causing you pain. See my answer for more details. — huu
– huu, Commented May 12, 2014 at 1:20

huu · Accepted Answer · 2014-05-12 01:16:35Z

1

The issue here is not your MySQL code but it is this try/except block:

try:
    cursor.execute("INSERT INTO radcheck(username, attribute, op, value) VALUES(%s, 'Password', ':=', %s)", (username, password))
    conn.commit()
    print 'OK user '+ username +' successfully added with hash'+ password
    sys.exit(0)
except:
    conn.rollback()
    print 'FEHLER query failed'
    sys.exit(1)

When you call sys.exit(0), it raises an exception called SystemExit. Since a bare except: catch statement catches SystemExit, this block will always be executed when you call sys.exit(0). To prevent this from happening, change your except: catch to:

except Exception as e:
    conn.rollback()
    print 'FEHLER query failed'
    sys.exit(1)

Even better is if you catch only MySQL errors with:

except MySQLdb.Error as e:
    conn.rollback()
    print 'FEHLER query failed'
    sys.exit(1)

answered May 12, 2014 at 1:16

huu

7,5502 gold badges36 silver badges49 bronze badges

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

3 Comments

Drudge Over a year ago

Oh thanks! I didn't know that sys.exit() raises an exeption. I thought the script just terminates and returns 0 to the process which called the python script. I didn't tried it yet, but if I take the "except Exception as e:" block, why is the raised SystemExit exception not catched there?

huu Over a year ago

Because SystemExit is not derived form Exception. Exception is, in general, a base class that users and module writers extend with their own code. For more information, see the official documentation for Exception and SystemExit

Drudge Over a year ago

That's interestion, then I do understand the logic behind. I thought each exception in Python is derived from exception. Good to know that. I used the last solution you provided and it works fine for me. Thanks! :)

Collectives™ on Stack Overflow

Python: MySQL-Query fails

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related