DEV Community: Ravi Shankar

Django

Ravi Shankar — Tue, 04 Apr 2023 12:37:46 +0000

Settings file

What is secret key?

The SECRET_KEY is a secret cryptographic key in Django that's used for secure hashing and cryptographic signing of sensitive data, such as cookies and sessions. It's a randomly-generated string that should be kept secret and never shared or exposed in your source code or version control. The SECRET_KEY helps protect against security vulnerabilities such as session hijacking and cross-site request forgery (CSRF).

What are the default Django apps inside it? Are there more?

Django includes several default apps, such as auth, admin, sessions, and messages. There are many more official Django apps available, such as cache, contenttypes, flatpages, and redirects, and you can also create your own custom apps as needed.

What is middleware? What are different kinds of middleware?

Middleware is a way to add extra functionality to the request/response processing flow in Django. Middleware sits between the web server and view layer and can modify requests, responses, or view processing.

There are different kinds of middleware, such as:

Security Middleware: adds extra security checks to your application, such as X-Content-Type-Options, X-XSS-Protection, and Strict-Transport-Security headers.
Session Middleware: provides session management for your application.
Authentication Middleware: handles user authentication and authorization.
Cache Middleware: caches responses to improve application performance.
CORS Middleware: adds Cross-Origin Resource Sharing support to your application to allow cross-domain requests.\

CSRF

CSRF (Cross-Site Request Forgery) is a type of web security vulnerability where an attacker can exploit a user's authenticated session to perform unauthorized actions on a web application. This type of attack is commonly performed by tricking the user into clicking on a malicious link or visiting a malicious website that contains a specially-crafted form that submits a request to the target web application.

To prevent CSRF attacks, Django provides built-in CSRF protection middleware that adds a CSRF token to all HTML forms and checks the token on form submissions. The CSRF token is a unique value that is generated for each user session and is stored in a cookie.

XSS

XSS (Cross-Site Scripting) is a type of web security vulnerability that allows attackers to inject malicious code into a web page viewed by other users.

XSS attacks can be performed by injecting code into input fields, URLs, or other parts of a web application that are not properly sanitized or validated.

To prevent XSS attacks, it's important to properly sanitize and validate all user input and output, and to use tools such as Django's built-in template escaping and the django-html-sanitizer package.

Click Jacking

Clickjacking is a type of web security vulnerability where an attacker tricks a user into clicking on a button or link on a web page that is actually hidden or transparent, causing the user to inadvertently perform an action that they did not intend to.

Clickjacking attacks can be performed by overlaying a transparent iframe on top of a legitimate button or link, or by using CSS to position a hidden button or link on top of a legitimate one.

To prevent clickjacking attacks, it's important to use the X-Frame-Options header to restrict the domains that can embed your website in an iframe.

What is WSGI?

WSGI (Web Server Gateway Interface) is a standard interface between web servers and Python web frameworks, such as Django, Flask, and Pyramid.

WSGI specifies how web servers should communicate with Python web applications and how web applications should respond to requests.

By conforming to the WSGI standard, Python web applications can be deployed on a variety of web servers, such as Apache, Nginx, and uWSGI, without requiring any modifications to the application code.

Models file

What is ondelete Cascade?

on_delete = CASCADE is a parameter in Django's ForeignKey field that specifies what should happen to the child objects when the referenced parent object is deleted.

When a ForeignKey is created with on_delete = CASCADE, it means that when the referenced parent object is deleted, all the child objects that have a foreign key to the parent object will also be deleted.

Understanding the difference between Python module and Python class?

In Python, a module is a file that contains Python code, such as functions, classes, and variables, that can be imported and used in other Python code. A module can be thought of as a collection of related code that serves a specific purpose.

On the other hand, a class is a blueprint for creating objects that defines a set of attributes and methods that the objects will have. A class can be thought of as a template or a user-defined data type.

In other words, a module is a way to organize and reuse code, while a class is a way to define and create objects with specific properties and behaviors. Modules can contain multiple classes, as well as other code, while a class is typically defined within a module or in a separate module.

Modules and classes are both important concepts in Python programming and are often used together to create complex and reusable code.

Django ORM

Using ORM queries in Django Shell

Start the Django shell by running the command python manage.py shell.
Once in the shell, you can import the model that you want to query,
for example:

from myapp.models import MyModel

You can then perform ORM queries on the model using the standard Django query syntax. For example, to retrieve all objects of a model, you can use

MyModel.objects.all()

Turning ORM to SQL in Django Shell

To turn ORM queries into SQL queries in the Django shell, you can use the query method of a QuerySet object. This method returns the raw SQL query that Django generates based on the ORM query.

Here's an example of how to do this:

Open the Django shell by running the command python manage.py shell.
Import the model you want to query, for example:

from myapp.models import MyModel

Perform the ORM query on the model, for example:

queryset = MyModel.objects.filter(name='John')

Call the query method on the QuerySet object to retrieve the SQL query, for example:

print(queryset.query)

The SQL query will be printed to the console, allowing you to inspect it and analyze its performance.

What are Aggregations?

Aggregations in Django's ORM refer to the process of performing calculations on groups of objects in a QuerySet, such as calculating the average, count, sum, or maximum/minimum value of a field in the objects.

Django provides a number of built-in aggregation functions, such as Count, Sum, Avg, Max, and Min, which can be used in ORM queries to generate the desired result.

What are Annotations?

Annotations in Django's ORM refer to the process of adding extra information to each object in a QuerySet, such as calculating a value based on a related object or performing complex calculations on the fields of the object.

Django provides a number of built-in annotation functions, such as Count, Sum, Avg, Max, and Min, which can be used in ORM queries to generate the desired result.

What is a migration file? Why is it needed?

In Django, a migration file is a Python script that defines the changes to be made to a database schema, such as adding or removing tables, columns, or constraints.

Migration files are needed to manage database schema changes in a consistent and controlled way, ensuring that the changes are properly tracked, applied, and rolled back if necessary.

When you create a new model or modify an existing model in Django, you need to create a migration file to define the database changes associated with that model. You can then apply the migration file to your database using the manage.py migrate command, which updates the schema of your database to match the current state of your models.

