你说得非常对！我之前虽然提到了 `models/` 目录，但没有给出 **完整的、可运行的模块化模型实现**。现在我将为你 **彻底重写并补全 `models/` 模块的完整结构和代码实现**，确保：
✅ 使用 `SQLModel`（兼容 SQLAlchemy）
✅ 按功能拆分为多个文件
✅ 支持异步数据库操作
✅ 所有模型可通过 `from models import X` 统一导入
✅ 与前面的 `database.py` 和路由完全兼容
---
## 🧱 最终目录结构（models 部分）
```bash
backend/
└── models/
├── __init__.py # 统一导出所有模型类
├── user.py # 用户 + 用户资料
├── department.py # 院系 + 用户-院系关联
├── post.py # 文章、评论、点赞
├── search.py # 搜索趋势
└── chat.py # 聊天会话 + 消息记录
```
---
## ✅ 第一步：`models/__init__.py` —— 统一接口入口
```python
# backend/models/__init__.py
"""
统一导出所有数据库模型，方便外部使用：
from models import User, Post, Comment, ...
"""
from .user import User, UserProfile
from .department import Department, UserDepartmentLink
from .post import Post, Comment, Like
from .search import SearchTrend
from .chat import ChatSession, Message
__all__ = [
"User",
"UserProfile",
"Department",
"UserDepartmentLink",
"Post",
"Comment",
"Like",
"SearchTrend",
"ChatSession",
"Message",
]
```
> 这样你就可以在任何地方用 `from models import User, Post` 来导入。
---
## ✅ 第二步：`models/user.py` —— 用户系统
```python
# backend/models/user.py
from sqlmodel import SQLModel, Field
from typing import Optional
from enum import Enum as SQLEnum
class RoleType(str, SQLEnum):
user = "user"
moderator = "moderator"
admin = "admin"
class User(SQLModel, table=True):
__tablename__ = "users"
id: int = Field(default=None, primary_key=True)
account: str = Field(unique=True, index=True, min_length=3, max_length=50)
password_hash: str = Field(min_length=60, max_length=128)
nickname: Optional[str] = Field(default=None, max_length=50)
avatar_url: str = Field(default="/static/default_avatar.png", max_length=255)
email: Optional[str] = Field(default=None, unique=True, max_length=100)
role: RoleType = Field(default=RoleType.user)
created_at: datetime = Field(default_factory=lambda: datetime.now())
updated_at: datetime = Field(default_factory=lambda: datetime.now())
class UserProfile(SQLModel, table=True):
__tablename__ = "user_profiles"
user_id: int = Field(foreign_key="users.id", primary_key=True)
personality: Optional[str] = Field(default=None)
role_setting: Optional[str] = Field(default=None)
max_history: int = Field(default=4)
created_at: datetime = Field(default_factory=lambda: datetime.now())
updated_at: datetime = Field(default_factory=lambda: datetime.now())
```
---
## ✅ 第三步：`models/department.py` —— 院系管理
```python
# backend/models/department.py
from sqlmodel import SQLModel, Field
class Department(SQLModel, table=True):
__tablename__ = "departments"
id: int = Field(default=None, primary_key=True)
name: str = Field(unique=True, max_length=100)
description: Optional[str] = Field(default=None)
created_at: datetime = Field(default_factory=lambda: datetime.now())
class UserDepartmentLink(SQLModel, table=True):
__tablename__ = "user_departments"
user_id: int = Field(foreign_key="users.id", primary_key=True)
dept_id: int = Field(foreign_key="departments.id", primary_key=True)
joined_at: datetime = Field(default_factory=lambda: datetime.now())
```
---
## ✅ 第四步：`models/post.py` —— 博客文章相关
```python
# backend/models/post.py
from sqlmodel import SQLModel, Field
from typing import Optional
from sqlalchemy import Column, Text
from enum import Enum as SQLEnum
class VisibilityType(str, SQLEnum):
public = "public"
private = "private"
friends = "friends"
class Post(SQLModel, table=True):
__tablename__ = "posts"
id: int = Field(default=None, primary_key=True)
user_id: int = Field(foreign_key="users.id")
title: str = Field(max_length=200)
content: str = Field(sa_column=Column("content", Text))
summary: Optional[str] = Field(default=None, max_length=500)
cover_image: Optional[str] = Field(default=None, max_length=255)
visibility: VisibilityType = Field(default=VisibilityType.public)
view_count: int = Field(default=0)
like_count: int = Field(default=0)
comment_count: int = Field(default=0)
tags: Optional[str] = Field(default=None) # JSON string
is_pinned: bool = Field(default=False)
created_at: datetime = Field(default_factory=lambda: datetime.now())
updated_at: datetime = Field(default_factory=lambda: datetime.now())
__table_args__ = (
Index("idx_created_at", "created_at"),
Index("idx_like_count", "like_count"),
Index("idx_view_count", "view_count"),
Index("idx_is_pinned", "is_pinned"),
Index("idx_user_id", "user_id"),
)
class Comment(SQLModel, table=True):
__tablename__ = "comments"
id: int = Field(default=None, primary_key=True)
post_id: int = Field(foreign_key="posts.id")
user_id: int = Field(foreign_key="users.id")
parent_id: Optional[int] = Field(default=None, foreign_key="comments.id")
content: str = Field()
like_count: int = Field(default=0)
created_at: datetime = Field(default_factory=lambda: datetime.now())
__table_args__ = (
Index("idx_post_id", "post_id"),
Index("idx_parent_id", "parent_id"),
)
class Like(SQLModel, table=True):
__tablename__ = "likes"
user_id: int = Field(primary_key=True, foreign_key="users.id")
target_type: str = Field(primary_key=True, max_length=20) # 'post' or 'comment'
target_id: int = Field(primary_key=True)
created_at: datetime = Field(default_factory=lambda: datetime.now())
__table_args__ = (
Index("idx_target", "target_type", "target_id"),
)
```
---
## ✅ 第五步：`models/search.py` —— 搜索趋势
```python
# backend/models/search.py
from sqlmodel import SQLModel, Field
from datetime import datetime
class SearchTrend(SQLModel, table=True):
__tablename__ = "search_trends"
keyword: str = Field(primary_key=True, max_length=100)
hit_count: int = Field(default=1)
last_searched_at: datetime = Field(default_factory=lambda: datetime.now())
is_hot: bool = Field(default=False)
```
---
## ✅ 第六步：`models/chat.py` —— 聊天系统
```python
# backend/models/chat.py
from sqlmodel import SQLModel, Field
from enum import Enum as SQLEnum
from datetime import datetime
class ChatType(str, SQLEnum):
ai_room = "ai_room"
multi_user_chat = "multi_user_chat"
roleplay = "roleplay"
class SenderType(str, SQLEnum):
user = "user"
ai = "ai"
system = "system"
class Character(SQLModel, table=True):
__tablename__ = "characters"
id: int = Field(default=None, primary_key=True)
name: str = Field(max_length=100)
trait: str = Field() # 角色设定描述
created_at: datetime = Field(default_factory=lambda: datetime.now())
updated_at: datetime = Field(default_factory=lambda: datetime.now())
class ChatSession(SQLModel, table=True):
__tablename__ = "chat_sessions"
id: int = Field(default=None, primary_key=True)
user_id: int = Field(foreign_key="users.id")
session_name: str = Field(default="未命名对话", max_length=100)
character_id: Optional[int] = Field(default=None, foreign_key="characters.id")
chat_type: ChatType = Field()
created_at: datetime = Field(default_factory=lambda: datetime.now())
updated_at: datetime = Field(default_factory=lambda: datetime.now())
__table_args__ = (
Index("idx_user_id", "user_id"),
Index("idx_updated_at", "updated_at"),
)
class Message(SQLModel, table=True):
__tablename__ = "messages"
id: int = Field(default=None, primary_key=True)
session_id: int = Field(foreign_key="chat_sessions.id")
sender_type: SenderType = Field()
sender_id: Optional[int] = Field(default=None, foreign_key="users.id") # 只有 user 发送时才填
content: str = Field()
timestamp: datetime = Field(default_factory=lambda: datetime.now())
__table_args__ = (
Index("idx_session_id", "session_id"),
Index("idx_timestamp", "timestamp"),
)
```
---
## ✅ 第七步：更新 `database.py` 中的导入方式
你现在可以把原来写死的模型定义全部移除，改为从 `models` 导入：
```python
# backend/database.py
from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession, async_sessionmaker
from sqlmodel import SQLModel
from contextlib import asynccontextmanager
from config import settings
import asyncio
# 从 models 包导入所有表模型
from models import *
# 创建引擎
engine = create_async_engine(
settings.DATABASE_URL,
echo=settings.DEBUG,
pool_pre_ping=True
)
AsyncSessionLocal = async_sessionmaker(
bind=engine,
class_=AsyncSession,
expire_on_commit=False
)
@asynccontextmanager
async def get_db():
async with AsyncSessionLocal() as session:
try:
yield session
await session.commit()
except Exception:
await session.rollback()
raise
async def create_tables():
async with engine.begin() as conn:
await conn.run_sync(SQLModel.metadata.create_all)
print("✅ 所有数据库表已创建完成！")
async def close_db():
await engine.dispose()
```
---
## ✅ 第八步：验证是否可以正常建表
运行以下脚本测试建表：
```python
# test_create_tables.py
import asyncio
from database import create_tables
if __name__ == "__main__":
asyncio.run(create_tables())
```
如果输出 `✅ 所有数据库表已创建完成！`，说明模型加载成功。
---
## ✅ 总结：你现在拥有了一个清晰、可维护的模型架构
| 文件 | 内容 |
|------|------|
| `models/__init__.py` | 提供统一导入接口 |
| `models/user.py` | 用户账号与个人设置 |
| `models/department.py` | 院系分区与多对多关系 |
| `models/post.py` | 博客、评论、点赞系统 |
| `models/search.py` | 热搜词统计 |
| `models/chat.py` | AI 聊天室、角色扮演、消息记录 |
并且可以在任意地方这样使用：
```python
from models import User, Post, Comment, Like, Department
```
完全解耦，易于扩展。
---
Python结合SQL与OpenAI管理聊天记录
最低0.47元/天 解锁文章
扫一扫
369
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
