Python MySQL Where
Select With a Filter
When selecting records from a table, you can filter the selection by using the “WHERE” statement:
Example
Select record(s) where the address is “Park Lane 38”: result:
12345678910111213141516171819
import mysql.connector mydb = mysql.connector.connect( host="localhost", user="yourusername", password="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)
Wildcard Characters
You can also select the records that starts, includes, or ends with a given letter or phrase.
Use the % to represent wildcard characters:
Example
Select records where the address contains the word “way”:
12345678910111213141516171819
import mysql.connector mydb = mysql.connector.connect( host="localhost", user="yourusername", password="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)
Prevent SQL Injection
When query values are provided by the user, you should escape the values.
This is to prevent SQL injections, which is a common web hacking technique to destroy or misuse your database.
The mysql.connector module has methods to escape query values:
Example
Escape query values by using the placholder %s method:
1234567891011121314151617181920
import mysql.connector mydb = mysql.connector.connect( host="localhost", user="yourusername", password="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)
