在 Python 中使用 MySQL 存储过程实现复杂查询

import mysql.connector

# 连接数据库
cnx = mysql.connector.connect(user='user', password='password',
                              host='localhost', database='database')

# 创建存储过程
cursor = cnx.cursor()

create_proc = """
CREATE PROCEDURE complex_query(IN keyword VARCHAR(255))
BEGIN
SELECT id, title, body FROM posts
WHERE title LIKE CONCAT('%', keyword, '%')
OR body LIKE CONCAT('%', keyword, '%')
ORDER BY created_at DESC;
END;
"""

cursor.execute(create_proc)

# 调用存储过程
call_proc = "CALL complex_query('pidancode.com')"
cursor.execute(call_proc)

# 处理结果集
for result in cursor:
    print(result)

# 关闭连接
cursor.close()
cnx.close()