{
"cells": [
{
"cell_type": "code",
"execution_count": 1,
"metadata": {},
"outputs": [],
"source": [
"import psycopg2 as pg\n",
"import random"
]
},
{
"attachments": {},
"cell_type": "markdown",
"metadata": {},
"source": [
"# 获取DB连接对象\n",
"4个用户 \\\n",
" pm: create 用户 数据库 \\\n",
" t_coder: 老师端, select 教师表, insert\\update 成绩表 \\\n",
" s_coder: 学生端, select 学生表 课程表 成绩表 \\ \n",
" affair: 教务处, insert 教师表 学生表 课程表 \\"
]
},
{
"cell_type": "code",
"execution_count": 2,
"metadata": {},
"outputs": [],
"source": [
"def get_conn(user='pm'):\n",
" conn = pg.connect(database=\"db_school\", \n",
" user=user, \n",
" password=\"123456twG\", \n",
" host=\"127.0.0.1\", port=\"7654\")\n",
" return conn"
]
},
{
"attachments": {},
"cell_type": "markdown",
"metadata": {},
"source": [
"# 从CSV文件导入\n",
"Day1, affair用户, 将student.tsv, teacher.tsv, course.tsv导入到数据库中"
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {},
"outputs": [],
"source": [
"def insert_tsv(conn, table_name, tsv_path, sep='\\t'):\n",
" print(\"Opened database successfully\")\n",
" cur = conn.cursor()\n",
"\n",
" copy_query = f\"COPY public.{table_name} FROM STDIN WITH (FORMAT CSV, HEADER TRUE, DELIMITER E'\\\\t', ENCODING 'UTF-8')\"\n",
" with open(tsv_path, 'r', encoding='utf-8') as file:\n",
" cur.copy_expert(copy_query, file)\n",
" print(f\"Copy {tsv_path} ok\")\n",
" # 提交事务\n",
" conn.commit()\n",
" # 关闭数据库连接\n",
" cur.close()\n",
" conn.close()"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"conn = get_conn(user='affair')\n",
"insert_tsv(conn, \"teacher\", '../data/teacher.tsv')"
]
},
{
"cell_type": "code",
"execution_count": 6,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Opened database successfully\n",
"Copy ../data/student.tsv ok\n",
"Opened database successfully\n",
"Copy ../data/course.tsv ok\n"
]
}
],
"source": [
"conn2 = get_conn(user='affair')\n",
"conn3 = get_conn(user='affair')\n",
"insert_tsv(conn2, \"student\", '../data/student.tsv')\n",
"insert_tsv(conn3, \"course\", '../data/course.tsv')"
]
},
{
"attachments": {},
"cell_type": "markdown",
"metadata": {},
"source": [
"# 向score添加数据\n",
"Day2, t_coder用户，向score表插入数据"
]
},
{
"cell_type": "code",
"execution_count": 11,
"metadata": {},
"outputs": [],
"source": [
"def insert_score(conn):\n",
" student_id = range(20230001, 20230041)\n",
" course_id = range(10, 15)\n",
" cur = conn.cursor()\n",
" \n",
" insert_value = []\n",
" for s_id in student_id:\n",
" for c_id in course_id:\n",
" random_score = random.randint(75, 95)\n",
" insert_value.append((s_id, c_id, random_score))\n",
"\n",
" insert_sql = \"INSERT INTO public.score (student_id, course_id, score) VALUES (%s, %s, %s)\"\n",
" cur.executemany(insert_sql, insert_value)\n",
" conn.commit()\n",
" cur.close()\n",
" conn.close()\n",
" \n",
"\n",
" \n",
"# 修改成绩\n",
"def update_score(conn, repete_times=10):\n",
" cur = conn.cursor() \n",
" \n",
" update_value = []\n",
" for i in range(repete_times):\n",
" s_id = random.randint(20230001, 20230040)\n",
" c_id = random.randint(10, 14)\n",
" det_score = random.randint(-5, 0)\n",
" update_value.append((det_score, s_id, c_id))\n",
" \n",
" update_sql = \"UPDATE public.score SET score = score+(%s) WHERE student_id=(%s) AND course_id=(%s) \"\n",
" cur.executemany(update_sql, update_value)\n",
" conn.commit()\n",
" cur.close()\n",
" conn.close()"
]
},
{
"cell_type": "code",
"execution_count": 10,
"metadata": {},
"outputs": [],
"source": [
"conn = get_conn(user='t_coder')\n",
"insert_score(conn)"
]
},
{
"cell_type": "code",
"execution_count": 12,
"metadata": {},
"outputs": [],
"source": [
"conn_update_score = get_conn(user='t_coder')\n",
"update_score(conn_update_score)"
]
},
{
"attachments": {},
"cell_type": "markdown",
"metadata": {},
"source": [
"# 查询teacher,student,score\n",
"Day1-5: t_coder查询teacher表，s_coder查询student表、course表 \\\n",
"Day2: teacher查询score, 学生查询score"
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {},
"outputs": [],
"source": [
"# 对表{table_name}指定{query_id}查询{repete_times}次\n",
"# 查询student teacher course都是调用这个函数\n",
"def select(conn, table_name, repete_times, query_id, id_range):\n",
" cur = conn.cursor() \n",
" query_value = [] \n",
" for t in range(repete_times):\n",
" query_value.append((random.randint(id_range[0], id_range[1]),))\n",
" query_sql = f\"SELECT * FROM public.{table_name} WHERE {query_id}=(%s)\"\n",
" \n",
" cur.executemany(query_sql, query_value)\n",
" conn.commit()\n",
" cur.close()\n",
" conn.close()\n",
" \n",
"\n",
"# 学生查询课表\n",
"def select_course(conn, repete_times):\n",
" cur = conn.cursor() \n",
" query_sql = \"SELECT course_id, cname, name, point FROM public.course c, public.teacher t where c.teacher_id = t.teacher_id\" \n",
" \n",
" for i in range(repete_times):\n",
" cur.execute(query_sql)\n",
" result = cur.fetchall()\n",
" conn.commit()\n",
" cur.close()\n",
" conn.close()\n",
"\n",
"\n",
"# 学生查询成绩\n",
"def select_score(conn):\n",
" cur = conn.cursor() \n",
" query_sql = \"\"\"SELECT s.student_id, s.\"name\", c.cname, s2.score \n",
" FROM public.score s2, public.course c, public.student s \n",
" WHERE s2.course_id = c.course_id AND s2.student_id = s.student_id AND s.student_id=(%s)\"\"\" \n",
" query_value = [(i,) for i in range(20230001, 20230041)]\n",
" \n",
" cur.executemany(query_sql, query_value) \n",
" conn.commit()\n",
" cur.close()\n",
" conn.close() \n",
"\n",
"\n",
"# 老师查询指定课程的所有成绩\n",
"def select_score_teacher(conn):\n",
" cur = conn.cursor() \n",
" query_value = [(i,) for i in range(10, 15)]\n",
" query_sql = \"SELECT * FROM public.score WHERE course_id=(%s) ORDER BY score desc;\"\n",
" \n",
" cur.executemany(query_sql, query_value)\n",
" conn.commit()\n",
" cur.close()\n",
" conn.close()\n",
" "
]
},
{
"attachments": {},
"cell_type": "markdown",
"metadata": {},
"source": [
"执行查询学生信息、老师信息、课程信息脚本"
]
},
{
"cell_type": "code",
"execution_count": 6,
"metadata": {},
"outputs": [],
"source": [
"conn_s = get_conn(user='s_coder')\n",
"conn_s2 = get_conn(user='s_coder')\n",
"conn_t = get_conn(user='t_coder')\n",
"\n",
"\n",
"s_repete = random.randint(70,100)\n",
"s2_repete = s_repete + random.randint(5,10)\n",
"t_repete = random.randint(10,30)\n",
"\n",
"# s_coder查询学生信息\n",
"select(conn_s, \"student\", s_repete, \"student_id\", (20230001, 20230040))\n",
"# s_coder查询课程信息\n",
"select_course(conn_s2, s2_repete)\n",
"# t_coder查询教师信息\n",
"select(conn_t, \"teacher\", t_repete, \"teacher_id\", (101, 105))"
]
},
{
"attachments": {},
"cell_type": "markdown",
"metadata": {},
"source": [
"查询成绩"
]
},
{
"cell_type": "code",
"execution_count": 15,
"metadata": {},
"outputs": [],
"source": [
"conn_score = get_conn(user='s_coder')\n",
"conn_score_t = get_conn(user='t_coder')\n",
"\n",
"# s_coder查询成绩\n",
"select_score(conn_score)\n",
"# t_coder按照课程号查询成绩\n",
"select_score_teacher(conn_score_t)"
]
}
],
"metadata": {
"kernelspec": {
"display_name": "opengauss",
"language": "python",
"name": "python3"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.8.16"
},
"orig_nbformat": 4
},
"nbformat": 4,
"nbformat_minor": 2
}