What are SQL transactions? (non ORM concept)

SQL transactions are a set of one or more database operations that are performed as a single logical unit of work. A transaction is typically used to ensure that a set of related database operations are performed atomically, meaning that they are either all completed successfully, or all rolled back to their previous state if an error occurs.

What are atomic transactions?

Atomic transactions in Django's ORM refer to the concept of ensuring that a set of related database operations are performed as a single, indivisible unit of work. In other words, either all the operations are performed successfully, or none of them are performed at all.

In Django, you can use the transaction.atomic() decorator or context manager to wrap a set of database operations in an atomic transaction. This ensures that if any of the operations fail or encounter an error, the entire transaction is rolled back, and the database is returned to its previous state.

References

HTML CSS concepts

Ravi Shankar — Thu, 02 Mar 2023 08:14:11 +0000

Box Model

The CSS box model is essentially a box that wraps around every HTML element. It consists of margins, borders, padding, and the actual content.

Content: The content of the box, where text and images appear
Padding: Clears an area around the content. The padding is transparent
Border: A border that goes around the padding and content
Margin: Clears an area outside the border.
The margin is transparent

The box model allows us to add a border around elements, and to define space between elements.

Inline versus Block Elements. Examples.

Block Level Elements

Block level elements take up as much space as possible by default.

Each block level element will start a new line on the page, stacking down the page.

In addition to stacking vertically, block level elements will also take up as much horizontal space as possible.

The p element is an example of a block level element. Each new paragraph tag will appear on its own line vertically.

Paragraphs with longer content will stretch all the way to the edge of the page.

Examples of block level elements:

<p>
<ol>, <ul>, <dl>
All heading tags
<article>, <section>, <div>

Inline Elements

Inline elements display in a line. They do not force the text after them to a new line.

An anchor (or link) is an example of an inline element. You can put several links in a row, and they will display in a line.

Examples of inline elements:

<a>
<strong>, <em>, <b>, <i>, <mark>
<span>

Positioning: Relative/Absolute

position: relative

Position an element based on its current position without changing other elements position.

position: absolute

Position an element based on its closest positioned ancestor position.

Common CSS structural classes

Structural pseudo classes allow access to the child elements present within the hierarchy of parent elements.

We can select first-child element, last-child element, alternate elements present within the hierarchy of parent elements.

:first-child It represents the element that is prior to its siblings in a tree structure.
:nth-child(n) :nth-child(Expression) class applies CSS properties to those elements that appear at the position evaluated by the resultant of an expression. The expression evaluates to a value resulting in the position of the element in a tree structure.
:last-child This pseudo class represents the element that is at the end of its siblings in a tree structure.
:nth-last-child(n) :nth-last-child(Expression) is the same as :nth-child(Expression) but the positioning of elements start from the end.
:only-child It represents the element that is a sole child of the parent element and there is no other sibling.
:first-of-type There might be more than one type of siblings under a common parent. It selects the first element of the one type of sibling.
:nth-of-type(n) There may be more than one elements of the same type under a common parent. :nth-of-type(Expression) represents those elements of the same type at the position evaluated by the Expression.
:last-of-type This represents the last element in the list of same type of siblings.
:nth-last-of-type(n) It is the same as :nth-of-type(n) but it starts counting of position from the end instead of start.
:not This is a negation pseudo-class selector. This takes simple selector as an argument and selects elements that are not represented by the argument.
:empty Selects empty elements.
:root Selects root element. Root element refers the HTML element.

Common CSS styling classes

CSS styling classes are pre-defined styles that can be applied to HTML elements using the class attribute.

They allow you to apply consistent styling to multiple elements across your webpage, making it easier to maintain and update the styling of your website.

Here are some common CSS styling classes:

.container: Defines a container element that wraps other elements on the page.
.button: Defines a styled button element that can be used for call-to-action or navigation purposes.
.card: Defines a card element with a border, background color, and padding that can be used to display content.
.text-center: Centers text within an element.

CSS Specificity

If there are two or more CSS rules that point to the same element, the selector with the highest specificity value will "win", and its style declaration will be applied to that HTML element.

Specificity Hierarchy

There are four categories which define the specificity level of a selector:

Inline styles
IDs
Classes, pseudo-classes, attribute selectors
Elements and pseudo-elements

Inline style gets a specificity value of 1000, and is always given the highest priority!

There is one exception to this rule: if you use the !important rule, it will even override inline styles!

CSS Responsive Queries

Media queries are a popular technique for delivering a tailored style sheet to different devices.

To demonstrate a simple example, we can change the background color for different devices:

body {
  background-color: tan;
}

/* On screens that are 1440px or more,
set the background color to blue */
@media screen and (min-width: 1440px) {
  body {
    background-color: blue;
  }
}

/* On screens that are 376px or more, 
set the background color to olive */
@media screen and (min-width: 376px) {
  body {
    background-color: olive;
  }
}

Grid

The CSS Grid Layout Module offers a grid-based layout system, with rows and columns, making it easier to design web pages without having to use floats and positioning.

A grid layout consists of a parent element, with one or more child elements.

An HTML element becomes a grid container when its display property is set to grid or inline-grid.

The vertical lines of grid items are called columns.

The lines between rows are called row lines.

The spaces between each column/row are called gaps.

grid-template-columns Specifies the size of the columns, and how many columns in a grid layout
grid-template-rows Specifies the size of the rows in a grid layout
grid-column A shorthand property for the grid-column-start and the grid-column-end properties
grid-row A shorthand property for the grid-row-start and the grid-row-end properties
gap A shorthand property for the row-gap and the column-gap properties

 <div class="grid-container">
  <div class="grid-item">1</div>
  <div class="grid-item">2</div>
  <div class="grid-item">3</div>
  <div class="grid-item">4</div>
</div>

.grid-container {
  display: grid;
  grid-template-columns:repeat(2,1fr);
  grid-template-rows:repeat(2,1fr);
}

Common header meta tags

The <meta> tag defines metadata about an HTML document. Metadata is data (information) about data.

Metadata will not be displayed on the page, but is machine parsable.

Metadata is used by browsers how to display content or reload page, search engines (keywords), and other web services.


