Database Connectivity in Python

1. What is Database Connectivity?

Database connectivity in Python means **connecting Python programs to a database** to store, retrieve, update, and delete data.

Databases are used to store data permanently, unlike variables which disappear when a program stops.

2. Why Databases are Needed

• Store large amounts of structured data
• Fast searching, sorting, filtering
• Multi-user access
• Data security and backup
• Used in web apps, ML, finance, ERP systems

3. Types of Databases in Python

4. SQLite with Python

Workflow

1. Import sqlite3
2. Create/connect to database
3. Create cursor
4. Execute SQL queries
5. Commit changes
6. Close connection

import sqlite3

conn = sqlite3.connect("students.db")
cursor = conn.cursor()

cursor.execute("""
CREATE TABLE IF NOT EXISTS students (
    id INTEGER PRIMARY KEY,
    name TEXT,
    age INTEGER
)
""")

cursor.execute("INSERT INTO students (name, age) VALUES (?, ?)", ("Alice", 22))
conn.commit()

cursor.execute("SELECT * FROM students")
print(cursor.fetchall())

conn.close()
      

5. MySQL with Python

Installation

pip install mysql-connector-python
      

Workflow

1. Install MySQL & connector
2. Create connection
3. Create cursor
4. Execute SQL
5. Commit
6. Close

import mysql.connector

conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="school"
)

cursor = conn.cursor()
cursor.execute("SELECT * FROM students")
print(cursor.fetchall())

conn.close()
      

6. SQLite vs MySQL

7. Real-Life Use Cases

• User login & authentication systems
• E-commerce orders & payments
• Student management systems
• ML dataset storage
• ERP & CRM software

8. External Learning Resources

• Python SQLite Official Docs
• MySQL Documentation
• Real Python – SQL Libraries
• SQL Basics (W3Schools)