python笔记：后端MySQL数据库 - 从连接到写入数据，sqlalchemy vs pymysql和sql-connector-python_mysql-connector 和 pymysql

简单版：如何选择sqlalchemy、pymysql和sql-connector-python

mysql-connector-python、pymysql 和 sqlalchemy 是 Python 中常用的几种连接和操作 MySQL 数据库的库。

• 当写入dataframe的时候选择sqlalchemy，因为代码量小，符合pandas习惯；
• 当写入原始数据byrow的时候选择pymysql 或者sql-connector-python，因为相比 sqlalchemy 更加轻量，操作也更加简单。

一、连接MySQL

1. 使用mysql-connector-python

import mysql

DB_CONFIG = {
	'user': 'your_user_name', 
	'password': 'your_passward',
	'host': 'your_host',
	'port': your_port_number,   # 确保使用整数，例如3306
	'database': 'your_database_name',  # 在pymysql中还可以使用 'db' 作为键名
	'charset':'utf8mb4'	
} 
conn = mysql.connector.connect(**DB_CONFIG)
1
2
3
4
5
6
7
8
9
10
11

2. 使用pymysql

import pymysql

DB_CONFIG = {
	'user': 'your_user_name', 
	'password': 'your_passward',
	'host': 'your_host',
	'port': your_port_number,   # 确保使用整数，例如3306
	'database': 'your_database_name',  # 在pymysql中还可以使用 'db' 作为键名
	'charset':'utf8mb4'	
} 
conn = pymysql.connect(**DB_CONFIG)
1
2
3
4
5
6
7
8
9
10
11

3. 使用SQLAlchemy

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

DATABASE_URL = 'mysql+pymysql://your_user_name:your_password@your_host:your_port_number/your_database_name'  # 或替换为 mysql+mysqlconnector
engine = create_engine(DATABAASE_URL)
Session = sessionmaker(bind=engine)
session=Session()
1
2
3
4
5
6
7

当使用sqlalchemy连接MySQL时，需要创建一个engine对象，让后用它建立一个sessions。sqlalchemy 是一个 ORM（Object-relational Mapping）库，它使用高层次的抽象来描述和操作数据库对象，因此你将使用一个会话对象来进行数据库操作，而不是直接处理连接对象。
\n
需要注意的是，sqlalchemy 本身不是一个驱动程序库，它赖以连接 MySQL 的驱动是其他的驱动程序库，例如 pymysql 或 mysql-connector-python。可以在连接字符串（DATABASE_URI）中指定要使用的驱动程序库。

总的来说，mysql-connector-python 和 pymysql 在连接方式上非常相似，它们提供了直接与 MySQL 数据库服务器通信的功能。然而，sqlalchemy 则采用了一个不同的、面向对象的方法来连接和操作数据库。在实际应用中，我们可以根据对高级抽象、数据库无关和其他 ORM 特性的需求来选择使用哪个库。

二、创建database和table

1. 使用mysql-connector-python 或 使用pymysql

# 注意: conn此处使用的DB_CONFIG里不再传入`db`或者`database`
cursor = conn.cursor()

cursor.execute("CREATE DATABASE IF NOT EXISTS your_database")
cursor.execute("USE your_database")
cursor.execute("""
CREATE TABLE IF NOT EXISTS your_table (
	id INT AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(255) NOT NULL,
	age INT)
""")

cursor.close()
conn.closer()
1
2
3
4
5
6
7
8
9
10
11
12
13
14

2. 使用SQLAlchemy

from sqlalchemy.orm import declarative_base # 1.4之前是 from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine, Column, Integer, String
# 注意此处的database后缀加`?`符号
DATABASE_URI = 'mysql+pymysql://your_user_name:your_password@your_host:your_port_number/your_database_name?charset=utf8mb4'
engine = create_engine(DATABASE_URI)

Base = declarative_base()

# 定义表模型
class YourTable(Base):
	__tablename__ = 'your_table'
	id = Column(Integer, primary_key=True, autoincrement=True)
	name = Column(String(255), nullable=False)
	age = Column(Integer)

# 创建数据库（如果不存在） & 选择数据库
engine.execute("CREATE DATABASE IF NOT EXISTS your_database")
engine.execute("USE your_database")

# 创建表
Base.metadata.create_all(engine)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

SQLAlchemy 是一个 ORM 库，在使用时，需要首先定义一个表模型，它是一个继承自 Base（通过 declarative_base() 函数创建）的 Python 类。然后，在创建数据库和数据表时，SQLAlchemy 会自动将表模型转换为 SQL 语句。注意，在使用 SQLAlchemy 创建表时需要指定连接字符串中的数据库名称。

