- 通过pandas读取excel文件，并将读取的内容写入sqlite3数据库
import pandas,sqlite3,os
from sqlalchemy import create_engine
def exlToSql(excel_file,db_file):
result = pandas.read_excel(excel_file) #读取excel内容
conn = sqlite3.connect(db_file) #创建数据库
result.to_sql(db_file,con=conn,if_exists='replace',index=False) #将excel内容写入到数据库
excel_file = r'D:\abc.xlsx'
db_file = os.path.join(os.path.dirname(__file__),'test.db')
exlToSql(excel_file,db_file)
可以通过SQLiteStudio来连接数据库验证数据是否导入成功
- 读取数据库表的内容，将内容写入excel
import sqlite3,os
import pandas as pd
def get_data(db_file):
conn = sqlite3.connect(db_file)
cursor = conn.cursor()
cursor.execute('SELECT name FROM sqlite_master where type = "table" ')
table_name = cursor.fetchall()
cursor.execute(f'SELECT * from {table_name[0][0]}')
global result
result = cursor.fetchall()
conn.close()
def export_excel(excel_file):
rs = pd.DataFrame(result,columns=("c1","c1","cn")) #用获取的数据库数据创建DataFrame并设置字段名
rs.to_excel(excel_file,sheet_name='abc',index=False,header=True) #导出数据到excel文件
db_file = r'D:\test1.db'
excel_file = r'D:\one.xlsx'
if __name__ == '__main__':
get_data(db_file)
export_excel(excel_file)
除了可以通过pandas导出数据到excel外，我们也可以用openpyxl来完成这个步骤
from openpyxl import Workbook
def write_excel(excel_file):
wb = Workbook()
ws = wb.active
ws.title = 'abc'
for i in result:
ws.append(i)
ws.save(excel_file)
excel_file = os.path.join(os.path.dirname(__file__),'two.xlsx')
write_excel(excel_file)
