DEV Community: Aviator

Understanding SQL Commands

Aviator — Mon, 19 Jun 2023 23:30:00 +0000

SQL (Structured Query Language) serves as the query language for interacting with relational databases. It enables the storage, manipulation, and retrieval of data from a database. Commands written in SQL to perform specific tasks are referred to as SQL commands. These commands function as written instructions to communicate with the database.

SQL commands facilitate a wide range of tasks, including table creation, data insertion into tables, table modification, the establishment of relationships between tables, and even setting user permissions within the database.

These commands are grouped into 5 categories

Data Definition Language (DDL)
Data Manipulation Language (DML)
Data Control Language (DCL)
Transaction Control Language (TCL)
Data Query Language (DQL)

Note: For this article, MySQL will be the preferred database used in examples and demonstrations.

DDL: Data Definition Language

Data Definition Language commands define the database structure or schema. It is a set of SQL commands used to create, modify, and delete database structures.

Commands include:
CREATE, ALTER, DROP, TRUNCATE, RENAME

CREATE:
This command is used in creating the database and its associated objects. Some objects of the database include Table, View, Stored Procedure, Trigger, etc

Examples

CREATE DATABASE Students_DB

CREATE TABLE table_name (
  column_name1 data_type(size),
  column_name2 data_type(size)
);

There are also some advanced use cases of the create statement, where it can be used to create views, stored procedures, etc.

Note: These are advanced SQL concepts, so if you aren’t feeling adventurous at the moment, you can safely ignore these concepts.

CREATE VIEW view_name
AS
SELECT column1 [,column2 ] from table_name

CREATE PROCEDURE
 procedure_name()
BEGIN
 SELECT * FROM Table_name
END

ALTER
This is used to alter the structure of the database or its objects
The alter command changes the structure of the database objects. This command can be used to add a new attribute.

ALTER TABLE table_name ADD (
 column_name1 data_type (size),
 column_name2 data_type (size)
);

Also, to modify existing attributes/columns added to a table, the alter command comes in handy

Syntax

ALTER TABLE table_name MODIFY (
 column_name new_data_type(new_size)
);

ALTER TABLE Students MODIFY (
level char(30)
);

To drop a column, the alter command can also perform such an operation

ALTER TABLE table_name DROP COLUMN column_name;

To rename an existing attribute/column name, we can also make use of the alter command

ALTER TABLE Table_name
RENAME COLUMN Old_column_name TO New_column_name;

DROP
This command removes/deletes a database or table

DROP DATABASE Students_DB
DROP TABLE Students

TRUNCATE
This command will delete all the records/rows present in a table. The database table isn’t removed, only the records stored

TRUNCATE TABLE Students

RENAME
Renames an object existing in a database.

RENAME old_table_name TO new_table_name

RENAME Employees_Info TO Employees

DML: Data Manipulation Language

These commands are responsible for performing all types of data manipulation. It allows the user to modify a database table by inserting, modifying/updating, and deleting its data.

Rollbacks(reverse of query operations) to DML statements are possible if the operations we carried out were made out of error.

DML Commands include
INSERT, UPDATE, DELETE

INSERT
This command is used to insert new records into a database table

INSERT INTO Employees (emp_id, name, department)
VALUES (“rec64”, “John”, “Finance);

UPDATE
This command modifies or updates records stored in a table

UPDATE Employees SET name = “Mark”
WHERE emp_id = “bbc54”;

DELETE
Removes one, more, or all rows/records in a table

DELETE FROM Employees;

The above command removes all records of a company’s employees from the Employees table.

To remove a record based on a condition

DELETE FROM Employees WHERE name = “John”;

DQL: Data Query Language

Data Query Language (DQL) is used to fetch/retrieve data from the database. It uses only one command: the SELECT clause

SELECT column_1 [,column_2] FROM table_name

SELECT * FROM Employees
SELECT name, department FROM Employees

DCL: Data Control Language

Protects information in a table from unauthorized access. With a DCL command, a user can either be enabled or disabled from accessing information from a database or its objects. This command is used in managing roles and permissions.

Syntax

GRANT object_privileges ON table_name
TO user_name1[, user_name2]

Example

GRANT SELECT, UPDATE ON Students TO ‘Aviator’@localhost

Here, the database administrator is granting only select and update privileges to a database user with the name Aviator. This user is only allowed to perform select and update queries on the Students table but other queries such as alter and delete are restricted.

GRANT ALL ON Students TO ‘Aviator’@localhost

Here, the database user named Aviator is granted all privileges on the database table named Students. The user can create, update, alter, and delete records from a database table named Students.

REVOKE

REVOKE object_privileges ON table_name
FROM user_name1[, user_name2]

Used for taking back permission granted to a user.

REVOKE UPDATE ON Students FROM ‘Aviator’@localhost

If a database administrator wants to take back privileges and permission granted to a user, the revoke command is used.
With the above example, the user named Aviator is being revoked of the update privileges granted to them. In this case, they won’t be able to perform update queries on the database table named Students again.

TCL: Transaction Control Language

TCL manages all operations related to transactions in a database. They make it possible to roll back or commit changes to the database.
TCL commands can only be used with DML commands like INSERT, DELETE, and UPDATE.

Commands under TCL include
COMMIT, ROLLBACK, SAVEPOINT

COMMIT
Saves all transactions in a database. With this command, all changes made during a transaction are made permanent.

DELETE FROM Students WHERE registration_number = 20;
COMMIT;

ROLLBACK
This command is used to undo transactions that have not already been committed/saved into the database. All changes made during a transaction are undone. This could be because of an error or mistake made during the commit process of a transaction.

DELETE FROM Students WHERE registration_number = 20;
ROLLBACK;

SAVEPOINT
It is used to roll a transaction back to a certain point without having the entire transaction rolled back.

During the process of making a commit into the database, several savepoints can be created at each stage, such as savepoint_1, and savepoint_2.
When we are ready to move to a particular savepoint to inspect what we have done, we could reach that point using the savepoint command.

SAVEPOINT savepoint_name

SAVEPOINT savepoint_1

Conclusion

In this tutorial, you learned the different types of SQL commands and the categories in which they are grouped. I do hope this article gave you some understanding of SQL commands and would serve as a helpful guide as you continue to learn about SQL.

Connect with me on Linkedin Twitter

Data extraction with Python and BeautifulSoup

Aviator — Wed, 14 Jun 2023 09:44:47 +0000

An important prerequisite for making data-driven decisions is the acquisition of data.

Data extraction, also known as data gathering, is the process of acquiring data that facilitates data analysis. Given that not all data holds relevance, it is essential to exercise caution while gathering data that proves useful for data analysis.

The primary objective of this article is to offer a guide on how to extract data from websites. Not all websites provide a public API that allows for data retrieval. Consequently, in the absence of an API, resorting to web scraping becomes the viable approach for acquiring data from a website.

Installing Necessary Libraries

Before you begin, ensure that you have Python installed on your computer. It's recommended to have Python version 3.7 or newer.

First, create a folder for your project. Then, open the command prompt and navigate to your created folder.

Once you're in the correct folder using the command prompt, enter the following command:

pip install beautifulsoup4
pip install requests
pip install jupyter

