Python - Site Connectivity Checker

import urllib.request as urllib

​def main(url):

    print("Checking connectivity ")

    response = urllib.urlopen(url)

    print("Connected to" , url, "succesfully")

    print("The response code was: ", response.getcode())

​

print("This is a site connectivity checker program")

input_url = input("Input the url of the site you want to check: ")

​main(input_url)

This is a site connectivity checker program
Input the url of the site you want to check: https://www.w3schools.com/
Checking connectivity 
Connected to https://www.w3schools.com/ succesfully
The response code was:  200

Python - Email Slicer Project

 def main():

    print(" Welcome to the email slicer ")

    email_input = input("Input your email address: ")

    (username, domain) = email_input.split("@")

    (domain, extension) = domain.split(".")

    print("Name : ", username)

    print("Domain : ", domain)

    print("Extension: ", extension)

​

main()

 Welcome to the email slicer 
Input your email address: john@google.co
Name :  john
Domain :  google
Extension:  co

Python - What is print(f"...")

 The f means Formatted string literals and it's new in Python 3.6.

Python - Insert into Maria DB

#import mysql.connector
# Module Imports
import mariadb
import sys

try:
    conn = mariadb.connect(
        user="root",
        password="root",
        host="127.0.0.1",
        port=3306,
        database="my_workspace"

    )
except mariadb.Error as e:
    print(f"Error connecting to MariaDB Platform: {e}")
    sys.exit(1)

# Get Cursor

cur = conn.cursor()
 

#insert information
try:
    cur.execute("INSERT INTO employee (Emp_No,Emp_Name,Emp_Add,Emp_Phone,Dept_No,Dept_Name,Salary) VALUES (?, ?, ?, ?, ?, ?, ?)", (9,"Maria","Mumbai",8908908900,3445,"DB",22222))
except mariadb.Error as e:
    print(f"Error: {e}")

conn.commit()     
conn.close()

 

 

 

SAMPLE TABLE CREATION SQL:

 

CREATE TABLE `employee` (
    `Emp_NO` INT(11) NOT NULL,
    `Emp_Name` TEXT NOT NULL,
    `Emp_Add` TEXT NOT NULL,
    `Emp_Phone` TEXT NOT NULL,
    `Dept_No` TEXT NOT NULL,
    `Dept_Name` TEXT NOT NULL,
    `Salary` TEXT NOT NULL,
    PRIMARY KEY (`Emp_NO`)
)

Python - PIP vs PIP3

Pip3 is the Python3 version of pip.

If you use pip, then only the python2.7 version will be installed. You have to use pip3 for it to be installed on Python3. So to install packages in python3, you should use pip3.

NOTE:- Its not necessary that pip will install in python 2.7, if python2 is absent then pip will do it in python3. The above statement was if you have both the version of python installed.

Python - Connecting to MariaDB Server

# Module Imports

import mariadb

import sys

​

try:

    conn = mariadb.connect(

        user="root",

        password="root",

        host="127.0.0.1",

        port=3306,

        database="my_workspace"

​

    )

except mariadb.Error as e:

    print(f"Error connecting to MariaDB Platform: {e}")

    sys.exit(1)

​

# Get Cursor

cur = conn.cursor()

some_name = "Sales" 

cur.execute("SELECT Emp_Name,Dept_Name FROM EMPLOYEE WHERE Dept_Name=?", (some_name,)) 

​

for first_name, last_name in cur: 

    print(f"First name: {first_name}, Last name: {last_name}")

First name: Ramesh, Last name: Sales
First name: Suresh, Last name: Sales
First name: Rajesh, Last name: Sales
First name: Shyamu, Last name: Sales
First name: Ramu,   Last name: Sales
First name: Mahesh, Last name: Sales

Python - Install mariadb

Command to install mariadb,

pip3 install mariadb

 

if needed, try this.

pip3 install mariadb-connector-python

 

Python - PIP package remove

pip uninstall camelcase

Python - How to check is MySQL client is configured/ installed

To check you have MySQLdb installed on your machine. 

#!/usr/bin/python

