#头条创作挑战赛#

技术干货|Python MySQL驱动模块mysql-connector-python常见用法

mysql-connector-python是Python中官方的MySQL驱动模块，提供了连接MySQL数据库并进行各种操作的功能。

下面代码示例，展示了mysql-connector-python的常见用法：

连接MySQL数据库：

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="username",
  password="password",
  database="database_name"
)

创建数据库：

mycursor = mydb.cursor()
mycursor.execute("CREATE DATABASE mydatabase")

创建表：

mycursor.execute("CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))")

插入数据：

sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = ("John", "Highway 21")
mycursor.execute(sql, val)
mydb.commit()

查询数据：

mycursor.execute("SELECT * FROM customers")
myresult = mycursor.fetchall()
for x in myresult:
  print(x)

更新数据：

sql = "UPDATE customers SET address = 'Canyon 123' WHERE address = 'Highway 21'"
mycursor.execute(sql)
mydb.commit()

删除数据：

sql = "DELETE FROM customers WHERE address = 'Mountain 21'"
mycursor.execute(sql)
mydb.commit()

使用占位符防止SQL注入：

sql = "SELECT * FROM customers WHERE address = %s"
adr = ("Yellow Garden 2", )
mycursor.execute(sql, adr)
myresult = mycursor.fetchall()
for x in myresult:
  print(x)

获取插入的ID：

sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = ("Michelle", "Blue Village")
mycursor.execute(sql, val)
mydb.commit()
print("1 record inserted, ID:", mycursor.lastrowid)

批量插入数据：

sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = [
  ("Peter", "Lowstreet 4"),
  ("Amy", "Apple st 652"),
  ("Hannah", "Mountain 21"),
  ("Michael", "Valley 345")
]
mycursor.executemany(sql, val)
mydb.commit()

使用事务：

mydb.start_transaction()
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = ("Peter", "Lowstreet 4")
mycursor.execute(sql, val)
mydb.rollback()

使用LIMIT限制结果集数量：

mycursor.execute("SELECT * FROM customers LIMIT 5")
myresult = mycursor.fetchall()
for x in myresult:
  print(x)

使用ORDER BY排序结果集：

mycursor.execute("SELECT * FROM customers ORDER BY name")
myresult = mycursor.fetchall()
for x in myresult:
  print(x)

使用JOIN连接多个表：

sql = "SELECT \
  customers.name AS customer_name, \
  orders.name AS order_name \
  FROM customers \
  INNER JOIN orders ON customers.id = orders.customer_id"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
  print(x)

创建索引：

mycursor.execute("CREATE INDEX idx_name ON customers (name)")

创建存储过程：

mycursor.execute("CREATE PROCEDURE myprocedure() \
  BEGIN \
  SELECT * FROM customers; \
  END")

调用存储过程：

mycursor.callproc("myprocedure")
for result in mycursor.stored_results():
  print(result.fetchall())

执行自定义查询：

mycursor.execute("SELECT * FROM customers WHERE address LIKE '%way%'")

获取表结构信息：

mycursor.execute("DESCRIBE customers")
myresult = mycursor.fetchall()
for x in myresult:
  print(x)

获取数据库版本信息：

mycursor.execute("SELECT VERSION()")
myresult = mycursor.fetchone()
print(myresult)

使用连接池管理数据库连接：

from mysql.connector import pooling

dbconfig = {
  "host": "localhost",
  "user": "username",
  "password": "password",
  "database": "database_name"
}
cnxpool = pooling.MySQLConnectionPool(pool_name = "mypool", pool_size = 5, **dbconfig)
cnx = cnxpool.get_connection()
cursor = cnx.cursor()
cursor.execute("SELECT * FROM customers")

使用上下文管理器管理数据库连接：

from mysql.connector import connect

with connect(
  host="localhost",
  user="username",
  password="password",
  database="database_name"
) as cnx:
  with cnx.cursor() as cursor:
    cursor.execute("SELECT * FROM customers")

处理查询结果中的NULL值：

mycursor.execute("SELECT name, address FROM customers WHERE address IS NULL")
myresult = mycursor.fetchall()
for x in myresult:
  print(x)

获取受影响的行数：

mycursor.execute("DELETE FROM customers WHERE address = 'Mountain 21'")
print("Deleted rows:", mycursor.rowcount)

关闭数据库连接：

mydb.close()

这些示例展示了mysql-connector-python模块的一些基本用法，你可以根据自己的需求进行进一步的学习和扩展。

有关mysql的其他操作可参考：

mysql8安全基线及安全优化

mysql8.0安装、部署及优化

Shell脚本来实现MySQL服务的定时健康性检查

shell脚本实现mysql数据库文件和日志备份

技术干货|MySQL 8索引优化、查询优化、表设计优化和配置优化

Ansible实现生产环境一键式Linux+Nginx+MySQL+Tomcat企业级架构

技术干货|Python MySQL驱动模块mysql-connector-python常见用法