In this tutorial, we will extract website content for a list of all states in the USA, along with their abbreviated state names.

You can find the website we'll be working with at the following URL: USA_STATES_AND_ABBR

After successfully installing the required libraries, open the command prompt and enter the following command:

jupyter notebook

This will launch a Jupyter coding environment instance.

To obtain your browser's user-agent, simply enter "my user agent" (without quotes) in your browser's search engine.

Now, let's proceed with the code.
On line 1, import the necessary libraries. On lines 2 and 3, set the headers and the URL for the website you want to scrape. Then, make a request to the website using the requests library. The response from the website will be stored in a variable named "response."

On line 4, use the open() command in Python to save the response from the website to a file named "usa_states.html". This approach helps avoid making repeated HTTP calls to the website.

On line 5, read from the saved file, and use the BeautifulSoup library to parse the contents of the website, which will be transformed into a Python object.

On line 6, use the soup.find() method to search for the <table> element. Once found, search for all <tr> tags that are children of the table element.

The resulting list is stored in the variable named states_table.

To iterate through the states_table list, we can use a loop to find the <td> elements that are children of the <tr> element. When found, we can append the result to the states_names variable. To avoid duplication, we can clear any previously saved content in the states_names list using the states_names.clear() method.

On line 10, we can use the CSV module, which is built-in, to write the result to a CSV file named "usa_states.csv".

Finally, we can delete the "usa_states.html" file, which was used to store the contents of the HTML website.

Conclusion

Here, a simple method of website scraping to extract data has been demostrated. While website scraping can sometimes be challenging, I hope this article provides you with a basic understanding of how to scrape a website.

Introduction to SQL keys

Aviator — Thu, 01 Jun 2023 09:40:08 +0000

In the usage of databases for storage and retrieval of data, keys are very important. Both SQL and NoSQL databases alike have the concept of keys.
In SQL, keys are fields in a table that are used to identify the specific row(s) and find or create relationships between tables.

Keys are considered useful because of the following

Creating relationships between two tables.
To keep data consistent and valid in the database.
Helps in fast retrieval of data.
Maintaining uniqueness in a table

SQL supports various types of keys:

Primary Key
Candidate Key
Unique Key
Composite Key
Super Key
Alternate Key
Foreign Key

NOTE: You should have MySQL relational database management system (RDBMS) installed

Primary Key

A primary key is created to identify each record uniquely in a table. Columns marked as primary keys aren't allowed to have null values. It keeps unique values throughout the column.
A table is expected to have only one primary key, although there are cases where a table can derive its primary key using two columns.

Hint: See below - Composite key.
Primary keys can be defined using the create statement when creating a table.

Adding a Primary Key To A Table

CREATE TABLE Product(
  product_id INT NOT NULL PRIMARY KEY,
  product_name VARCHAR(200) NOT NULL
)

Another way to create a table with the primary key

CREATE TABLE Product(
  product_id INT NOT NULL,
  product_name VARCHAR(200) NOT NULL,
  PRIMARY KEY (product_id)
)

Suppose you didn't define the primary key when the table was first created, you can define it later using the Alter table command:

ALTER TABLE Product
ADD CONSTRAINT PK_Product PRIMARY KEY (product_id);

Candidate Key

A candidate key is a key of a table that can be used as the primary key. A table can consist of multiple candidate keys; out of these, one can be selected as a primary key. Any column(s) that can be selected as a primary key, that column or columns are candidate keys.

CREATE TABLE Students(
  id PRIMARY KEY NOT NULL,
  first_name VARCHAR(100),
  last_name VARCHAR(100),
  email VARCHAR(255) NOT NULL,
  registration_number NOT NULL,
  ssn integer NOT NULL,
  residence VARCHAR(200)
)

id, registration_number, and social security number(ssn) are candidate keys. We have identified the id to serve as the primary key. Should we drop/remove the id from the table, registration_number or ssn can serve as the primary key. This is because each attribute is unique to each student. It wouldn't be right to have two students that have the same registration number or social security number (ssn).

Unique Key

A unique key can identify each row in a table uniquely, just like a primary Key. But, unlike a primary key, a unique key can have only a single null value and it does not allow for duplicate values in the column. In the students' table, registration_nos is identified as the unique key. We can't have two students having the same registration number.

CREATE TABLE Students( 
  id PRIMARY KEY NOT NULL,
  first_name VARCHAR(100),
  last_name VARCHAR(100),
  email VARCHAR(100) NOT NULL,
  registration_number VARCHAR(100) NOT NULL UNIQUE,
  ssn INT NOT NULL,
  residence VARCHAR(200)
)

Composite Key

When creating a table, situations may arise where a single column doesn't provide enough unique information to serve as a primary key. In this case, two columns in the table would be combined to serve as the primary key.
When the primary key in the table consists of two or more columns, we have a composite key.
A composite key can also be called a compound or concatenated key.

CREATE TABLE sales_order( 
  customer_id VARCHAR(150) NOT NULL,
  order_id INT NOT NULL,
  product_code VARCHAR(150) NOT NULL,

  PRIMARY KEY (customer_id, product_code)
)

customer_id    order_id    product_code
 --------   --------    ------------
   C01        O001         P007
   C02        O123         P007
   C02        O123         P230
   C01        O001         P890

None of these columns alone can play a role as a primary key.
customer_id alone cannot become a key as the same customer can place multiple orders, thus the same customer can have multiple entries.
order_id itself cannot be a primary key as the same order can contain the order of multiple products, thus the same order_id can be present multiple times.
product_code cannot be a primary key as more than one customer can place an order for the same product.

Based on this, it is safe to assume that the primary key should be derived from more than one column: Primary Key in the above table can be derived from: {customer_id, product_code}

Super Key

It is a set of columns that uniquely identifies each row in a table. It is the combination of two or more columns that can be used to identify a record uniquely in a table.

Alternate Key

Alternate key is a candidate key, that is currently not selected as a primary key. It can work as a primary key but currently isn't the primary key. Alternate keys can also be called secondary keys.
ssn, registration_number in the above-defined student table are examples of alternate keys.

Foreign Key

Foreign Key is a column in a table that is used as the primary key in another table. It can accept multiple nulls and duplicate values. In the students' table, the dept_id column identifies as the foreign key, because it is the primary key in the department table and it relates each student to a department (Each student must belong to a department).

CREATE TABLE Department (
  dept_id INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  location VARCHAR(200) NOT NULL
)

CREATE TABLE Students (
  id PRIMARY KEY NOT NULL,
  first_name VARCHAR(100),
  last_name VARCHAR(100),
  email VARCHAR(255) not null,
  registration_nos NOT NULL UNIQUE,
  ssn INT NOT NULL,
  residence VARCHAR(200),
  dept_id INT FOREIGN KEY REFERENCES Department(dept_id)
)

Conclusion

Knowledge of keys in relational databases, and how they are defined and used is an important concept for any SQL developer or administrator.
In this article, we have looked at the different types of keys, how they are used, and how they relate to each other.

Please leave your comments and reviews, it would be helpful to me.

Connect with me on Linkedin or Twitter

Getting started with bash scripting

Aviator — Wed, 24 May 2023 05:33:00 +0000

