# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3 (ipykernel)
#     language: python
#     name: python3
# ---

# ## Load Required Libraries
# <br> 
#
# **Standard Process** <br>
#  - ```pip install pandas```<br>
#  - ```pip install sqlalchemy```<br> 
#  
# **Parallel Process** (Not Recommended) <br>
#  - ```pip install dask```<br>
#  - ```pip install toolz```<br>
#  
# **SQL Server Driver** <br>
#  - ```pip install sqlalchemy-pytds```
#  - ```pip install python-tds```
#  - or ```pip install pymssql```
#  - or ```pip install pyodbc```<br>
#  
# **MySQL Driver** <br>
#  - ```pip install pymysql```<br>
#  
# **Google's BigQuery Driver** <br>
#  - ```pip install google-cloud-bigquery```<br>
#  - ```pip install google-cloud-bigquery-storage```<br>
#  - ```pip install pybigquery```<br>
#  - ```pip install pandas-gbq```<br>
#  - ```pip install pyarrow```<br>
#  
# **PostGreSQL Driver** <br>
#  - ```pip install psycopg2```<br>
#  
# **SQLite Driver** <br>
#  - I'm already forgot =_="
#  
# # Article about this notebook
#  - https://faun.pub/quick-etl-with-python-part-2-modify-and-upsert-table-into-sql-a192a6b81ad4

# +
## Load Password from my PC
import os, re,json
import pandas as pd

with open('setting.txt','r') as f:
    setting = json.load(f)
# -

# # Working with lazy_SQL 
# Change from da_tran_SQL since 0.3.11

from py_topping.data_connection.database import lazy_SQL

# ## SQL Server (or MS SQL)

set_mssql = setting['MSSQL']
print(set_mssql['type'])

# ### Create connection to server

mssql = lazy_SQL(sql_type = 'MSSQL', #set_mssql['type'], #
                host_name = '{YOUR HOST NAME or ID Address}', #set_mssql['host'], #
                database_name = '{Your Database Name}', #set_mssql['database'], #
                user = '{Your Username}', #set_mssql['user'], #
                password = '{<PASSWORD>}') #set_mssql['password']) #

# for SQL Server the <b>sql_type must == "MSSQL" (ignore case sensitive in version >= 0.1.3)</b>
#   
#     
# by Default, the class will using <span style="color:red">**pytds**</span>. as driver and port 1433<br>
# **change from pymssql to pytds in version >= 0.3.16*
#     
# But you could also change them if you want like below

mssql = lazy_SQL(sql_type = 'MSSQL', #set_mssql['type'],
                host_name = '{YOUR HOST NAME or ID Address}', #set_mssql['host'],
                database_name = '{Your Database Name}', #set_mssql['database'],
                user = '{Your Username}', #set_mssql['user'],
                password = '{<PASSWORD>}',#set_mssql['password'])
                port = '1433',
                driver = 'pymssql')

# **from version 0.3.13**<br>
# You can mute with parameter "mute"

mssql = lazy_SQL(sql_type = 'MSSQL', #set_mssql['type'],
                host_name = '{YOUR HOST NAME or ID Address}', #set_mssql['host'],
                database_name = '{Your Database Name}', #set_mssql['database'],
                user = '{Your Username}', #set_mssql['user'],
                password = '{Your Password}',#set_mssql['password'])
                port = '1433',
                driver = 'pymssql',
                mute = True )

# **from version >= 0.2.2**<br>
# You can add more parameter in class by passing to parameter

mssql = lazy_SQL(sql_type = 'MSSQL', #set_mssql['type'],
                host_name = '{YOUR HOST NAME or ID Address}', #set_mssql['host'],
                database_name = '{Your Database Name}', #set_mssql['database'],
                user = '{Your Username}', #set_mssql['user'],
                password = '{<PASSWORD>}',#set_mssql['password'])
                port = '1433',
                driver = 'pyodbc', 
                parameter = 'driver=SQL+Server')

