pip install mysql-connector-python
pip install pymysql

CREATE USER 'root'@'192.168.10.102' IDENTIFIED BY 'pwd123';

GRANT REPLICATION  ON *.* TO 'root'@'192.168.10.102';

ALTER USER 'root'@'192.168.10.102' IDENTIFIED WITH mysql_native_password BY 'pwd123';

FLUSH PRIVILEGES;

-- 創(chuàng)建數(shù)據(jù)庫
CREATE DATABASE IF NOT EXISTS testdb;

-- 切換到 testdb 數(shù)據(jù)庫
USE testdb;

-- 創(chuàng)建 users 表
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT NOT NULL
);

-- 創(chuàng)建 orders 表
CREATE TABLE IF NOT EXISTS orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    amount DECIMAL(10, 2) NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- 插入數(shù)據(jù)到 users 表
INSERT INTO users (name, age) VALUES
('Alice', 25),
('Bob', 30),
('Charlie', 35),
('David', 28);

-- 插入數(shù)據(jù)到 orders 表
INSERT INTO orders (user_id, amount) VALUES
(1, 100.50),
(2, 150.75),
(3, 200.00),
(1, 50.25);

-- -- 查詢所有用戶信息
-- SELECT * FROM users;

-- -- 查詢所有訂單信息
-- SELECT * FROM orders;

-- -- 查詢用戶及其對(duì)應(yīng)的訂單信息（JOIN查詢）
-- SELECT users.name, users.age, orders.amount
-- FROM users
-- JOIN orders ON users.id = orders.user_id;

-- -- 更新某個(gè)用戶的數(shù)據(jù)，修改 Bob 的年齡
-- UPDATE users SET age = 31 WHERE name = 'Bob';

-- -- 刪除 Charlie 用戶及其對(duì)應(yīng)的訂單數(shù)據(jù)
-- DELETE FROM orders WHERE user_id = (SELECT id FROM users WHERE name = 'Charlie');
-- DELETE FROM users WHERE name = 'Charlie';

-- -- 設(shè)置事務(wù)隔離級(jí)別為 REPEATABLE READ
-- SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

import pymysql

db = pymysql.connect(
    host="192.168.10.101",  #MySQL數(shù)據(jù)庫的地址
    user="root",           #數(shù)據(jù)庫的用戶名
    password="pwd123",     #密碼
    database="testdb",     #要連接的數(shù)據(jù)庫的名稱
    charset="utf8mb4"
)         #創(chuàng)建數(shù)據(jù)庫的連接

cursor = db.cursor()   #創(chuàng)建游標(biāo)對(duì)象

cursor.execute("select * from users")   #執(zhí)行SQL語句

results=cursor.fetchall()
for row in results:
    print(row)       #獲取查詢結(jié)果


data = cursor.fetchone()
print("MySQL 版本：", data)


cursor.close()   #關(guān)閉游標(biāo)連接
db.close()     #關(guān)閉數(shù)據(jù)庫連接

python3 01_connect.py

import pymysql

db = pymysql.connect(
    host="192.168.10.101",
    user="root",
    password="pwd123",
    database="testdb",
    charset="utf8mb4"
)
cursor = db.cursor()

# 創(chuàng)建表
cursor.execute("""
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    age INT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
""")

# 插入
cursor.execute("INSERT INTO users(name, age) VALUES(%s, %s)", ("Alice", 25))
db.commit()

# 查詢
cursor.execute("SELECT * FROM users")
results = cursor.fetchall()
for row in results:
    print(row)

# 更新
cursor.execute("UPDATE users SET age=%s WHERE name=%s", (26, "Alice"))
db.commit()

# 刪除
cursor.execute("DELETE FROM users WHERE name=%s", ("Alice",))
db.commit()

# 模糊查詢
cursor.execute("SELECT * FROM users WHERE name LIKE %s", ("%li%",))
print("模糊查詢：", cursor.fetchall())

# 聯(lián)合查詢
cursor.execute("""
SELECT users.name, orders.amount
FROM users
INNER JOIN orders ON users.id = orders.user_id
""")
print("聯(lián)合查詢：", cursor.fetchall())

cursor.close()
db.close()

python3 02_operate.py

import pymysql

db = pymysql.connect(
    host="192.168.10.101",
    user="root",
    password="pwd123",
    database="testdb"
)
cursor = db.cursor()

db.autocommit = False

try:
    cursor.execute("INSERT INTO users(name, age) VALUES('Tom', 20)")
    cursor.execute("UPDATE users SET age=21 WHERE name='Tom'")
    db.commit()
    print("事務(wù)執(zhí)行成功")
except:
    db.rollback()
    print("事務(wù)已回滾")
finally:
    cursor.close()
    db.close()

python3 03_transaction.py

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

pip install dbutils

from dbutils.pooled_db import PooledDB
import pymysql

#數(shù)據(jù)庫的連接配置
pool = PooledDB(
    creator=pymysql,
    host="192.168.10.101",
    user="root",
    password="pwd123",
    database="testdb",
    charset="utf8mb4",
    maxconnections=10,
    mincached=2
)     
  
#創(chuàng)建連接池
connection_pool=PooledDb(
    creator=pymysql,    #使用pymysql作為數(shù)據(jù)庫連接庫
    maxconnection=5,    #連接池中最大連接數(shù)
    **dbconfig
)

#獲取連接
db_connection=connection_pool.connection()
cursor=db_connecction.cursor()

cursor.execute("select * from users")
results=cursor.fetchall()

for row in results:
    print(row)

cursor.close()
db_connection.close()   #連接會(huì)自動(dòng)歸還給連接池

python3 04_pool.py