python查数据_python pymysql 数据查询

#!/usr/bin/env python3#-*- coding:utf-8 -*-

importpymysqlfrom datetime importdatetry:#连接数据库

conn =pymysql.connect(

host='******.com',

user= 'test',

password= 'test',

db= 'market_test',

charset= 'utf8')except:print("连接数据库失败")

exit(-1)

cur=conn.cursor()

timeStart= date(2019,8,2)

timeEnd= date(2019,8,16)print("日期：", timeStart,"~",timeEnd)#查询某个期间所有订单数(已支付+未支付)

sql_countAll = "select count(*) from record where createtime>'%s' and createtime

cur.execute(sql_countAll)

countAll=cur.fetchall()[0][0]print("订单数：",countAll)#查询某个期间已支付订单数

sql_countPay = "select count(*) from record where createtime>'%s' and createtime

cur.execute(sql_countPay)

countPay=cur.fetchall()[0][0]print("已支付订单数：", countPay)#查询某个期间的下单总额(已支付+未支付)

sql_amountAll = "select sum(amount) as total from record where createtime>'%s' and createtime

cur.execute(sql_amountAll)#获得的数值类型是decimal，需要转化为float进行运算，否则会报错

amountAll = float(cur.fetchall()[0][0])/100

print("消费金额：%.2f" %amountAll)#查询某个期间已支付的订单金额

sql_amountPay = "select sum(amount) as total from record where createtime>'%s' and createtime

cur.execute(sql_amountPay)#获得的数值类型是decimal，需要转化为float进行运算，否则会报错

amountPay = float(cur.fetchall()[0][0])/100

print("已支付消费金额：%.2f" %amountPay)#查询某个期间下单的用户数(已支付+未支付，用户去重)

sql_userCountPay = "select count(*) from record where createtime>'%s' and createtime

userCountPay=float(cur.execute(sql_userCountPay))if countPay==0:print("无支付用户")else:print("笔单价：%.2f" %(amountPay/countPay))if userCountPay ==0:print("无下单用户")else:print("客单价：%.2f" %(amountPay/userCountPay))

cur.close()

conn.close()#####################

'''结果：

日期： 2019-08-02 ~ 2019-08-16

订单数： 445

已支付订单数： 284

消费金额：147642.00

已支付消费金额：78025.00

笔单价：274.74

客单价：268.13'''

#####################