<meta name="keywords" content="HTML, CSS, JavaScript">

<meta name="description" content="HTML and CSS">

<meta name="viewport" content="width=device-width, initial-scale=1.0">

References

Revision 1-03-2023

Ravi Shankar — Wed, 01 Mar 2023 06:51:25 +0000

1. What is Atomicity in SQL?

Atomicity property says that, when an action is performed on the data, it should either be executed completely or not executed at all.

Suppose there's an application that transfers funds between accounts. In this case, the property of atomicity guarantees that when a withdrawal is made from one account, the corresponding deposit is also made to the other account, ensuring a successful transaction. If any part of the transaction fails due to insufficient funds, the entire transaction is rolled back to its initial state.

2. What is the difference between inline, internal, and external styles in the context of HTML and CSS?

Inline CSS:

Inline CSS contains the CSS property in the body section attached with element is known as inline CSS. This kind of style is specified within an HTML tag using the style attribute.

Internal or Embedded CSS:

The CSS rule set should be within the HTML file in the head section i.e the CSS is embedded within the HTML file.

External CSS:

External CSS contains separate CSS file which contains only style property with the help of tag attributes. CSS property written in a separate file with .css extension and should be linked to the HTML document using link tag.

3. What is the difference between shallow copy and deep copy in Python?

In Python, a shallow copy creates a new object that references the original object's memory. Any changes made to the original object will be reflected in the copied object. In contrast, a deep copy creates a completely new object with its own memory. Changes made to the original object will not be reflected in the copied object.

Shallow copy of the list

original_list = [[1, 2, 3], [4, 5, 6], [7, 8, 9]]

# Make a shallow copy of the list
shallow_copy_list = original_list.copy()

# Modify the nested list in the shallow copy
shallow_copy_list[0][0] = 0

# Print the original list and the shallow copy
print(original_list)
print(shallow_copy_list)

# Original List:  [[0, 2, 3], [4, 5, 6], [7, 8, 9]]
# Shallow Copy List:  [[0, 2, 3], [4, 5, 6], [7, 8, 9]]

As we can see from the output, modifying the nested list in the shallow copy also modified the nested list in the original list. This is because the shallow copy only copied the reference to the nested list, not the nested list itself.

Deep copy of the list

original_list = [[1, 2, 3], [4, 5, 6], [7, 8, 9]]

# Make a deep copy of the list
import copy
deep_copy_list = copy.deepcopy(original_list)

# Modify the nested list in the deep copy
deep_copy_list[0][0] = 0

# Print the original list and the deep copy
print("Original List: ", original_list)
print("Deep Copy List: ", deep_copy_list)

# Original List:  [[1, 2, 3], [4, 5, 6], [7, 8, 9]]
# Deep Copy List:  [[0, 2, 3], [4, 5, 6], [7, 8, 9]]

As we can see from the output, modifying the nested list in the deep copy did not modify the nested list in the original list. This is because the deep copy created a new nested list with new memory addresses for each of its elements.

4. How can we apply polymorphism in Python?

Polymorphism defines the ability to take different forms. Polymorphism in Python allows us to define methods in the child class with the same name as defined in their parent class.

Method Overriding

class A:
    def display(self):
        print("Inside A class")

class B(A):
    def display(self):
        print("Inside class B")

a1 = B()
a1.display()

Here child class inherited it's parent class method but it made changes to the method in child class by doing that we are overriding the parent class display method with the child class display method.

We can use the concept of polymorphism while creating class methods as Python allows different classes to have methods with the same name.

5. During file operations involving CSV files, we make use of 'open' and 'DictReader' to perform our data operations with the help of iterations. After the iteration is completed, can we still perform any operation that requires accessing the DictReader object, and if so, how?

No, once the iteration is completed, the DictReader object is closed and you can no longer access it to perform any further operations. If you want to perform additional operations on the CSV data, you will need to create a new DictReader object by calling open again with the CSV file path and the appropriate parameters.

6. How do you convert an INTEGER value to FLOAT in SQL?

By using CAST function we can convert INTEGER value to FLOAT value.

SELECT CAST(3 AS FLOAT);

-- Output: 3.000000

7. In Python, the access time for fetching any 'value' of a dictionary using a 'key' in Big(O) is 1. Why is it direct access time in Python since there could be numerous key-value pairs in the dictionary?

In Python, the access time for fetching any value of a dictionary using a key is O(1) because Python uses hash tables to implement dictionaries. This allows for constant time access to values, regardless of the number of key-value pairs in the dictionary.

8. How do you restore CSS property?

To restore a CSS property to its default value, you can use the "initial" keyword in the style rule, for example:

.my-class {
  color: initial;
}

9. Difference between TRUNCATE and DELETE in SQL?

DELETE removes one or more rows from a table, while TRUNCATE removes all rows from a table. DELETE is a logged operation, meaning that it can be rolled back, while TRUNCATE is not logged and cannot be rolled back. TRUNCATE is also faster than DELETE for large tables.

10. How do you combine two dictionaries in Python?

To combine two dictionaries in Python, you can use the "update" method, for example:

dict1 = {'a': 1, 'b': 2}
dict2 = {'c': 3, 'd': 4}

dict1.update(dict2)

print(dict1)  

# Output: {'a': 1, 'b': 2, 'c': 3, 'd': 4}

11. What is the difference between dir() and "dict" dunder method in Python?

The "dir()" function returns a list of all the names in the current scope, while the "dict" dunder method returns a dictionary containing the namespace of an object. In other words, "dir()" gives you a list of names, while "dict" gives you a dictionary of names and their corresponding values.

12. What is the git command to save all committed and uncommitted changes in the local machine?

The git command to save all committed and uncommitted changes in the local machine is "git stash". This command saves changes to a temporary area and reverts the working directory to the last committed state.

13. Suppose we need to add all files except a specific file in git without using gitignore. How do we do so?

To add all files except a specific file in git without using gitignore, you can use the "git add" command with a file glob pattern that excludes the specific file, for example:

git add *.txt !important.txt

This will add all .txt files in the directory except for "important.txt".

SQL Concepts

Ravi Shankar — Wed, 22 Feb 2023 06:32:52 +0000

