Searching a mysql query from python

Question

I have my Python program linked with MYSQL database and in that database I have a table called 'user_data' where simply different user data are stored. I am trying to select a particular data from the database and display it or use it for some other purpose. The following code works likes a charm:

    self.cursor = self.db.cursor()
    self.cursor.execute("SELECT * from user_data WHERE Name = 'Anup'")
    rows = self.cursor.fetchone()
    print(rows)

However, this program isn't actually feasible for searching in the database. So when i try to modify my program to the one that is feasible, no output is available. Or simply the output is displayed as none. Following is the code that is displaying the error:

    self.cursor = self.db.cursor()
    name="Anup"
    self.cursor.execute("SELECT * from user_data WHERE Name = '%s'",('name'))
    rows = self.cursor.fetchone()
    print(rows)

mhawke · Accepted Answer · 2015-08-02 11:42:07Z

2

You should use a parameterised query like this:

name="Anup"
self.cursor.execute("SELECT * from user_data WHERE Name = %s", (name,))

Note here that you do not add single quotes to your query (the DBAPI driver will add that for you), and that a tuple (or other sequence such as a list) is passed as an argument to cursor.execute().

An alternative is to use named parameters with values passed as a dictionary:

name="Anup"
self.cursor.execute("SELECT * from user_data WHERE Name = %(name)s", {'name': name})

answered Aug 2, 2015 at 11:42

mhawke

87.5k10 gold badges122 silver badges142 bronze badges

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

Comments

Saurabh Pandey · Accepted Answer · 2015-08-02 11:33:09Z

0

I think you are doing something wrong with your code Try This One

self.cursor = self.db.cursor()
name="Anup"
self.cursor.execute("SELECT * from user_data WHERE Name = '%s'" % (name))
rows = self.cursor.fetchone()
print(rows)

answered Aug 2, 2015 at 11:33

Saurabh Pandey

212 bronze badges

4 Comments

mhawke Over a year ago

While this will work, it is not a good idea to use string interpolation to generate queries. Using parameterised queries instead avoids common SQL injection vulnerabilities.

mhawke Over a year ago

@user5177957 : as per my comment, you should not do it this way due to the possibility of SQL injection. There is some information about this here (it's for SQLite but it's the same rationale for other drivers such as MySQL)

Saurabh Pandey Over a year ago

to avoid SQL Injection you can use this line self.cursor.execute("SELECT * from user_data WHERE Name = %s",('name',))

mhawke Over a year ago

@SaurabhPandey: yes, except that that would pass the literal string 'name'. The variable name should be used, i.e. (name, ), not the string 'name'.

Salem · Accepted Answer · 2015-08-02 11:34:34Z

0

You need to use a tuple (note the comma after name):

self.cursor.execute("SELECT * from user_data WHERE Name = %s",('name',))

answered Aug 2, 2015 at 11:34

Salem

13k4 gold badges37 silver badges56 bronze badges

Collectives™ on Stack Overflow

Searching a mysql query from python

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related