extended-sql/_generated.py at main · salamaaya/extended-sql · GitHub

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
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96

import os
import psycopg2
import psycopg2.extras
import tabulate
from dotenv import load_dotenv
import sys

# do not edit this file, it is generated by generator.py

def update(mf_struct, gv, cur):
    for f in list(mf_struct.keys()):
        if gv in f:
            if "count" in f:
                mf_struct[f] += 1
            if "max" in f:
                mf_struct[f] = max(mf_struct[f], cur)
            if "min" in f:
                mf_struct[f] = min(mf_struct[f], cur)
            if "avg" in f:
                if "count" not in mf_struct:
                    mf_struct['count'] = 1
                    mf_struct[f] = cur
                else:
                    mf_struct['count'] += 1
                    mf_struct[f] = (mf_struct[f] * (mf_struct['count']-1) + cur) / mf_struct['count']
            if "sum" in f:
                mf_struct[f] += cur

def query():
    load_dotenv()

    user = os.getenv('USER')
    password = os.getenv('PASSWORD')
    dbname = os.getenv('DBNAME')

    conn = psycopg2.connect("dbname="+dbname+" user="+user+" password="+password,
                            cursor_factory=psycopg2.extras.DictCursor)
    cur = conn.cursor()
    cur.execute("SELECT * FROM sales")
    rows = cur.fetchall()

    if not rows:
        print("No data found.")
        return ""


    _global = []

    mf_structs = {}
    for row in rows:
        if row['cust'] not in mf_structs:

            mf_struct = {}
            mf_struct['count_1_quant'] = 0
            mf_struct['sum_2_quant'] = 0
            mf_struct['max_3_quant'] = 0
            mf_structs[row['cust']] = mf_struct


    for row in rows:
        if row['state'] == 'NY':
            if row['cust'] in mf_structs:
                update(mf_structs[row['cust']], '1', row['quant'])

    for row in rows:
        if row['state'] == 'NJ':
            if row['cust'] in mf_structs:
                update(mf_structs[row['cust']], '2', row['quant'])

    for row in rows:
        if row['state'] == 'CT':
            if row['cust'] in mf_structs:
                update(mf_structs[row['cust']], '3', row['quant'])

    for mf_struct in list(mf_structs.values()):
        mf_struct.pop('count', None)

    #print(mf_structs)

    # output mf_structs (append to _global)
    for key, mf_struct in mf_structs.items():
        row_dict = {'cust': key}
        for f in mf_struct.keys():
            row_dict[f] = mf_struct[f]
        _global.append(row_dict)

    return tabulate.tabulate(_global,
                        headers="keys", tablefmt="psql")

def main():
    print(query())

if "__main__" == __name__:
    main()