sqlite3模块读取数据库然后写入excel表格

sqlite3模块读取数据库然后写入excel表格

做个笔记：sqlite3模块读取数据库表内容，然后三个表'learning_logs_topic'，'learning_logs_entry'，'auth_user'分别读取并写入'wjj_topic.csv'，'wjj_entry.csv'，'wjj_users.csv'三个csv。然后用pandas的ExcelWriter，将三个csv写入一个excel中的三个sheet。这个写入三个sheet步骤待改进，有时间再研究。

# -*- encoding=UTF-8 -*-
__author__ = 'wjj1982'
__date__ = '2019/5/30 16:42'
__product__ = 'PyCharm'
__filename__ = 'sqlite3-get'

import sqlite3,csv,os
import pandas as pd

def write_csv(filename,rows):
    with open(filename,'w') as wf:
        writer = csv.writer(wf)
        for row in rows:
            writer.writerows([row])


def read(db_file, table_name):
    conn=sqlite3.connect(db_file)
    cursor=conn.cursor()
    cursor.execute('SELECT * from {}'.format(table_name))  #查询数据库中表里的所有数据
    values=cursor.fetchall()
    cursor.close()
    conn.close()
    return values


db_file1='D:\ProgramData\Anaconda3\envs\mydjango_env\db.sqlite3'
table_name1='learning_logs_topic'
table_name2 = 'learning_logs_entry'
table_name3 = 'auth_user'
file_name1 = 'wjj_topic.csv'
file_name2 = 'wjj_entry.csv'
file_name3 = 'wjj_users.csv'

value_topic = read(db_file1,table_name1)
value_entry = read(db_file1,table_name2)
value_users = read(db_file1,table_name3)

write_csv(file_name1,value_topic)
write_csv(file_name2,value_entry)
write_csv(file_name3,value_users)



# 创建一个输出文件
writer = pd.ExcelWriter('wjj.xlsx')

data = pd.read_csv('wjj_topic.csv',encoding='GBK')
data.to_excel(writer,'topic',index=False)


base = pd.read_csv('wjj_entry.csv',encoding='GBK')
base.to_excel(writer,'entry',index=False)


base1 = pd.read_csv('wjj_users.csv',encoding='GBK')
base1.to_excel(writer,'users',index=False)

#保存
writer.save()
writer.close()
os.remove(file_name1)
os.remove(file_name2)
os.remove(file_name3)