前言
本章继续介绍MySQL - mysql-connector 驱动。
where 条件语句
如果我们要读取指定条件的数据,可以使用 where 语句:
demo_mysql_test.py
读取 name 字段为 CSDN 的记录:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="CSDN_db"
)
mycursor = mydb.cursor()
sql = "SELECT * FROM sites WHERE name ='CSDN'"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
print(x)
执行代码,输出结果为:
(1, 'CSDN', 'https://www.CSDN.com')
也可以使用通配符 %:
demo_mysql_test.py
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="CSDN_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, 'CSDN', 'https://www.CSDN.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="CSDN_db"
)
mycursor = mydb.cursor()
sql = "SELECT * FROM sites WHERE name = %s"
na = ("CSDN", )
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="CSDN_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, 'CSDN', 'https://www.CSDN.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="CSDN_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, 'CSDN', 'https://www.CSDN.com')
(2, 'Google', 'https://www.google.com')
(3, 'Github', 'https://www.github.com')
Limit
如果我们要设置查询的数据量,可以通过 “LIMIT” 语句来指定
demo_mysql_test.py
读取前 3 条记录:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="CSDN_db"
)
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM sites LIMIT 3")
myresult = mycursor.fetchall()
for x in myresult:
print(x)
执行代码,输出结果为:
(1, 'CSDN', 'https://www.CSDN.com')
(2, 'Google', 'https://www.google.com')
(3, 'Github', 'https://www.github.com')
也可以指定起始位置,使用的关键字是 OFFSET:
demo_mysql_test.py
从第二条开始读取前 3 条记录:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="CSDN_db"
)
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM sites LIMIT 3 OFFSET 1") # 0 为 第一条,1 为第二条,以此类推
myresult = mycursor.fetchall()
for x in myresult:
print(x)
执行代码,输出结果为:
(2, 'Google', 'https://www.google.com')
(3, 'Github', 'https://www.github.com')
(4, 'Taobao', 'https://www.taobao.com')
删除记录
删除记录使用 “DELETE FROM” 语句:
demo_mysql_test.py
删除 name 为 stackoverflow 的记录:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="CSDN_db"
)
mycursor = mydb.cursor()
sql = "DELETE FROM sites WHERE name = 'stackoverflow'"
mycursor.execute(sql)
mydb.commit()
print(mycursor.rowcount, " 条记录删除")
执行代码,输出结果为:
1 条记录删除
注意:要慎重使用删除语句,删除语句要确保指定了 WHERE 条件语句,否则会导致整表数据被删除。
为了防止数据库查询发生 SQL 注入的攻击,我们可以使用 %s
占位符来转义删除语句的条件:
demo_mysql_test.py
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="CSDN_db"
)
mycursor = mydb.cursor()
sql = "DELETE FROM sites WHERE name = %s"
na = ("stackoverflow", )
mycursor.execute(sql, na)
mydb.commit()
print(mycursor.rowcount, " 条记录删除")
执行代码,输出结果为:
1 条记录删除
更新表数据
数据表更新使用 “UPDATE” 语句:
demo_mysql_test.py
将 name 为 Zhihu 的字段数据改为 ZH:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="CSDN_db"
)
mycursor = mydb.cursor()
sql = "UPDATE sites SET name = 'ZH' WHERE name = 'Zhihu'"
mycursor.execute(sql)
mydb.commit()
print(mycursor.rowcount, " 条记录被修改")
执行代码,输出结果为:
1 条记录被修改
注意:UPDATE 语句要确保指定了 WHERE 条件语句,否则会导致整表数据被更新。
为了防止数据库查询发生 SQL 注入的攻击,我们可以使用 %s 占位符来转义更新语句的条件:
demo_mysql_test.py
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="CSDN_db"
)
mycursor = mydb.cursor()
sql = "UPDATE sites SET name = %s WHERE name = %s"
val = ("Zhihu", "ZH")
mycursor.execute(sql, val)
mydb.commit()
print(mycursor.rowcount, " 条记录被修改")
执行代码,输出结果为:
1 条记录被修改
删除表
删除表使用 “DROP TABLE” 语句, IF EXISTS
关键字是用于判断表是否存在,只有在存在的情况才删除:
demo_mysql_test.py
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="CSDN_db"
)
mycursor = mydb.cursor()
sql = "DROP TABLE IF EXISTS sites" # 删除数据表 sites
mycursor.execute(sql)