基于科研成果管理系统的海南特色研究与实现

import sqlite3

# 创建连接对象

conn = sqlite3.connect('hainan_research.db')

cursor = conn.cursor()

# 创建Researcher表

cursor.execute('''

CREATE TABLE Researcher (

id INTEGER PRIMARY KEY AUTOINCREMENT,

name TEXT NOT NULL,

email TEXT UNIQUE NOT NULL

)

''')

# 创建Project表

cursor.execute('''

CREATE TABLE Project (

id INTEGER PRIMARY KEY AUTOINCREMENT,

title TEXT NOT NULL,

start_date DATE,

end_date DATE

)

''')

# 创建Result表

cursor.execute('''

CREATE TABLE Result (

id INTEGER PRIMARY KEY AUTOINCREMENT,

title TEXT NOT NULL,

type TEXT NOT NULL,

researcher_id INTEGER,

project_id INTEGER,

FOREIGN KEY(researcher_id) REFERENCES Researcher(id),

FOREIGN KEY(project_id) REFERENCES Project(id)

一站式网上服务大厅

)

''')

# 提交更改并关闭连接

conn.commit()

conn.close()

]]>

def get_results_by_researcher(researcher_name):

conn = sqlite3.connect('hainan_research.db')

cursor = conn.cursor()

# 获取researcher_id

cursor.execute("SELECT id FROM Researcher WHERE name = ?", (researcher_name,))

researcher_id = cursor.fetchone()[0]

# 查询该研究者的所有成果

cursor.execute("""

SELECT r.title, rs.type

FROM Result r

JOIN Researcher re ON r.researcher_id = re.id

WHERE re.id = ?

""", (researcher_id,))

results = cursor.fetchall()

return results

# 示例调用

print(get_results_by_researcher('张三'))

]]>