-- 用户表

CREATE TABLE User (

id INT AUTO_INCREMENT PRIMARY KEY,

username VARCHAR(50) NOT NULL UNIQUE,

password VARCHAR(255) NOT NULL,

email VARCHAR(100),

role ENUM('admin', 'researcher') DEFAULT 'researcher'

);

-- 项目表

CREATE TABLE Project (

id INT AUTO_INCREMENT PRIMARY KEY,

title VARCHAR(255) NOT NULL,

description TEXT,

start_date DATE,

end_date DATE,

status ENUM('active', 'completed', 'canceled')

);

-- 参与人员表

CREATE TABLE Participant (

user_id INT,

project_id INT,

role ENUM('PI', 'co-PI', 'member'),

PRIMARY KEY (user_id, project_id),

FOREIGN KEY (user_id) REFERENCES User(id),

FOREIGN KEY (project_id) REFERENCES Project(id)

);

]]>

from flask import Flask, jsonify, request

from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)

app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://username:password@localhost/research_system'

综合服务门户

db = SQLAlchemy(app)

class Project(db.Model):

id = db.Column(db.Integer, primary_key=True)

title = db.Column(db.String(255), nullable=False)

description = db.Column(db.Text)

start_date = db.Column(db.Date)

end_date = db.Column(db.Date)

status = db.Column(db.Enum('active', 'completed', 'canceled'))

@app.route('/projects', methods=['GET'])

def get_projects():

projects = Project.query.all()

return jsonify([{'id': p.id, 'title': p.title, 'status': p.status} for p in projects])

if __name__ == '__main__':

app.run(debug=True)

]]>