4 种 Python 连接 MySQL 数据库的方法

import pymysql
 
# 方法1：使用pymysql直接连接
def connect_mysql1():
    connection = pymysql.connect(host='localhost',
                                 user='user',
                                 password='passwd',
                                 database='db',
                                 charset='utf8mb4',
                                 cursorclass=pymysql.cursors.DictCursor)
 
    try:
        with connection.cursor() as cursor:
            sql = "SELECT * FROM `table`"
            cursor.execute(sql)
            result = cursor.fetchall()
            print(result)
    finally:
        connection.close()
 
# 方法2：使用SQLAlchemy连接
from sqlalchemy import create_engine
 
def connect_mysql2():
    engine = create_engine('mysql+pymysql://user:passwd@localhost:3306/db')
    with engine.connect() as connection:
        result = connection.execute("SELECT * FROM `table`").fetchall()
        print(result)
 
# 方法3：使用pymysql连接，并使用with自动管理连接
from contextlib import closing
 
def connect_mysql3():
    with closing(pymysql.connect(host='localhost',
                                 user='user',
                                 password='passwd',
                                 database='db',
                                 charset='utf8mb4',
                                 cursorclass=pymysql.cursors.DictCursor)) as connection:
        with connection.cursor() as cursor:
            sql = "SELECT * FROM `table`"
            cursor.execute(sql)
            result = cursor.fetchall()
            print(result)
 
# 方法4：使用pandas的read_sql_query读取数据
import pandas as pd
 
def connect_mysql4():
    connection = pymysql.connect(host='localhost',
                                 user='user',
                                 password='passwd',
                                 database='db',
                                 charset='utf8mb4',
                                 cursorclass=pymysql.cursors.DictCursor)
 
    try:
        with connection.cursor() as cursor:
            sql = "SELECT * FROM `table`"
            cursor.execute(sql)
            result = pd.read_sql_query(sql, connection)
            print(result)
    finally:
        connection.close()
 
# 调用方法
connect_mysql1()
connect_mysql2()
connect_mysql3()
connect_mysql4()

这段代码提供了四种连接MySQL数据库的方法，并展示了如何使用pymysql、SQLAlchemy和pandas库来执行SQL查询并获取结果。每种方法都包含了错误处理（例如使用\`wit