# **from version >= 0.1.9**<br>
# You could fine tune insert's speed by adjust these parameters into the lazy_SQL class :
# - chunksize, chunksize parameter in pandas' to_sql method (Default Value 150)
# - partition_size,  dataframe will divide into smaller size as partition_size before dump (Default Value 5,000)
# - parallel_dump, to activate parallel dump (Default Value False) **Risk, not recommend to use**
# - max_parallel, number of parellel dump execute pertime (Default Value 2)

mssql = lazy_SQL(sql_type = 'MSSQL', #set_mssql['type'],
                host_name = '{YOUR HOST NAME or ID Address}', #set_mssql['host'],
                database_name = '{Your Database Name}', #set_mssql['database'],
                user = '{Your Username}', #set_mssql['user'],
                password = '{<PASSWORD>}',#set_mssql['password'])
                port = '1433',
                driver = 'pymssql',
                chunksize = 150, 
                partition_size = 5000,
                parallel_dump = False,
                max_parallel = 2)


# The lazy_SQL class also has engine method which equal to engine in sqlalchemy
#
# Which I'm going to use it to set up sample environment that include,
#  * 2 dataframe
#  * table in mssql
#  * store procedure in mssql

# +
def setup_sample(class_in):
    test_table = 'unit_test_git'
    df1 = pd.DataFrame({'col1' : [1,2,3,4,5] , 'col2' : [1,1,2,2,3] 
                        ,'date' : pd.date_range('2020-10-10','2020-10-14') , 'col3' : [1,1,1,1,1]})
    new_df = pd.DataFrame({'col1' : [4,5,6,7,8] , 'col2' : [3,3,4,4,5] 
                        ,'date' : pd.date_range('2020-10-12','2020-10-16') , 'col3' : [2,2,2,2,2]})
    df1.to_sql(test_table, index = False, if_exists = 'replace', con = mssql.engine)

    sql_q = """ CREATE OR ALTER PROCEDURE unit_test_git_SP (@PARAM1 AS VARCHAR(100))
                AS
                BEGIN
                SELECT * FROM {} WHERE col1 = @PARAM1 
                END""".format(test_table)

    class_in.engine.execute(sql_q)

    return df1, new_df, test_table, 'unit_test_git_SP'

df1, new_df, table_name, sp = setup_sample(mssql)
pd.read_sql('unit_test_git', con = mssql.engine)
# -

# ### 1st Method : Read
#
# You could read any table or view from your database by use method "read"

mssql.read(table_name)

# You could also use pass SQL's "WHERE" statement into parameter "condition_in"
#
# to filter the result

mssql.read(table_name, condition_in = 'col1 <= 2')

# **from version>= 0.3.5**<br>
# You could also use select statement directly with "raw" = True

mssql.read("""select 'foo' as test_col""", raw = True)

# **from version >= 0.3.7**<br>
# You could select only some columns by pass list of columns in "columns_list"

mssql.read(table_name, columns_list = ['col1','date'])

# futher more, you could also use store procedure and pass dict parameter with read method

mssql.read(sp, SP = True, param = {'@PARAM1' : 3})

# You could select only unique value of selected column

# +
# Coming Soon...
# -

# You could group by and use aggregrate function 

# +
# Coming Soon...
# -

# ### 2nd Method : dump_whole 
#
# You could replace whole table with new dataframe with is method
#
# **Becareful with Columns' Type, I'm suggest to fix columns' type before using this method**

df1, new_df, table_name, sp = setup_sample(mssql)
mssql.read(table_name)

# +
mssql.dump_whole(new_df, table_name)

mssql.read(table_name)
# -

# By the way, **dump_whole will use columns and types as the New DataFrame**

# +
mssql.dump_whole(new_df.drop('col1', axis = 1) , table_name)

mssql.read(table_name).dtypes
# -

# In case that you would like to fixe original table's columns and types
#
# You could add parameter **fix_table = True**

# +
df1, new_df, table_name, sp = setup_sample(mssql)

mssql.engine.execute("""ALTER TABLE {} ALTER COLUMN col1 varchar(10)""".format(table_name))

mssql.read(table_name).dtypes

# +
mssql.dump_whole(new_df.drop('col1', axis = 1), table_name , fix_table = True) 

mssql.read(table_name)
# -

