Python Databases | SQLite, PostgreSQL, and ORMs Explained

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

• [Python web deployment](/en/blog/python-series-15-deployment/
• Pandas for data analysis

---

Related posts

• [Django basics | Get started with Python’s full-stack web framework](/en/blog/python-series-12-django-basics/

---

자주 묻는 질문 (FAQ)

Q. 이 내용을 실무에서 언제 쓰나요?

A. Work with databases in Python: sqlite3, SQLAlchemy models, CRUD, relationships, and a Flask + SQLAlchemy API example—SQL… 실무에서는 위 본문의 예제와 선택 가이드를 참고해 적용하면 됩니다.

Q. 선행으로 읽으면 좋은 글은?

A. 각 글 하단의 이전 글 또는 관련 글 링크를 따라가면 순서대로 배울 수 있습니다. Python 시리즈 목차에서 전체 흐름을 확인할 수 있습니다.

Q. 더 깊이 공부하려면?

A. cppreference와 해당 라이브러리 공식 문서를 참고하세요. 글 말미의 참고 자료 링크도 활용하면 좋습니다.

---

같이 보면 좋은 글 (내부 링크)

이 주제와 연결되는 다른 글입니다.

• [Django Basics](/en/blog/python-series-12-django-basics/
• [Python REST APIs | Build API Servers with Flask and Django](/en/blog/python-series-13-rest-api/
• [Node.js Database Integration: MongoDB, PostgreSQL, and MySQL](/en/blog/nodejs-series-06-database/
• [Sequelize ORM Complete Guide for Node.js | PostgreSQL](/en/blog/nodejs-sequelize-orm-guide/

---

이 글에서 다루는 키워드 (관련 검색어)

Python, Database, SQLite, PostgreSQL, SQLAlchemy, ORM 등으로 검색하시면 이 글이 도움이 됩니다.