Python数据分析：连接数据库的几种方式

在数据分析和科学计算领域，数据通常存储在数据库中。Python 连接 MySQL 数据库可以使用多种库，常见的有 mysql-connector-python、PyMySQL等。

一、Python操作数据库

1.1、mysql-connector-python（MySQL官方的纯Python驱动）

import mysql.connector

# 连接到 MySQL 数据库
conn = mysql.connector.connect(
    host="xx.xx.xx.xxx",
    port=3306,
    user="write",
    password="****************",
    database="dbname"
)

# 创建游标
cursor = conn.cursor()

# 执行 SQL 查询
sql_text='''
    select 
        start_node_id, 
        end_node_id,
        start_coordinate_x, 
        start_coordinate_y, 
        end_coordinate_x, 
        end_coordinate_y, 
        pipe_length
    from 
        pt_pipeline_base
    where credit_code ="91370211727832262X"
        and is_delete=0
        and is_break_point=0
        and pipe_status=1
'''
cursor.execute(sql_text)

# 获取查询结果
result = cursor.fetchall()

# 输出结果
for row in result:
    print(row)

# 关闭游标和连接
cursor.close()
conn.close()

1.2、PyMySQL连接MySql数据库

PyMySQL 是一个纯 Python 实现的 MySQL 客户端库，功能类似于 mysql.connector。这种方式较为麻烦。

import pymysql

# 创建数据库连接
conn = pymysql.connect(
    host='xx.xx.xx.xxx',
    user='read',  # 用户名
    passwd='****************',  # 密码
    port=3306,  # 端口，默认为3306
    db='dbname',  # 数据库名称
    charset='utf8'  # 字符编码
)

# 生成游标对象 cursor
cursor = conn.cursor()

# 查询数据库版本
cursor.execute("select version()")  # 返回值是查询到的数据数量
# 通过 fetchall方法获得数据
data = cursor.fetchone()
print("Database Version:%s" % data)

sql = '''
    SELECT 
        pipe_id, 
        start_node_id, 
        end_node_id, 
        pipe_length 
    FROM 
        pt_pipeline_base 
    WHERE 
        credit_code ="91370211727832262X"
'''
cursor.execute(sql)  # 返回值是查询到的数据数量
data = cursor.fetchall()  # 查询一条数据
print(data)

cursor.close()  # 关闭游标
conn.close()  # 关闭连接

1.3、sqlite3

sqlite3 是 Python 标准库的一部分，用于操作 SQLite 数据库。

import sqlite3

connection = sqlite3.connect('example.db')

try:
    cursor = connection.cursor()
    cursor.execute("SELECT * FROM mytable")
    result = cursor.fetchall()
    for row in result:
        print(row)
finally:
    connection.close()

1.4、Pandas

Pandas 是一个强大的数据分析库，它提供了 read_sql 函数，用于从 SQL 数据库中读取数据并将其加载到 DataFrame 中。read_sql 函数可以与多种数据库驱动程序一起使用，如 SQLite、MySQL、PostgreSQL 等。以下是一些使用 pandas.read_sql 的示例：
要连接到 MySQL 数据库，可以使用 pymysql 或 mysql.connector 驱动程序。

import pandas as pd
import pymysql

# 创建数据库连接
conn = pymysql.connect(
    host='localhost',
    user='user',
    password='password',
    database='database'
)

# 使用 read_sql 读取数据
df = pd.read_sql('SELECT * FROM mytable', conn)

# 打印 DataFrame
print(df)

# 关闭连接
conn.close()

二、Java操作数据库（从JDBC到Mybatis）

除了 JDBC 之外，Java 还提供了多种工具和框架，如Mybatis来简化和增强数据库操作。MyBatis 是一个半 ORM 框架，提供了 SQL 映射功能，允许开发者直接编写 SQL 语句，同时支持对象映射。

public interface UserMapper {
    @Select("SELECT * FROM users WHERE id = #{id}")
    User getUserById(int id);

    @Insert("INSERT INTO users(name) VALUES(#{name})")
    void insertUser(User user);
}

SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
try (SqlSession session = sqlSessionFactory.openSession()) {
    UserMapper mapper = session.getMapper(UserMapper.class);
    User user = mapper.getUserById(1);
    System.out.println(user.getName());
}

使用jdbc技术连接数据库需要经历一系列的步骤，先要加载驱动，然后获取连接，获取预处理对象，然后通过预处理对象进行查询，得到结果集。查询结束后还需要关闭连接。这一系列的过程都需要我们自己手动去实现，包括对结果集的遍历封对象，可见代码的冗余，这就需要一个工具类来帮我们做这一件事，下面开始介绍mybatis框架。

使用mybatis以后，生成的结果集不需要我们自己来封装对象，mybatis以经帮我们完成了，并且不需要我们来手动注册驱动以及获取连接。还有通过注解的方式我们不需要实现dao接口，就可以完成dao的查询功能，提高了编码是效率与准确性。

总的来说mybatis是基于jdbc而优于Jdbc，除上面所说的之外，MyBatis还对JDBC操作数据库做了一些别的优化： 提供了一级和二级缓存，提高了程序性能。mybatis使用动态SQL语句，提高了SQL维护。（此优势是基于XML配置）。

MyBatis-Plus LambdaQueryWrapper使用说明
mybatis与jdbc的区别