import sqlite3

# 創(chuàng)建連接對象

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

cursor = conn.cursor()

# 創(chuàng)建Researcher表

cursor.execute('''

CREATE TABLE Researcher (

id INTEGER PRIMARY KEY AUTOINCREMENT,

name TEXT NOT NULL,

email TEXT UNIQUE NOT NULL

)

''')

# 創(chuàng)建Project表

cursor.execute('''

CREATE TABLE Project (

id INTEGER PRIMARY KEY AUTOINCREMENT,

title TEXT NOT NULL,

start_date DATE,

end_date DATE

)

''')

# 創(chuàng)建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)

一站式網(wǎng)上服務大廳

)

''')

# 提交更改并關(guān)閉連接

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

# 示例調(diào)用

print(get_results_by_researcher('張三'))

]]>