Python for Database Interaction: SQLite, MySQL, PostgreSQL 🗄️🐍

Python provides excellent database support using built-in and third-party libraries. Let’s explore how to interact with SQLite, MySQL, and PostgreSQL using Python.

1️⃣ SQLite – Lightweight Database 🗂️
🔹 SQLite is a built-in file-based database in Python.
🔹 Ideal for small applications, testing, and local storage.
🔹 No server setup required!

Installing SQLite (if needed)

pip install sqlite3
Basic SQLite Example

import sqlite3

# Connect to SQLite database (creates a file if it doesn’t exist)
conn = sqlite3.connect("example.db")
cursor = conn.cursor()

# Create a table
cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY,
name TEXT,
age INTEGER
)
''')

# Insert data
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("Alice", 25))

# Commit and close
conn.commit()
conn.close()
Fetching Data

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

cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()

for row in rows:
print(row)

conn.close()

✅ Key Features:

✔ Built into Python (no installation needed)

✔ Great for local data storage

✔ Easy to use with simple SQL commands

2️⃣ MySQL – Scalable Relational Database 🏛️

🔹 MySQL is a popular database for web applications and large-scale systems.
🔹 Requires a server setup (localhost or remote).

Installing MySQL Connector

pip install mysql-connector-python
Connecting to MySQL

import mysql.connector

# Connect to MySQL
conn = mysql.connector.connect(
host="localhost",
user="root",
password="yourpassword",
database="testdb"
)
cursor = conn.cursor()

# Create table
cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255),
age INT
)
''')

# Insert data
cursor.execute("INSERT INTO users (name, age) VALUES (%s, %s)", ("Bob", 30))
conn.commit()

# Fetch data
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
print(row)

conn.close()

✅ Key Features:

✔ Ideal for web applications

✔ Supports large datasets

✔ Used in Flask & Django projects

3️⃣ PostgreSQL – Advanced SQL Database 🏢
🔹 PostgreSQL is a powerful open-source database with advanced features.
🔹 Great for complex queries, scalability, and enterprise applications.

Installing psycopg2 (PostgreSQL Library)

pip install psycopg2
Connecting to PostgreSQL

import psycopg2

# Connect to PostgreSQL
conn = psycopg2.connect(
dbname="testdb",
user="postgres",
password="yourpassword",
host="localhost",
port="5432"
)
cursor = conn.cursor()

# Create table
cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
id SERIAL PRIMARY KEY,
name VARCHAR(255),
age INT
)
''')

# Insert data
cursor.execute("INSERT INTO users (name, age) VALUES (%s, %s)", ("Charlie", 28))
conn.commit()

# Fetch data
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
print(row)

conn.close()
✅ Key Features:
✔ High performance & supports big data
✔ Advanced querying features
✔ Used in data-heavy applications

4️⃣ Comparison: SQLite vs. MySQL vs. PostgreSQL

Feature SQLite MySQL PostgreSQL

Installation Built-in Requires MySQL server Requires PostgreSQL server

Best For Local apps, testing Web apps, e-commerce Data-heavy apps, analytics

Scalability Low Medium High
Speed Fast (small apps) Faster for reads Best for complex queries

    Date: 2025-03-24 00:00:00.000000