<jupyter_start><jupyter_text>ETL ( Extract, Transform, Load)ETL generically refers to the process of taking some data from source systems, (such as your CRM, ERP, or Marketing stack) transforming said data, and then loading it to some end system / application. **Extract**Taking data from a source system- Building pipelines to pull data from some source system(s) - Considerations around volume, speed (performance), cost**Transform**Manipulating Data for some end purpose, common transformations can include:- Aggregation - Applying logic or calculations- Joins, sorts, or pivots- Applying data validation**Load**Loading or sending the data to some end system or application- Output to a data warehouse- Generating  flat files- Updating / Creating tables in a database ETL is typically the domain of a data engineer, since there are typically considerations around volume, speed, and cost of shuttling data around a large enterprise systems. However, today you are typically seeing the rise of various developer positions that typically deal with creating automation around smaller volumes of data / non-critical systems.![ETL](https://www.altexsoft.com/media/2019/06/word-image-29.png)  Before Connecting to the DB Let's talk a little a bit about credentials in scriptsItems to be aware of with data connections in scripts:- Do not store plain text passwords in scripts!- Preferablly there should be some sort of encryption on credentials as you pass them- Its generally a good practice to keep credentialing values in a seperate file to be referenced by your scripts How do we do this?Network security, encryption, and best security practices are a entire domain within computer science. We will not be able to cover every aspect of it that you may need to know to optimally protect your systems or connections.What I'll attempt to do here is show you a few techniques that you can use to make yourself more defensible and keep your credential information from being stored directly in your scripts.  Building a JSON to push and pull credential information<jupyter_code>#Creating some token to reuse in scripts so you don't need to store credentials / sensitive info in the script
import json 

#example token created with a dictionary
example_token = {'key1':'value1','key2':'value2','key3':'value3'}

#saving that token(dictionary) as a json file
example = json.dumps(example_token)
# f = open("example_token.json","w")
# f.write(example)
# f.close()
#loading a json token back into memory
with open('example_token.json', "r") as file:
    token = json.load(file)

#call the value of a particular key
print(token['key1'])<jupyter_output><empty_output><jupyter_text>Lets Build our CredentialWhat do we need in order to establish a connection to a MS based DB?- Host name (Name of the machine the DB is on)- User name - Password (***NOT*** *IN PLAIN TEXT*)- DatabaseSo how do we get our password into the credential without adding it in plain text?ENTER - Cryptography!<jupyter_code>from cryptography.fernet import Fernet

#generate a key that will allow you to encrypt / decrypt your password
key = Fernet.generate_key()

print(f'Your key is: {key}') # we'll store this in a seperate key file to decrypt our password

#create a cipher suite that will allow for encryption

#prefix string with b to pass it as bytecode
pword = b'<PASSWORD>' 

cipher_suite = Fernet(key) # used to encrypt / decrypt the password

ciphered_text = cipher_suite.encrypt(pword) #we'll store this in our credential file
print('')
print(f'Your encrypted password: {ciphered_text}')


#decrypt password 
unciphered_text = cipher_suite.decrypt(ciphered_text)
print('')
print(f'You password after decription: {unciphered_text}')
#lets create our credential to connect

#example token created with a dictionary
sql_token = {'server':r'py-please.database.windows.net',
             'user':r'py-please',
             'password':r'<PASSWORD>', # put the ciphered password here
             'database':r'py_please'}

#saving that token(dictionary) as a json file
sql = json.dumps(sql_token)
f = open("sql_token.json","w")
f.write(sql)
f.close()

#do the same thing to create a key file!<jupyter_output><empty_output><jupyter_text>SQL ALCHEMY / PYODBC<jupyter_code>import json
#loading a json token back into memory
with open(r'../../sql_token_real.json', "r") as file:
    token = json.load(file)
    
with open(r'../../sql_key_real.json','r') as file:
    key = json.load(file)
import pyodbc
import pandas as pd<jupyter_output><empty_output><jupyter_text>odbc driverhttps://go.microsoft.com/fwlink/?linkid=2120137<jupyter_code>server = token['server']
database = token['database']
username = token['user']
password = <PASSWORD>.decrypt(str.encode(token['password'])).decode()
driver= '{ODBC Driver 17 for SQL Server}'
cnxn = pyodbc.connect('DRIVER='+driver+';SERVER='+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password)

stmt = r"""SELECT TOP(100) * FROM Owners"""

# Tables available for query:
# Owners
# Pets
# Procedures_Details
# Procedures_Histtory

df=pd.read_sql_query(stmt,cnxn)

df.head()
#Output our SQL query data pull
df.to_csv('cad_logging.csv')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09199614271938283, 'fraction_numerical': 0.005978784956605593, 'mean_word_length': 5.255729794933655, 'pattern_counts': {'":': 0, '<': 20, '<?xml version=': 0, '>': 20, 'https://': 2, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '417195', 'license': 'MIT', 'n_tokens_mistral': 1525, 'n_tokens_neox': 1447, 'n_words': 614, 'path': 'ETL & Programmatic Solutions/PYODBC.ipynb', 'repo_name': 'Jaunson/Analysis-DS_Techniques', 'size': '5203'}