Python MySQL - mysql-connector 驱动-4
where 条件语句
如果我们要读取指定条件的数据,可以使用 where 语句:
demo_mysql_test.py
读取 name 字段为 RUNOOB 的记录:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="runoob_db"
)
mycursor = mydb.cursor()
sql = "SELECT * FROM sites WHERE name ='RUNOOB'"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
print(x)
执行代码,输出结果为:
(1,'RUNOOB','https://www.runoob.com')
也可以使用通配符 %:
demo_mysql_test.py
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="runoob_db"
)
mycursor = mydb.cursor()
sql = "SELECT * FROM sites WHERE url LIKE '%oo%'"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
print(x)
执行代码,输出结果为:
(1,'RUNOOB','https://www.runoob.com')
(2,'Google','https://www.google.com')
为了防止数据库查询发生 SQL 注入的攻击,我们可以使用 %s 占位符来转义查询的条件:
demo_mysql_test.py
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="runoob_db"
)
mycursor = mydb.cursor()
sql = "SELECT * FROM sites WHERE name = %s"
na = ("RUNOOB", )
mycursor.execute(sql, na)
myresult = mycursor.fetchall()
for x in myresult:
print(x)
排序
查询结果排序可以使用 ORDER BY 语句,默认的排序方式为升序,关键字为 ASC ,如果要设置降序排序,可以设置关键字 DESC 。
demo_mysql_test.py
按 name 字段字母的升序排序:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="runoob_db"
)
mycursor = mydb.cursor()
sql = "SELECT * FROM sites ORDER BY name"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
print(x)
执行代码,输出结果为:
(3,'Github','https://www.github.com')
(2,'Google','https://www.google.com')
(1,'RUNOOB','https://www.runoob.com')
(5,'stackoverflow','https://www.stackoverflow.com/')
(4,'Taobao','https://www.taobao.com')
(6,'Zhihu','https://www.zhihu.com')
降序排序实例:
demo_mysql_test.py
按 name 字段字母的降序排序:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="runoob_db"
)
mycursor = mydb.cursor()
sql = "SELECT * FROM sites ORDER BY name DESC"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
print(x)
执行代码,输出结果为:
(6,'Zhihu','https://www.zhihu.com')
(4,'Taobao','https://www.taobao.com')
(5,'stackoverflow','https://www.stackoverflow.com/')
(1,'RUNOOB','https://www.runoob.com')
(2,'Google','https://www.google.com')
(3,'Github','https://www.github.com')