ACID Properties

DBMS is the management of data that should remain integrated when any changes are done in it.

It is because if the integrity of the data is affected, whole data will get disturbed and corrupted.

Therefore, to maintain the integrity of the data, there are four properties described in the database management system, which are known as the ACID properties.

The ACID properties are meant for the transaction that goes through a different group of tasks, and there we come to see the role of the ACID properties.

1. Atomicity

The term atomicity defines that the data remains atomic.

It means if any operation is performed on the data, either it should be performed or executed completely or should not be executed at all.

Commit: In case a transaction commits, the changes made are visible to us. Thus, atomicity is also called the ‘All or nothing rule’.
Abort: In case a transaction aborts, the changes made to the database are not visible to us.

2. Consistency

Consistency means that we have to maintain the integrity constraints so that any given database stays consistent both before and after a transaction.

3. Isolation

Isolation ensures the occurrence of multiple transactions concurrently without a database state leading to a state of inconsistency.

A transaction occurs independently, i.e. without any interference.

Any changes that occur in any particular transaction would NOT be ever visible to the other transactions unless and until this particular change in this transaction has been committed or written to the memory.

4. Durability

The durability property states that once the execution of a transaction is completed, the modifications and updates on the database gets written on and stored in the disk.

These persist even after the occurrence of a system failure. Such updates become permanent and get stored in non-volatile memory. Thus, the effects of this transaction are never lost.

CAP Theorem

The CAP theorem, originally introduced as the CAP principle, can be used to explain some of the competing requirements in a distributed system with replication. It is a tool used to make system designers aware of the trade-offs while designing networked shared-data systems.

Consistency

Consistency means that all clients see the same data at the same time, no matter which node they connect to.

For this to happen, whenever data is written to one node, it must be instantly forwarded or replicated to all the other nodes in the system before the write is deemed successful.

Availability

Availability means that any client making a request for data gets a response, even if one or more nodes are down. Another way to state this all working nodes in the distributed system return a valid response for any request, without exception.

Partition tolerance

A partition is a communications break within a distributed system a lost or temporarily delayed connection between two nodes.

Partition tolerance means that the cluster must continue to work despite any number of communication breakdowns between nodes in the system.

Joins

Joins clause is used to combine records from two or more tables in a database. A JOIN is a means for combining fields from two tables by using values common to each.

Join Types in SQL are:

The CROSS JOIN
The INNER JOIN
The LEFT OUTER JOIN
The RIGHT OUTER JOIN
The FULL OUTER JOIN

The CROSS JOIN

A CROSS JOIN matches every row of the first table with every row of the second table. If the input tables have m and n columns, respectively, the resulting table will have m + n columns. Because CROSS JOIN have the potential to generate extremely large tables, care must be taken to use them only when appropriate.

Syntax:

SELECT ... FROM TABLE1 CROSS JOIN TABLE2 ...

SELECT EMP_ID, NAME, DEPT 
FROM COMPANY 
CROSS JOIN DEPARTMENT;

emp_id	name	dept
1	Paul	IT Billing
1	Mark	IT Billing
1	David	IT Billing
1	Allen	IT Billing
1	Paul	IT Billing
2	Paul	Engineering
2	Mark	Engineering
2	David	Engineering
2	Allen	Engineering
2	Paul	Engineering
7	Paul	Finance
7	Mark	Finance
7	David	Finance
7	Allen	Finance
7	Paul	Finance

The INNER JOIN

A INNER JOIN creates a new result table by combining column values of two tables (table1 and table2) based upon the join-condition.

The query compares each row of table1 with each row of table2 to find all pairs of rows, which satisfy the join-condition.

When the join-condition is satisfied, column values for each matched pair of rows of table1 and table2 are combined into a result row.

Syntax:

SELECT TABLE1.COLUMN1,TABLE2.COLUMN1 
FROM TABLE1 
[INNER] JOIN TABLE2 
ON TABLE1.COMMON_COLUMN = TABLE2.COMMON_COLUMN ...

SELECT EMP_ID, NAME, DEPT 
FROM COMPANY 
INNER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID;

emp_id	name	dept
1	Paul	IT Billing
2	Allen	Engineering

The OUTER JOIN

The OUTER JOIN is an extension of the INNER JOIN. SQL standard defines three types of OUTER JOINs: LEFT, RIGHT, and FULL and PostgreSQL supports all of these.

The LEFT OUTER JOIN

In case of LEFT OUTER JOIN, an inner join is performed first.

Then, for each row in table T1 that does not satisfy the join condition with any row in table T2, a joined row is added with null values in columns of T2.

Thus, the joined table always has at least one row for each row in T1.

Syntax:

SELECT T1.COLUMN_1,T2.COLUMN1
FROM TABLE1 AS T1
LEFT [OUTER] JOIN TABLE2 AS T2
ON T1.COMMON_COLUMN = T2.COMMON_COLUMN ...

SELECT EMP_ID, NAME, DEPT 
FROM COMPANY 
LEFT OUTER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID;

emp_id	name	dept
1	Paul	IT Billing
2	Allen	Engineering
	David
	Paul
	Mark

The RIGHT OUTER JOIN

In case of RIGHT OUTER JOIN, an inner join is performed first.

Then, for each row in table T2 that does not satisfy the join condition with any row in table T1, a joined row is added with null values in columns of T1.

Thus, the joined table always has at least one row for each row in T2.

Syntax:

SELECT T1.COLUMN_1,T2.COLUMN1
FROM TABLE1 AS T1
RIGHT [OUTER] JOIN TABLE2 AS T2
ON T1.COMMON_COLUMN = T2.COMMON_COLUMN ...

SELECT EMP_ID, NAME, DEPT 
FROM COMPANY 
RIGHT OUTER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID;

emp_id	name	dept
1	Paul	IT Billing
2	Allen	Engineering
7		Finance

The FULL OUTER JOIN

First, an inner join is performed.

Then, for each row in table T1 that does not satisfy the join condition with any row in table T2, a joined row is added with null values in columns of T2.

In addition, for each row of T2 that does not satisfy the join condition with any row in T1, a joined row with null values in the columns of T1 is added.

Syntax:

