Python MySQL Where

Select using a filter

When selecting records from a table, you can use the "WHERE" clause to filter the selection:

Example

Select records for "Park Lane 38", results:

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  passwd="yourpassword",
  database="mydatabase"
)
mycursor = mydb.cursor()
sql = "SELECT * FROM customers" WHERE address = 'Park Lane 38'"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
  print(x)

Run Instance

Wildcard

You can also select records that start with, contain, or end with the given letter or phrase.

Please use % Represents the wildcard character:

Example

Select records containing the word "way" in the address:

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  passwd="yourpassword",
  database="mydatabase"
)
mycursor = mydb.cursor()
sql = "SELECT * FROM customers WHERE address LIKE '"%way%"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
  print(x)

Run Instance

Prevent SQL Injection

When the user provides query values, you should escape these values.

This is done to prevent SQL injection, a common network hacker technique that can damage or misuse your database.

The mysql.connector module has methods to escape query values:

Example

Use the placeholder %s method to escape query values:

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  passwd="yourpassword",
  database="mydatabase"
)
mycursor = mydb.cursor()
sql = "SELECT * FROM customers WHERE address =" %s"
adr = ("Yellow Garden 2", )
mycursor.execute(sql, adr)
myresult = mycursor.fetchall()
for x in myresult:
  print(x)

Run Instance