Foreign Keys Constraint

Foreign Keys Tutorial

Introduction

Welcome to our comprehensive guide on foreign keys in MySQL! Foreign keys play a crucial role in building relationships between tables within a relational database. They are essential for maintaining data consistency, enforcing referential integrity, and establishing meaningful connections between data sets. In this tutorial, we will explore the concept of foreign keys, their significance, practical applications, advantages, and provide detailed instructions for adding and removing foreign keys from tables using MySQL syntax.

Understanding Foreign Keys

A foreign key is a field in one table that references the primary key of another table, creating a linkage between the two tables and representing a relationship between their data. Foreign keys enforce referential integrity, ensuring that values in the foreign key column correspond to values in the referenced primary key column.

Use Cases

  • In an e-commerce system, associating orders with customers.
  • In inventory management, linking products to specific categories.
  • Representing parent-child relationships in hierarchical data structures.

Advantages of Utilizing Foreign Keys

  • Referential Integrity: Maintains data consistency by preventing orphaned or inconsistent data.
  • Relationships: Facilitates meaningful connections between related tables.
  • Data Integrity: Enforces constraints, preventing the entry of invalid data.
  • Data Retrieval: Simplifies querying by providing structured relationships.
  • Cascading Actions: Enables automatic updates or deletions in related tables.

Adding Foreign Keys to a Table

You can define a foreign key while creating a table using the `FOREIGN KEY` constraint.

customer_id

first_name

last_name

email

101

Alice

Johnson

alice@example.com

102

Bob

Smith

bob@example.com

103

Charlie

Brown

charlie@example.com

104

Eva

Lee

eva@example.com

105

Frank

Davis

frank@example.com

Example Query:

				
					CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

				
			

Explanation: In this example, the `customer_id` column in the `orders` table is designated as a foreign key, referencing the `customer_id` column in the `customers` table.

Method 2: By Altering the Table

You can also add a foreign key to an existing table using the `ALTER TABLE` statement.

Example Query:

				
					ALTER TABLE order_items
ADD FOREIGN KEY (product_id) REFERENCES products(product_id);

				
			

Explanation: This query applies a foreign key constraint to the `product_id` column of the `order_items` table, referencing the `product_id` column in the `products` table.

Removing a Foreign Key from a Table

To eliminate a foreign key constraint from a table, you can employ the `ALTER TABLE` statement with the `DROP FOREIGN KEY` clause.

Example Query:

				
					ALTER TABLE orders
DROP FOREIGN KEY fk_customer;

				
			

Explanation: This query removes the foreign key constraint named `fk_customer` from the `orders` table.

Primary Keys Constraint

Primary Keys Tutorial

Introduction

Welcome to our comprehensive guide on primary keys in MySQL! Primary keys play a foundational role in relational databases, guaranteeing data integrity, optimizing indexing, and establishing vital relationships between tables. In this tutorial, we’ll dive deep into the world of primary keys, uncovering their significance, practical applications, advantages, and offering step-by-step guidance on how to add or remove primary keys from your tables using MySQL syntax.

Understanding Primary Keys

A primary key stands as a unique identifier for each record (row) within a table. It serves as the linchpin of relational databases, ensuring that no two rows share the same primary key value. These primary keys distinguish records uniquely, leading to efficient data retrieval and seamless cross-referencing between tables.

Practical Applications

  • Student Database: Identifying individual students in an educational database.
  • Inventory Management: Keeping track of product inventory with distinct product IDs.
  • E-commerce Records: Managing customer information within an e-commerce system.

Advantages of Employing Primary Keys

  • Impeccable Uniqueness: Guaranteeing each row boasts a one-of-a-kind identifier.
  • Swift Data Retrieval: Accelerating data access through indexed pathways.
  • Data Purity: Warding off duplicates or conflicting data.
  • Relationship Building: Facilitating table connections via foreign keys.
  • Seamless Joins: Enabling effortless data amalgamation across tables.
  • Query Optimization: Enhancing query performance to the fullest.

Adding Primary Keys to a Table

Method 1: During Table Creation

You can establish a primary key when creating a table using the `PRIMARY KEY` constraint.

