在python中,虽然pandas也能进行数据操作,但可读性显然比不上sql语句;考虑到python自带sqlite,使用sqlite操作数据也就成为一种方便的选择。
要存入数据库的是一个excel,名称为 美国家庭收入中位数平均数.xlsx,为真实数据,单位为美元。内有3列,全是数字,分别代表年份 、 美国家庭住户年收入中位数、,美国家庭住户年收入平均数。
数据如下:
2008 | 61,521.00 | 79,634.00 |
---|---|---|
2009 | 60,088.00 | 78,538.00 |
2010 | 60,236.00 | 78,180.00 |
2011 | 60,974.00 | 81,007.00 |
2012 | 62,241.00 | 82,843.00 |
2013 | 65,471.00 | 87,671.00 |
2014 | 66,632.00 | 88,765.00 |
2015 | 70,697.00 | 92,673.00 |
2016 | 72,707.00 | 97,357.00 |
2017 | 75,938.00 | 100,400.00 |
处理数据的思路:先用openpyxl包读取数据,然后将数据按行插入sqlite(其实这样不大方便,如果将dataframe整个存进去不更好吗?)
代码如下:
#!/usr/bin/env python
# -*- coding:utf-8 -*-
# author: codeuniverse time:2020/10/06
# 参考资料
# https://blog.csdn.net/weixin_43894266/article/details/86821437
# https://docs.python.org/zh-cn/2/library/sqlite3.html
import sqlite3
import openpyxl
# 从excel中读取数据,注意openpyxl只能读取xslx不能读取xls
# 从Excel表格中读入数据, 并写入book中进行存储
book = openpyxl.load_workbook(r'....\美国家庭收入中位数平均数.xlsx')
#获取excel文件当前表格
sheet = book.active
#数据库操作
# 新建表格
con = sqlite3.connect(r"....\USA历年收入2021.db")
# 创建游标对象
cursor = con.cursor()
#创建1个名为USAsalary的表,内有3列,NowYear作为主键,美国家庭住户年收入中位数,美国家庭住户年收入平均数,数据类型均为整数
sql = '''
create table if not exists USAsalary
(
NowYear integer primary key autoincrement,
美国家庭住户年收入中位数 integer not null,
美国家庭住户年收入平均数 integer not null
);
'''
#执行创建表格
cursor.execute(sql)
# 插入一行数据
cursor.execute("INSERT INTO USAsalary VALUES (2032,100,35)")
# 插入多行数据
data_truck = '''INSERT INTO USAsalary(NowYear,美国家庭住户年收入中位数,美国家庭住户年收入平均数) VALUES (?,?,?)'''
for row in sheet.iter_rows():
#使excel各行数据成为迭代器
#使每行中单元格成为迭代器
cargo = [cell.value for cell in row]
#写入一行数据到数据库中
cursor.execute(data_truck, cargo)
#查询2007年的收入
t = ('2007',)
cursor.execute('SELECT * FROM USAsalary WHERE NowYear=?', t)
print(cursor.fetchone())
#提交更改
con.commit()
#提交后关闭连接
con.close()