A bash script is a series of commands written in a file, which is read and executed by the bash program. The program executes line by line. For example, if you have multiple bash commands that you want to run, you can compile these commands into a bash script instead of typing and executing the commands manually one by one.

You can do the same sequence of steps by saving the commands in a bash script and running it. The script can be run any number of times without having to retype it.

These commands are a mixture of commands we would normally type ourselves on the command line (ls, cp, cat, awk).

Anything that you normally do on the command line can be put into a bash script and it will do the same thing.

Reasons to learn bash scripting
1. Flexibility
With Bash Scripting, you will be more flexible to use the terminals more often, rather than clicking upon GUIs.
2. Less Resource-Intensive:
Learning bash scripting and using the command line maximizes your speed when you lack computing resources.
3. Accessibility
Bash scripts can be created and executed by anyone with a basic understanding of the command-line interface.

In this guide, we will explore bash scripting and its features. Here, you will learn

How to create a bash script and execute it.
The basic syntax of shell scripting.
Creating Variables
Comments
Define Functions
Create Loops
Conditional statements
Defining Arrays

What you should know

How to Use Unix/Linux shell
Familiar with Linux or Unix commands. Some of the common commands include grep, ls, echo, date, cd, cat, etc
Minimal programming knowledge - Understands variables, functions, etc.

A bash script tells the bash shell what to do. A bash script is a useful way to group commands to create a program. Any command you could execute directly from the command line can be put into a bash script, and you could expect it to do the same actions as it would from the command line.

Identifying a Bash Script
By naming conventions, bash scripts end with a .sh. Although, sometimes the .sh extension can be removed and the script will run perfectly fine.

Scripts start with a shebang

#!/usr/bin/bash

The beginning of a script file is identified with a shebang. This is the first line of the script. Shebang tells the shell to execute it via bash shell. Shebang is simply an absolute path to the bash interpreter.
Formatting is important here. The shebang must be on the very first line of the file.
There must also be no spaces before the # or between the ! and the path to the interpreter.

To know the shell interpreter you are using

echo $0

To check the list of available shells on your Unix operating system:

cat /etc/shells

Let's create a simple script in bash that outputs Hello World.
Create a file named hello_world.sh

touch hello_world.sh

find the path to your bash shell

which bash

Place the outputted path on the top of your script.

#!/usr/bin/bash

echo "Hello World"

Run the script.
You can run the script in the following ways:

bash hello_world.sh

./hello_world.sh

With the second option, bash outputs a permission denied error message.

The reason for this is because of execution rights. Scripts have execution rights for the user executing them.

ls -l hello_world.sh

An execution right is represented by x. To give execution rights to the user.

chmod +x hello_world.sh

chmod modifies the existing rights of a file for a user.

Now execute the file again

./hello_world.sh

Creating Comments
Comments are lines that aren't executed by the interpreter
Single-line comments:

# This is a comment

Multi-line comments

: "The following is a 
Multi-line comment "

Defining Variables
variable=value
Note that there must be no spaces around the "=" sign: variable=value works; variable = value doesn't work

#!/usr/bin/bash

GREETING="Hello World"
echo $GREETING

#!/usr/bin/bash

message="Learning Bash Scripting"
echo $message

Shell scripting adheres to case sensitivity. If you define your variables in uppercase or lowercase, you must consistently use uppercase and lowercase letters.

You should always make sure variable names are descriptive. This makes them easier for you to remember and their purpose known.

Variables in the bash shell do not have to be declared, as is the case in other languages like Python, etc, where the variable has to be declared before they are accessed.
If you try to read an undeclared variable, the result is an empty string.

#!/usr/bin/bash
message="Learn Bash scripting"
echo $mesage

Notice the omission of an s from the variable name. The program still runs but returns an empty string.

Ask the User for Input
If we would like to ask the user for input then we use a command called read. This command takes the input and will save it into a variable.

#!/usr/bin/bash
echo what is your name?
read name
echo $name

If you would want to include an inline message with the variable, use the -p flag.

#!/usr/bin/bash

read -p "what is your name " name

echo $name

Adding -s makes the user input silent. That is, the input won't be shown when typed.

#!/usr/bin/bash

read -sp "Enter your password " password
echo $password

You can also ask the user for multiple input

#!/usr/bin/bash

read -p "What are your two favorite colors " color_1 color_2

echo "color_1 is $color_1"
echo "color_2 is $color_2"

Using command line arguments

#!/usr/bin/bash
echo $1 $2

./hello_world.sh Learning Bash

This also serves as a way to get inputs from the user. $1 represents the first argument. $2 represents the second argument.

This also serves as a way to get inputs from the user. $1 represents the first argument. $2 represents the second argument

Other Special Variables
Some system-defined variables include:
$0 - The name of the Bash script.
$1 - $9 - The arguments to the Bash script.
$# - The number of arguments passed to the Bash script.
$@ - All arguments supplied to the script.

Perform Mathematical Calculations
Performing mathematical calculations can be easily done in bash scripts using different methods.

expr 2 + 3 

expr 3 * 5
expr 4+4

This expression will return as is without performing a calculation.
All mathematical operators can be used here + - / * % etc.

let is a built-in function of Bash that allows us to do simple mathematical calculations.
let is a built-in function of Bash that allows us to do simple mathematical calculations.

let a=4+5

echo $a

Note here that there is no space between the values.
To add spaces, a quote can be used. A single or double quote is preferred.

let "a = 6 + 6"

echo $a

Mathematical expressions can also be stored using the syntax below

var=$((expression))  

var=$((3+9))

echo $var

With this method, $((expression)), decimal calculations aren't done properly.
bc command is used to perform the right decimal calculation

echo "scale=2 10/3" | bc

scale defines the number of decimal places required.

Creating functions
The purpose of writing functions is to avoid writing the same code constantly, and it also makes your code more readable.

example_function () {
  commands
}

Another method to create a function is to begin the line with the reserved bash keyword: function, then the function name and curly braces for the function body - we do not need the parentheses.

# Another way to write a bash function.
function example_function {
  commands
}

Defining the function will not execute the commands. To invoke a function, you must call the function name, as follows:

#!/bin/bash

# A hello world bash function
hello_world () {
  echo "Hello World!"
}

# Invoking the hello_world function
hello_world

Passing arguments to functions
Arguments are variables used in a specific function.
In shell scripts, we cannot pass arguments to functions like you would in other programming languages such as Python, etc.
Instead, shell functions have their command line argument and we can pass parameters to a function by placing them after a call to the function.

#!/bin/bash
greeting () {
  echo "Hello $1 $2"
}

greeting "Mark" "Joe"

Writing "If" Statements
"If" statements are conditional statements used to determine whether a command will run or not. If a statement is proven to be true, then a condition would run, else it is skipped.
Conditional statements begin with an if statement and ends with fi (if spelled backward).

#!/bin/bash
read -p "What is the secret number: " number

if [ $number -eq 10 ];
then
  echo "Your guess is right. The secret number is $number"
fi

Sometimes, based on a given input you would love to return a different output. The if-else statement is used.

if [ condition ]
then
  statement
else
  do this by default
fi

You may have multiple conditions to perform.

