pandas+sqlAlchemy操作数据库

依赖库

• pandas
• sqlalchemy
• pymysql

读取数据库

from sqlalchemy import create_engine
import pandas as pd
# 创建数据库连接对象
win_user = 'root'  # 数据库用户名
win_password = '123456'  # 数据库密码
win_host = 'localhost'  # 数据库ip地址
win_port=3306  # 端口号
win_test = test  # 数据库
engine = create_engine(f'mysql+pymysql://{win_user}:{win_password}@{win_host}:{win_port}/{win_test}', echo=True)

# sql语句
sql = 'SELECT * FFROM test'
# 通过pandas读取数据
data = pd.read_sql(sql,engine)

写入数据库

from sqlalchemy import create_engine
import pandas as pd
# 创建数据库连接对象
win_user = 'root'  # 数据库用户名
win_password = '123456'  # 数据库密码
win_host = 'localhost'  # 数据库ip地址
win_port=3306  # 端口号
win_test = test  # 数据库
engine = create_engine(f'mysql+pymysql://{win_user}:{win_password}@{win_host}:{win_port}/{win_test}', echo=True)

# sql语句
sql = 'SELECT * FFROM test'
# 通过pandas读取数据
data = pd.read_sql(sql,engine)
# 数据进行处理

# 处理好的数据写入到数据库
data.to_sql('表名', engine)  # 表不存在直接创建

参数

• read_sql()

pandas.read_sql(sql, 
            con, 
            index_col=None, 
            coerce_float=True, 
            params=None, 
            parse_dates=None, 
            columns=None, 
            chunksize=None)

sql: SQL命令字符串

con: 连接sql数据库的engine，一般可以用SQLalchemy或者pymysql之类的包建立

index_col: 选择某一列作为index

coerce_float: 将数字形式的字符串直接以float型读入

parse_dates: 将某一列日期型字符串转换为datetime型数据，与pd.to_datetime函数功能类似。可以直接提供需要转换的列名以默认的日期形式转换，也可以用字典的格式提供列名和转换的日期格式，比如{column_name: format string}（format string："%Y:%m:%H:%M:%S"）。

columns: 要选取的列。一般没啥用，因为在sql命令里面一般就指定要选择的列

chunksize：如果提供了一个整数值，那么就会返回一个generator，每次输出的行数就是提供的值的大小。

• to_sql()

data.to_sql(name,
        con,
        schema=None,
        if_exists="fail",
        index=True,
        index_label=None,
        chunksize=None,
        dtype=None,
        method=None)

name: 要写入表的名字

con: 创建数据库连接的对象。

schema: 用于创建数据库对象，基本上都是使用默认值。

if_exists：如果表存在怎么办?

• fail：抛出ValueError异常、
• replace：在插入数据之前删除表。注意不是仅删除数据，是删除原来的表，重新建表.
• append：插入新数据。如果有主键，要避免主键冲突；看清表的格式，DataFrame的columns与表的columns是对应的；DF的index默认是作为一列数据的，也就是说默认会写入数据库的，

index: 将索引作为一列写入数据库，默认为True,也就是说默认DF的索引是要写入数据库的，index_label为列名

index_label: 将索引写入数据库时的列名，默认为index；如果DF是多级索引，则index_label应为一个序列

chunksize: 批处理，每次处理多少条数据。默认全部，一般没啥用，除非数据量太大，明显感觉卡的时候可以分批处理。

dtype: 一个字典，指定列的数据类型。键是列的名字，值是sqlalchemy types或者sqlite3的字符串形式。如果是新建表，则需要指定类型，不然会以存储量最大类型作为默认类型。比如varchar类型就会成为text类型，空间资源浪费很多。如果是添加数据，则一般不需要规定该参数。

method：哪种类型的插入语句？

• None: 默认单行插入
• multi: 多行插入
• callable: 以回调函数插入