SELECT T1.COLUMN_1,T2.COLUMN1
FROM TABLE1 AS T1
FULL [OUTER] JOIN TABLE2 AS T2
ON T1.COMMON_COLUMN = T2.COMMON_COLUMN ...

SELECT EMP_ID, NAME, DEPT 
FROM COMPANY 
FULL OUTER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID;

emp_id	name	dept
1	Paul	IT Billing
2	Allen	Engineering
7		Finance
	David
	Paul
	Mark

Aggregation

An aggregation is the result returned from running an aggregate function in a query and is displayed in a new column in the results table.

An aggregate function is a function that results of a set of the data queried being condensed into an aggregation.

sum()

sum() function returns the sum of all numeric value in a specified column.

Syntax:

SELECT SUM(COLUMN) FROM TABLE;

count(*)

count(*) function is used to count number of input rows in the table.

Syntax:

SELECT COUNT(*) FROM TABLE;

count(Expression)

counts number of input rows for which the value of expression is not null.

Syntax:

SELECT COUNT(COLUMN) FROM TABLE;

avg(expression)

The average (arithmetic mean) of all non-null input values.

Syntax:

SELECT AVG(COLUMN) FROM TABLE;

min(expression)

returns minimum value of expression across all non-null input values.

Syntax:

SELECT MIN(COLUMN) FROM TABLE;

max(expression)

returns maximum value of expression across all non-null input values.

Syntax:

SELECT MAX(COLUMN) FROM TABLE;

Filters in queries

Filters in queries are used to reduce the data that is displayed on the output or to reduce the data in the calculation of an aggregate function.

WHERE clause:

WHERE clause is used to specify a condition while fetching the data from single table or joining with multiple tables.

If the given condition is satisfied, only then it returns specific value from the table. You can filter out rows that you do not want included in the result-set by using the WHERE clause.

The WHERE clause not only is used in SELECT statement, but it is also used in UPDATE, DELETE statement, etc., which we would examine in subsequent chapters.

Syntax:

SELECT column1, column2, columnN
FROM table_name
WHERE [search_condition]

GROUP BY clause:

The GROUP BY clause divides the rows returned from the SELECT statement into groups. For each group, you can apply an aggregate function e.g., SUM() to calculate the sum of items or COUNT() to get the number of items in the groups.

Syntax:

SELECT column_1, column_2,..., aggregate_function(column_3)
FROM table_name
GROUP BY column_1,column_2,...;

HAVING clause

The HAVING clause specifies a search condition for a group or an aggregate. The HAVING clause is often used with the GROUP BY clause to filter groups or aggregates based on a specified condition.

Syntax:

SELECT column1,aggregate_function (column2)
FROM table_name
GROUP BY column1
HAVING condition;

Normalization

Normalization is the process to eliminate data redundancy and enhance data integrity in the table. Normalization also helps to organize the data in the database. It is a multi-step process that sets the data into tabular form and removes the duplicated data from the relational tables.

Normalization organizes the columns and tables of a database to ensure that database integrity constraints properly execute their dependencies. It is a systematic technique of decomposing tables to eliminate data redundancy (repetition) and undesirable characteristics like Insertion, Update, and Deletion anomalies.

1st Normal Form (1NF)

A table is referred to as being in its First Normal Form if atomicity of the table is 1.
Here, atomicity states that a single cell cannot hold multiple values. It must hold only a single-valued attribute.
The First normal form disallows the multi-valued attribute, composite attribute, and their combinations.

2nd Normal Form (2NF)

The first condition for the table to be in Second Normal Form is that the table has to be in First Normal Form. The table should not possess partial dependency. The partial dependency here means the proper subset of the candidate key should give a non-prime attribute.

3rd Normal Form (3NF)

The first condition for the table to be in Third Normal Form is that the table should be in the Second Normal Form.
The second condition is that there should be no transitive dependency for non-prime attributes, which indicates that non-prime attributes (which are not a part of the candidate key) should not depend on other non-prime attributes in a table. Therefore, a transitive dependency is a functional dependency in which A → C (A determines C) indirectly, because of A → B and B → C (where it is not the case that B → A).
The third Normal Form ensures the reduction of data duplication. It is also used to achieve data integrity.

Boyce Codd Normal Form (BCNF)

Boyce Codd Normal Form is also known as 3.5 NF. It is the superior version of 3NF and was developed by Raymond F.
Boyce and Edgar F. Codd to tackle certain types of anomalies which were not resolved with 3NF.
The first condition for the table to be in Boyce Codd Normal Form is that the table should be in the third normal form.
Secondly, every Right-Hand Side (RHS) attribute of the functional dependencies should depend on the super key of that particular table.

Indexes

Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Simply put, an index is a pointer to data in a table. An index in a database is very similar to an index in the back of a book.

PostgreSQL provides several index types: B-tree, Hash, GiST, SP-GiST and GIN. Each Index type uses a different algorithm that is best suited to different types of queries. By default, the CREATE INDEX command creates B-tree indexes, which fit the most common situations.

Syntax:

CREATE INDEX index_name ON table_name;

Transactions

Transactions are a fundamental concept of all database systems.

The essential point of a transaction is that it bundles multiple steps into a single, all-or-nothing operation.

The intermediate states between the steps are not visible to other concurrent transactions, and if some failure occurs that prevents the transaction from completing, then none of the steps affect the database at all.

Locking mechanism

PostgreSQL locks often called “write locks” or “exclusive locks,” restrict users from modifying a row or a PostgreSQL table’s contents.

Rows that have undergone a DELETE or UPDATE operation will be locked solely until the transaction is finished.

Other users won’t be able to change the same rows until the transaction is rolled back or committed.

Users won’t have to wait to alter various rows because this locking mechanism only engages when they attempt to modify the same rows.

PostgreSQL serializes changes to vital portions of the database using many layers of locks to ensure that complex transactions can run safely in parallel.

Until they attempt to acquire a conflicting lock, such as when they update the same row, transactions are carried out concurrently.

The first transaction to obtain the lock may then continue, while the second transaction must wait until the first transaction commits or aborts before proceeding.

Although some locking in databases happens automatically, there are some circumstances when locking must be done manually. The PostgreSQL LOCK command enables manual locking.