if [ <some test> ]
then
 <commands>
elif [ <some test> ] 
then
  <different commands>
else
 <other commands>
fi

Multiple Conditions with Boolean Operators
Sometimes you only want to take an action when multiple conditions are met. Other times you would like to act if one condition is met at least. This can be achieved with Boolean operators
and - &&: Multiple conditions must be met
or - ||: At least, a single condition is met.

if [$age < 18] || [$age > 50]
then
  echo "Not allowed to party"
fi

You can also do this

if [ $age -gt 18 -a $age -lt 50 ]
then
  echo "Allowed to party"
fi

if [ $age -lt 18 -o $age -gt 50 ]
then
  echo "Not Allowed to party"
fi

-a is similar to && (And)
-o is similar to || (Or)

Case Statement
If you want your program to take different paths based on different variable-matching, case statements to the rescue.

case $INPUT_STRING in
hello)
  echo "Hello!"
  break
  ;;
hi)
  echo "hi!"
  break
  ;;
*)
  echo "Sorry, I don't understand your greeting"
  ;;
  esac

Loops
Loops are useful if you want to execute commands multiple times. The available loops include- for, while, and until.
The for loop runs the command for a list of items:

#!/bin/bash

for item in [list]
do
  [commands]
done

This example uses a for loop to print the days of the week:

#!/bin/bash

for days in Monday Tuesday Wednesday Thursday Friday Saturday Sunday
do
echo "Day: $days"
done

While Loop
The script will evaluate a condition. If the condition is true, it will keep executing the commands, when the condition becomes false, it will stop the execution.

#!/bin/bash
while [condition]
do
[commands]
done

#!/bin/bash
counter=0
while [ $counter -le 10 ]
do 
  echo $counter
  ((counter++))
done

Until Loop
The opposite of the while loop. It will continue executing the command until the condition becomes true.

If we want the same output as the while example above using the until loop, we can write the script like this:

#!/bin/bash

counter=0

until [ $counter -gt 10 ]
do
  echo Counter: $counter
  ((counter++))
done

Select Loop
Allows you to create a simple menu.

select var in <list>
do
<commands>
done

names='Monday Tuesday Wednesday Thursday Friday Saturday Sunday None'

select name in $names 
do 
if [ $name == 'None' ] 
then 
break 
fi 
echo Hello $name 
done

Extras
Range Calculation

for value in {1..5} 
do 
  echo $value 
done

Break and continue
A break statement is used to exit from a running program while continue is used to skip the current iteration and move to the next one.

for value in 1,2,3,4,5
do 
if [ $value == 3 ] 
then 
continue 
fi 
  echo $value
done

#!/bin/bash

for value in 1 2 3 4 5 
do 
if [ $value == 3 ] 
then 
break
fi 
  echo $value

done

Arrays
When working with a large amount of data, it is very helpful to initiate the use of arrays.
There are two types of arrays that we can work with, in shell scripts.
Indexed Arrays - Store elements with an index starting from 0
Associative Arrays - Store elements in key-value pairs

The default array that's created is an indexed array
To create an indexed array, you use the declare command with the -a option

declare -a indexed_array

indexed_array = (1,2,3,4,5)
indexed_array[0]

To display all the values of an array we can use the following shell expansion syntax

echo ${indexed_array[@]}

Associative Array
To declare an associative array, use the -A option

declare -A associative_array
associative_array=([foo]=bar [baz]=foobar)

Note that you can create arrays without the declare keyword, but for associative arrays, when using the declare keyword, the -A is a must.

indexed_array=("first" "second")
associative_array=([first]="first goal" [second]="second goal")

# Print all elements
echo ${associative_array[@]}
echo ${indexed_array[@]}

Getting the number of elements
We can retrieve the number of elements contained

echo "the array contains ${#indexed_array[@]} elements"
echo "the array contains ${#associative _array[@]} elements"

Adding elements to an array
You can add elements to an indexed or associative array by specifying respectively their index or associative key

indexed_array = (1,2,3) indexed_array+= (9)

To add elements to an associative array, you have to specify their associated keys:

declare -A associative_array

# Add single element
$ associative_array[foo]="bar"

# Add multiple elements at a time
associative_array+=([baz]="foobar" [john]="doe")

# Acceesing elements in an array
echo ${associative_array[foo]}

Deleting an element from an array
To delete an element from the array, use the unset command. But you need to know its index or its key in the case of an associative array.

unset indexed_array[0]
unset associative_array[foo]

Delete the entire array

unset indexed_array
unset associative_array

Conclusion
A bash script is a series of commands written in a file. bash scripts can be used to replace any operation that you can perform on the terminal.

In this tutorial, you were introduced to how to create a bash script, write loops, define functions, and declare arrays.
Understand that any command that you can type manually in your terminal can be used inside a bash script.

This tutorial has aimed to get you up and running with writing shell scripts. There is still a lot of learning to do when it comes to using bash scripts.

I hope you now have an understanding of writing bash scripts and have it can help you automate your workflow and make you productive.

Connect with me on Twitter or Linkedin

Getting started with SQL View

Aviator — Mon, 22 May 2023 05:01:48 +0000

A SQL view returns a table containing only the information you want the end user to see.

Databases are designed to store millions of rows of data, and it can be difficult to query or filter a vast amount of data repeatedly using the same query or filter commands. With SQL views, it becomes much easier.

The type of table a view creates is called a virtual table. A virtual table doesn’t store data, rather it rebuilds itself every time the table is queried.

End users aren’t querying directly the original table created in the database using create table command, but rather a copy of the original table.
This copy of the original table can have more columns returned from it or it can have fewer columns returned. More columns when two or more tables are joined together, and fewer columns when only specific columns are selected.

For example, suppose you are a data analyst working in a bank. The bank manager has authorized you to run an analysis of employee data.

Part of the analysis requires that you use the employee’s table. This employee’s table contains fields/column names such as first and last name, age, contact details, email address, and NIN (National Identification Number).

You do not want to show the employee’s contact details, email address, and NIN. In this scenario, a view can be created that returns the employee’s general information (first name, last name, age), while sensitive information such as the NIN, email address, and contact details, if not needed for the analysis can be hidden.

Another example requires you to combine data from two or more tables. Instead of running the same join query command repeatedly when you want to have access to data, you can create a view that hides all the complex SQL join queries. With this, you can query your underlying data with a simple select statement without having to type repeatedly the complex SQL queries for joining tables.

Benefits of using views

Simplify Complex Queries:
Views can be used to simplify complex queries by breaking them down into smaller, more manageable pieces

Limited Access:
Views can be used to enforce security by limiting access to sensitive data. For example, you can create a view that only shows the first name and last name of employees, but not their salary or other confidential information.

Consistency:
Views can be used to enforce consistency ensuring that all queries use a common data definition.

Creating SQL view.

For this, we will be using the PostgreSQL database.

To create a view, you use the CREATE VIEW statement

CREATE VIEW view_name
AS
SELECT column1 [,column2 ] from original_table_name

CREATE VIEW: Begin the creation of the views

view_name: The name of the view to be created

AS: Specifies the table(s) from which we are populating data from

