<jupyter_start><jupyter_text>![alt text](https://4.bp.blogspot.com/-gbL5nZDkpFQ/XScFYwoTEII/AAAAAAAAAGY/CcVb_HDLwvs2Brv5T4vSsUcz7O4r2Q79ACK4BGAYYCw/s1600/kk3-header00-beta.png)[<NAME>](http://www.linkedin.com/in/prithwis)  Install Pandas, MySQL client<jupyter_code>!apt install libmysqlclient-dev > /dev/null
!pip install mysqlclient > /dev/null
import pandas as pd
import MySQLdb<jupyter_output>WARNING: apt does not have a stable CLI interface. Use with caution in scripts.<jupyter_text>Set up Credentials Securely from GDrive<jupyter_code>from google.colab import drive
drive.mount('/content/drive')
#!ls /content/drive/'My Drive'/Praxis/WebCredentials
!cp /content/drive/'My Drive'/Praxis/WebCredentials/sqlCredentials_020221.py ./Credentials.py
# credential file for Prithwis Mukerjee
# this file needs to be uploaded into the VM
# https://drive.google.com/drive/u/0/folders/1-EKWXDjIO_wOrNR6bR0U9IXbcFh31AMT
from Credentials import hostName,userName,passWord,dbName

#for the sake of privacy
#the following credentials need to be stored in a text file called sqlCredentials.py
#in the format given below
#in the Colab VM

#otherwise, the values can be directly placed here

#hostName = 'remotemysql.com'   # uncomment this line and place your own credentials here
#userName = 'yourusername'      # uncomment this line and place your own credentials here
#passWord = '<PASSWORD>'      # uncomment this line and place your own credentials here
#dbName =  'yourdbname'         # uncomment this line and place your own credentials here

print(hostName,userName,dbName)<jupyter_output>remotemysql.com aMxFStsiZ2 aMxFStsiZ2<jupyter_text>Credentials visible in notebook<jupyter_code>#otherwise, the values can be directly placed here

#hostName = 'remotemysql.<EMAIL>'   # uncomment this line and place your own credentials here
#userName = 'yourusername'      # uncomment this line and place your own credentials here
#passWord = '<PASSWORD>'      # uncomment this line and place your own credentials here
#dbName =  'yourdbname'         # uncomment this line and place your own credentials here

print(hostName,userName,dbName)<jupyter_output>remotemysql.com aMxFStsiZ2 aMxFStsiZ2<jupyter_text>Test Connection<jupyter_code>DBConn = MySQLdb.connect(hostName,userName,passWord,dbName)
#df_mysql = pd.read_sql('select * from emp2;', con=con_mysql)  
df_mysql = pd.read_sql('show tables', con=DBConn)    
print ('loaded dataframe from MySQL. records:', len(df_mysql))
DBConn.close()
df_mysql<jupyter_output><empty_output><jupyter_text>Define Python functions for MySQL access<jupyter_code>#To run any non-SELECT SQL command
def runCMD (DDL):
    DBConn= MySQLdb.connect(hostName,userName,passWord,dbName)
    myCursor = DBConn.cursor()
    retcode = myCursor.execute(DDL) 
    print (retcode)
    DBConn.commit()
    DBConn.close()

#To run any SELECT SQL command
def runSELECT (CMD):
    DBConn= MySQLdb.connect(hostName,userName,passWord,dbName)
    df_mysql = pd.read_sql(CMD, con=DBConn)    
    DBConn.close()
    return df_mysql<jupyter_output><empty_output><jupyter_text>Create and Load Tables Table : Emp<jupyter_code>runCMD("DROP TABLE IF EXISTS Emp;")
runCMD("CREATE TABLE IF NOT EXISTS Emp ( \
  EmpID char(6)  NOT NULL, \
  LastName varchar(50) , \
  FirstName varchar(50) , \
  JobDesc varchar(50) , \
  JoinDate date NOT NULL, \
  Salary int(11) , \
  Comm float , \
  DeptID char(2) \
) ;")
runCMD("INSERT INTO Emp (EmpID, LastName, FirstName, JobDesc, JoinDate, Salary, Comm, DeptID) \
    VALUES \
	('742866', 'Bacchan', 'Amitabh', 'Executive', '2003-03-10', 50000, 0.1, '10'), \
	('349870', 'Mukherjee', 'Rani', 'Manager', '2005-05-04', 25000, 0.06, '40'), \
	('865477', 'Dikshit', 'Madhuri', 'Clerk', '2002-04-04', 10000, 0.02, '20'), \
	('239456', 'Khan', 'Shahrukh', 'Manager', '2004-01-03', 30000, 0.07, '20'), \
	('897889', 'Sehwag', 'Virender', 'Cus_Rep', '2005-01-02', 15000, 0.05, '20'), \
	('123980', 'Dhoni', 'Mahender', 'Clerk', '2004-10-09', 9000, 0.02, '40'), \
	('822134', 'Dravid', 'Rahul', 'Sr Manager', '2000-06-04', 40000, 0.08, '30'), \
	('997445', 'Dalmia', 'Jagmohan', 'Clerk', '2001-07-01', 12000, 0.02, '30'), \
	('989007', 'Ganguly', 'Sourav', 'Cus_Rep', '2002-01-01', 20000, 0.03, '40'), \
	('299034', 'Ganesan', 'Rekha', 'Director', '2002-10-10', 60000, 0.11, '10'), \
	('546223', 'Karthikeyan', 'Narayan', 'Secretary', '2005-12-04', 40000, 0.09, '10'), \
	('223112', 'Mirza', 'Sania', 'Cus_Rep', '2001-11-19', 25000, 0.04, '30');"
    )<jupyter_output>12<jupyter_text>Table : Dept<jupyter_code>runCMD("DROP TABLE IF EXISTS Dept;")
runCMD("CREATE TABLE Dept ( \
  DeptID char(2)  NOT NULL, \
  DeptName varchar(50) , \
  ManagerID char(6) , \
  Location varchar(50) \
 );")
runCMD("INSERT INTO Dept (DeptID, DeptName, ManagerID, Location) VALUES \
	('10', 'Corporate', '299034', 'Calcutta'), \
	('20', 'Sales', '239456', 'Calcutta'), \
	('30', 'Accounts', '822134', 'Calcutta'), \
	('40', 'Production', '349870', 'Bombay');")<jupyter_output>4<jupyter_text>Run SQL Select Commands<jupyter_code>runSELECT('Select * from Emp;')
runSELECT('Select * from Dept;')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.15210608424336974, 'fraction_numerical': 0.07273790951638065, 'mean_word_length': 5.172081829121541, 'pattern_counts': {'":': 0, '<': 32, '<?xml version=': 0, '>': 34, 'https://': 2, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 1, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '209558', 'license': 'MIT', 'n_tokens_mistral': 2189, 'n_tokens_neox': 1916, 'n_words': 461, 'path': 'DDL_Getting_Started_with_SQL_&_Pandas.ipynb', 'repo_name': 'Praxis-bs/RDWH', 'size': '5139'}