总的来说，mysql-connector-python 和 pymysql 在创建数据库和表的过程中相似，它们都处理原始的 SQL 查询，适合喜欢编写 SQL 语句的开发者。在 SQLAlchemy 中，创建过程更加抽象，面向对象，适合喜欢跨数据库兼容和使用 ORM 技术的开发者。

三、写入爬取的数据

例如有爬取的数据data = [('John', 30), ('Jane', 25), ('Mike', 35)]

1. 使用mysql-connector-python 或 使用pymysql

import pandas as pd

# 选择数据库
cursor.execute("USE your_database")

# 遍历数据，插入到表中
"""方法1. 直接写入table"""
for item in data:
	name, age = item
	cursor.execute("INSERT INTO your_table (name, age) VALUE (%s, %s)", (name, age))
	
"""方法2. 转换成dataframe再写入table"""
df = pd.DataFrame(data, columns=['name', 'age'])
for _, row in df.iterrows():
	cursor.execute("INSERT INTO your_table (name, age) VALUES (%s, %s)", (row['name'], row['age']))

# 确认更改
cursor.commit()

cursor.close()
conn.close()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

2. 使用SQLAlchemy

import pandas as pd

"""方法1. 直接写入table"""
# 遍历数据，插入到表中
for item in data:
	name, age = item
	new_entry = YourTable(name=name, age=age)  # YourTable是在第二步里自定义的class
	session.add(new_entry)

# 确认更改
session.commit()
session.close()

"""方法2. 转换成dataframe再写入table"""
df = pd.DataFrame(data, columns=['name', 'age'])
df.to_sql(name='your_table', con=engine, if_exists='append', index=False)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

mysql-connector-python 和 pymysql 本身并不支持 to_sql 方法，该方法是由 pandas 库提供的。

详细版：mysql-connector-python和pymysql和sqlalchemy各有什么特点和区别

mysql-connector-python

1. 官方库：mysql-connector-python 是 MySQL 官方提供的纯 Python 驱动程序，用于连接和操作 MySQL 数据库。
   支持原生协议：mysql-connector-python 使用纯 Python 的方式实现了 MySQL 协议，不需要依赖额外的 C 扩展库。
2. 稳定性：作为官方支持的库，mysql-connector-python 在功能、稳定性和文档方面都得到了很好的支持。
3. 跨平台：这个库可以与大多数 Python 发行版一起工作，支持多种操作系统。

pymysql

1. 社区库：pymysql 是一个由社区开发和维护的 Python MySQL 驱动程序库，使用纯 Python 编写，与 MySQL 服务器建立连接、发送查询和读取结果等。
2. 纯 Python：pymysql 完全采用 Python 编写，易于安装和部署，无需额外的 C 扩展。
3. 与 mysql-connector-python 功能相似，但速度较慢。
4. 此库具有良好的文档和大量的实例，方便开发人员快速入门。

SQLAlchemy

1. ORM：sqlalchemy 是一个 Python ORM（对象关系映射）库，为开发者提供了一种更高级别的抽象方式来操作数据库，而不需要编写原生的 SQL 查询。
2. 数据库无关：sqlalchemy 支持多种数据库引擎，如 MySQL、PostgreSQL、SQLite 等，可以在不同的数据库引擎之间无缝切换。
3. 高度灵活：sqlalchemy 提供了两种主要的 API 风格，即核心 API（低级别、类似于原生 SQL）和 ORM（高级别、面向对象）。
4. 丰富的功能：sqlalchemy 提供了许多功能，如事务管理、连接池、映射继承、查询组合等，有助于创建企业级应用程序。

总结：

当需要一个轻量级、易于使用的 MySQL 驱动程序库时，可以选择 mysql-connector-python 或 pymysql。
当需要一个功能丰富、灵活、多数据库支持的 ORM 库时，可以选择 sqlalchemy。
需要注意的是：sqlalchemy 本身不是一个驱动程序，但可以与其他驱动——如 mysql-connector-python、pymysql 或 psycopg2 等，无缝集成使用。

附注

- 不要忘记使用 conn.close() 关闭数据库连接，释放资源及对数据库的影响。

- SQL中float和decimal的区别(参考链接)

· float和decimal，分别是非标准数据类型和标准数据类型。
· 区别是，float等非标准类型、在DB中保存的是近似值，而decimal则是以字符串的形式保存数值。

- 关于MySQL格式的区别见<MYSQL（8.0版本及以上）- utf8mb3，utf8mb4 和utf8的含义和由来>