Python MySQL WHERE
-
查找带过滤器
从表中选择记录时,可以使用“WHERE”语句过滤选择:选择地址为“Park Lane 38”的记录:结果:
尝试一下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)
-
通配符
您还可以选择以给定字母或短语开头,包含或结尾的记录。使用%来代表通配符:选择地址中包含单词“way”的记录:
尝试一下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)
-
防止SQL注入
当用户提供查询值时,应转义这些值。 这是为了防止SQL注入,这是破坏或滥用数据库的常见Web黑客技术。mysql.connector模块具有转义查询值的方法:通过使用%s占位符方法转义查询值:
尝试一下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)