Setting up a Database using PostgreSQL
For this tutorial, we will be creating our database. Doing so I believe will cement the knowledge and help you comprehend the concept better.

Create Database

CREATE DATABASE explore_view;

With our database created, let’s now create tables and populate data into it.

CREATE TABLE employee_data(
  id SERIAL PRIMARY KEY,
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  salary INT NOT NULL,
  department VARCHAR(50) NOT NULL,
  gender CHAR(10),
  employee_id VARCHAR(50) NOT NULL
)

Insert records

INSERT INTO employee_data (id, first_name, last_name, salary, department, gender, employee_id)
VALUES (1, 'Mark', 'Morisson', 2000, 'Tech', 'female', 1),
       (2, 'Jennifer', 'Michael', 5000, 'Tech', 'female', 3),
       (3, 'Jemmy', 'Ken', 2500, 'Finance', 'male', 5),
       (4, 'Ruth', 'Stones', 7000, 'Customer Care', 'female', 6),
       (5, 'Ernest', 'Luke', 2000, 'Maintenance', 'male', 2),
       (6, 'Madison', 'George', 2000, 'Maintenance', 'male', 4);

Creating the view
To create this view, we would want to exclude the employees’ salary and employee_id from the result of the SQL query. This information is considered sensitive and only important to internal management. External management or auditors aren’t expected to see it.

CREATE VIEW Vemployee_data AS 
  SELECT id, first_name, last_name, department, gender 
 FROM employee_data

I usually prefix the name of the view with a V, to signify that the table created is a views table

Query a view
Once a view is created, it can be queried like a regular table.

SELECT * FROM Vemployee_data

When creating the view with the create view statement, we selected specific columns from the Vemployee_data table, which excluded the salary column. Here the salary column is no longer returned from the result of the query.

View with conditions

SELECT * FROM Vemployee_data WHERE gender = 'female'

Using the where clause, we filter the views table for employees who are female.

We can also define the filter condition when creating the view.

CREATE VIEW Vemployee_data_male AS SELECT id, first_name, last_name, department, gender FROM employee_info WHERE gender = 'male'

Now when you query the newly created view, it returns all rows where the employee’s gender is male.

SELECT * FROM Vemployee_data_male

I usually filter my data after the underlying views have been created, and I would encourage you to do the same. Unless your analysis requires a particular use case where not all rows from the original table should be returned.
E.g: You are trying to run your analysis only for male employees, so returning all rows for both genders would be a waste of time and computing resources.

Insert data into a view
In general, it isn’t recommended to directly insert data into a views table as views are virtual tables and don’t store data on their own. However, there are advanced cases where you can perform data insertion through the view. This is beyond the scope of this article.

It is recommended, to insert the data directly through the original table.

Insert data through the original table

If you add data to one of the tables from which the view is derived, the data will automatically be added and updated in the view.

INSERT INTO employee_data(id, first_name, last_name, salary, department, gender, employee_id) VALUES ('7', 'Eke', 'Mercy', '3500', 'Retail', 'female', '10')

Execute the view

SELECT * FROM Vemployee_data

Updating a View
In most cases, you can’t update a view directly. However, you can update the original table and the changes will be reflected in the view.

Modify a View
In PostgreSQL, you can modify a view by using the ALTER VIEW statement. This statement allows you to change the definition of the view without affecting the underlying data in the tables. To alter a view, you can use the following syntax:

ALTER VIEW Vemployee_data RENAME TO Vemployee_info

To replace a particular column from the view, you first have to drop the existing view and then recreate it.

Here, the create view query is named Vemployee_info because it was renamed above with the ALTER VIEW command.

DROP VIEW IF EXISTS Vemployee_info;
CREATE VIEW Vemployee_info AS SELECT id, first_name, department, gender 
FROM employee_data;

SELECT * FROM Vemployee_info

Here the Vemployee_info view is modified to exclude the last_name column.

Deleting/Removing a View

To delete/remove a view, use the DROP VIEW command.

For example:

DROP VIEW view_name

Where view_name is the name of the views you want to delete

DROP VIEW Vemployee_info

DROP VIEW Vemployee_data_male

Summary

SQL view is a powerful tool that allows you to simplify complex queries, enforce security, and simplify the structure of tables.

In this tutorial, you’ve learned

What SQL view is.
Why a view can be useful.
How to create, query, change, and delete them from a database.

Always remember, functionalities and syntax supported on views vary between relational database management systems (RDBMS).

SQL Constraints 101: Ensuring Data Accuracy and Integrity

Aviator — Thu, 18 May 2023 01:18:07 +0000

Enforcing restrictions on the type of data that goes into a database table is a way to protect it from malicious attacks and have consistent data that adheres to requirements. When creating a database table, you may want to put restrictions on the type of data that gets stored in the table.
For example, when creating a table for students for a particular institution, each student is assigned a unique id, for which no other student is assigned that id. If some other student is being assigned the id that is already assigned, then that id cannot be assigned. This simply means that no two students can have the same student id.

The purpose of imposing constraints is to maintain the integrity and accuracy of the data stored.
Any attempt to store values that violate what the table expects, the database will throw an error. They are simply rules that are used to restrict the type of data that can go into either a table or column(s).

Benefits of SQL constraints:

Maintaining data integrity SQL constraints ensure that the data stored in a database is accurate and consistent by enforcing rules that prevent invalid or inconsistent data from being entered.
Avoiding errors By preventing invalid or inconsistent data from being entered, SQL constraints help avoid errors that can arise from incorrect data.
Improving performance By enforcing data rules at the database level, SQL constraints can help improve the performance of database queries and reduce the risk of application crashes.
Enforcing business rules SQL constraints can be used to enforce business rules and ensure that data entered into a database meets the requirements of the organization, leading to more effective decision-making and data analysis.

Constraints are categorized into two

Table Level Constraints: Constraints defined independent of the column definition. These types of constraints are usually defined at the end of the create table statement.

CREATE TABLE Student (
  student_id INT SERIAL,
  department_name VARCHAR(100),
  college_name VARCHAR(100),

  CONSTRAINT student_pk PRIMARY KEY(student_id)
);

Column Level Constraints: Constraints defined during column definition. They are usually written at the column level where we want the constraints to be enforced.

CREATE TABLE Students (
  student_id INT SERIAL PRIMARY KEY,
  department_name VARCHAR(20) NOT NULL,
  college_name VARCHAR(50) NOT NULL
);

SQL constraints can be defined when the table is first created, they can also be added using the ALTER command.

The different constraints used in SQL include:

Primary Key Constraint
Not Null Constraint
Unique Constraint
Check Constraint
Default Constraint
Foreign Key Constraint

PRIMARY KEY Constraint

The PRIMARY KEY constraint is used to uniquely identify rows/records created in a table. One or more combination of columns in a table defines the primary key.
Primary keys are enforced to contain unique values and they cannot be with null values.

CREATE TABLE Students (
  student_id INT SERIAL PRIMARY KEY,
  department_name VARCHAR(100),
  college_name VARCHAR(200)
);

Here the primary key constraint is defined for the student_id and it is defined at the column level.

Using the ALTER command

CREATE TABLE Student (
  student_id INT SERIAL,
  department_name VARCHAR(100),
  college_name VARCHAR(100)
);

