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
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
|
## pip3 install mysql-connector-python
import mysql.connector as mysql
# enter your server IP address/domain name
HOST = "127.0.0.1" # or "domain.com"
# database name, if you want just to connect to MySQL server, leave it empty
DATABASE = "dev_pythia_db"
# this is the user you create
USER = "pythia_db_user_dev" ## "pythia_db_user_dev@cloudsqlproxy~35.203.252.44"
# user password
PASSWORD = "VnEP0eysjiXDHcfM"
# connect to MySQL server
_dbc = mysql.connect(host=HOST, database=DATABASE, user=USER, password=PASSWORD)
print("Connected to:", _dbc.get_server_info())
# enter your code here!
def get_data_from_db(cursor, sql):
output = []
cursor.execute(sql)
row = cursor.fetchone()
while row is not None:
row_to_return = row.decode('utf-8') if isinstance(row, bytearray) else row
output.append(row_to_return)
row = cursor.fetchone()
return output
cursor_ = _dbc.cursor()
#### cursor_.execute('''
#### SELECT count(dop.order_id) as FREQuency,
#### dop.product_id as product_id,
#### pb.brand_name,
#### pl.barcode, pl.skl_code, pl.eys_code,
#### IF( pd.description IS NOT NULL , pd.description , pl.product_description) AS product_description
#### FROM delivery_orders_products AS dop
#### LEFT JOIN delivery_orders AS do ON dop.order_id = do.id
#### LEFT JOIN product_list AS pl ON dop.product_id = pl.eys_code
#### LEFT JOIN product_list AS replacement ON dop.replacement_for = replacement.eys_code
#### LEFT JOIN product_details AS pd ON pl.eys_code = pd.eys_code
#### INNER JOIN product_brands pb ON pl.brand_id = pb.id
#### GROUP BY dop.product_id
#### ORDER BY FREQuency DESC
#### ''')
####
#### results = cursor_.fetchall()
####
#### for rec in results :
#### print(rec)
sqlq = '''
SELECT count(dop.order_id) as FREQuency,
dop.product_id as product_id,
pb.brand_name,
pl.barcode, pl.skl_code, pl.eys_code,
IF( pd.description IS NOT NULL , pd.description , pl.product_description) AS product_description
FROM delivery_orders_products AS dop
LEFT JOIN delivery_orders AS do ON dop.order_id = do.id
LEFT JOIN product_list AS pl ON dop.product_id = pl.eys_code
LEFT JOIN product_list AS replacement ON dop.replacement_for = replacement.eys_code
LEFT JOIN product_details AS pd ON pl.eys_code = pd.eys_code
INNER JOIN product_brands pb ON pl.brand_id = pb.id
GROUP BY dop.product_id
ORDER BY FREQuency DESC
'''
results = get_data_from_db(cursor_, sqlq)
for r in results :
if r[1] in [1176465, 1430400, 1220273] :
print(r[1], r[6].split())
## prepare cloud-sql-proxy
## ---
## * install cloud-sql-proxy
## : sudo wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O /usr/local/cloud_sql_proxy
## : sudo chmod +x /usr/local/cloud_sql_proxy
##
## * prepare/export/publish/copy credentials ...
## : sudo cp /some/path/to/cloudsqlproxy.json /usr/local
##
## * finaly run the database instance
## : /usr/local/cloud_sql_proxy -instances=pythia-251711:europe-west4:pythia-db-eu=tcp:3306 -credential_file=cloudsqlproxy.json
## Queries
## ---
'''
-- ORDERS PER PRODUCT
SELECT count(dop.order_id) as FREQuency,
dop.product_id as product_id,
pb.brand_name,
pl.barcode, pl.skl_code, pl.eys_code,
IF( pd.description IS NOT NULL , pd.description , pl.product_description) AS product_description,
pcs4.description AS productGroup
FROM delivery_orders_products AS dop
LEFT JOIN delivery_orders AS do ON dop.order_id = do.id
LEFT JOIN product_list AS pl ON dop.product_id = pl.eys_code
LEFT JOIN product_list AS replacement ON dop.replacement_for = replacement.eys_code
LEFT JOIN product_details AS pd ON pl.eys_code = pd.eys_code
LEFT JOIN product_categories_sap_4 pcs4 ON pcs4.id = pl.product_category_sap_4
INNER JOIN product_brands pb ON pl.brand_id = pb.id
GROUP BY dop.product_id
ORDER BY FREQuency DESC
'''
|
