Python连接MySQL数据库实战：PyMySQL建表与Flask API开发

Python连接MySQL数据库的核心流程

第1步：安装必要依赖库

在虚拟环境中确保安装Web框架、数据库驱动及密码加密工具：

pip install flask pymysql bcrypt

第2步：初始化MySQL实例与数据表结构

启动MySQL服务后，创建专用数据库及对应表结构。以下命令定义了用户身份与消息记录的基础字段：

CREATE DATABASE chat_db;

USE chat_db;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL
);

CREATE TABLE messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sender VARCHAR(255) NOT NULL,
    receiver VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
);

第3步：编写Flask数据库交互服务

使用PyMySQL建立连接，结合Flask路由实现注册、校验登录与会话消息的增查功能。代码已包含基础的错误捕获与资源释放逻辑：

from flask import Flask, request, jsonify
import pymysql
import bcrypt

app = Flask(__name__)

# 数据库连接配置信息
db_config = {
    'host': 'localhost',
    'user': 'root',
    'password': '123456',
    'database': 'chat_db'
}


def get_db_connection():
    return pymysql.connect(**db_config)


@app.route('/register', methods=['POST'])
def register():
    data = request.json
    username = data['username']
    password = data['password']

    # 对密码进行哈希加盐处理
    hashed = bcrypt.hashpw(password.encode('utf-8'), bcrypt.gensalt())

    conn = get_db_connection()
    cursor = conn.cursor()

    try:
        cursor.execute("INSERT INTO users (username, password) VALUES (%s, %s)", (username, hashed))
        conn.commit()
        return jsonify({'message': 'Registration successful!'}), 201
    except pymysql.IntegrityError:
        return jsonify({'message': 'Username already exists!'}), 400
    finally:
        cursor.close()
        conn.close()


@app.route('/login', methods=['POST'])
def login():
    data = request.json
    username = data['username']
    password = data['password']

    conn = get_db_connection()
    cursor = conn.cursor()

    try:
        cursor.execute("SELECT password FROM users WHERE username = %s", (username,))
        result = cursor.fetchone()

        # 校验明文密码与数据库中存储的哈希值是否匹配
        if result and bcrypt.checkpw(password.encode('utf-8'), result[0].encode('utf-8')):
            return jsonify({'message': 'Login successful!'}), 200
        else:
            return jsonify({'message': 'Incorrect username or password!'}), 401
    finally:
        cursor.close()
        conn.close()


@app.route('/send_message', methods=['POST'])
def send_message():
    data = request.json
    sender = data['sender']
    receiver = data['receiver']
    message = data['message']

    conn = get_db_connection()
    cursor = conn.cursor()

    cursor.execute("INSERT INTO messages (sender, receiver, message) VALUES (%s, %s, %s)", (sender, receiver, message))
    conn.commit()
    cursor.close()
    conn.close()

    return jsonify({'message': 'Message sent successfully!'}), 201


@app.route('/get_messages', methods=['GET'])
def get_messages():
    user1 = request.args.get('user1')
    user2 = request.args.get('user2')

    conn = get_db_connection()
    cursor = conn.cursor()

    # 检索双方交互记录并按时间排序
    cursor.execute('''SELECT sender, receiver, message, timestamp FROM messages 
                      WHERE (sender = %s AND receiver = %s) OR (sender = %s AND receiver = %s)
                      ORDER BY timestamp''', (user1, user2, user2, user1))

    messages = cursor.fetchall()
    cursor.close()
    conn.close()

    return jsonify(messages), 200


if __name__ == '__main__':
    app.run(debug=True)

第4步：启动服务并进行接口验证

将上述脚本保存为 main.py 并执行，服务默认监听 http://127.0.0.1:5000。可通过标准JSON载荷测试核心链路：

• 注册用户：POST http://127.0.0.1:5000/register

  
  {
      "username": "alice",
      "password": "password123"
  }
  

• 发送私聊消息：POST http://127.0.0.1:5000/send_message

  
  {
      "sender": "alice",
      "receiver": "bob",
      "message": "Hello Bob!"
  }
  

• 拉取会话记录：GET http://127.0.0.1:5000/get_messages?user1=alice&user2=bob

  
  [
      [
          "alice",
          "bob",
          "Hello Bob!",
          "Fri, 15 Nov 2024 16:06:33 GMT"
      ]
  ]
  

该方案覆盖了从物理层连接到业务层接口的基本实现。针对生产环境部署，建议引入HTTPS传输加密、JWT令牌鉴权机制以及数据库连接池管理以提升稳定性。

以上就是Python连接MySQL数据库实战的详细流程，更多关于Python后端开发与数据持久化的技术资料请关注本站其它相关文章！