import MySQLdb

If the execution is success, then already installed.

If not, it will throw the below error,

Traceback (most recent call last):
   File "test.py", line 3, in <module>
      import MySQLdb
ImportError: No module named MySQLdb

 

Python - Install mysql

 Command to install mysql client,

pip install mysqlclient

 

Note : MySQL Bundle-3.23 through 5.5 and Python-2.4 through 2.7 are currently supported.

To install MySQLdb module, use the following command

pip install MySQL-python

 

Python - Exceptions Handling

Here is simple syntax of try....except...else blocks −

try:
   You do your operations here;
   ......................
except ExceptionI:
   If there is ExceptionI, then execute this block.
except ExceptionII:
   If there is ExceptionII, then execute this block.
   ......................
else:
   If there is no exception then execute this block. 

#!/usr/bin/python

try:

   fh = open("\abc\testfile", "w")

   fh.write("This is my test file for exception handling!!")

except IOError:

   print ("Error: can\'t find file or read data")

else:

   print ("Written content in the file successfully")

   fh.close()

Error: can't find file or read data

Python Directories

 #!/usr/bin/python
import os

# Create a directory "test"
os.mkdir("test")

 

#!/usr/bin/python
import os

# This would give location of the current directory
os.getcwd() 

#!/usr/bin/python
import os

# Changing a directory to "/home/newdir"
os.chdir("/home/newdir")

#!/usr/bin/python
import os

# This would  remove "/tmp/test"  directory.
os.rmdir( "test"  )

Python - File Operations

#FILE READ

#!/usr/bin/python

​# Open a file

fo = open("sample.txt", "r")

content = fo.read()

print(content)

# Close opened file

fo.close()

​

print ("Name of the file: ", fo.name)

print ("Closed or not : ", fo.closed)

print ("Opening mode : ", fo.mode)

welcome1 Python is a great language.
Yeah its great!!

Name of the file:  sample.txt
Closed or not :  True
Opening mode :  r
 
# FILE WRITE
#!/usr/bin/python
# Open a file

fo = open("sample.txt", "w")
fo.write( "welcome1 Python is a great language.\nYeah its great!!\n")

# Close opend file
fo.close() 

Python Functions

 def my_function_test(inparam):

  print(inparam + " Cars")

​

my_function_test("Maruthi")

my_function_test("Honda")

my_function_test("Kia")

Maruthi Cars
Honda Cars
Kia Cars

Python - Get Date Time

from datetime import datetime

getsysdatetime = datetime.now()

gettime = getsysdatetime.strftime("%H:%M:%S")

gettimeapproach2 = datetime.now().strftime("%H:%M:%S")

print("date and time : ",getsysdatetime)

print("time : ",gettime)

print("time approach 2: ",gettime)

date and time :  2023-01-23 10:47:46.738581
time :  10:47:46
time approach 2:  10:47:46

Python - IF ELIF ELSE & FOR

 ##IF ELIF ELSE

a = 400

b = 66

if b > a:

  print("b is greater than a")

elif a == b:

  print("a and b are equal")

else:

  print("a is greater than b")

a is greater than b

 

fruits = ["apple", "banana", "cherry"]

for x in fruits:

  print(x)

apple
banana
cherry

for x in "banana":

  print(x)

b
a
n
a
n
a

fruits = ["apple", "banana", "cherry"]

for x in fruits:

  print(x)

  if x == "banana":

    break

apple
banana

Python - Collections (Arrays)

Collections (Arrays)

There are 4 built-in data types in Python used to store collections of data.

List, Tuple, Set, and Dictionary, all with different qualities and usage.

• List is a collection which is ordered and changeable. Allows duplicate members.

• Tuple is a collection which is ordered and unchangeable. Allows duplicate members.

• Set is a collection which is unordered, unchangeable#, and unindexed. No duplicate members.

• Dictionary is a collection which is ordered## and changeable. No duplicate members.

#Set items are unchangeable, but you can remove items and add new items.

 

##As of Python version 3.7, dictionaries are ordered. In Python 3.6 and earlier, dictionaries are unordered.

