python对MySQL数据库进行读写

数据分析师需要经常从数据库中取数据进行分析，MySQL数据库是较为常用的关系型数据库，python是目前火热的数据分析语言，在python中，利用第三方库直接对数据库进行操作，包括数据的读取和写入等，能极大提高数据分析师的工作效率。本文介绍利用PyMySQL和SQLAlchemy两个python库对MySQL数据库进行读写操作。

1 软件版本

• Win10 64bit
• Python 3.8
• PyCharm 2019.3.4
• PyMySQL 0.9.3
• SQLAlchemy 1.3.16

2 安装库

需要安装PyMySQL和SQLAlchemy两个库，在终端中用pip命令进行安装，如下：

pip install PyMySQL
pip install SQLAlchemy

3 PyMySQL读写数据库

脚本如下：

import pymysql
import pandas as pd

if __name__ == '__main__':
    # 创建数据库连接(需要修改)
    con = pymysql.connect(host='xxx',
                          port=3306,
                          user='xxx',
                          password='xxx',
                          db='xxx',
                          charset="utf8")
    try:
        # 创建游标(默认数据返回tuple,修改为dict)
        cur = con.cursor(cursor=pymysql.cursors.DictCursor)

        # 读取表
        get_sql = "select * from tb_newCity"  # sql语句
        cur.execute(get_sql)  # 执行sql语句
        get_df = pd.DataFrame(cur.fetchall())  # 获取结果转为dataframe
        print(get_df)

        # 创建表
        create_sql = 'create table if not exists new(id int,value double)'
        cur.execute(create_sql)

        # 写入表(数据库中必须存在该表)
        df = pd.DataFrame({'id': [1, 2], 'value': [12, 13]})
        insert_sql = 'insert into new (id,value) values (%s,%s)'  # %s占位符
        # 1.循环执行
        for i in range(df.__len__()):
            # 插入的数据类型需要与数据库中字段类型保持一致
            cur.execute(insert_sql, (int(df.iloc[i, 0]), float(df.iloc[i, 1])))
        # 2.批量执行
        cur.executemany(insert_sql, [df.iloc[0].to_list(), df.iloc[1].to_list()])

        # 提交所有执行命令
        con.commit()
        print('数据写入成功！')
        cur.close()  # 关闭游标
    except Exception as e:
        raise e
    finally:
        con.close()  # 关闭连接

注意点：

• 连接及游标：

1. 创建数据库连接时，需要根据实际情况修改主机host、端口port，用户名user，密码password，数据库db参数。
2. 端口port默认值是3306，设置charset为utf8，解决读取中文问题。
3. 默认游标返回的查询数据格式是tuple，通过修改游标类型可以控制返回数据格式。

• 读取表：

1. 用游标cur的execute()方法运行sql语句完成表的读取。
2. 调用游标cur的fetchall()方法可以获取全部的查询数据。此外，fetchone()方法获取第一条数据，fetchmany(n)方法获取前n条数据。

• 写入表：

1. 写入表时，需要保证数据库中存在该表，可以先创建该表。
2. 用sql语句完成写入表的操作，sql语句中可以先用%s表示占位符，在执行语句中再用具体值替换。
3. pandas的dataframe类型数据写入数据库中，可以用循环execute()或采用executemany()实现。
4. 写入数据库中的数据必须与数据库中定义的字段类型保持一致，不一致时可以进行转换。
5. 创建表和写入表操作，需要最后用commit()方法提交，才算完成操作。

运行脚本，输出如下：

4 SQLAlchemy读写数据库

脚本如下：

from sqlalchemy import create_engine
import pandas as pd

if __name__ == '__main__':
    try:
        # 创建数据库引擎
        con = create_engine("mysql+pymysql://user:password@host:port/db")
        # 读取表
        sql = 'select * from t_json'  # sql语句
        get_df = pd.read_sql_query(sql, con)  # 结果为dataframe
        print(get_df)

        # 写入表
        df = pd.DataFrame({'id': [1, 2], 'value': [12, 13]})
        df.to_sql('new', con, if_exists='append', index=False) 
        print('数据写入成功！')
    except Exception as e: 
        raise e

注意点：

1. 创建数据库引擎con时，需要根据实际情况修改主机host、端口port，用户名user，密码password，数据库db参数。

2. 调用pandas的read_sql_query方法运行sql语句完成表的读取。

3. pandas的dataframe对象可以直接写入数据库，调用to_sql方法即可，数据库中无该表时，会自动创建表，无需先建表。其中的if_exists参数控制写入行为，具体解释如下：

   if_exists	参数解释
   fail	存在表，则报错
   replace	存在表，则替换原表
   append	存在表，则在原表行末追加

运行脚本，输出如下：

5 常见报错

• Access denied for user…
  原因：MySQL配置参数出错
  解决办法：检查user,password,host,port,db等参数的设置，用其他工具测试mysql连接是否成功，做对比检查。

• pymysql.err.InterfaceError…
  
  原因：使用PyMySQL时，关闭了创建的连接，对游标操作产生
  解决办法：重新创建连接，游标，再对游标进行操作。

• AtrributeError: numpy.int64 object has…
  
  原因：使用PyMySQL时，python里定义的数据类型mysql不支持
  解决办法：对数据进行类型转换，参考第三章的脚本。

6 总结

PyMySQL和SQLAlchemy是两个常见的python操作MySQL工具，但从数据分析角度出发，数据分析师基本的数据库操作就是读写。就数据库的读写方面而言，SQLAlchemy库的优势更明显，操作简单，取与pandas的dataframe之间的转换更为容易，python做数据分析推荐用SQLAlchemy库去完成MySQL的读写操作。