import pymysql
from decimal import Decimal

# 1. 連接時指定轉(zhuǎn)換規(guī)則（推薦）
config = {
    'host': 'localhost',
    'user': 'root',
    'password': '你的密碼',
    'database': 'test_db',
    'conv': pymysql.converters.conversions.copy()  # 復(fù)制默認(rèn)轉(zhuǎn)換規(guī)則
}
# 重寫DECIMAL轉(zhuǎn)換規(guī)則：轉(zhuǎn)Decimal而非float
config['conv'][pymysql.FIELD_TYPE.DECIMAL] = Decimal
config['conv'][pymysql.FIELD_TYPE.NEWDECIMAL] = Decimal

# 2. 連接并查詢
connection = pymysql.connect(**config)
cursor = connection.cursor(pymysql.cursors.DictCursor)

# 創(chuàng)建測試表（含DECIMAL字段）
cursor.execute("""
CREATE TABLE IF NOT EXISTS order_info (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_no VARCHAR(20),
    amount DECIMAL(10,2)  # 金額，10位總長度，2位小數(shù)
)
""")
# 插入數(shù)據(jù)（直接傳Decimal更精準(zhǔn)）
cursor.execute("INSERT INTO order_info (order_no, amount) VALUES (%s, %s)", 
               ("ORD202501", Decimal("99.99")))
connection.commit()

# 查詢驗證（amount直接是Decimal類型）
cursor.execute("SELECT * FROM order_info WHERE id = %s", (1,))
result = cursor.fetchone()
print(f"金額：{result['amount']}，類型：{type(result['amount'])}")  # 輸出：99.99，類型：<class 'decimal.Decimal'>

cursor.close()
connection.close()

import pymysql
from datetime import datetime

connection = pymysql.connect(
    host='localhost',
    user='root',
    password='你的密碼',
    database='test_db',
    cursorclass=pymysql.cursors.DictCursor
)
cursor = connection.cursor()

# 1. 寫入：Python datetime對象直接傳入（自動轉(zhuǎn)MySQL DATETIME）
insert_time = datetime(2025, 1, 19, 10, 30, 0)
cursor.execute("INSERT INTO users (name, create_time) VALUES (%s, %s)", 
               ("王五", insert_time))
connection.commit()

# 2. 讀?。篸atetime對象轉(zhuǎn)自定義格式字符串
cursor.execute("SELECT create_time FROM users WHERE name = %s", ("王五",))
result = cursor.fetchone()
dt_obj = result['create_time']
# 轉(zhuǎn)成"YYYY-MM-DD HH:MM:SS"字符串
dt_str = dt_obj.strftime("%Y-%m-%d %H:%M:%S")
print(f"創(chuàng)建時間：{dt_str}，類型：{type(dt_str)}")  # 輸出：2025-01-19 10:30:00，類型：str

# 3. 反向：字符串轉(zhuǎn)datetime寫入
dt_str2 = "2025-01-20 15:00:00"
dt_obj2 = datetime.strptime(dt_str2, "%Y-%m-%d %H:%M:%S")
cursor.execute("UPDATE users SET create_time = %s WHERE name = %s", 
               (dt_obj2, "王五"))
connection.commit()

cursor.close()
connection.close()

import pymysql
import json

connection = pymysql.connect(
    host='localhost',
    user='root',
    password='你的密碼',
    database='test_db',
    cursorclass=pymysql.cursors.DictCursor
)
cursor = connection.cursor()

# 1. 創(chuàng)建含JSON字段的表
cursor.execute("""
CREATE TABLE IF NOT EXISTS user_profile (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    profile JSON  # JSON類型字段
)
""")

# 2. 寫入：Python字典轉(zhuǎn)JSON字符串
profile_dict = {"hobby": ["籃球", "編程"], "address": {"city": "北京"}}
profile_json = json.dumps(profile_dict, ensure_ascii=False)  # ensure_ascii=False保留中文
cursor.execute("INSERT INTO user_profile (user_id, profile) VALUES (%s, %s)", 
               (1, profile_json))
connection.commit()

# 3. 讀?。篔SON字符串轉(zhuǎn)Python字典
cursor.execute("SELECT profile FROM user_profile WHERE user_id = %s", (1,))
result = cursor.fetchone()
profile_str = result['profile']
profile_dict2 = json.loads(profile_str)
print(f"愛好：{profile_dict2['hobby']}，城市：{profile_dict2['address']['city']}")
# 輸出：愛好：['籃球', '編程']，城市：北京

cursor.close()
connection.close()

import pymysql

connection = pymysql.connect(
    host='localhost',
    user='root',
    password='你的密碼',
    database='test_db',
    cursorclass=pymysql.cursors.DictCursor
)
cursor = connection.cursor()

# 1. 創(chuàng)建含BLOB字段的表
cursor.execute("""
CREATE TABLE IF NOT EXISTS file_store (
    id INT AUTO_INCREMENT PRIMARY KEY,
    file_name VARCHAR(100),
    file_data BLOB  # 二進(jìn)制數(shù)據(jù)字段
)
""")

# 2. 寫入：讀取文件轉(zhuǎn)bytes寫入
with open("test.jpg", "rb") as f:
    file_bytes = f.read()
cursor.execute("INSERT INTO file_store (file_name, file_data) VALUES (%s, %s)", 
               ("test.jpg", file_bytes))
connection.commit()

# 3. 讀取：bytes寫入文件
cursor.execute("SELECT file_data FROM file_store WHERE file_name = %s", ("test.jpg",))
result = cursor.fetchone()
file_bytes2 = result['file_data']
with open("test_copy.jpg", "wb") as f:
    f.write(file_bytes2)
print("文件已保存為test_copy.jpg")

cursor.close()
connection.close()