List

• Lists are used to store multiple items in a single variable. 
• Lists are created using square brackets. 
• List items are ordered, changeable, and allow duplicate values. 
• List items are indexed, the first item has index [0], the second item has index [1] etc. 
• When we say that lists are ordered, it means that the items have a defined order, and that order will not change. 
• If you add new items to a list, the new items will be placed at the end of the list. 
• The list is changeable, meaning that we can change, add, and remove items in a list after it has been created. 
• A list can contain different data types.
  
• From Python's perspective, lists are defined as objects with the data type 'list'    
  

Tuple

• A tuple is a collection which is ordered and unchangeable. 
• When we say that tuples are ordered, it means that the items have a defined order, and that order will not change. 
• Tuples are unchangeable, meaning that we cannot change, add or remove items after the tuple has been created. 
• But there is a workaround. You can convert the tuple into a list, change the list, and convert the list back into a tuple.

CRONTAB

 To edit the crontab

    crontab -e

To view the crontab

    crontab -l

After change, for save

    ctrl+c, :wq!

After change, not to save (wrongly given, back to original)

    ctrl+c, :q!

Postgresql - To find the time differences between two columns in a table

SELECT 

A.id, A.ts, TIMEDIFF (B.ts,A.ts) AS timedifference

FROM log A 

INNER JOIN log B ON B.id = (A.id + 1)

ORDER BY TIMEDIFF (B.ts,A.ts) DESC

Penatho - Expressions for FILE READ

The file name is, 'j_dataload_20230117_18031673958817.log'

SCENARIO: If a directory contains more number of files and you want to read the 'j_dateload_' files alone, then you have to use the below expression.

EXPRESSION:  (j_dataload)_[0-9]{8}_[0-9]{14}\.log

SELECTING WHERE TWO COLUMNS ARE IN A SET

 -- works in PostgreSQL, Oracle, MySQL, DB2, HSQLDB 

SELECT whatever 

FROM t                              

WHERE 

(col1, col2) IN ((val1a, val2a), (val1b, val2b), ...) ;

List views in postgresql

SELECT 

table_name 

FROM INFORMATION_SCHEMA.views 

WHERE 

table_schema = ANY (current_schemas(false)) 

ORDER BY 1

Kill job in Linux/Ubuntu

To list the running jobs,

ps -ef|grep java

To kill the job,

ill -9 pid

To kill the application by drag & drop

xkill

Sample shell script - To call the pentaho job

 #!/bin/bash -l

sh /home/abc/data-integration/kitchen.sh -file="/home/abc/etl/j_dataload.kjb" –level:Basic -logfile:/home/abc/etl/Logfiles/j_dataload_$(date +"%Y%m%d_%H%M%s").log

sh /home/abc/data-integration/kitchen.sh -file="/home/abc/etl/j_logfile_archive.kjb" –level:Basic -logfile:/home/abc/etl/Logfiles/j_logfile_archive_$(date +"%Y%m%d_%H%M%s").log

CRON Schedule

 Sample cron schedule,

0 14 * * * /home/abc/etl/ExecutJob.sh

Postgresql - Get the last 'X' months

To get the last 'x' months from the current date,

Method 1:

SELECT date_trunc('month', current_date - interval '4' month) as mydate

e.g. Current date is, 2022-01-03 19:30:00

Output is, 2022-09-01 00:00:00

Method 2:

SELECT CURRENT_DATE - INTERVAL '4 months' as mydate

e.g. Current date is, 2022-01-03 19:30:00

Output is, 2022-09-03 00:00:00

Pentaho - Read file and store content in the memory

 Step Name : Load file content in memory

Returns two columns, 

1. File Content

2. File Size

Penatho - Expressions

Compare two columns:

Source:

Comparison:

Pentaho Steps:

Use 'User Defined Java Expression' step in the pentho to achieve this, some sample expressions are below,

Count.equals(Tgt_Count) ? "TRUE" : "FALSE"

Value Type : String

Count<=Tgt_Count ? "OK" : "NOTOK"

Value Type : String