5.6

今日
1.继续学习python
用py进行数据库增删改查

import mysql.connector
 
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="123456",
  database="runoob_db"
)
mycursor = mydb.cursor()
 
mycursor.execute("SHOW TABLES")
 
for x in mycursor:
  print(x)

首先检查是否连接，mycursor.execute("SHOW TABLES")

---

插入数据

sql = "INSERT INTO sites (name, url) VALUES (%s, %s)"
val = ("test", "test1")
mycursor.execute(sql, val)

mydb.commit()    # 数据表内容有更新，必须使用到该语句
print(mycursor.rowcount, "记录插入成功。")

能防止sql注入
%s 不是字符串拼接，而是 参数占位符
错误样例

name = "RUN'; DROP TABLE sites;--"  # 恶意输入
sql = f"INSERT INTO sites (name, url) VALUES ('{name}', '{url}')"
mycursor.execute(sql)

---

查询数据

mycursor.execute("SELECT * FROM sites")
 
myresult = mycursor.fetchall()     # fetchall() 获取所有记录
 
for x in myresult:
  print(x)

---

删除

sql = "DELETE FROM sites WHERE name = 'stackoverflow'"
 
mycursor.execute(sql)
 
mydb.commit()
 
print(mycursor.rowcount, " 条记录删除")

要慎重使用删除语句，删除语句要确保指定了 WHERE 条件语句，否则会导致整表数据被删除。

---

更新

sql = "UPDATE sites SET name = 'ZH' WHERE name = 'Zhihu'"
 
mycursor.execute(sql)
 
mydb.commit()
 
print(mycursor.rowcount, " 条记录被修改")