使用Flask框架连接MySQL数据库的步骤指南
1. 安装PyMySQL模块
在开始之前，请确保已经安装了PyMySQL模块。可以通过以下命令安装：
pip install pymysql2. 配置数据库连接
创建一个名为config.py的文件，用于存储数据库连接信息和管理类。
import pymysql
DATABASE_CONFIG = {
"host": "localhost",
"port": 3306,
"user": "admin",
"password": "securepassword",
"database": "mydatabase",
"charset": "utf8mb4"
}
class DatabaseConnectionManager:
def __init__(self):
self.connection = None
self.cursor = None
self.connect()
def connect(self):
self.connection = pymysql.connect(
host=DATABASE_CONFIG["host"],
port=DATABASE_CONFIG["port"],
user=DATABASE_CONFIG["user"],
password=DATABASE_CONFIG["password"],
database=DATABASE_CONFIG["database"],
charset=DATABASE_CONFIG["charset"]
)
self.cursor = self.connection.cursor(pymysql.cursors.DictCursor)
def fetch_all(self, query, parameters=None):
self.cursor.execute(query, parameters)
return self.cursor.fetchall()
def fetch_one(self, query, parameters=None):
self.cursor.execute(query, parameters)
return self.cursor.fetchone()
def execute_update(self, query, parameters=None):
self.cursor.execute(query, parameters)
self.connection.commit()
def execute_batch_update(self, query, parameters=None):
self.cursor.executemany(query, parameters)
self.connection.commit()
def insert_record(self, query, parameters=None):
self.cursor.execute(query, parameters)
self.connection.commit()
return self.cursor.lastrowid
def disconnect(self):
self.cursor.close()
self.connection.close()
def __enter__(self):
return self
def __exit__(self, exc_type, exc_val, exc_tb):
self.disconnect()
3. 在Flask应用中使用配置
在app.py文件中导入配置文件并初始化Flask应用：
from flask import Flask, render_template
from config import DatabaseConnectionManager
import math
app = Flask(__name__)
app.config.from_mapping(
DATABASE_URL="mysql://admin:securepassword@localhost:3306/mydatabase"
)
# 在应用上下文中使用数据库连接
with DatabaseConnectionManager() as db:
# 示例查询
result = db.fetch_all("SELECT * FROM city")
print(result)
4. 实现数据库操作路由
创建一个路由来处理城市列表的查询和显示：
@app.route('/cities', methods=['GET'])
def display_cities():
with DatabaseConnectionManager() as db:
cities = db.fetch_all("SELECT * FROM city")
return render_template('city_list.html', cities=cities)
5. 创建模板文件
在模板文件city_list.html中使用Jinja2模板引擎显示城市数据：
{% extends 'base.html' %}
{% block content %}
<div class="city-container">
<h1>城市列表</h1>
{% for city in cities %}
<div class="city-item">
<h3><a href="{{ url_for('city_details', city_id=city.ID) }}">{{ city.Name }}</a></h3>
<p>国家代码：{{ city.CountryCode }}</p>
</div>
{% endfor %}
</div>
{% endblock %}
