Skip to content

README.md

Latest commit

 

History

History
221 lines (175 loc) · 9.39 KB

README.md

File metadata and controls

221 lines (175 loc) · 9.39 KB
Raw

About

Python Database Connectivity (PDBC) is the mechanism to connect Python applications with relational databases (MySQL) using the standard Python DB-API 2.0 (PEP 249) interface.

Key Points:

  • Uniform Interface: Write database code once using standard DB-API methods (connect, cursor, execute, commit, close).
  • 1:1 Mirror Structure: Every theory concept in THEORY/ has an exact corresponding simple code script in PRACTICAL/.
  • Zero Complexity: Clean, direct, student-friendly code without unnecessary libraries or boilerplate.

Database Request Flow

flowchart LR
    A[Python Application] --> B[DB-API 2.0 Interface]
    B --> C[mysql-connector-python]
    C --> D[MySQL Server]
    D --> E[(Database: test)]
Loading

Repository Structure

Python_Database_Connectivity/
│
├── README.md                                          # Master documentation & guide
├── requirements.txt                                   # Dependencies
├── .gitignore                                         # Git ignore rules
├── LICENSE                                            # MIT License
│
├── THEORY/                                            # Point-by-point conceptual notes
│   ├── 01_db_api_fundamentals.md                     # DB-API 2.0, architecture & lifecycle
│   ├── 02_batch_operations_and_data_retrieval.md     # executemany, fetchone/all & dict cursor
│   └── 03_parameterized_queries_and_crud.md          # %s placeholders, dynamic queries & CRUD
│
└── PRACTICAL/                                         # Simple, runnable practical code
    ├── 01/                                            # Unit 01: Connection & Basics
    │   ├── 01_connection.py                           # Connect to MySQL & create table
    │   └── 02_insertion.py                            # Insert record & commit transaction
    ├── 02/                                            # Unit 02: Batch Operations & Fetching
    │   ├── 01_executemany.py                          # Batch insert using executemany()
    │   ├── 02_executemany_user_input.py               # Batch insert with user input
    │   ├── 03_fetchone.py                             # Fetch single row & cursor loop
    │   ├── 04_fetch_columns.py                        # Read column values by index
    │   ├── 05_dictionary_cursor.py                    # Dictionary cursor format
    │   ├── 06_count_records.py                        # Count records with fetchall()
    │   ├── 07_filter_salary.py                        # Filter records (salary > 50000)
    │   └── 08_search_name.py                          # Search records by employee name
    └── 03/                                            # Unit 03: Dynamic Queries & CRUD
        ├── 01_dynamic_insert.py                       # Dynamic insert with user input
        ├── 02_dynamic_query.py                        # Dynamic query with multiple conditions
        ├── 03_select_namelike.py                      # Search using LIKE pattern (%s)
        ├── 04_update_salary.py                        # Update salary by id
        ├── 05_delete_id.py                            # Delete employee by id
        ├── 06_in_operator.py                          # Query using IN operator
        └── 07_crud_operations.py                      # Complete CRUD operations flow

Mirrored Curriculum Index

Unit 01: Connection & DDL/DML Basics

Practical Script Description
01_connection.py Connect to MySQL server and create demo table
02_insertion.py Insert record into demo table with conn.commit()

Unit 02: Batch Operations & Data Fetching

Practical Script Description
01_executemany.py Insert multiple records at once using cursor.executemany()
02_executemany_user_input.py Read records dynamically from user and batch insert
03_fetchone.py Fetch single row with fetchone() and iterate remaining
04_fetch_columns.py Access row column values by tuple index (row[0])
05_dictionary_cursor.py Fetch records as dictionary keys (dictionary=True)
06_count_records.py Fetch all rows and count table records
07_filter_salary.py Query records where salary > 50000
08_search_name.py Safe parameterized search by employee name

Unit 03: Parameterized Queries & Dynamic CRUD

Practical Script Description
01_dynamic_insert.py Dynamic parameterized insert from user input
02_dynamic_query.py Dynamic select with multiple conditions (AND)
03_select_namelike.py Pattern search using SQL LIKE and wildcards
04_update_salary.py Update record salary based on employee id
05_delete_id.py Delete employee record by id
06_in_operator.py Query multiple ids using SQL IN operator
07_crud_operations.py Complete CRUD lifecycle (Create, Read, Update, Delete)

Standard Database Workflow

Every script in this repository follows the exact 7-step DB-API lifecycle:

1. import mysql.connector
       ↓
2. conn = mysql.connector.connect(...)
       ↓
3. cursor = conn.cursor()
       ↓
4. cursor.execute(query, params)
       ↓
5. rows = cursor.fetchall() / cursor.fetchone()
       ↓
6. conn.commit()  (for INSERT / UPDATE / DELETE)
       ↓
7. cursor.close() & conn.close()

Simple Template:

import mysql.connector

conn = None
cursor = None

try:
    conn = mysql.connector.connect(
        host="localhost",
        user="root",
        password="YOUR_PASSWORD",
        database="test"
    )
    cursor = conn.cursor()

    cursor.execute("SELECT * FROM demo")
    for row in cursor.fetchall():
        print(row)

except Exception as err:
    print(f"Error: {err}")

finally:
    if cursor is not None:
        cursor.close()
    if conn is not None and conn.is_connected():
        conn.close()

Setup

# 1. Clone the repository
git clone git@github.com:AnilYadav17/Python_Database_Connectivity.git
cd Python_Database_Connectivity

# 2. Install MySQL Connector
pip install mysql-connector-python

Learning Roadmap

  • DB-API 2.0 & PEP 249 Fundamentals
  • MySQL Connection & Status Verification
  • DDL Operations (Table Creation)
  • Record Insertion & commit()
  • Batch Operations with executemany()
  • Result Retrieval (fetchone, fetchall, cursor loop)
  • Column Access (Tuples vs Dictionary Cursor)
  • Parameterized Queries & SQL Injection Prevention
  • Pattern Matching with LIKE
  • Record Updates with Transaction Commit
  • Record Deletions by Primary Key
  • Filtering with IN Operator
  • Complete CRUD Workflow

Author

Anil Yadav
Computer Science Engineering Student
GitHub: @AnilYadav17

License

This project is licensed under the MIT License.