ALTER TABLE Student ADD PRIMARY KEY (student_id);

Table Level Primary Key Constraints

CREATE TABLE Student (
  student_id INT SERIAL,
  department_name VARCHAR(100),
  college_name VARCHAR(100),

  CONSTRAINT Student_PK PRIMARY KEY (student_id)
);

NOT NULL Constraint

The NOT NULL constraint specifies that the values to be inserted in a particular column cannot be blank.

CREATE TABLE Student (
  student_id INT PRIMARY KEY,
  department_name VARCHAR(100) NOT NULL,
  college_name VARCHAR(100) NOT NULL
);

Here, the department_name and the college_name columns of the Students table won't accept NULL values.

Using the ALTER command

CREATE TABLE Student (
  student_id INT PRIMARY KEY,
  department_name VARCHAR(100) NOT NULL,
  college_name VARCHAR(100)
);

ALTER TABLE Student ALTER COLUMN college_name SET NOT NULL;

Error when inserting NULL values
A value must be inserted into columns with the NOT NULL constraint. Otherwise, SQL will throw an error.
In the example below, SQL will throw an error because the value of college_name is not given.

INSERT INTO Students (student_id, department_name, college_name) 
VALUES (1, 'Economics')

UNIQUE Constraint

The UNIQUE constraint means that the column must have a unique value. Duplicate values are not allowed in columns to which the UNIQUE constraint is applied.
Unique constraints allow for null values. That is, null values can be stored in columns with a UNIQUE constraint.

CREATE TABLE Student (
  student_id INT SERIAL PRIMARY KEY,
  department_name VARCHAR(100) NOT NULL,
  college_name VARCHAR(100) NOT NULL,
  student_email varchar(100) UNIQUE,
);

Table-level UNIQUE constraints

CREATE TABLE Students (
student_id INT PRIMARY KEY,
department_name VARCHAR(20) NOT NULL,
college_name VARCHAR(50) NOT NULL,
student_email varchar(50),
student_phone INT,

CONSTRAINT unique_column UNIQUE (student_email)
);

Using ALTER command

CREATE TABLE Student (
  student_id INT PRIMARY KEY,
  department_name VARCHAR(20) NOT NULL,
  college_name VARCHAR(50) NOT NULL,
  student_email varchar(50),
  student_phone INT
);

ALTER TABLE Students ADD UNIQUE (student_email);

Above, the SQL command adds the UNIQUE constraint to the student_email columns in the existing Student table.

-- Add a unique constraint to multiple columns
ALTER TABLE Student ADD UNIQUE (student_email, student_phone);

Error when inserting duplicate values
We will get an error if we try to insert duplicate values in a column with the UNIQUE constraint.

CREATE TABLE Student (
  student_id INT SERIAL PRIMARY KEY,
  department_name VARCHAR(100) NOT NULL,
  college_name VARCHAR(100) NOT NULL,
  student_email VARCHAR(100) UNIQUE,
  student_phone INT UNIQUE
);

-- Insert values to the Student table
-- student_email has duplicate values

INSERT INTO Student(student_id, department_name,college_name, 
        student_email, student_phone)
VALUES (1, "Philosophy", "Madonna University", "mymy@gmail.com", 4487),
       (2, "Sociology", "Gallas University", "mymy@gmail.com", 9976 );

CHECK Constraint

The CHECK constraint is used to ensure that all the records in a certain column follow a specific rule.
When the user wants to insert the value into a column with the CHECK constraint applied, the value will first be checked to make sure it meets the defined conditions before inserting the value into that column.

Example: If the table has a salary column defined, without the check constraint, the user may insert any value of their choice, which may be below or above the required range.
But, if the user has applied a check constraint on the salary column, the column will not accept a value that isn't defined in its requirements.
This prevents salaries from being entered below or above the known salary range.

CREATE TABLE employees (
  employee_id INT NOT NULL PRIMARY KEY,
  employee_name VARCHAR(55) NOT NULL,
  salary INT NOT NULL CHECK (salary >= 1000 AND salary <= 10000)
);

Table-level CHECK constraint

CREATE TABLE employees (
  employee_id INT NOT NULL PRIMARY KEY,
  employee_name VARCHAR(55) NOT NULL,
  salary INT NOT NULL

  CONSTRAINT salaryCK CHECK (salary >= 1000 AND salary <= 10000)
);

Using the ALTER Command

CREATE TABLE employees (
employee_id INT NOT NULL PRIMARY KEY,
employee_name VARCHAR(55) NOT NULL,
salary INT NOT NULL
);

ALTER TABLE employees 
  ADD CONSTRAINT salaryCK CHECK (salary >= 1000 AND salary <= 10000)

DEFAULT Constraint

The DEFAULT constraint is used to set the default value for a column when the value is NULL.

CREATE TABLE User (
  id NOT NULL SERIAL PRIMARY KEY,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50),
  country VARCHAR(30) NOT NULL DEFAULT 'Belgium'
);

Using the ALTER command

CREATE TABLE User (
  id NOT NULL PRIMARY KEY SERIAL,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50),
  country VARCHAR(30) NOT NULL
);

ALTER TABLE User
  ALTER COLUMN country SET DEFAULT 'Belgium';

Inserting values into the table

-- Using the insert command, the default value of the country column 
-- is set to Belgium.

-- So, when we try to insert a NULL value to the country column, 
-- it is replaced with Belgium by default.

INSERT INTO User (id, first_name, last_name, country) 
  VALUES (1, 'John', 'Agba');

