Using SQL in Python

SQL (Structured Query Language) is a standard language used to communicate with databases. Python provides multiple libraries such as sqlite3, SQLAlchemy, and integration with tools like Pandas and DuckDB to work with SQL seamlessly.

Why Use SQL in Python?

• Efficient querying of structured data
• Database integration for applications and analysis
• Combines the power of SQL with Python's flexibility
• Useful for handling large datasets directly in memory or files

Popular Libraries

• sqlite3 - Built-in module to interact with SQLite databases
• SQLAlchemy - ORM that allows SQL expressions and database abstraction
• Pandas.read_sql() - Read SQL query results into a DataFrame
• DuckDB - Fast analytical database with SQL interface, works well with Pandas

10 Examples

import sqlite3
import pandas as pd

# Example 1: Create connection and cursor
conn = sqlite3.connect(':memory:')
cursor = conn.cursor()

# Example 2: Create table
cursor.execute("CREATE TABLE students (id INTEGER, name TEXT, score REAL)")

# Example 3: Insert data
cursor.execute("INSERT INTO students VALUES (1, 'Alice', 85.5)")
conn.commit()

# Example 4: Query data
cursor.execute("SELECT * FROM students")
print(cursor.fetchall())

# Example 5: Use parameters
cursor.execute("SELECT * FROM students WHERE score > ?", (80,))
print(cursor.fetchall())

# Example 6: Read into Pandas DataFrame
pd.read_sql("SELECT * FROM students", conn)

# Example 7: Use Pandas to SQL
df = pd.DataFrame({'id': [2], 'name': ['Bob'], 'score': [90.0]})
df.to_sql('students', conn, if_exists='append', index=False)

# Example 8: SQLAlchemy ORM basics (requires install)
from sqlalchemy import create_engine
engine = create_engine('sqlite:///:memory:')

# Example 9: DuckDB in-memory SQL
import duckdb
duckdb.query("SELECT 1 + 1").fetchall()

# Example 10: Join query with Pandas
pd.read_sql_query("SELECT * FROM students s JOIN students t ON s.id = t.id", conn)