mssql.read(table_name).dtypes

# ### 3rd Method : dump_replace
#
# You could update the existing dataframe by using this method
#
# `self.dump_replace(df_in, table_name_in, list_key, math_logic = '', partition_delete = 100000, debug = False)**`
#
# **List of Parameter**
#  - **df_in** : DataFrame to dump into Table (Pandas DataFrame)
#  - **table_name_in** : Name of Table (Str)
#  - **list_key** : List of key columns (Str or List)
#  - **math_logic** : Use math logic to filter your table (Dict), default : ''
#  - **partition_delete** : Number of row delete per time (Int), default : 100,000 *Available 0.3.6*
#  - **debug** : To show SQL Query or Not (Boolean), default : False

df1, new_df, table_name, sp = setup_sample(mssql)
mssql.read(table_name)

new_df

# If col1 is the primary key for table and new_df
#
# we could update the existing key and add new key into table by using dump_replace 

mssql.dump_replace(new_df, table_name, list_key = ['col1'])
mssql.read(table_name)

# For table with multiple keys, we could just simple add more key into the parameter "list_key"

df1, new_df, table_name, sp = setup_sample(mssql)
mssql.read(table_name)

mssql.dump_replace(new_df, table_name, list_key = ['col1','col2'])
mssql.read(table_name)

# dump_replace also has math logic for some ETL process

df1, new_df, table_name, sp = setup_sample(mssql)

# +
mssql.dump_replace(new_df, table_name, list_key = ['date'], 
                   math_logic = {'date' : {'logic' : '>=', 'value' : new_df['date'].min().date(), 'type' : 'date'}} )

mssql.read(table_name)
# -

#  math logic also could use with num too

df1, new_df, table_name, sp = setup_sample(mssql)

# +
mssql.dump_replace(new_df, table_name, list_key = ['col1','date'], 
                   math_logic = {'col1' : {'logic' : '>=', 'value' : new_df['col1'].min(), 'type' : 'int'} ,
                                 'date' : {'logic' : '>=', 'value' : new_df['date'].min().date(), 'type' : 'date'}} )

mssql.read(table_name)
# -

# You could also combine math_logic and non logic key together

df1, new_df, table_name, sp = setup_sample(mssql)

# +
mssql.dump_replace(new_df, table_name, list_key = ['col1','col2'], 
                   math_logic = {'col1' : {'logic' : '>', 'value' : new_df['col1'].min(), 'type' : 'int'} } )

mssql.read(table_name)
# -

# Since there're many things going inside this function
#
# This Function also have boolean paramter "debug" to print what's going on inside

# +
mssql.dump_replace(new_df, table_name, list_key = ['col1','date'], 
                   math_logic = {'col1' : {'logic' : '>=', 'value' : new_df['col1'].min(), 'type' : 'int'} ,
                                 'date' : {'logic' : '>=', 'value' : new_df['date'].min().date(), 'type' : 'date'}}
                  ,debug = True)

mssql.read(table_name)
# -

# To be honest, I'd recommend you to use a text and good defined key.
#
# ***
#
# ### 4th Method : dump_new
#
# dump only non existing key into the table

# +
df1, new_df, table_name, sp = setup_sample(mssql)

mssql.read(table_name)

# +
mssql.dump_new(new_df, table_name, list_key = ['col1'])

mssql.read(table_name)
# -

# Just like dump_replace, you colud also use multiple key at once.

df1, new_df, table_name, sp = setup_sample(mssql)

# +
mssql.dump_new(new_df, table_name, list_key = ['col1','col2'])

mssql.read(table_name)
# -

# # MySQL

set_mysql = setting['MYSQL']
print(set_mysql['type'])

# ### Create connection to server

mysql = lazy_SQL(sql_type = 'MYSQL', #set_mysql['type'],
                host_name = '{YOUR HOST NAME or ID Address}', #set_mysql['host'],
                database_name = '{Your Database Name}', #set_mysql['database'],
                user = '{Your Username}', #set_mysql['user'],
                password = '{<PASSWORD>}')#set_mysql['password'])

