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)
    
    尝试一下