先上代码
import pandas as pd
from sqlalchemy import create_engine
def excel_to_DB(host='host', port='port', user='user', passwd='123456', db='dbname', path_excel='acu.xlsx',
table_name='obstacle_2d_00'):
"""
表数据存入DB
:param host: 数据库地址
:param port: 数据库端口
:param user: 数据库账号
:param passwd: 账号密码
:param db: 数据库名
:param path_excel: excel数据文件名
:param table_name: 存入数据库的表名
"""
try:
engine = create_engine(f'postgresql+psycopg2://{user}:{passwd}@{host}:{port}/{db}', encoding='utf8')
data_excel = pd.read_excel(path_excel, engine='openpyxl')
data_dataframe = pd.DataFrame(data_excel)
data_dataframe.to_sql(table_name, con=engine, if_exists='replace', index=False)
except:
raise ValueError(f"write data from {path_excel} failure !")
-
create_engine()函数连接数据库
postgresql+psycopg2://{user}:{passwd}@{host}:{port}/{db}'
postgresql是需要连接的数据库,psycopg2是连接数据库使用的第三方库,如果是连接mysql数据库,可以使用pymysql或者mysql。 -
pd.read_excel() 读取excel文件
pd.read_excel(io, sheetname=0, header=0, index_col=None, names=None, dtype=None)
数名 说明 io string ,表示文件路径 sheetname string,int,代表excel表内数据分表的位置,默认0 header int, sequence,表示某行数据作为列表。默认inger, 表示自动识别。 index_col int, sequence,False,索引列的位置,取值为sequence时代表多重索引。默认为None names int,sequence、False,索引列的位置,取值为sequence时代表多重索引。默认None dtype dict,代表写入的数据类型(列表为key,数据格式为values)。默认为None 如果读取的Pandas 读取超过 65536 行的 Excel 文件时(xls最多支持65536 行,256 列; xlsx 最多支持 1,048,576行,16,384列),会因为默认读取 Excel 文件的引擎xlrd只能读取65536 行,会出现数据丢失的问题。需要将pd.read_excel中的引擎设置为openpyxl (一个专门用来操作 .xlsx 格式文件的 Python 库,它对于最大行列数的支持和 .xlsx 文件所定义的最大行列数一致)
-
pd.to_sql()函数将dataframe数据写入数据库
data_dataframe.to_sql(table_name, con=engine, if_exists='replace', index=False)
参数 | |
name: | string SQL表的名称。 |
con: | sqlalchemy.engine.Engine或sqlite3.Connection 使用SQLAlchemy可以使用该库支持的任何数据库。为sqlite3.Connection对象提供了旧版支持。 |
schema: | string,optional 指定架构(如果数据库支持)。如果为None,请使用默认架构。 |
if_exists: | {‘fail’,‘replace’,‘append’},默认’fail’ 如果表已存在的情况如下, * fail:引发ValueError。 * replace:在插入新值之前删除表。 * append:将新值插入现有表。 |
index: | 布尔值,默认为True 将DataFrame索引写为列。使用index_label作为表中的列名。 |
index_label: | 字符串或序列,默认为None 索引列的列标签。如果给出None(默认)且 index为True,则使用索引名称。如果DataFrame使用MultiIndex,则应该给出一个sequence。 |
chunksize: | int,可选 行将一次批量写入的数量。默认情况下,所有行都将立即写入。 |
dtype: | dict,可选 指定列的数据类型。键应该是列名,值应该是SQLAlchemy类型,或sqlite3传统模式的字符串。 |