Code tree reorganized; older implemenatations act as a start point

1 files changed, 121 insertions, 0 deletions

diff --git a/python/readmysql.py b/python/readmysql.py
new file mode 100644
index 0000000..a3cd31f
--- /dev/null
+++ b/python/readmysql.py
@@ -0,0 +1,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
+'''