Mysql 数据库操作查询记录

插入多行

要将多行插入到表中，使用executemany()方法。
executemany()方法的第二个参数是一个元组列表，包含了要插入的数据:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="你的用户名",
  passwd="你的密码",
  database="mydatabase"
)

mycursor = mydb.cursor()

sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = [
  ('Peter', 'Lowstreet 4'),
  ('Amy', 'Apple st 652'),
  ('Hannah', 'Mountain 21'),
  ('Michael', 'Valley 345'),
  ('Sandy', 'Ocean blvd 2'),
  ('Betty', 'Green Grass 1'),
  ('Richard', 'Sky st 331'),
  ('Susan', 'One way 98'),
  ('Vicky', 'Yellow Garden 2'),
  ('Ben', 'Park Lane 38'),
  ('William', 'Central st 954'),
  ('Chuck', 'Main Road 989'),
  ('Viola', 'Sideway 1633')
]

mycursor.executemany(sql, val)

mydb.commit()

print(mycursor.rowcount, " 条已经插入")

获取插入行的ID

可以通过查询cursor对象，获得刚才插入行的id。
注意: 如果插入多行，则返回最后插入行的id。

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="你的用户名",
  passwd="你的密码",
  database="mydatabase"
)

mycursor = mydb.cursor()

sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = ("Michelle", "Blue Village")
mycursor.execute(sql, val)

mydb.commit()

print("1 条记录插入, ID:", mycursor.lastrowid)

防止SQL注入

当用户提供查询值时，为了防止SQL注入，应该转义这些值。

SQL注入是一种常见的web黑客技术，用于破坏或误用数据库。
mysql.connector 模块有方法可以转义查询值:
示例
使用占位符%s方法转义查询值:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="你的用户名",
  passwd="你的密码",
  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)

ORDER BY DESC

使用DESC关键字，可按降序对结果排序。
示例
按name的字母降序，对结果进行排序:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="你的用户名",
  passwd="你的密码",
  database="mydatabase"
)

mycursor = mydb.cursor()

sql = "SELECT * FROM customers ORDER BY name DESC"

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

删除记录

可以使用“DELETE FROM”语句，从现有表中删除记录:
示例
删除地址为“Mountain 21”的记录:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="你的用户名",
  passwd="你的密码",
  database="mydatabase"
)

mycursor = mydb.cursor()

sql = "DELETE FROM customers WHERE address = 'Mountain 21'"

mycursor.execute(sql)

mydb.commit()

print(mycursor.rowcount, " 条记录删除")

注意： 数据库修改后，需要使用mydb.commit()语句提交，不提交，修改不会生效。
注意DELETE语句中的WHERE子句: WHERE子句指定应该删除哪些记录。如果省略WHERE子句，将删除所有记录!

仅当表存在时才删除

如果要删除的表不存在，会报错，可以使用If EXISTS关键字判断表是否存在，避免报错。
示例
删除存在的表“customers”:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="你的用户名",
  passwd="你的密码",
  database="mydatabase"
)

mycursor = mydb.cursor()

sql = "DROP TABLE IF EXISTS customers"

mycursor.execute(sql)

更新表

可以使用“UPDATE”语句，更新表格内的现有记录:
示例
将地址栏由“Valley 345”改写为“Canyoun 123”:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="你的用户名",
  passwd="你的密码",
  database="mydatabase"
)

mycursor = mydb.cursor()

sql = "UPDATE customers SET address = 'Canyon 123' WHERE address = 'Valley 345'"

mycursor.execute(sql)

mydb.commit()

print(mycursor.rowcount, " 条记录已更新")

注意： 数据库修改后，需要使用mydb.commit()语句提交，不提交，修改不会生效。
注意UPDATE语句中的WHERE子句: WHERE子句指定应该更新哪些记录。如果省略WHERE子句，将更新所有记录!

防止SQL注入

在update语句中，为了防止SQL注入，通常应该转义查询值。
SQL注入是一种常见的web黑客技术，用于破坏或误用数据库。
mysql.connector 模块有方法可以转义查询值:
示例
使用占位符%s方法转义查询值：

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="你的用户名",
  passwd="你的密码",
  database="mydatabase"
)

mycursor = mydb.cursor()

sql = "UPDATE customers SET address = %s WHERE address = %s"
val = ("Valley 345", "Canyon 123")

mycursor.execute(sql, val)

mydb.commit()

print(mycursor.rowcount, " 条记录已更新")

限制结果数量

可以使用“LIMIT”语句，限制查询返回的记录数量:
示例
在“customers”表中，选择前5条记录:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="你的用户名",
  passwd="你的密码",
  database="mydatabase"
)

mycursor = mydb.cursor()

mycursor.execute("SELECT * FROM customers LIMIT 5")

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

从指定位置开始

如果想返回，从第3条记录开始的5条记录，可以使用“OFFSET”关键字:
示例
从位置3开始，返回5条记录:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="你的用户名",
  passwd="你的密码",
  database="mydatabase"
)

mycursor = mydb.cursor()

mycursor.execute("SELECT * FROM customers LIMIT 5 OFFSET 2")

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

连接两个或多个表

可以使用JOIN语句，根据多张表之间的相关字段，把这些表组合成一个表。
假设有一个“users”表和一个“products”表:

users

{ id: 1, name: 'John', fav: 154},
{ id: 2, name: 'Peter', fav: 154},
{ id: 3, name: 'Amy', fav: 155},
{ id: 4, name: 'Hannah', fav:},
{ id: 5, name: 'Michael', fav:}

products

{ id: 155, name: 'Tasty Lemons' },
{ id: 156, name: 'Vanilla Dreams' }

可以使用用户的fav字段和产品的id字段，组合这两个表。
示例
连接users表与products表，查看用户喜欢的产品:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="你的用户名",
  passwd="你的密码",
  database="mydatabase"
)

mycursor = mydb.cursor()

sql = "SELECT 
  users.name AS user, 
  products.name AS favorite 
  FROM users 
  INNER JOIN products ON users.fav = products.id"

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

注意: JOIN与INNER JOIN等价，它们会给你相同的结果。

左连接

在上面的例子中，Hannah和Michael被排除在结果之外，这是因为INNER JOIN只显示匹配的记录。
如果想显示所有用户，即使他们没有喜欢的产品，可以使用LEFT JOIN语句:

示例
查询所有用户和他们喜欢的产品:

sql = "SELECT 
  users.name AS user, 
  products.name AS favorite 
  FROM users 
  LEFT JOIN products ON users.fav = products.id"

右连接

如果你想要返回所有产品，以及喜欢它们的用户，可以使用RIGHT JOIN语句:

示例
查询所有产品以及喜欢它们的用户:

sql = "SELECT 
  users.name AS user, 
  products.name AS favorite 
  FROM users 
  RIGHT JOIN products ON users.fav = products.id"

注: Hannah 与 Michael，没有喜欢的产品，被排除在结果之外。