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: 以回调函数插入