Python Databases | SQLite, PostgreSQL, and ORMs Explained

· 20분 읽기 · 수정 Intermediate Tutorial
Python Databases | SQLite, PostgreSQL, and ORMs Explained

이 글의 핵심

From raw SQLite to SQLAlchemy ORM: connect, model tables, run CRUD, define relationships, and wire a small Flask API.

Introduction

“Persist your data reliably”

A database is the core technology for storing and managing data safely.

1. SQLite basics

Using SQLite

import sqlite3

# Connect
conn = sqlite3.connect('mydb.db')
cursor = conn.cursor()

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

# Insert row
cursor.execute(
    'INSERT INTO users (name, email, age) VALUES (?, ?, ?)',
    ('Alice', '[email protected]', 25)
)
conn.commit()

# Query
cursor.execute('SELECT * FROM users')
users = cursor.fetchall()
for user in users:
    print(user)

# Close
conn.close()

2. SQLAlchemy ORM

Install and setup

pip install sqlalchemy
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

# Engine
engine = create_engine('sqlite:///mydb.db')
Base = declarative_base()
Session = sessionmaker(bind=engine)
session = Session()

Defining models

class User(Base):
    __tablename__ = 'users'
    
    id = Column(Integer, primary_key=True)
    name = Column(String(100), nullable=False)
    email = Column(String(100), unique=True)
    age = Column(Integer)
    
    def __repr__(self):
        return f"<User(name='{self.name}', email='{self.email}')>"

# Create tables
Base.metadata.create_all(engine)

3. CRUD operations

Create

# Single row
new_user = User(name='Alice', email='[email protected]', age=25)
session.add(new_user)
session.commit()

# Multiple rows
users = [
    User(name='Bob', email='[email protected]', age=30),
    User(name='Carol', email='[email protected]', age=28)
]
session.add_all(users)
session.commit()

Read

# All rows
all_users = session.query(User).all()
for user in all_users:
    print(user.name, user.email)

# Filter
young_users = session.query(User).filter(User.age < 30).all()

# One row
user = session.query(User).filter(User.email == '[email protected]').first()
print(user.name)

# Count
count = session.query(User).count()
print(f"Total users: {count}")

Update

# Update via object
user = session.query(User).filter(User.name == 'Alice').first()
user.age = 26
session.commit()

# Bulk update
session.query(User).filter(User.name == 'Alice').update({'age': 27})
session.commit()

Delete

# Delete by object
user = session.query(User).filter(User.name == 'Alice').first()
session.delete(user)
session.commit()

# Bulk delete
session.query(User).filter(User.age < 20).delete()
session.commit()

4. Relationships

One-to-many

from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship

class User(Base):
    __tablename__ = 'users'
    
    id = Column(Integer, primary_key=True)
    name = Column(String(100))
    posts = relationship('Post', back_populates='author')

class Post(Base):
    __tablename__ = 'posts'
    
    id = Column(Integer, primary_key=True)
    title = Column(String(200))
    content = Column(String)
    user_id = Column(Integer, ForeignKey('users.id'))
    author = relationship('User', back_populates='posts')

# Usage
user = User(name='Alice')
post1 = Post(title='First post', content='Body', author=user)
post2 = Post(title='Second', content='Body2', author=user)

session.add_all([user, post1, post2])
session.commit()

# Navigate
user = session.query(User).first()
for post in user.posts:
    print(post.title)

5. Practical example

Flask + SQLAlchemy API

from flask import Flask, jsonify, request
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///blog.db'
db = SQLAlchemy(app)

class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(200), nullable=False)
    content = db.Column(db.Text)
    
    def to_dict(self):
        return {
            'id': self.id,
            'title': self.title,
            'content': self.content
        }

# Create tables
with app.app_context():
    db.create_all()

@app.route('/api/posts', methods=['GET'])
def get_posts():
    posts = Post.query.all()
    return jsonify([post.to_dict() for post in posts])

@app.route('/api/posts', methods=['POST'])
def create_post():
    data = request.get_json()
    post = Post(title=data['title'], content=data['content'])
    db.session.add(post)
    db.session.commit()
    return jsonify(post.to_dict()), 201

if __name__ == '__main__':
    app.run(debug=True)

Practical tips

Database tips

# Connection context
with engine.connect() as conn:
    result = conn.execute(query)

# Transactions
try:
    session.add(user)
    session.commit()
except Exception as e:
    session.rollback()
    raise

# Avoid N+1 queries
from sqlalchemy.orm import joinedload
users = session.query(User).options(
    joinedload(User.posts)
).all()

Summary

Key takeaways

  1. SQLite: file-based, great for learning and small apps
  2. SQLAlchemy: popular Python ORM
  3. Models: classes map to tables
  4. CRUD: create, read, update, delete
  5. Relationships: ForeignKey and relationship

Next steps