使用Python操作MySQL的常见方式有以下几种：
- MySQL-python
MySQL-python 又叫MySQLdb，是Python最流行的连接MySQL一个驱动，但是只支持Python 2.x。 - mysqlclient
是MySQL-python的Fork版本，完全兼容MySQL-python，同时支持Python 3.x。如果希望使用原生SQL来操作数据库，那么比较适合使用该驱动。 - mysql-connector-python
mysql-connector-python是MySQL官方的纯Python驱动 - PyMySQL
PyMySQL是纯Python实现的驱动，速度不及MySQLdb，但安装方式简单，是Python 3.x环境中较为常用的一种方式。
这些方法对MySQL库的操作大同小异。都是通过执行“connect”操作，跟数据库相关信息的参数连接数据库。通过“execute”执行字符串中的SQL语句。对于查询操作返回的结果，可以使用类似“fetchone”、“fetchall”的语句进行读取。
下面以PyMySQL为例进行简单介绍。
PyMySQL对MySQL数据库的读写
pymysql.connect有很多参数，其中常用的包括
| 参数名 | 含义 |
|---|---|
| host | 数据库服务器所在的主机地址 |
| user | 登陆用户名 |
| password（passwd） | 登录用户密码 |
| database（db） | 使用的数据库，为空则为特定的数据库 |
| port | 使用的port端口，默认为3306 |
| charset | 链接字符集 |
conn = pymysql.connect(host='...',
port=3306,
user='*',
passwd='*',
charset = 'utf8'
))
connect的部分方法包括
| 方法名 | 含义 |
|---|---|
| begin | 开始事务 |
| commit | 提交事务 |
| rollback | 回滚事务 |
| show_warnings | 执行“SHOW WARNINGS”SQL语句 |
| select_db | 选择数据库 |
| cursor | 创建新的游标来执行语句 |
| close | 关闭数据库 |
数据查询
使用cursor()创建游标对象后可执行进一步sql语句，如
cursor = conn.cursor()
cursor.execute("show databases;")
cursor.execute("use database_name;")
cursor.execute("show tables;")
cursor.execute("select * from tables_name")
cursor的常用方法有
| 方法名 | 含义 |
|---|---|
| execute | 执行查询 |
| executemany | 在多数据上执行同一语句 |
| fetchone | 获取下一行 |
| fetchmany | 获取多行 |
| fetchall | 获取所有行 |
| scroll | 移动游标，有“relative”和“absolute” 两种模式 |
| close | 关闭游标 |
另外cursor的属性 rowcount 记录当前游标所在的行数。初始为-1，此时使用 fetchone 获取下一行得到的就是查询结果数据的第一行。
这里对 fetch 和 scroll 的使用一下说明。
当使用 fetchone 或 fetchmany 获取指定个数行数据后，游标会向下移动响应函数。这时执行fetchall方法，返回的是游标当前位置以后的所有数据。整个 cursor 的操作都是基于游标位置进行的。要想获得全部数据，要将游标置于初始位置。
使用scroll方法有两种模式：
# 两种移动模式
cur.scroll(0, mode='absolute') #绝对位置移动，0为起点。
cur.scroll(-1, mode='relative') #相对位置移动，像上移动一位。
其他属性及方法可详见于源码。
数据操纵
对于增删改操作，在执行cursor.execute后，要执行connect.commit()提交事务，才会在数据库中完成操作。当SQL执行失败后，需要对事务进行回滚connect.rollback()。
cur = conn.cursor()
sql_insert = "insert into ** values(*, * ,*)" # 新增
sql_update = "update ** set ** WHERE ** " # 修改
sql_delete = "delete from ** where ** " # 删除
try:
cur.execute(sql_insert)
cur.execute(sql_update)
cur.execute(sql_delete)
conn.commit() # 提交事务
except Exception as e:
conn.rollback() # 如果发生错误，回滚事务
finally:
cur.close() # 关闭游标
conn.close() # 关闭数据库
pandas对MySQL数据库的读写
pandas的最新版本，只支持保存到 sqlite 数据库。要保存数据到 MySQL，还需要使用SQLAlchemy工具。
QLAlchemy提供了SQL工具包及对象关系映射（ORM）工具。ORM（Object-Relational Mapping）对象关系映射技术，指的是把关系数据库的表结构映射到对象上，通过使用描述对象和数据库之间映射的元数据，将程序中的对象自动持久化到关系数据库中。
SQLAlchemy模块提供了create_engine()函数用来初始化数据库连接。用一个字符串表示连接信息：
#数据库类型+数据库驱动名称://用户名:口令@机器地址:端口号/数据库名
engine = create_engine('mysql+pymysql://user:passwd@localhost:3306/DataBase')
读写操作分别调用pandas的read_sql()、to_sql()函数即可
# 读
sql = '''
select * from datatable;
'''
# read_sql的两个参数: sql语句， 数据库连接
df = pd.read_sql(sql, engine)
# 直接得到DataFrame
print(df)
# 写
df = pd.read_csv("C://***.csv", sep=',')
# 将新建的DataFrame储存为MySQL中的数据表，且不保存DataFrame的index
df.to_sql('mydf', engine, index= False)
网友评论