医院科研管理系统

-- 创建用户表

CREATE TABLE users (

user_id INT PRIMARY KEY AUTO_INCREMENT,

name VARCHAR(100) NOT NULL,

email VARCHAR(100) UNIQUE NOT NULL

);

-- 创建论文表

CREATE TABLE papers (

paper_id INT PRIMARY KEY AUTO_INCREMENT,

title VARCHAR(255) NOT NULL,

author_id INT,

高校科研管理系统

FOREIGN KEY (author_id) REFERENCES users(user_id)

);

]]>

from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData, ForeignKey

from sqlalchemy.orm import sessionmaker

engine = create_engine('mysql+pymysql://root:password@localhost/research_system')

Session = sessionmaker(bind=engine)

session = Session()

# 查询排行榜

result = session.query(User.name, func.count(Paper.paper_id).label('paper_count')).\

join(Paper, User.user_id == Paper.author_id, isouter=True).\

group_by(User.user_id).order_by(desc('paper_count')).all()

for row in result:

print(f"{row.name}: {row.paper_count} 篇论文")

]]>