pg_app_db_func.py
2.3 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
#! /usr/bin/env python
# -*- coding: utf-8 -*-
# Date: 2020/7/12 16:27
# @Author:bxh
# @file: pg_db_func.py
import psycopg2
import datetime
# 获得连接
def GetConnect():
conn = False
try:
conn = psycopg2.connect(database="partner_test",
user="bianxinhua_user",
password="Bianxinhua_user#123",
host="101.37.68.23",
port="15432")
except Exception as err:
print("连接数据库失败，%s" % err)
return conn
#获取超级管理员账号
def get_partner_id_from_database(tbl):
db = GetConnect()
cursor = db.cursor()
# sql= "select id from " + '''"''' + tbl + '''"'''+ ' ' + 'order by id desc '
sql = '''select id from \"{}\" where account = '13459147023' '''.format(tbl)
# print(sql)
try:
cursor.execute(sql)
result = cursor.fetchone()
return result[0]
except Exception as err:
# 发生错误时回滚
print(err)
db.rollback()
db.close()
#累计实发订单
def get_cumulativeQuantity_from_database(tbl):
db = GetConnect()
cursor = db.cursor()
# sql= "select id from " + '''"''' + tbl + '''"'''+ ' ' + 'order by id desc '
sql = '''select COUNT(id) from \"{}\" where partner_id = {} and order_status in (2,3) '''.format(tbl,get_partner_id_from_database('partner_info'))
# print(sql)
try:
cursor.execute(sql)
result = cursor.fetchone()
return result[0]
except Exception as err:
# 发生错误时回滚
print(err)
db.rollback()
db.close()
#累计实发订单金额
def get_cumulativeMoney_from_database(tbl):
db = GetConnect()
cursor = db.cursor()
# sql= "select id from " + '''"''' + tbl + '''"'''+ ' ' + 'order by id desc '
sql = '''select sum(order_actual_amount) from \"{}\" where partner_id = {} and order_status in (2,3) '''.format(tbl,get_partner_id_from_database('partner_info'))
# print(sql)
try:
cursor.execute(sql)
result = cursor.fetchone()
return int(result[0])
except Exception as err:
# 发生错误时回滚
print(err)
db.rollback()
db.close()
# print(get_cumulativeMoney_from_database('order'))