#頭條創作挑戰賽#

技術幹貨|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常見用法