Database Isolation Levels

In database systems, isolation determines how transaction integrity is visible to other users and systems.

A lower isolation level increases the ability of many users to access the same data at the same time, but increases the number of concurrency effects (such as dirty reads or lost updates) users might encounter.

Conversely, a higher isolation level reduces the types of concurrency effects that users may encounter, but requires more system resources and increases the chances that one transaction will block another.

Dirty Read : A Dirty read is a situation when a transaction reads data that has not yet been committed.
Non Repeatable read : Non Repeatable read occurs when a transaction reads the same row twice and gets a different value each time.
Phantom Read : Phantom Read occurs when two same queries are executed, but the rows retrieved by the two, are different.

Based on these phenomena, The SQL standard defines four isolation levels:

Read Uncommitted : Read Uncommitted is the lowest isolation level. In this level, one transaction may read not yet committed changes made by other transactions, thereby allowing dirty reads. At this level, transactions are not isolated from each other.
Read Committed : This isolation level guarantees that any data read is committed at the moment it is read. Thus it does not allow dirty read. The transaction holds a read or write lock on the current row, and thus prevents other transactions from reading, updating, or deleting it.
Repeatable Read : This is the most restrictive isolation level. The transaction holds read locks on all rows it references and writes locks on referenced rows for update and delete actions. Since other transactions cannot read, update or delete these rows, consequently it avoids non-repeatable read.
Serializable : This is the highest isolation level. A serializable execution is guaranteed to be serializable. It is defined to be an execution of operations in which concurrently executing transactions appears to be serially executing.

Triggers

A trigger is a stored procedure in database which automatically invokes whenever a special event in the database occurs.

For example, a trigger can be invoked when a row is inserted into a specified table or when certain table columns are being updated.

Syntax:

create trigger [trigger_name] 
[before | after]  
{insert | update | delete}  
on [table_name]  
[for each row]  
[trigger_body]

Listing Triggers

You can list down all the triggers in the current database from pg_trigger table as follows

postgres=# SELECT * FROM pg_trigger;

Dropping Triggers

The following is the DROP command, which can be used to drop an existing trigger

postgres=# DROP TRIGGER trigger_name;

Resources:

PostgreSQL Documentation
Locking Mechanism
Database isolation levels
Triggers

SOLID Principles

Ravi Shankar — Mon, 20 Feb 2023 06:31:29 +0000

The SOLID principles are set of five design principles that are commonly used in object-oriented programming.

If you follow, these principles you can improve the reliability of your code by working on its structure and its logical consistency.

They are:

Single Responsibility Principle (SRP)
Open/Closed Principle (OCP)
Liskov Substitution Principle (LSP)
Interface Segregation Principle (ISP)
Dependency Inversion Principle (DIP)

Single Responsibility Principle (SRP)

A class should have only one reason to change.In python, you can apply the SRP by creating small, focused classes that do one thing well.

In the below example, we have two classes that have single responsibility.
The Calculator class handles mathematical operations, and the CalculatorPrinter class handles printing the results.

class Calculator:

    def add(self, a, b):
        return a + b;

    def sub(self, a, b):
        return a - b;

class CalculatorPrinter:

    def print_result(self, result):
        print("The result is ", result)

Open/Closed Principle (OCP)

A class should be open for extension but closed for modification. In python we can apply OCP by using inheritance or composition to add new functionality to a class instead of modifying it's existing code.

class Shape:

    def area(self):
        pass


class Rectangle(Shape):

    def __init__(self, width, height):
        self.width = width
        self.height = height

    def area(self):
        return self.width * self.height


class Circle(Shape):

    def __init__(self, radius):
        self.radius = radius

    def area(self):
        return 3.14 * self.radius ** 2

In this example, we have a base Shape class that defines an area method. We also have two subclasses, Rectangle and Circle, that extends the Shape class and implement the area method.

Liskov Substitution Principle (LSP)

Subtypes should be substitutable for their base types.
In python, we can apply the LSP by ensuring that subclasses can be used in place of their parent classes without caysing unexpected behaviour.

class Bird:

    def fly(self):
        print("Flying")
        pass

class Penguin(Bird):

    def fly(self):
        raise Exception("I can't fly")

def do_fly(bird):
    bird.fly()

In this example, we have a Penguin subclass that overrides the fly method of it's parent class Bird.
We can pass an instance of Penguin to a function that excepts a Bird instance and the fly method of Penguin class will be called.

Interface Segregation Principle (ISP)

Clients should not depend on interfaces they do not use.In python, you can ISP by creating small focused interfaces that contain only the methods properties that a client needs.

In this example, we have three interfaces, Animal, Carnivore, and Herbivore that contains only the methods and properties that a client needs. We also have two subclasses, Lion and Giraffe that implements the methods of their respective interfaces.

class Animal:

    def eat(self):
        pass

class Carnivore(Animal):

    def hunt(self):
        pass

class Herbivore(Animal):

    def graze(self):
        pass

class Lion(Carnivore):

    def hunt(self):
        print("Lion is hunting")

class Giraffe(Herbivore):

    def graze(self):
        print("Giraffe is grazing")

Dependency Inversion Principle (DIP)

High-level modules should not depend on low-level modules and both should not depend on abstractions. In python, we can apply DIP by using dependency injection to separate the dependencies of a class from its implementation.

For example, instead of creating an instance of a database class inside another class, you can inject the database instance into the class's constructor or method. This way, the class depends on a abstraction instead of a concrete implementation, making it more flexible and easier to test.

class Database:
    def __init__(self, db_url):
        self.db_url = db_url

    def query(self,sql):
        pass

class UserDAO:
    def __init__(self, db):
        self.db = db

    def get_user(self, user_id):
        pass


db = Database("mysql://localhost")
user_dao = UserDAO(db)

References:

SOLID Coding in python

Object-Oriented Programming

Ravi Shankar — Thu, 16 Feb 2023 06:28:54 +0000

Class

A class is the blueprint or the prototype from which the objects are being created.It contains some attributes and methods.

class Person:
    def __init__(self, name):
        self.name = name

    def talk(self):
        print(f"Hey, I am {self.name}")

Object

The object is an entity that has a state and behavior associated with it. It may be any real-world object like a table,dog,car,etc.
An object has attributes and methods in it.

class Person:
    def __init__(self, name):
        self.name = name

    def talk(self):
        print(f"Hey, I am {self.name}")

p_1 = Person('raina')
print(p_1.name)
p_1.talk()

init method

The __init__ method is similar to constructor. It is executed as an object of a class is created. The method is useful to do any initialization to the object.

self

self represents the instance of a class.
self allows to access variables,attributes,and methods of a defined class in python.
self must always be the first argument of any class method.

Inheritance

Inheritance is the capability of one class to derive or inherit the properties from another class. The class that derives properties is called the derived class or child class and the class from which the properties are being derived is called the base class or parent class.
Types of Inheritance:
Single Inheritance
Single-level inheritance enables a derived class to inherit characteristics from a single-parent class.

# parent class
class Person:
    def __init__(self, name, age):
        self.name = name
        self.age = age

    def details(self):
        print(f"I am {self.name}")
        print(f"My id is {self.age}")


# child class
class Employee(Person):
    def __init__(self, name, age, pay, job):
        self.pay = pay
        self.job = job
        Person.__init__(self,name, age)

    def details(self):
        super().details()
        print(f"My job is {self.job}")
        print(f"My salary is {self.pay}")

Emp_1 = Employee('raina',24,10000,"Software")
Emp_1.details()

Multilevel Inheritance
Multi-level inheritance enables a derived class to inherit properties from an immediate parent class which in turn inherits properties from his parent class.

class Person:
    def __init__(self, name, age):
        self.name = name
        self.age = age

    def details(self):
        print(f"I am {self.name}")
        print(f"My id is {self.age}")

    def fun(self):
        print("Inside Person class")


class Employee(Person):
    def __init__(self,name,age,pay, job):
        self.pay = pay
        self.job = job
        Person.__init__(self,name,age)

    def details(self):
        super().details()
        print(f"My job is {self.job}")
        print(f"My salary is {self.pay}")

    def run(self):
        print("Inside Employee class")


class Manager(Employee):
    def __init__(self,name,age,pay,job,exp):
        self.exp = exp
        Employee.__init__(self,name,age,pay,job)

    def details(self):
        super().details()
        print(f"Experience : {self.exp}")


Man_1 = Manager('raina',24,1000,"Software",5)
Man_1.details()
Man_1.fun()
Man_1.run()

Hierarchical Inheritance
Hierarchical level inheritance enables more than one derived class to inherit properties from a parent class.

class Vehical:
    def __init__(self,wheels,seats,doors):
        self.wheels = wheels
        self.seats = seats
        self.doors = doors

    def horn(self):
        print("Honk Honk")

    def start(self):
        print("Started")

    def stop(self):
        print("Stoped")


class Car(Vehical):
    def __init__(self,wheels,seats,doors):
        Vehical.__init__(self,wheels,seats,doors)

    def display(self):
        print("It is a car")


class Bus(Vehical):
    def __init__(self,wheels,seats,doors):
        Vehical.__init__(self,wheels,seats,doors)

    def display(self):
        print("It is a Bus")


omini = Car(4,6,4)
bus1 = Bus(6,20,2)
omini.start()
bus1.start()

Multiple Inheritance
Multiple level inheritance enables one derived class to inherit properties from more than one base class.

class Alpha:
    def SayHi(self):
        print("Hi inside Alpha class")


class Beta:
    def SayBye(self):
        print("Bye from Beta class")


class Gamma(Alpha,Beta):
    def display(self):
        print("Welcome to Gamma class")
        super().SayHi()
        super().SayBye()


a1 = Gamma()
a1.display()

Polymorphism

Polymorphism defines the ability to take different forms. Polymorphism in Python allows us to define methods in the child class with the same name as defined in their parent class.

Method Overriding

class A:
    def display(self):
        print("Inside A class")

class B(A):
    def display(self):
        print("Inside class B")

a1 = B()
a1.display()

Encapsulation

The process of wrapping up variables and methods into a single entity is known as Encapsulation.It acts as a protective shield that puts restrictions on accessing variables and methods directly.

Using double underscore (__variablename) to make a variable private and single underscore (_variablename) to make it protected.

Private variables can be accessed within the same class.
Protected variables can be accessed within the same class and sub classes.

Encapsulation has some benefits, some of which are :

Data Hiding
Flexibility
Reusability

class Tech:

    def __init__(self, name, course):
        self.name = name
        # private variable
        self.__course = course

    def set__course(self,c):
        self.__course = c

    def get__course(self):
        return self.__course 


t1 = Tech('Raina', 'Python')
print("Name:",t1.name)

# accessing private variable __course using 
# _Tech__course name
print("Course:",t1._Tech__course)

#accessing private variable using setter and getter methods
t1.set__course('Java')
print("Course:",t1.get__course())

Abstraction

Abstraction refers to hiding unnecessary details to focus on the whole product instead of parts of the project separately. It is a mechanism that represents the important features without including implementation details. Abstraction helps us in partitioning the program into many independent concepts so we may hide the irrelevant information in the code. It offers the greatest flexibility when using abstract data-type objects in different situations.


from abc import ABC, abstractmethod

class Vehicle(ABC):

    @abstractmethod
    def start(self):
        pass

class Car(Vehicle):
    def start(self):
        print("Car has started")

nano = Car()
nano.start()

References:
Python Documentation
OOPs in python by freecodecamp

Array Methods

Ravi Shankar — Thu, 16 Feb 2023 04:08:01 +0000

Array are used to store elements of same data type.
We import array module to use arrays in python.

import array as arr
arr_1 = arr.array('i', [10,20,30,40])
print(arr_1)

append()

append() function is used to add an element at the end of the array.

import array as arr
arr_1 = arr.array('f', [10,11,12,13])
arr_1.append(14)
print(arr_1)

reverse()

Reverses the order of an array.

import array as arr
arr_1 = arr.array('d', [10,11,12,13])
arr_1.reverse()
print(arr_1)

pop()

pop() function is used to remove an element from the specified position.

