将 FastAPI 连接到 PostgreSQL
使用 SQLAlchemy 在 FastAPI 应用与 PostgreSQL 数据库之间建立连接。
将 FastAPI 连接到 PostgreSQL 是 CoddyKit 上的免费 FastAPI Backend Development Bootcamp 课时。 这是第 2 节课，共 4 节。 你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。 这是 FastAPI Backend Development Bootcamp 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 FastAPI Backend Development Bootcamp 课程共包含 4 节课。
本课时的部分内容尚未翻译，以英文显示。
Why Connect to a Database?
Web applications often need to store and retrieve data persistently. This data could be user profiles, product listings, or transaction records.
Instead of losing data when your app restarts, we connect to a database. Databases provide a structured and efficient way to manage large amounts of information.
Introducing PostgreSQL
PostgreSQL is a powerful, open-source relational database system. It's known for its robustness, feature set, and performance.
- Reliable: Ensures data integrity.
- Scalable: Handles large data volumes and high user loads.
- Extensible: Supports custom data types and functions.
Many FastAPI applications use PostgreSQL as their primary data store.
The Database Connection URL
To connect to any database, you need a connection string or URL. This URL tells SQLAlchemy (our ORM) how to find and authenticate with your database.
For PostgreSQL, a typical URL looks like this:
postgresql://user:password@host:port/database_nameIt's best practice to store this URL in an environment variable for security and flexibility.
Creating the SQLAlchemy Engine
The first step in SQLAlchemy is to create an Engine. The Engine is responsible for communicating with the database.
We use create_engine from sqlalchemy to establish this connection. For our runnable example, we'll use SQLite, but the principle for PostgreSQL is the same – just the URL changes.
from sqlalchemy import create_engine
# For PostgreSQL, this would be:
# SQLALCHEMY_DATABASE_URL = "postgresql://user:password@localhost/dbname"
# For a runnable example, we'll use SQLite:
SQLALCHEMY_DATABASE_URL = "sqlite:///./sql_app.db"
engine = create_engine(
SQLALCHEMY_DATABASE_URL, connect_args={"check_same_thread": False}
)
print("Engine created successfully!")Managing Database Sessions
Directly interacting with the database through the engine isn't ideal for every request. Instead, we use Sessions.
A Session is like a temporary workspace for your database operations. It handles transactions and ensures changes are committed or rolled back properly.
We create a SessionLocal class using sessionmaker, which will produce session instances.
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
# Using SQLite for a runnable example
SQLALCHEMY_DATABASE_URL = "sqlite:///./sql_app.db"
engine = create_engine(
SQLALCHEMY_DATABASE_URL, connect_args={"check_same_thread": False}
)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
print("SessionLocal configured!")The Declarative Base (Review)
Recall from the previous lesson that declarative base is used to define your SQLAlchemy models. All your ORM models will inherit from this base.
Even though we won't define a new model here, it's an essential part of the setup for any ORM interaction.
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
print("Declarative Base initialized.")FastAPI Dependency: get_db()
FastAPI's powerful Dependency Injection system is perfect for managing database sessions.
We'll create a function, get_db, that creates a new database session for each request, uses it, and then closes it automatically. The yield keyword is key here!
from sqlalchemy.orm import Session
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
# Assuming these are defined elsewhere or imported
SQLALCHEMY_DATABASE_URL = "sqlite:///./sql_app.db"
engine = create_engine(
SQLALCHEMY_DATABASE_URL, connect_args={"check_same_thread": False}
)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
def get_db():
db = SessionLocal()
try:
yield db
finally:
db.close()
print("get_db dependency function defined.")Using the Session in an Endpoint
Now, you can inject the database session directly into your FastAPI endpoint functions using Depends.
FastAPI will call get_db(), pass the session to your route, and ensure it's closed after the request is handled.
from fastapi import FastAPI, Depends
from sqlalchemy.orm import Session
# For this snippet, we'll use a mock get_db to make it runnable
class MockSession:
def close(self): pass
def get_db_mock():
yield MockSession()
app = FastAPI()
@app.get("/db-status")
def read_db_status(db: Session = Depends(get_db_mock)):
# In a real app, 'db' would be your actual SQLAlchemy session
return {"message": "Database session received!"}
print("FastAPI endpoint defined using DB dependency.")Full FastAPI-PostgreSQL Setup
Here's a complete example bringing everything together. This app sets up the connection to a database (using SQLite for local testing, but easily swappable for PostgreSQL) and exposes an endpoint that uses a database session.
To run this with a real PostgreSQL, replace the SQLALCHEMY_DATABASE_URL and ensure your PostgreSQL server is running.
from fastapi import FastAPI, Depends
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, Session
from sqlalchemy.ext.declarative import declarative_base
import uvicorn
# --- Database Configuration (for PostgreSQL, replace URL) ---
# For a runnable example, we'll use SQLite in-memory:
SQLALCHEMY_DATABASE_URL = "sqlite:///./sql_app.db"
# For PostgreSQL, it would be:
# SQLALCHEMY_DATABASE_URL = "postgresql://user:password@localhost/dbname"
engine = create_engine(
SQLALCHEMY_DATABASE_URL, connect_args={"check_same_thread": False}
)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()
# --- Dependency to get DB session ---
def get_db():
db = SessionLocal()
try:
yield db
finally:
db.close()
# --- FastAPI Application ---
app = FastAPI()
@app.get("/connect-test")
def connect_test(db: Session = Depends(get_db)):
# We've successfully received a DB session 'db'
# In a real app, you'd perform DB operations here.
return {"status": "Connected to DB", "db_type": SQLALCHEMY_DATABASE_URL.split('://')[0]}
# To run this, save as 'main.py' and run 'uvicorn main:app --reload'
# if __name__ == "__main__":
# uvicorn.run(app, host="0.0.0.0", port=8000)Quick Check: DB Session
Consider the get_db dependency function:
def get_db():
db = SessionLocal()
try:
yield db
finally:
db.close()What is the primary reason for using yield db instead of return db in this FastAPI dependency?
Recap: Connecting FastAPI to PostgreSQL
In this lesson, you learned the essential steps to connect your FastAPI application to a database, specifically focusing on PostgreSQL (with runnable SQLite examples for convenience).
- We defined the Database URL to locate the database.
- We used
create_engineto establish the connection. - We set up
SessionLocalfor managing database sessions. - We created a
get_dbdependency usingyieldto inject and manage sessions per request in FastAPI.
Now your FastAPI app is ready to interact with a database! Next, we'll learn how to perform CRUD operations.
常见问题解答
「将 FastAPI 连接到 PostgreSQL」课时是免费的吗？
是的 — 「将 FastAPI 连接到 PostgreSQL」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 FastAPI Backend Development Bootcamp 课程的其余内容，请升级到 CoddyKit PRO。 FastAPI Backend Development Bootcamp 课程共包含 4 节课。
「将 FastAPI 连接到 PostgreSQL」这节课中我会学到什么？
使用 SQLAlchemy 在 FastAPI 应用与 PostgreSQL 数据库之间建立连接。 你通过在浏览器中直接运行的动手代码来练习 FastAPI Backend Development Bootcamp，全天候 AI 导师会在你学习这节课的过程中回答你的问题。
学习 FastAPI Backend Development Bootcamp 需要有经验吗？
无需任何先前经验。CoddyKit 上的 FastAPI Backend Development Bootcamp 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。 这是第 2 节课，共 4 节。
「将 FastAPI 连接到 PostgreSQL」课时需要多长时间？
大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。
我能在这节 FastAPI Backend Development Bootcamp 课中编写并运行代码吗？
能。每节 FastAPI Backend Development Bootcamp 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。
此课程中的所有课时
- SQLAlchemy ORM 基础
- 将 FastAPI 连接到 PostgreSQL
- 使用 SQLAlchemy 执行 CRUD 操作
- 使用 Alembic 进行数据库迁移