Querying MySQL database using Python

Question

I have a table named 'staff', with several columns, but the two I'm interested in are 'username' and 'password'. I am trying to create a prompt which asks the user for their username (and later I will do password) and checks the table in the database to see if that username exists. I am a bit clueless about how to do this, but this is what I have coded so far.

import MySQLdb


db = MySQLdb.connect(host="127.0.0.1", user="root", passwd="", db="sakila")

cur = db.cursor()

search = raw_input("Enter username: ")

query = ('SELECT username, password FROM staff')

cur.execute(query)


results = cur.fetchall()

if search in results:
    print("username found")
else:
    print("invalid username")

db.close()

I think you want a WHERE clause in the SQL statement to select the user. — Gordon Linoff
– Gordon Linoff, Commented Jun 1, 2015 at 10:56
Oh, so how exactly would that work? (I literally just started SQL yesterday) — Programmer
– Programmer, Commented Jun 1, 2015 at 10:58
I would look at SQLAlchemy if I was you. Trust me, your life will be better for it — Mantis
– Mantis, Commented Jun 1, 2015 at 11:32

NDevox · Accepted Answer · 2015-06-01 11:27:46Z

2

EDIT: Make sure to check the comment from @FallenAngel at the bottom and check for security risks in your code!

As Gordon mentioned, you should use a WHERE clause to make things easier. Like so:

import re

import MySQLdb

db = MySQLdb.connect(host="127.0.0.1", user="root", passwd="", db="sakila")

cur = db.cursor()

search = raw_input("Enter username: ")

user_regex =  # input a regex here to make sure the username matches a format which avoids SQL injection.

if re.search(user_regex, search):

    query = ("SELECT password FROM staff WHERE username = %s", (search,)) #  add a WHERE clause

    cur.execute(query)

    results = cur.fetchall()

else:
    results = None

if results:  # Now we just need to check if the queryset is empty or not.
    print("username found")
else:
    print("invalid username")

db.close()

General rule of thumb is to try and make SQL do the searching for things, it's built for it so will be faster than Python.

Make sure your username column is a primary key (or a unique one at least) so you don't have duplicates.

EDIT: Based on @FallenAngels point, you shouldn't directly inject user input into an SQL query as it would expose you to SQL injection.

EDIT2:

First note that we are no longer using "%s" % var formatting for this solution as it is unsafe! Instead we are using "%s", (var,) formatting which is for db queries.

%s (it can also be %d, %n and a few other letters) in a string is a form of string formatting used to insert variable content into a string.

Essentially if you say:

"some stuff here: %s" % var

Anything that is in var will replace the %s in the string. There are several intricacies so it's worth reading more here: https://docs.python.org/2/library/string.html

edited Jun 1, 2015 at 11:27

answered Jun 1, 2015 at 11:05

NDevox

4,0865 gold badges25 silver badges36 bronze badges

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

14 Comments

NDevox Over a year ago

for anyone who downvotes - Let me know why please so I can improve my answer!

Mp0int Over a year ago

Never use string operations for database queries since they are vulnerable to SQL injections...

NDevox Over a year ago

@SakshamYadav, make sure you take into account what FallenAngel has said, I'm updating my answer atm.

Mp0int Over a year ago

("SELECT password FROM staff WHERE username = %s", (search,)) you must use %s for all placeholders and pass the values with a list or tuple.

Programmer Over a year ago

alright, will do, thanks :). By the way, why are we selecting password?

|

Pralhad Narsinh Sonar · Accepted Answer · 2015-06-01 11:27:43Z

1

Perhaps you need to distribute this in two files

In first file you please build the form and through app.route you link it to the def in python file.

This way you can have your presentation and business model completely separate and that will remain more maintainable as well.

Please let me know if you need more simplification along with the code.

answered Jun 1, 2015 at 11:27

Pralhad Narsinh Sonar

1,4541 gold badge14 silver badges23 bronze badges

Collectives™ on Stack Overflow

Querying MySQL database using Python

2 Answers 2

14 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

14 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related