import array as arr
arr_1 = arr.array('l', [10,11,12,13])
arr_1.pop(2)
print(arr_1)

count()

Returns the count of given number.

import array as arr
arr_1 = arr.array('f', [10,11,10,10])
print(arr_1.count(10))

insert()

Adds an element at the specified position in the array.

import array as arr
arr_1 = arr.array('i', [10,11,12,13])
arr_1.insert(4,14)
print(arr_1)

remove()

Removes the first element with the specified value.

import array as arr
arr_1 = arr.array('d', [10,11,10,12,13])
arr_1.remove(10)
print(arr_1)

index()

Returns the index of the first element in an array with the specified value.

import array as arr
arr_1 = arr.array('i', [10,20,30,40,10])
print(arr_1.index(10))

sorted()

sorts the array in the ascending order.

import array as arr
arr_1 = arr.array('f', [19, 14, 16, 12, 10, 9])
print(sorted(arr_1))

Reference:
Array Methods

String Methods

Ravi Shankar — Thu, 16 Feb 2023 04:05:29 +0000

Strings in python are enclosed in single quotes('..') or double quotes("..").

print("Hello World!")
print('Python')

You can use three single quotes or three double quotes to assign multi line string to a variable.

var = '''The output string is 
enclosed in quotes and 
special characters are escaped with backslashes.'''
print(var)

String Length len()

To get the length of a string, use the len() function.

print(len(var))

split()

To get a list of substrings from the given string we use split() function.

Syntax: str.split(separator, maxsplit)

str1 = "Hello World"
print(str1.split())

If we don't specify any separator it takes space as default value.

Maxsplit is used to specify number of splits we need and it's default value is -1.

join()

To join a list of substrings into a single string we use join() function.

Syntax: str.join(iterable)

list_1 = ['Hello','World!']
string1 = " ".join(list_1)

index()

To get the index of a substring we use index() function.

str1 = 'Python Programming'
print(str1.index('i'))

It raises an ValueError exception if substring is not found.

lower()

Converts all uppercase letters in string to lowercase.

str1 = 'Python Programming'
print(str1.lower())

upper()

Converts all lowercase letters in string to uppercase.

str1 = 'Python Programming'
print(str1.upper())

islower()

Returns True only when all the characters in string are in lowercase and False otherwise.

str1 = 'Python Programming'
print(str1.islower())

isupper()

Returns True only when all the characters in string are in uppercase and False otherwise.

str1 = 'Python Programming'
print(str1.isupper())

isdigit()

Returns True only when all the characters in string are digits and False otherwise.

str1 = '123'
print(str1.isdigit())

isalpha()

Returns True only when all the characters in string are alphabetic and False otherwise.

str1 = 'Python Programming'
print(str1.isalpha())

capitalize()

Converts the first character in string to upper case and all other characters to lowercase.

str1 = 'python'
print(str1.capitalize())

casefold()

Returns a copy of string in lowercase.

str1 = 'Python Programming'
print(str1.casefold())

endswith()

Returns True if the string ends with the specified suffix, otherwise False.

str1 = 'Python Programming'
print(str1.endswith('ing'))

startswith()

Returns True if the string starts with the specified prefix, otherwise False.

str1 = 'Python Programming'
print(str1.startswith('Pyth'))

swapcase()

Returns a string with uppercase characters converted to lowercase and vice versa.

str1 = 'pYTHON pROGRAMMING'
print(str1.swapcase())

strip()

Returns a copy of the string with leading and trailing characters removed.

Syntax: str.strip([chars])

The chars argument is a string specifying the set of characters to be removed.

By default chars set to remove whitespace.

str1 = '  Python   '
print(str1.strip())

replace()

Syntax: str.replace(old, new)

Returns a copy of the string with all occurrences of old substring replaces by new substring.

str1 = 'Python Programming'
print(str1.replace('Python', 'Java'))

Reference:
String Methods

Basic Linux Commands

Ravi Shankar — Thu, 02 Feb 2023 04:54:42 +0000

sort

sort command is used to sort the data inside the file.

If we don't mention any options it will sort the lines which begin with

Empty Lines
Numerical Values
Alphabetical Order

Syntax of sort command

sort [ options ] filename

These are some of the options used along with sort command
-b To ignore leading spaces
-d To sort in dictionary order
-f Converts all lower case to upper case while comparing
-r To reverse sort
-M To sort according to months

Examples

sort -b filename
sort -db filename

date

date command is used to display the date and time on the system and also to set the date and time.

Syntax of date command

date [OPTION]... [+FORMAT]
date [-u|--utc|--universal] [MMDDhhmm[[CC]YY][.ss]]

Example
To print the date and time

date

Options with date command
-s To set the date and time
-d To display time described by input string

tree

tree command displays the directories and files in a tree like structure.

Syntax

tree [options]

Example
To print the directories and files in tree like structure

tree

Options used with tree
-a All files are listed
-d Only directories are listed
-f Prints the full path for each file
-o Output to given file istead of stdout

wc

wc command is used to count newlines, words and characters in a file

Syntax

wc [OPTION] [FILE]

Example
To print the word count in a file

wc filename

Options used with wc command
-w Prints word counts
-c Prints character counts
-b Prints byte counts
-l Prints new line counts

ping

ping command is used to check network connection between host and server/host.
Along with ping we use ip address of a host or url.

Syntax

ping [options] <destination>

Options
-c Stop after count replies
-l Send preload number of packages while waiting replies
-s Use size as number of bytes to be sent
-W Time to wait for response

Example

ping -c 3 www.facebook.com

ifconfig

Ifconfig is used to configure the kernel-resident network interfaces.
It is used at boot time to set up interfaces as necessary.
After that, it is usually only needed when debugging or when system tuning is needed.

Syntax

ifconfig [-a] [-s] [interface]

Options
-a Displays all the interfaces available
-s Displays short list instead of details

Example

ifconfig -s

ssh

ssh is a program for logging into a remote machine and for executing commands on a remote machine.
It is intended to provide secure encrypted communications between two untrusted hosts over an insecure network.

Syntax

ssh username host

username represents the account that is being accessed on the host.
host refers to the machine which can be a computer or a router that is being accessed.