INSERT INTO User (id, first_name, last_name, country) 
  VALUES (1, 'John', 'Agba', 'Nigeria);

However, when we set the value of the country column, the default value is ignored and the value of the column is set.

FOREIGN KEY Constraint

The FOREIGN KEY constraint is a column used to reference a record that exists in another table.
Simply, it is a column in a table that refers to the primary key in another table. The table with the foreign key is called the child table while the referenced table with the primary key is called the parent table.

Example

CREATE TABLE Customer (
  customer_id INT PRIMARY KEY,
  address VARCHAR(200) NOT NULL,
  email varchar(100) UNIQUE
);

CREATE TABLE Orders (
  id INT PRIMARY KEY,
  product VARCHAR(50),
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES Customer(customer_id)
);

Using the ALTER Command

CREATE TABLE Customer (
  id INT PRIMARY KEY,
  address VARCHAR(200) NOT NULL,
  email varchar(100) unique
)

CREATE TABLE Orders (
  order_id INT PRIMARY KEY,
  product varchar(50),
  customer_id
)

ALTER TABLE Orders
  ADD FOREIGN KEY (customer_id) REFERENCES Customer(id);

Conclusion

In conclusion, SQL constraints are an essential aspect of database design, enabling developers and administrators to maintain data integrity, and ensuring that the data stored in your database is accurate, consistent, and secure.
SQL constraints also help to reduce errors and improve database performance, by preventing invalid data from being inserted.
Throughout this article, we have explored the different types of SQL constraints, such as primary keys constraints, foreign keys constraints, unique constraints, and check constraints, and provided examples of how they can be used in practice.

Implementing SQL constraints on a database table is a best practice that can help to build a robust and reliable system.
I hope that this article has provided you with a clear understanding of SQL constraints and how they can be used to build better, more secure, and more efficient databases.

Connect with me on Twitter or Linkedin

Introduction to SQL set operators

Aviator — Sun, 14 May 2023 23:57:49 +0000

Retrieving data stored in databases is part of the data-gathering process. Many organizations have their data already stored in databases, data is generated internally which eliminates the need for further search for data through third-party agencies, web scraping, etc.

It becomes the responsibility of the analyst to connect to the available data stored in the organization's database and perform the preferred analysis on it.

Sometimes when working with this data stored in the database, we have to make queries from two or more tables. Instead of merging these two tables as performed by an SQL Join, we would love to have the result from both tables brought together into a single table.

Rather than join our dataset from the two tables horizontally, which is performed by an SQL join, we join them vertically, made possible using SQL set operators.

Note: I am using two tables here to simplify the concept, however, you are not limited to the number of tables to use. Just make sure you aren't overdoing it, in order to keep your sanity in check.

The SQL term that makes it possible to achieve this result is termed set operators.
Set operators are operators which are used to combine the result of two or more queries.

These include:
UNION, UNION ALL, INTERSECT, MINUS/EXCEPT

All code examples shown here will be written using MySQL

Set Operators Syntax

  SELECT select_query
  set_operator
  SELECT another_select_query

Creating our table

With our table created, insert some data into it.

Inserting records into our table

INSERT INTO `students1`(`id`, `first_name`, `last_name`,
`department`, `level`, `residence`)
VALUES (1,"John","Mark","Economics",300,"Hostel"),
(2,"Ikenna","Matthew","Psychology",100,"Hostel"),
(3,"Jones","Jessica","Chemistry",400,"Off_Campus"),
(4,"Sarah","Mary","Sociology",200,"Off_Campus"),
(5,"King", "James", "Social Work", 300, "Hostel")


INSERT INTO `students2`(`id`, `first_name`, `last_name`, `department`, `level`, `residence`)
VALUES (1,"John","Mark","Economics",300,"Hostel"),
(2,"Ikenna","Matthew","Psychology",100,"Hostel"),
(3,"James","Bill","Agric",200,"Hostel"),
(4,"Sarah","Mary","Sociology",200,"Off_Campus"),
(5,"Queen", "Sharon","English", 100, "Off_Campus")

Set Operators

UNION

This set operator is used to combine the result of two select statements.
Duplicate rows will be eliminated from the results obtained after performing the UNION operation.

SELECT id, first_name, last_name, department, level,residence 
FROM students1
UNION
SELECT id, first_name, last_name, department, level,residence 
FROM students2

Since the union operator eliminates duplicate values, all values that appear in the second table already existing in the first table will be eliminated.

UNION ALL

This operator combines all the records from both queries.
Duplicate rows will not be eliminated from the results obtained after performing the UNION ALL operation.

SELECT id, first_name, last_name, department, level,residence 
FROM students1
UNION ALL
SELECT id, first_name, last_name, department, level,residence 
FROM students2

UNION vs UNION ALL: Which should you choose?

The distinguishing difference between UNION and UNION ALL is what happens when duplicate records(rows) are found in both tables.

Using the UNION operator, duplicate records are eliminated/removed. That is, if the record(row) found in the second table already exists in the first table, the row from the first table is returned but the row from the second table won't be returned because there already exists an exact record for it from the first table.

UNION operator says, I have scanned your tables and there appears to be a record in the first table that also appears in the second table. Now am going to help you return distinct records by not returning the same record found in the second table.

With the UNION ALL operator, all records are returned irrespective of whether they appear in the second table.
The UNION ALL operator says, I don't care if there are duplicates, this is all the record you have in both tables. The ALL in UNION ALL means show all records.

UNION is often slower than UNION ALL because there is an additional operation to remove duplicate values, which is usually a costly query step.
UNION ALL does not perform a distinct query, so it is usually faster.

So, with no intention of having unique records in your result, or if you are sure that the records in the table are already unique, use UNION ALL. But if you feel that your tables will contain duplicate values, use UNION.

INTERSECT

Returns the records which are common from both tables and removes duplicates.
Take note: it removes duplicates.

SELECT id, first_name, last_name, department, level,residence 
FROM students1
INTERSECT
SELECT id, first_name, last_name, department, level,residence 
FROM students2

MINUS/EXCEPT

Return rows that exist in the result of the first query but not in the results of the second query.

SELECT id, first_name, last_name, department, level,residence 
FROM students1
EXCEPT
SELECT id, first_name, last_name, department, level,residence 
FROM students2

Except and Minus perform the same query operation. Minus however is an Oracle relational database keyword. Except is used in other relational databases such as MySQL, and PostgreSQL.

Important points to note

1 When selecting columns from both tables, the number of columns returned needs to match between queries. If three columns are selected from the first query, you need to select three columns from the second query.

2 The order of the columns must be the same.

SELECT id, first_name, last_name, department, level,residence 
FROM students1
UNION
SELECT id, last_name, department, first_name, level,residence 
FROM students2
ORDER BY  department

Executing the above query will not generate an error but will however cause a disorder in the result.

3 The data type of each column needs to be compatible with both tables. If the data type of the column named first_name in the first table is a string, then the datatype of the column named first_name in the second table should also be a string.

4 If you wish to order the results of the query, then the ORDER BY must be written at the end of the last query

SELECT id, first_name, last_name, department, level,residence
FROM students1
UNION
SELECT id, first_name, last_name, department, level,residence 
FROM students2
ORDER BY  department

If the ORDER BY is placed in the first query, it will generate an error. You can try it out for yourself.
5 For the final result set, alias names assigned to columns are gotten from the names of the columns in the first query. Thus, aliasing column names in the second query won't be useful.

SELECT id AS studentID, first_name, last_name, department, level, residence AS location FROM students1
UNION
SELECT id as id_of_student, first_name, last_name, department,level, residence AS address FROM students2

Here, we have performed query operations using two tables. It is important to note that we can also add more tables to the set operations. Just make sure you aren't adding more tables that makes it difficult to keep track of operations.

Conclusion

Although an underused concept in SQL, set operators can be useful when we want to combine multiple tables with the same structure into a single table.

In this tutorial, we have learned:

What are set operators
Reasons for using set operators
Types of set operators
Points to note about set operators

I do hope you find this tutorial useful in helping you learn about SQL set operators
Any questions or feedback? Please comment below.

Connect with me on Linkedin or Twitter

Different methods to filter a Pandas DataFrame

Aviator — Sun, 26 Feb 2023 22:00:59 +0000

Pandas is a popular open-source python library used for data manipulation and analysis. It helps in the data analysis process which involves cleaning, exploring, analyzing, and visualizing data.

In this article, we will cover the various methods of performing filtering using pandas.

Data Filtering is one of the data manipulation operations we can perform when working with data. Filtering with pandas is similar to a WHERE clause in SQL or a filter in Microsoft Excel.

If you have worked with these tools, you have an idea of how filtering works.
Filters can be used to select a subset of data. It can also be used to exclude or discover null values from a dataset

Here are some examples of filtering

1. Select all employees who were employed by the organization after 2020
2. Select all male employees who work in the finance and marketing department.
3. Select all traffic accidents that occurred on Wednesday around 12 noon
4. Select all students whose registration number is null

These are some real-world examples of what we can do with filters depending on our dataset and use case for our analysis.

Note: This tutorial assumes that you are already familiar with the usage of pandas for data analysis and you can differentiate between its underlying data structure namely Series and Dataframe.

To keep this tutorial simple, we would be creating our sample dataset. It would be a dataset of employees working for an organization.

import pandas as pd

employees_data = {
    'first_name': ['Jamie', 'Michael', 'James', 'Mark', 'Stones', 'Sharon', 'Jessica', 'Johnson'],
    'last_name': ['Johnson', 'Marley', 'Peterson', 'Henry', 'Walker', 'White', 'Mendy', 'Johnson'],
    'department': ['Accounting', 'Marketing', 'Engineering', 'HR', 'HR', 'Sales', 'Data Analytics', 'Accounting'],
    'date_of_hire': [2011, 2012, 2022, 2000, 2007, 2018, 2020, 2018],
    'gender': ['M','M','M','M','F','F','M','F'],
    'salary': [10000,87500,77000,44500,90000,45000,25000,65000],
    'national_id': [22123,78656,98976,12765, None, None,56432,98744],
    'emp_id': ['Emp22', 'Emp54', 'Emp77', 'Emp99', 'Emp98', 'Emp01', 'Emp36', 'Emp04']
}

employees_df = pd.DataFrame(data=employees_data)

In pandas, there is more than one way to operate. Pandas provide multiple ways to filter data.

They include:

isin()
This method provides a way to apply single or multiple conditions for filtering.

employees_df[employees_df['department'].isin(['Marketing'])]

employees_df[employees_df['department'].isin(['Marketing','HR'])]

Logical operators
We can use the available logical operators to filter for a subset of data

employees_df[employees_df['salary'] > 30000]

Here we filter for employees whose salary is above 30000.

Occasions may arise where we have to filter our data for multiple conditions. The logical operator also makes this possible.

employees_df[(employees_df['salary'] > 30000) & (employees_df['department'] == 'Marketing')]

For all employees with a salary above 30000, who work in the marketing department

You can also use other logical operators such as less than(<), greater than(>), equal to(=), not equal to(!=), etc.

Query Function
The query function takes in an expression as an argument which evaluates to a Boolean that is used to filter the dataframe.

employees_df.query("department == 'Marketing'")

We can also query based on multiple conditions

employees_df.query("department == 'Marketing' and date_of_hire > 2006")

Str Accessor
Pandas make it easy to work with string values. Using the str accessor, we can filter for records whose values are strings.

employees_df[employees_df['department'].str.contains("M")]

employees_df[employees_df['department'].str.startswith("M")]

nlargest and nsmallest
Most times, we just need records of the highest or lowest values in a column.
These methods make it possible. We could filter for the highest 3 or lowest 3 salaries.

employees_df.nlargest(3, 'salary')

employees_df.nsmallest(3, 'salary')

Tilde sign (~)
Used to reverse the logic used in filter condition

employees_df[~employees_df['department'].str.contains("A")]

employees_df[~employees_df['department'].isin(['Marketing','HR'])]

isnull | notnull
Using the isnull method, we can return records that have NaN values and mark them for deletion. Using the notnull method, we can filter for records that do not contain NaN values.

employees_df[employees_df['national_id'].isnull()]

employees_df[employees_df['national_id'].notnull()]

Filter
Using this method, we can also filter for a subset of data.

employees_df.filter(items=['first_name', 'department'],axis=1)

Filter where the index begins with 5 or 8 using regex.

employees_df.filter(regex='5|8', axis=0)

filter the dataframe where the index is 6

employees_df.filter(like='6', axis=0)

In this tutorial, we discussed some of the different ways of filtering rows to return a subset of data from our pandas dataframe.
The only way to master pandas and be good at data manipulation is through practice.
I do hope you will make out time to practice.

Connect with me on Twitter or LinkedIn where I also share some useful data analytics tips

Fetching API Data With React-Async

Aviator — Tue, 20 Aug 2019 10:33:26 +0000

As developers,when making api requests, especially with react, we are always faced with the choice of either using the javascript fetch API or axios library. Picking one of the above has always worked great depending on the developers needs and usage.
However, I was curious in finding out other ways of doing things, and that was when I discovered a package: react-async.

This post is my attempt to understand and to teach myself the usage of the package

Introducing React-Async

React component and hook for declarative promise resolution and data fetching documentation

One of the benefits of using the package is:

It also works with promises, async/await and the Fetch API

The rest of this post will be code. Brace up😁😁

We will be using the react cli

First install the react cli: create-react-app ( CRA )

npx install create-react-app async-react

cd async-react

npm start

Note here that i am using npx to install CRA and bootstrap my project.
This saves me the time of first installing CRA globally in my system and then bootstrapping the project

But for your needs

npm install -g create-react-app
create-react-app async-react

cd async-react
npm start

Awesome!!! Our development environment has started

Open our project folder in your favorite code editor, and then move into the src folder

This is where we do most of our work

Rename App.js to Posts.js. We shall be rendering a list of posts

Open the Posts.js file and type in this code

//import react and react-async

import './App.css'
import React from "react"
import Async from "react-async"

const loadPosts = () =>
 fetch('https://jsonplaceholder.typicode.com/posts')
    .then(res => (res.ok ? res : Promise.reject(res)))
    .then(res => res.json())

const Posts = () => (
  <Async promiseFn={loadPosts}>
    {({ data, error, isLoading }) => {
      if (isLoading) return <div>Fetching data... Please Wait</div>

      if (error) return <div>Something went wrong: ${error.message}</div>

      if (data){
          const responseData = data.map((post)=>{
            return(
              <div>
               <p> {post.title} </p>
               <hr />
               <h1> {post.body} </h1>
              </div>
            )
          })
        return(
          <div>
           {responseData}
          </div>
        )
       }
      return null
    }}
  </Async>

)

export default Posts

At the top we are importing react and react-async

But we still don't have the react-async package yet
Lets install it

npm install --save react-async

There are three ways to use react-async

As a hook
As a component
As a factory

We are using the As a component functionality

Code Illustration

We create a loadPosts function which makes an api request using the fetch api
This request returns a promise which gets resolved or rejected.

If it gets resolved, it makes available some props to our component which includes -

data : The api request was successful and we have our data to inject into our component
error : Unsuccessful api request. This holds details of what went wrong
isLoading: The data response from our api is yet to arrive, so we wait until it is resolved

In the Posts component, we make use of the props being made available to us and return the data

Take note of this line

   <Async promiseFn={loadPosts}>

This is where the magic happens

We pass in the loadPosts function which makes the api request as a prop to the Async component and when it gets resolved, we are provided with the data, error and isLoading props which we can then use in our Posts component

Recall we imported Async from react-async

Update

Open index.js file and make the appropriate changes

//remove 
import App from './App'

//Add
import Posts from './Posts'

ReactDOM.render(<Posts />, document.getElementById('root') )

With these out of the way, our app should be working