from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class ResearchProject(Base):
__tablename__ = 'research_projects'
id = Column(Integer, primary_key=True)
title = Column(String)
funding = Column(Integer)
university = Column(String)
]]>
import pandas as pd
from sqlalchemy.orm import sessionmaker
# 创建数据库引擎
engine = create_engine('sqlite:///projects.db')
Base.metadata.create_all(engine)
# 创建会话
Session = sessionmaker(bind=engine)
session = Session()
# 读取Excel文件
df = pd.read_excel('projects.xlsx')
# 导入数据到数据库
for index, row in df.iterrows():
project = ResearchProject(title=row['Title'], funding=row['Funding'], university=row['University'])
session.add(project)
session.commit()
]]>
# 统计每个大学的平均资助金额
avg_funding = session.query(ResearchProject.university, func.avg(ResearchProject.funding)).group_by(ResearchProject.university).all()
# 打印结果
for uni, avg in avg_funding:
print(f"{uni}: {avg}")
]]>