sql script:
DROP TABLE InsuranceMoney GO create table InsuranceMoney ( ID INT IDENTITY(1,1) PRIMARY KEY, InsuranceName nvarchar(50), InsuranceCost float, IMonth int ) go
# coding=utf-8
"""
SQLServerDAL.py
SQL Server 数据库操作
date 2023-06-13
edit: Geovin Du,geovindu, 涂聚文
ide: Visual Studio 2022
参考：https://learn.microsoft.com/zh-cn/sql/connect/python/pymssql/step-3-proof-of-concept-connecting-to-sql-using-pymssql?view=sql-server-ver16
"""
import os
import sys
from pathlib import Path
import re
import pymssql #sql server
import Insurance
class SQLclass(object):
"""
Sql server
"""
def select(self):
"""
查询所有记录
"""
conn = pymssql.connect(server='DESKTOP-NQK85G5\GEOVIN2008', user='sa', password='770214', database='Student')
cursor = conn.cursor()
cursor.execute('select * from InsuranceMoney;')
row = cursor.fetchone()
while row:
print(str(row[0]) + " " + str(row[1]) + " " + str(row[2]))
row = cursor.fetchone()
def insert(iobject):
"""
插入操作
param:iobject 输入保险类
"""
dubojd=Insurance.InsuranceMoney(iobject)
conn = pymssql.connect(server='DESKTOP-NQK85G5\GEOVIN2008', user='sa', password='770214', database='Student')
cursor = conn.cursor()
cursor.execute("insert into InsuranceMoney(InsuranceName,InsuranceCost,IMonth) OUTPUT INSERTED.ID VALUES ('{0}', {1}, {2})".format(dubojd.getInsuranceName, dubojd.getInsuranceCost,dubojd.getIMonth))
row = cursor.fetchone()
while row:
print("Inserted InsuranceMoney ID : " +str(row[0]))
row = cursor.fetchone()
conn.commit()
conn.close()
def insertStr(InsuranceName,InsuranceCost,IMonth):
"""
插入操作
param:InsuranceName
param:InsuranceCost
param:IMonth
"""
conn = pymssql.connect(server='DESKTOP-NQK85G5\GEOVIN2008', user='sa', password='770214', database='Student')
cursor = conn.cursor()
cursor.execute("insert into InsuranceMoney(InsuranceName,InsuranceCost,IMonth) OUTPUT INSERTED.ID VALUES('{0}',{1},{2})".format(InsuranceName, InsuranceCost,IMonth))
row = cursor.fetchone()
while row:
print("Inserted InsuranceMoney ID : " +str(row[0]))
row = cursor.fetchone()
conn.commit()
conn.close()
调用：
# coding=utf-8
"""
PythonAppReadExcel.py
edit： geovindu,Geovin Du,涂聚文 python 11
date 2023-06-13
保险类
ide: Visual Studio 2022
"""
import sys
import xlrd
import xlwt
import xlwings as xw
import xlsxwriter
import openpyxl as ws
import pandas as pd
import pandasql
from pandasql import sqldf
import os
import sys
from pathlib import Path
import re
import pyspark
from pyspark.sql.functions import expr
from pyspark.sql import Row
from pyspark.sql import SparkSession
import Insurance
import ReadExcelData
import pymssql
import SQLServerDAL
if __name__ == '__main__':
#https://www.digitalocean.com/community/tutorials/pandas-read_excel-reading-excel-file-in-python
#https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.convert_dtypes.html
#https://www.geeksforgeeks.org/args-kwargs-python/
insura=[]
objlist=[]
#excelcovert()
s = '1123*#$ 中abc国'
str = re.sub('[a-zA-Z0-9!#$%&\()*+,-./:;<=>?@，。?★、…【】《》？！^_`{|}~\s]+', "", s)
# 去除不可见字符
str = re.sub('[\001\002\003\004\005\006\007\x08\x09\x0a\x0b\x0c\x0d\x0e\x0f\x10\x11\x12\x13\x14\x15\x16\x17\x18\x19\x1a]+',"", str)
print(str)
phone = "2004-959-559 # 这是一个电话号码"
tt="1月缴纳明细（元）"
newtt=re.sub(r'月缴纳明细（元）',"",tt)
print(newtt)
# 删除注释
num = re.sub(r'#.*$', "", phone)
print("电话号码 : ", num)
xlspath1 = r'C:\Users\geovindu\Documents\Visual Studio 2022\Projects\PythonAppReadExcel\1月.xls'
xlspath2 = r'C:\Users\geovindu\Documents\Visual Studio 2022\Projects\PythonAppReadExcel\2月.xls'
xlspath3 = r'C:\Users\geovindu\Documents\Visual Studio 2022\Projects\PythonAppReadExcel\3月.xls'
xlspath4 = r'C:\Users\geovindu\Documents\Visual Studio 2022\Projects\PythonAppReadExcel\4月.xls'
xlspath5 = r'C:\Users\geovindu\Documents\Visual Studio 2022\Projects\PythonAppReadExcel\5月.xls'
xlspath6 = r'C:\Users\geovindu\Documents\Visual Studio 2022\Projects\PythonAppReadExcel\6月.xls'
xlspath7 = r'C:\Users\geovindu\Documents\Visual Studio 2022\Projects\PythonAppReadExcel\7月.xls'
xlspath8 = r'C:\Users\geovindu\Documents\Visual Studio 2022\Projects\PythonAppReadExcel\8月.xls'
xlspath9 = r'C:\Users\geovindu\Documents\Visual Studio 2022\Projects\PythonAppReadExcel\9月.xls'
xlspath10 = r'C:\Users\geovindu\Documents\Visual Studio 2022\Projects\PythonAppReadExcel\10月.xls'
xlspath11 = r'C:\Users\geovindu\Documents\Visual Studio 2022\Projects\PythonAppReadExcel\11月.xls'
xlspath12 = r'C:\Users\geovindu\Documents\Visual Studio 2022\Projects\PythonAppReadExcel\12月.xls'
dulist=[]
# 封装成类操作
dulist1 = ReadExcelData.ReadExcelData.ReadDataFile(xlspath1)
dulist2 = ReadExcelData.ReadExcelData.ReadDataFile(xlspath2)
dulist3 = ReadExcelData.ReadExcelData.ReadDataFile(xlspath3)
dulist4 = ReadExcelData.ReadExcelData.ReadDataFile(xlspath4)
dulist5 = ReadExcelData.ReadExcelData.ReadDataFile(xlspath5)
dulist6 = ReadExcelData.ReadExcelData.ReadDataFile(xlspath6)
dulist7 = ReadExcelData.ReadExcelData.ReadDataFile(xlspath7)
dulist8 = ReadExcelData.ReadExcelData.ReadDataFile(xlspath8)
dulist9 = ReadExcelData.ReadExcelData.ReadDataFile(xlspath9)
dulist10 = ReadExcelData.ReadExcelData.ReadDataFile(xlspath10)
dulist11 = ReadExcelData.ReadExcelData.ReadDataFile(xlspath11)
dulist12 = ReadExcelData.ReadExcelData.ReadDataFile(xlspath12)
'''
#dulist.append(dulist2)
for Insurance.InsuranceMoney in dulist1:
duobj=Insurance.InsuranceMoney
dulist.append(duobj)
#print(duobj)
for Insurance.InsuranceMoney in dulist2:
duobj=Insurance.InsuranceMoney
#print(duobj)
dulist.append(duobj)
for Insurance.InsuranceMoney in dulist3:
duobj=Insurance.InsuranceMoney
dulist.append(duobj)
#print(duobj)
for Insurance.InsuranceMoney in dulist4:
duobj=Insurance.InsuranceMoney
#print(duobj)
dulist.append(duobj)
for Insurance.InsuranceMoney in dulist5:
duobj=Insurance.InsuranceMoney
dulist.append(duobj)
#print(duobj)
for Insurance.InsuranceMoney in dulist6:
duobj=Insurance.InsuranceMoney
#print(duobj)
dulist.append(duobj)
for Insurance.InsuranceMoney in dulist7:
duobj=Insurance.InsuranceMoney
dulist.append(duobj)
#print(duobj)
for Insurance.InsuranceMoney in dulist8:
duobj=Insurance.InsuranceMoney
#print(duobj)
dulist.append(duobj)
for Insurance.InsuranceMoney in dulist9:
duobj=Insurance.InsuranceMoney
dulist.append(duobj)
#print(duobj)
for Insurance.InsuranceMoney in dulist10:
duobj=Insurance.InsuranceMoney
#print(duobj)
dulist.append(duobj)
for Insurance.InsuranceMoney in dulist11:
duobj=Insurance.InsuranceMoney
dulist.append(duobj)
#print(duobj)
for Insurance.InsuranceMoney in dulist12:
duobj=Insurance.InsuranceMoney
dulist.append(duobj)
#print(duobj)
for Insurance.InsuranceMoney in dulist:
duobj = Insurance.InsuranceMoney
print(duobj)
'''
spark = SparkSession.builder.getOrCreate()
print("geovindu,*************")
datalist=[]
# 查询某文件夹下的文件名
folderPath=Path(r'C:\\Users\\geovindu\\Documents\\Visual Studio 2022\\Projects\\PythonAppReadExcel\\')
fileList=folderPath.glob('*.xls')
for i in fileList:
stname=i.stem
print(stname)
# 查询文件夹下的文件 print(os.path.join(path, "User/Desktop", "file.txt"))
dufile=ReadExcelData.ReadExcelData.ReadFileName(folderPath,'xls')
for f in dufile:
fileurl=os.path.join(folderPath,f)
dulist1 = ReadExcelData.ReadExcelData.ReadDataFile(fileurl) # object is not callable 变量名称冲突的原因
for duobj in dulist1:
dulist.append(duobj)
print(os.path.join(folderPath,f))
ylsum=0 # 养老
llsum=0 #医疗
totalsum=0 #一年费用
for geovindu in dulist:
#duobj = Insurance.Insurance
print(geovindu)
name = geovindu.getInsuranceName()
duname = name.convert_dtypes()
# yname = duname['Unnamed: 2']
print(type(duname))
print("保险类型：", duname) # class 'pandas.core.series.Series
strname = pd.Series(duname).values[0]
coas1=geovindu.getInsuranceCost()
#coast = int(geovindu.getInsuranceCost())
coas =coas1.convert_dtypes()
coast=pd.Series(coas).values[0] #int(coas)
#print("casa",int(coas))
totalsum = totalsum + coast
if (strname == "养老"):
ylsum = ylsum + coast
if (strname == "医疗"):
llsum = llsum + coast
print("费用：", coast)
month = int(geovindu.getIMonth())
print("月份：", month)
datalist.append([strname,coast,month])
SQLServerDAL.SQLclass.insertStr(strname,coast,month) #插入数据库中
print("一年养老",ylsum)
print("一年医疗",llsum)
print("一年费用",totalsum)
#https: // pandas.pydata.org / pandas - docs / stable / reference / api / pandas.DataFrame.groupby.html
#导出数据生成EXCEL
dataf = pd.DataFrame(datalist,columns=['保险类型','交费金额','交费月份']) #增加列名称
dataf2=pd.DataFrame({"统计类型":["一年养老","一年医疗","一年费用"],"金额":[ylsum,llsum,totalsum]})
dataf.sort_values('交费月份', inplace=True) #指定列排序
#duda=dataf.groupby(by=["保险类型"], dropna=False).sum()
#print(duda)
#https://www.datacamp.com/tutorial/how-to-use-sql-in-pandas-using-pandasql-queries
#sdf = dataf.sqldf("select '保险类型','交费金额','交费月份' from dataf")
#sdf.head()
#print(sdf)
#交费用分份统计
print(sqldf('''SELECT 交费金额,交费月份 FROM dataf group by 交费月份 LIMIT 25'''))
staicmont=sqldf('''SELECT 交费金额,交费月份 FROM dataf group by 交费月份 LIMIT 25''')
#pySpark
# https://spark.apache.org/docs/latest/api/python/getting_started/quickstart_df.html
geovindudf = spark.createDataFrame(dataf)
#
#geovindudf.show()
geovindudf.printSchema()
geovindudf.createOrReplaceTempView("GeovinDu")
#spark.sql("SELECT * from GeovinDu").show() #有异常
#spark.read.csv('foo.csv', header=True).show()
#query_df = pyspark.SQLContext(f"SELECT * FROM dataf")
#duda=dataf.groupby(by=["保险类型"], dropna=False).sum()
#print(duda)
#https://www.datacamp.com/tutorial/how-to-use-sql-in-pandas-using-pandasql-queries
#交费用分份统计
#print(sqldf('''SELECT 交费金额,交费月份 FROM dataf group by 交费月份 LIMIT 25'''))
staicmonth=sqldf('''SELECT 交费金额,交费月份 FROM dataf group by 交费月份 LIMIT 25''')
with pd.ExcelWriter('geovindu.xlsx') as writer:
dataf.to_excel(writer, sheet_name='2023年保险费用详情',index=False)
dataf2.to_excel(writer, sheet_name='保险统计',index=False)
staicmont.to_excel(writer, sheet_name='月份统计', index=False)
