Python Databases | SQLite, PostgreSQL, and ORMs Explained

Python Databases | SQLite, PostgreSQL, and ORMs Explained

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

이 글의 핵심

Work with databases in Python: sqlite3, SQLAlchemy models, CRUD, relationships, and a Flask + SQLAlchemy API example—SQLite vs PostgreSQL and ORM trade-offs.

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', 'alice@example.com', 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='alice@example.com', age=25)
session.add(new_user)
session.commit()
# Multiple rows
users = [
    User(name='Bob', email='bob@example.com', age=30),
    User(name='Carol', email='carol@example.com', 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 == 'alice@example.com').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

Frequently Asked Questions (FAQ)

Q. When would I use this in practice?

A. Work with databases in Python: sqlite3, SQLAlchemy models, CRUD, relationships, and a Flask + SQLAlchemy API example—SQL…

Q. What should I read before this?

A. Follow the previous article or related articles links at the bottom of each post to learn in sequence. See the Python series index for the full picture.

Q. Where can I study this more deeply?

A. Check cppreference and the relevant library’s official documentation. The reference links at the end of the article are also worth using.

Other articles related to this topic.

Keywords Covered in This Article (Related Search Terms)

This article covers Python, Database, SQLite, PostgreSQL, SQLAlchemy, ORM.