# Same as SQL Server, For MySQL the <b>sql_type must == "MYSQL" (ignore case sensitive)</b>
# <br><br>
# by Default, the class will using pymysql as driver and port 3306
# <br><br>
# But you could also change them if you want like below

mysql = lazy_SQL(sql_type = 'MYSQL', #set_mysql['type'],
                host_name = '{YOUR HOST NAME or ID Address}', #set_mysql['host'],
                database_name = '{Your Database Name}', #set_mysql['database'],
                user = '{Your Username}', #set_mysql['user'],
                password = '{<PASSWORD>}',#set_mysql['password'],
                port = '3306',
                driver = 'pymysql')

# <b>Every Method and Function are the same as SQL Server.</b>

# # Google's BigQuery

set_bgq = setting['gbq']
print(set_bgq['type'])

# ### Create connection to server

gbq = lazy_SQL(sql_type = 'bigquery', #set_bgq['type'], #
                host_name = '{YOUR Project ID}', #set_bgq['project_id'], #
                database_name = '{Your Dataset Name}') #set_bgq['dataset']) #

# For GCP's BigQuery the <b>sql_type must == "BIGQUERY" (ignore case sensitive)</b>
# <br><br>
# Follow by Project ID and Dataset's name
# <br><br>
# Parameter's Names are not related because I never thought this function will be used with GCP's BigQuery in the place.
# <br><br>
# If you have credential json file, you could add files' path into the "credentials" parameter 

gbq = lazy_SQL(sql_type = 'bigquery', #set_bgq['type'],
                host_name = '{YOUR Project ID}', #set_bgq['project_id'], #
                database_name = '{Your Dataset Name}', #set_bgq['dataset'], #
                credentials_path = '{Path to Credentials file (JSON)}') #set_bgq['credentials_path']) #

# <b>Every Method and Function are the same as SQL Server.</b>
#
# Because BigQuery not automated data type for us like other SQL<br>
# SUGGEST TO **USE KEY COLUMNS TYPE AS GOOD FORMATTING TEXT BEFORE DUMP**

# # PostGreSQL

set_plsql = setting['POSTGRESQL']
print(set_plsql['type'])

# ### Create connection to server

plsql = lazy_SQL(sql_type = 'POSTGRESQL', #set_plsql['type'],
                host_name = '{YOUR HOST NAME or ID Address}', #set_plsql['host'],
                database_name = '{Your Database Name}', #set_plsql['database'],
                user = '{Your Username}', #set_plsql['user'],
                password = '{<PASSWORD>}')#set_plsql['password'])

# Same as SQL Server, For PostGreSQL the <b>sql_type must == "POSTGRESQL" (ignore case sensitive)</b>
# <br><br>
# by Default, the class will using pymysql as driver and port 5432
# <br><br>
# But you could also change them if you want like below

plsql = lazy_SQL(sql_type = 'POSTGRESQL', #set_plsql['type'],
                host_name = '{YOUR HOST NAME or ID Address}', #set_plsql['host'],
                database_name = '{Your Database Name}', #set_plsql['database'],
                user = '{Your Username}', #set_plsql['user'],
                password = '{<PASSWORD>}', #set_plsql['password'])
                port = '5432',
                driver = 'psycopg2')

# <b>Every Method and Function are the same as SQL Server.<br>
# Except PostGreSQL still can't use Store Procedure or Function yet</b>

# # SQLite

sqlite = lazy_SQL(sql_type = 'sqlite', 
                    host_name = '{path to database file}', #'test.db', 
                    database_name = '', 
                    user = '', 
                    password = '' )

# Same as SQL Server, For SQLite the sql_type must == "SQLITE" (ignore case sensitive)
#
# by Default, the class will using sqlite as driver
#  - host_name for SQLite is the path to your .db file
#  - database_name, user and password will not be read if you didn't address special driver
#  - chunksize for SQLite will reduce to 50 because of SQLite's limit
#
# Every Method and Function are the same as SQL Server.<br>
# **Don't forget that SQLite don't have Store Procedure or Datetime Type**

# # OracleSQL

# +
# Coming Soon...

StarcoderdataJupyterScriptsDedupFiltered