Python MySQL WHERE - CodersLegacy

Description

Let’s say your the manager of a database for a large company . This Database has thousands of records stored in it for it’s employees. One day you are asked to retrieve the data for a specific employee. How are you going to do it? With the Python MySQL WHERE statement of course.

The WHERE statement is used together with the SELECT statement. It adds a condition on to the SELECT’s selection. For instance, the statement below.

SELECT * FROM employees WHERE age < 18

This SQL statement retrieves from the Database the data of all employees under the age of 18.

Example

In this example we select from the table student, the data of all the male students.

Remember to import the mysql.connector and create a connection to your database as shown below before attempting anything. Follow up by creating a cursor object which we’ll be using to manipulate to the database.

import mysql.connector

db = mysql.connector.connect(
  host = "localhost",
  user = "root",
  password = "******",
  database = "mydatabase" )

cursor = db.cursor() 
cursor.execute("SELECT * FROM student WHERE gender = 'Male'")

results = cursor.fetchall()
print(results)

Preventing SQL injection

While you’re working on the localhost, this isn’t a problem. However, when working with databases connected to the internet or on web servers, this is a glaring issue. SQL injection is when a hacker injects SQL code into your database using a vulnerability. Luckily, there is an easy fix for this.

Remember the %s placeholder character? Using this prevents SQL injection.

import mysql.connector

db = mysql.connector.connect(
  host = "localhost",
  user = "root",
  password = "******",
  database = "mydatabase"  )

cursor = db.cursor() 

sql = "SELECT * FROM student WHERE gender = %s"
gender = ("Male")

cursor.execute(sql, gender)

results = cursor.fetchall()
print(results)

This marks the end of our Python MySQL WHERE Article. Let us know if you have any suggestions or corrections in this Article to make. Contributions to help grow CodersLegacy are more than welcome.

Use this link to head back to main Python MySQL page: link