- 通過(guò)pandas讀取excel文件冈欢，并將讀取的內(nèi)容寫入sqlite3數(shù)據(jù)庫(kù)
import pandas,sqlite3,os
from sqlalchemy import create_engine
def exlToSql(excel_file,db_file):
result = pandas.read_excel(excel_file) #讀取excel內(nèi)容
conn = sqlite3.connect(db_file) #創(chuàng)建數(shù)據(jù)庫(kù)
result.to_sql(db_file,con=conn,if_exists='replace',index=False) #將excel內(nèi)容寫入到數(shù)據(jù)庫(kù)
excel_file = r'D:\abc.xlsx'
db_file = os.path.join(os.path.dirname(__file__),'test.db')
exlToSql(excel_file,db_file)
可以通過(guò)SQLiteStudio來(lái)連接數(shù)據(jù)庫(kù)驗(yàn)證數(shù)據(jù)是否導(dǎo)入成功
- 讀取數(shù)據(jù)庫(kù)表的內(nèi)容豆茫，將內(nèi)容寫入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")) #用獲取的數(shù)據(jù)庫(kù)數(shù)據(jù)創(chuàng)建DataFrame并設(shè)置字段名
rs.to_excel(excel_file,sheet_name='abc',index=False,header=True) #導(dǎo)出數(shù)據(jù)到excel文件
db_file = r'D:\test1.db'
excel_file = r'D:\one.xlsx'
if __name__ == '__main__':
get_data(db_file)
export_excel(excel_file)
除了可以通過(guò)pandas導(dǎo)出數(shù)據(jù)到excel外歹鱼，我們也可以用openpyxl來(lái)完成這個(gè)步驟
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)