Sample Query:

				
					CREATE TABLE students (
    student_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

				
			

student_id

first_name

last_name

1

John

Doe

2

Jane

Smith

3

Mike

Johnson

4

Emily

Brown

5

David

Lee

Explanation: In this instance, the `student_id` column is designated as the primary key while crafting the `students` table.

Method 2: Via Altering the Table

Alternatively, you can introduce a primary key to an existing table through the `ALTER TABLE` statement.

Sample Query:

				
					ALTER TABLE employees
ADD PRIMARY KEY (employee_id);

				
			

Explanation: This query imposes a primary key constraint on the `employee_id` column within the `employees` table.

Removing a Primary Key from a Table

To eliminate a primary key from a table, you can harness the `ALTER TABLE` statement, accompanied by the `DROP PRIMARY KEY` clause.

Sample Query:

				
					ALTER TABLE students
DROP PRIMARY KEY;

				
			

Explanation: This query eliminates the primary key constraint from the `students` table.

Python SQLite3 Module

Python SQLite3 Module Tutorial

Introduction

Welcome to our in-depth tutorial on the Python SQLite module! In the realm of data management, SQLite stands as a reliable, lightweight, and serverless database engine. The sqlite3 module in Python serves as a bridge to interact with SQLite databases, enabling developers to perform various operations seamlessly. In this tutorial, we’ll take an extensive journey through the SQLite module, understanding its features, discussing its unique aspects, and exploring the core CRUD (Create, Read, Update, Delete) operations through practical examples.

Features

Python’s sqlite3 module offers a range of features that make it an essential tool for working with SQLite databases:

  • Serverless: SQLite databases are self-contained and require no separate server setup.
  • Single File: The entire database is stored in a single file, simplifying deployment and management.
  • SQL Support: The module provides a comprehensive SQL interface for querying and modifying data.
  • Transactions: SQLite supports ACID-compliant transactions for data integrity.
  • Data Types: The module handles various data types and allows data validation and constraints.

How it is Different from Other Modules

While Python provides several database-related modules, the sqlite3 module is uniquely tailored to work seamlessly with SQLite databases. Unlike more heavyweight database solutions that require external servers or complex setup, SQLite, along with its module, is lightweight, serverless, and suitable for small to medium-sized projects.

CRUD Operations with Examples

Now let’s explore the core CRUD operations (Create, Read, Update, Delete) using the sqlite3 module. For these examples, we’ll create a simple “Students” table.

        1. Creating a Table:

				
					import sqlite3
conn = sqlite3.connect('mydatabase.db')
cursor = conn.cursor()
cursor.execute('''
    CREATE TABLE students (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        age INTEGER
    )
''')
conn.commit()

				
			
  1. Inserting Data:
				
					import sqlite3
conn = sqlite3.connect('mydatabase.db')
cursor = conn.cursor()
cursor.execute('INSERT INTO students (name, age) VALUES (?, ?)', ('Alice', 25))
conn.commit()

				
			
  1. Reading Data:
				
					import sqlite3
conn = sqlite3.connect('mydatabase.db')
cursor = conn.cursor()
cursor.execute('SELECT * FROM students')
rows = cursor.fetchall()
for row in rows:
    print(row)

				
			
  1. Updating Data:
				
					import sqlite3
conn = sqlite3.connect('mydatabase.db')
cursor = conn.cursor()
cursor.execute('UPDATE students SET age = ? WHERE name = ?', (26, 'Alice'))
conn.commit()

				
			
  1. Deleting Data:
				
					import sqlite3
conn = sqlite3.connect('mydatabase.db')
cursor = conn.cursor()
cursor.execute('DELETE FROM students WHERE name = ?', ('Alice',))
conn.commit()

				
			

Python JSON Module Tutorial

Python JSON Module Tutorial

Introduction

Welcome to our comprehensive guide on Python’s json module! In the world of data interchange and storage, JSON (JavaScript Object Notation) plays a pivotal role as a lightweight and human-readable format. Python’s json module equips developers with powerful tools to effortlessly handle JSON data, facilitating data serialization, deserialization, and manipulation. In this tutorial, we’ll embark on a journey through the capabilities of the json module, exploring its features, comparing it to other modules, and delving into a wide array of functions and methods with real-world examples.

Features

Python’s json module offers a range of features that make it an essential tool for working with JSON data:

  • Serialization: Convert Python objects into JSON-encoded strings.
  • Deserialization: Parse JSON-encoded strings into Python objects.
  • Human-Readable: JSON data is easily readable by both humans and machines.
  • Data Integrity: JSON ensures data integrity through structured representation.

How it is Different from Other Modules

While Python offers various modules for data manipulation and storage, the json module excels in its specialization for handling JSON data. Unlike general-purpose modules, the json module specifically addresses the challenges of working with JSON-encoded information, ensuring accurate data conversion and seamless interoperability with other systems.

Different Functions/Methods of the json Module with Examples

  1. json.dumps() – Serialize to JSON:

The dumps() function serializes Python objects to a JSON-encoded string.

				
					import json
data = {"name": "Alice", "age": 30}
json_string = json.dumps(data)
print(json_string)

				
			
  1. json.loads() – Deserialize from JSON:

The loads() function parses a JSON-encoded string into a Python object.

				
					import json
json_string = '{"name": "Alice", "age": 30}'
data = json.loads(json_string)
print(data["name"])

				
			
  1. json.dump() – Serialize to File:

The dump() function serializes Python objects to a JSON file.

				
					import json
data = {"name": "Alice", "age": 30}
with open("data.json", "w") as json_file:
    json.dump(data, json_file)

				
			
  1. json.load() – Deserialize from File:

The load() function parses a JSON file into a Python object.

				
					import json
with open("data.json", "r") as json_file:
    data = json.load(json_file)
print(data["age"])

				
			

Python datetime Module

Introduction

Welcome to our comprehensive guide on Python’s datetime module! In the world of programming, handling dates and times is a common requirement.

Features

The datetime module in Python boasts a range of features that make it an indispensable tool for working with date and time data:

  • Precise date and time representation.
  • Time zone awareness for handling time differences.
  • Arithmetic operations on dates and times.
  • Formatting and parsing of date and time strings.
  • Support for both Gregorian and Julian calendar systems.
  1. datetime.now() – Current Date and Time:

Returns the current date and time.

         2. datetime.combine() – Combine Date and Time:

Combines a date and a time into a single datetime object.

        3. datetime.strptime() – String to Datetime:

Converts a string to a datetime object based on a specified format.

Common Format Codes

Code Meaning
%Y Year (2025)
%y Year (25)
%m Month (01-12)
%B Month (full name)
%d Day (01-31)
%A Weekday (full name)
%H Hour (24 hr)
%I Hour (12 hr)
%M Minute
%S Second

        4. datetime.strftime() – Datetime to String:

Formats a datetime object as a string according to a given format.

        5. timedelta() – Time Interval:

Represents a duration of time, supporting arithmetic operations with datetime objects.

        6. datetime.date() – Extract Date:

Extracts the date portion from a datetime object.

        7. datetime.time() – Extract Time:

Extracts the time portion from a datetime object.

        8. datetime.replace() – Replace Components:

Creates a new datetime object by replacing specific components.

        9. datetime.weekday() – Weekday Index:

Returns the index of the weekday (0 for Monday, 6 for Sunday).

       10. datetime.isoweekday() – ISO Weekday:

Returns the ISO weekday (1 for Monday, 7 for Sunday).

       11. datetime.timestamp() – Unix Timestamp:

Returns the Unix timestamp (the number of seconds since January 1, 1970).

       12. datetime.astimezone() – Timezone Conversion:

Converts a datetime object to a different timezone.

       13. datetime.utcoffset() – UTC Offset:

Returns the UTC offset of a datetime object.

       14. datetime.timedelta.total_seconds() – Total Seconds:

Returns the total number of seconds in a timedelta object.

       15. datetime.fromtimestamp() – Datetime from Timestamp:

Creates a datetime object from a Unix timestamp.

Python sys Module

The argv list contains command-line arguments passed to the script.

The path list contains directories where Python searches for modules.

The version string provides information about the Python interpreter.

The platform string indicates the operating system platform.

The getsizeof() function returns the size of an object in bytes.

The exit() function terminates the program with an optional exit code.

The maxsize integer represents the maximum size of a list or range.

The modules dictionary contains information about loaded modules.

The exc_info() function returns information about the current exception.

Python Collection Module

The namedtuple() function creates a new subclass of tuple with named fields, enhancing code clarity.

The Counter() function creates a dictionary-like object to count occurrences of elements in an iterable.

The deque() function creates a double-ended queue, useful for fast appends and pops from both ends.

The defaultdict() function creates dictionaries with default values for missing keys.

The OrderedDict() function creates dictionaries that remember the order of insertion.

The ChainMap() function combines multiple dictionaries into a single view.

Python Exception Handling

Exception Handling

Exception Handling is used to manage runtime errors and prevent programs from crashing.

Exceptions occur when:

  • User enters invalid input
  • File not found
  • Network connection fails
  • Wrong operations (divide by zero)

Basic Structure


Basic Example


Catching Specific Exceptions


Multiple Except Blocks


Using else Block

Runs only when no exception occurs.


Using finally Block

Always runs — even if an error occurs.


Raise an Exception Yourself

Use raise for rules/validation.


Custom Exception


File Handling with Exception


Multiple Exceptions in One Line


Catch All Exceptions


Common Built-in Exceptions

ExceptionWhen it Occurs
ZeroDivisionErrorDivide by zero
ValueErrorWrong data type in conversion
TypeErrorUnsupported operations between types
FileNotFoundErrorFile does not exist
KeyErrorKey not found in dictionary
IndexErrorIndex out of range
NameErrorVariable not defined
ImportErrorModule not found

Real-Time Examples

User Input Validation


Retry File Opening Automatically


API Request Error Handling (Concept)

Python Modules

Python Modules Tutorial

Introduction

Python, renowned for its simplicity and versatility, owes a significant part of its power to modules. Modules are an essential concept in Python programming, enabling developers to organize code, enhance reusability, and maintain a clean project structure. In this tutorial, we’ll delve into the world of Python modules, exploring their significance, creation, unique features, and diverse applications.

Importance of Modules

Modules serve as building blocks that encapsulate code, variables, and functions, making it easier to manage and scale projects. By grouping related functionalities together, modules facilitate code readability, reduce redundancy, and enable collaborative development. This modular approach enhances the maintainability and extensibility of Python applications.

Creating a Module

Creating a module is a straightforward process. To begin, save a collection of related functions and variables in a .py file. This file name becomes the module name. For instance, let’s create a simple module named math_operations:

				
					# math_operations.py
def add(a, b):
    return a + b

def subtract(a, b):
    return a - b

def multiply(a, b):
    return a * b

				
			

Features

Python modules offer a range of features that streamline development and optimize code organization:

  1. Namespace Isolation: Modules create separate namespaces, preventing naming conflicts between variables and functions.
  2. Reusability: Code encapsulated within modules can be easily reused in multiple projects.
  3. Modularity: Modules support a modular architecture, enhancing code separation and maintainability.
  4. Information Hiding: By controlling what is exposed in a module’s interface, you can encapsulate implementation details.
  5. Standard Library: Python’s standard library provides a plethora of pre-built modules, saving time and effort in coding common functionalities.

Different Python Modules

  1. Math Module: The math module offers a suite of mathematical functions. Let’s calculate the factorial of a number using the math module:
				
					import math
num = 5
factorial = math.factorial(num)
print(f"The factorial of {num} is {factorial}")

				
			
  1. Datetime Module: The datetime module simplifies date and time manipulation. Here’s an example of getting the current date and time:
				
					import datetime
current_datetime = datetime.datetime.now()
print(f"Current date and time: {current_datetime}")

				
			
  1. Random Module: The random module facilitates random number generation. Let’s generate a random integer between 1 and 100:
				
					import random
random_number = random.randint(1, 100)
print(f"Random number: {random_number}")

				
			
  1. JSON Module: The json module simplifies JSON encoding and decoding. Here, we’ll encode a Python dictionary as a JSON string:
				
					import json
data = {'name': 'John', 'age': 30, 'city': 'New York'}
json_string = json.dumps(data)
print(f"JSON representation: {json_string}")

				
			

Python OOPS

Python OOP – Fundamentals

Object-Oriented Programming (OOP) is a programming style organized around objects instead of functions.
It helps structure code that is scalable, reusable, and modular.


Four Pillars of OOP

Pillar Meaning Example
Encapsulation Hiding internal details Private variables
Abstraction Showing only necessary features Interfaces-like behavior
Inheritance Acquiring properties from parent class Parent → Child
Polymorphism Same function name, different behavior Method overriding

Basic Terminology

Term Meaning
Class Blueprint/template for an object
Object Instance created from a class
Method A function inside a class
Attribute Variables in a class (properties)
Constructor Initializes object values (__init__())
class Person:
    def __init__(self, name, age):
        self.name = name    # Instance attribute
        self.age = age

    def greet(self):
        print(f"Hello, my name is {self.name} and I am {self.age} years old.")

p1 = Person("Amit", 25)
p1.greet()

Description

  • Person is a class (blueprint).
  • p1 is an object/instance of the class.
  • __init__ is a constructor that initializes object attributes when created.
  • self refers to the current object.
  • greet() is an instance method, accessible using the object.

class Employee:
    company = "Google"  # Class variable (shared by all objects)

    def __init__(self, name):
        self.name = name  # Instance variable (unique to each object)

e1 = Employee("Sam")
e2 = Employee("Riya")
print(e1.company, e2.company)
print(e1.name, e2.name)

Description

  • company belongs to the class, so every Employee object uses “Google”.
  • name is an instance variable — each object has a different value.
  • Helps differentiate data shared across objects and unique to each.

Encapsulation refers to the concept of bundling data and methods that operate on that data into a single unit, i.e., a class. It prevents direct access to data from outside the class and promotes data hiding.

class BankAccount:
    def __init__(self, balance):
        self.__balance = balance  # Private variable

    def get_balance(self):
        return self.__balance  # Getter method

    def deposit(self, amount):
        self.__balance += amount  # Setter method

account = BankAccount(1000)
account.deposit(500)
print(account.get_balance())

Description

  • The variable __balance is private and cannot be accessed directly using object.
  • Encapsulation protects data from accidental modification.
  • We control access through getter & setter functions.

from abc import ABC, abstractmethod

class Shape(ABC):
    @abstractmethod
    def area(self):
        pass  # Abstract method (must be implemented)

class Circle(Shape):
    def __init__(self, r):
        self.r = r

    def area(self):
        return 3.14 * self.r * self.r

c = Circle(5)
print(c.area())

Description

  • Shape is an abstract class — cannot be instantiated.
  • Contains an abstract method that forces subclasses to implement it.
  • The Circle must provide its own area method → ensuring a standard structure.

class Sample:
    def __init__(self):
        print("Constructor executed")

    def __del__(self):
        print("Destructor executed")

obj = Sample()
del obj

Description

  • __init__ runs automatically when the object is created.
  • __del__ runs when the object is destroyed or the program ends.
  • Used for resource management (like closing files, DB connections, etc.)

  1. Class Method
  • In Python, a class method is a type of method that is bound to the class itself rather than to instances of the class.
  • It can access and modify class-level attributes and perform actions related to the class as a whole.
  • Class methods are defined using the @classmethod decorator and take the class itself as the first parameter, conventionally named cls.
  • This makes them different from instance methods, which take the instance itself (self) as the first parameter.
  1. Static Method
  • A static method is a method that is defined within a class but is not bound to the class instance or class-level attributes.
  • It doesn’t receive any implicit reference to the class or its instances as parameters. Static methods are defined using the @staticmethod decorator.
  • Static methods are often used to create utility functions that are logically related to the class but don’t require access to instance-specific or class-level data.
class Student:
    school = "DAV School"

    @staticmethod
    def welcome():
        print("Welcome to the School!")  # No object needed

    @classmethod
    def get_school_name(cls):
        return cls.school

Student.welcome()
print(Student.get_school_name())

Description

  • @staticmethod → Does not access class or object data. Used as helper logic.
  • @classmethod → Access class variables using cls.

class Car:
    def __init__(self, brand, model, price):
        self.brand = brand
        self.model = model
        self.price = price

    def details(self):
        print(f"{self.brand} {self.model} costs ₹{self.price}")

c1 = Car("Toyota", "Fortuner", 3500000)
c1.details()

Description

  • Represents real-world entities as Python objects.
  • Helps in designing software similar to real systems.