1、python + pycharm
【2026 PyCharm安装】2026年最新版Python与PyCharm安装教程-CSDN博客
2、linux + docker
centos7.6
docker-compose.yml
version: '3'
services:
mysql:
image: mysql:8.0
container_name: mysql8
restart: no
ports:
- "3306:3306"
environment:
MYSQL_ROOT_PASSWORD: r*****5
TZ: Asia/Shanghai
MYSQL_USER: me
MYSQL_PASSWORD: m*****5
volumes:
- ./data:/var/lib/mysql
- ./conf:/etc/mysql/conf.d
- ./logs:/var/log/mysql
command:
- --character-set-server=utf8mb4
- --collation-server=utf8mb4_unicode_ci
- --lower_case_table_names=1
docker-compose up -d
3、数据库
-- 1. 设置字符集
SET NAMES utf8mb4;
-- 2. 创建数据库（如果不存在）
CREATE DATABASE IF NOT EXISTS dw DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
-- 3. 创建用户（如果不存在）
CREATE USER IF NOT EXISTS 'me'@'%' IDENTIFIED BY 'me3.1415';
-- 4. 授予权限（需要足够权限的用户执行）
GRANT ALL PRIVILEGES ON dw.* TO 'me'@'%';
# -- 只授予基本的 CRUD 权限
# GRANT SELECT, INSERT, UPDATE, DELETE ON dw.* TO 'me'@'%';
-- 5. 刷新权限
FLUSH PRIVILEGES;
-- 6. 查看用户的权限
SHOW GRANTS FOR 'me'@'%';
-- 7. 使用数据库
USE dw;
-- 8.1 主表
CREATE TABLE IF NOT EXISTS department (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '部门主键ID',
dept_code BIGINT NOT NULL UNIQUE COMMENT '部门编码',
is_active BOOL NOT NULL DEFAULT TRUE COMMENT '是否启用',
budget DECIMAL(12, 2) NOT NULL COMMENT '部门预算',
created_at DATE NOT NULL COMMENT '成立日期',
logo LONGBLOB COMMENT '部门Logo',
extra_info JSON COMMENT '扩展信息',
uid CHAR(32) NOT NULL COMMENT '全局唯一标识'
);
-- 8.2 从表（含外键）
CREATE TABLE IF NOT EXISTS employee (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '员工主键ID',
age SMALLINT NOT NULL COMMENT '年龄',
salary FLOAT NOT NULL COMMENT '薪资（浮点）',
precise_salary DECIMAL(10, 2) NOT NULL COMMENT '精确薪资',
is_fulltime BOOL NOT NULL DEFAULT TRUE COMMENT '是否全职',
birth_date DATE COMMENT '出生日期',
punch_in TIME COMMENT '上班打卡时间',
hired_at DATETIME NOT NULL COMMENT '入职日期时间',
photo LONGBLOB COMMENT '员工照片',
tags JSON COMMENT '标签信息',
level ENUM('junior','mid','senior','lead') NOT NULL COMMENT '员工级别：junior/mid/senior/lead',
emp_uuid CHAR(32) NOT NULL COMMENT '员工UUID',
department_id INT NOT NULL COMMENT '所属部门ID',
# PRIMARY KEY (id),
FOREIGN KEY (department_id) REFERENCES department(id)
);
4、py
pymysql
sqlalchemy asyncmy
ORM Quick Start — SQLAlchemy 2.0 Documentation
SQLAlchemy 全方位指南：从入门到精通_sqlalchemy菜鸟教程-CSDN博客
pip install uv
uv add fastapi uvicorn requests pymysql sqlalchemy asyncmy sqlalchemy-utils pandas
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship
class Base(DeclarativeBase):
pass
config
class Config():
def __init__(self):
self.host = '10'
self.port = 3306
self.user = 'me'
self.password = 'me****5'
self.charset = 'utf8mb4'
self.db = 'dw'
config = Config()
client
from sqlalchemy.ext.asyncio import create_async_engine, AsyncEngine, async_sessionmaker, AsyncSession
from config import config, Config
class MySQLClient():
def __init__(self, conf: Config):
self.conf = conf
self.engine: AsyncEngine | None = None
self.session_maker = None
def _get_url(self):
return f"mysql+asyncmy://{self.conf.user}:{self.conf.password}@{self.conf.host}:{self.conf.port}/{self.conf.db}?charset=utf8mb4"
def init(self):
self.engine = create_async_engine(url=self._get_url(),
pool_size=10,
pool_pre_ping=True)
self.session_maker = async_sessionmaker(self.engine,
autoflush=True,
expire_on_commit=False)
def new_session(self) -> AsyncSession:
return AsyncSession(self.engine, autoflush=True, expire_on_commit=False)
async def close(self):
await self.engine.dispose()
mysql_client = MySQLClient(config)
import asyncio
from sqlalchemy import text
if __name__ == '__main__':
mysql_client.init()
async def test():
async with mysql_client.new_session() as session:
sql = 'SHOW TABLES'
ret = await session.execute(text(sql))
rows = ret.mappings().fetchall()
print(rows) # [{'Tables_in_dw': 'address'}, {'Tables_in_dw': 'department'}, {'Tables_in_dw': 'employee'}, {'Tables_in_dw': 'user_account'}]
print(type(rows)) #<class 'list'>
print(rows.__len__()) #4
asyncio.run(test())
model
from typing import Optional, List
from datetime import datetime, date
from decimal import Decimal
import uuid
from sqlalchemy import (
String, Integer, BigInteger, SmallInteger,
Float, Numeric, Boolean, Date, DateTime,
Text, LargeBinary, Enum, JSON, Uuid, Time, ForeignKey
)
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship
class Base(DeclarativeBase):
pass
class User(Base):
__tablename__ = "user_account"
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str] = mapped_column(String(30))
fullname: Mapped[Optional[str]]= mapped_column(String(100))
addresses: Mapped[List["Address"]] = relationship(
back_populates="user", cascade="all, delete-orphan"
)
class Address(Base):
__tablename__ = "address"
id: Mapped[int] = mapped_column(primary_key=True)
email_address: Mapped[str]= mapped_column(String(100))
user_id: Mapped[int] = mapped_column(ForeignKey("user_account.id"))
user: Mapped["User"] = relationship(back_populates="addresses")
# ── 主表：部门 ──
class Department(Base):
__tablename__ = "department"
id: Mapped[int] = mapped_column(
Integer,
primary_key=True,
autoincrement=True,
comment="部门主键ID"
)
dept_code: Mapped[int] = mapped_column(
BigInteger,
unique=True,
comment="部门编码"
)
is_active: Mapped[bool] = mapped_column(
Boolean,
default=True,
comment="是否启用"
)
budget: Mapped[Decimal] = mapped_column(
Numeric(12, 2),
comment="部门预算"
)
created_at: Mapped[date] = mapped_column(
Date,
comment="成立日期"
)
logo: Mapped[Optional[bytes]] = mapped_column(
LargeBinary,
comment="部门Logo"
)
extra_info: Mapped[Optional[dict]] = mapped_column(
JSON,
comment="扩展信息"
)
uid: Mapped[uuid.UUID] = mapped_column(
Uuid,
default=uuid.uuid4,
comment="全局唯一标识"
)
# 一对多：一个部门有多个员工
employees: Mapped[List["Employee"]] = relationship(
back_populates="department"
)
# ── 从表：员工（含外键） ──
class Employee(Base):
__tablename__ = "employee"
id: Mapped[int] = mapped_column(
Integer,
primary_key=True,
autoincrement=True,
comment="员工主键ID"
)
age: Mapped[int] = mapped_column(
SmallInteger,
comment="年龄"
)
salary: Mapped[float] = mapped_column(
Float,
comment="薪资（浮点）"
)
precise_salary: Mapped[Decimal] = mapped_column(
Numeric(10, 2),
comment="精确薪资"
)
is_fulltime: Mapped[bool] = mapped_column(
Boolean,
default=True,
comment="是否全职"
)
birth_date: Mapped[Optional[date]] = mapped_column(
Date,
comment="出生日期"
)
punch_in: Mapped[Optional[datetime]] = mapped_column(
Time,
comment="上班打卡时间"
)
hired_at: Mapped[datetime] = mapped_column(
DateTime,
comment="入职日期时间"
)
photo: Mapped[Optional[bytes]] = mapped_column(
LargeBinary,
comment="员工照片"
)
tags: Mapped[Optional[dict]] = mapped_column(
JSON,
comment="标签信息"
)
level: Mapped[str] = mapped_column(
Enum("junior", "mid", "senior", "lead", name="emp_levels"),
comment="员工级别：junior/mid/senior/lead"
)
emp_uuid: Mapped[uuid.UUID] = mapped_column(
Uuid,
default=uuid.uuid4,
comment="员工UUID"
)
# ── 外键：关联到部门表 ──
department_id: Mapped[int] = mapped_column(
ForeignKey("department.id"),
nullable=False,
comment="所属部门ID"
)
# 反向关系
department: Mapped["Department"] = relationship(
back_populates="employees"
)
from sqlalchemy import create_engine
from config import config
# 创建表
def create_table():
# 同步引擎创建
url = f"mysql+pymysql://{config.user}:{config.password}@{config.host}:{config.port}/{config.db}?charset=utf8mb4"
engine = create_engine(url, echo=True)
# 创建表
Base.metadata.create_all(engine)
if __name__ == '__main__':
create_table()
