Python從MySQL數據庫中面抽取試題,生成試卷
一、背景
本文章主要是分享如何使用Python從MySQL數據庫中面抽取試題,生成的試卷每一份都不一樣。
二、準備工作
1.安裝Python3
下載地址:https://www.python.org/downloads/windows/
2.安裝庫
pip install python-docx==0.8.10
pip install PyMySQL==1.0.2
3.試題庫.xlsx
開發程序前需要先收集試題,本文是將試題收集存放MySQL數據庫中,格式如下:
選擇題數據庫截圖:
填空題/解答題/綜合題數據庫截圖:
三、代碼
Python+MySQL隨機試卷及答案生成程序.py
# _*_ coding:utf-8 _*_ import random,os,pymysql from docx import Document from docx.shared import Inches,Pt from docx.enum.text import WD_ALIGN_PARAGRAPH,WD_LINE_SPACING from docx.oxml.ns import qn from docx.shared import Inches class SunckSql(): def __init__(self, host, user, passwd, dbName='', charset='utf8'): self.host = host self.user = user self.passwd = passwd self.dbName = dbName self.charset = charset def connet(self): self.db = pymysql.connect(host=self.host, user=self.user, passwd=self.passwd, db=self.dbName, charset=self.charset) # 連接數據庫 self.cursor = self.db.cursor() # 獲取操作遊標 def close(self): self.cursor.close() # 釋放遊標 self.db.close() # 關閉數據庫連接 # 查詢 def get_all(self, sql): res = None try: self.connet() self.cursor.execute(sql) # 執行sql語句 res = self.cursor.fetchall() # 返回查詢所有結果 except Exception as e: print('查詢失敗:%s' % e) finally: self.close() return res # 增加、刪除、修改 def shell_sql(self, sql): "執行sql語句" print(sql) count = 0 try: self.connet() count = self.cursor.execute(sql) # 執行sql語句 self.db.commit() # 提交 except Exception as e: print('事務提交失敗:%s' % e) self.db.rollback() # 如果提交失敗,回滾到上一次數據 finally: self.close() return count def router_docx(choice1='', choice2='', choice3='', choice5='', choice6='', choice7='',paper_path='',name='1'): "生成網絡通信方向試題及答案" docx1 = Document() docx2 = Document() docx1.styles['Normal'].font.name = '宋體' #選擇字體 docx1.styles['Normal']._element.rPr.rFonts.set(qn('w:eastAsia'), '宋體') #默認字體 docx1.styles['Normal'].font.size = Pt(11) #默認字號大小 docx1.styles['Normal'].paragraph_format.space_before = Pt(0) #默認段前間距 docx1.styles['Normal'].paragraph_format.space_after = Pt(0) #默認段後間距 docx1.styles['Normal'].paragraph_format.line_spacing_rule = WD_LINE_SPACING.ONE_POINT_FIVE #默認單倍行距 sec = docx1.sections[0] # sections對應文檔中的“節” sec.left_margin = Inches(1) # 設置左頁面邊距 sec.right_margin = Inches(1) #設置右頁面邊距 sec.top_margin = Inches(0.5) # 設置上頁面邊距 sec.bottom_margin = Inches(0.5) #設置下頁面邊距 p=docx1.add_paragraph() #添加段落 run = p.add_run('軟件測試(網絡通信)方向試題(%s)' % name) #使用add_run添加文字 run.font.name = '微軟雅黑' #設置字體 run._element.rPr.rFonts.set(qn('w:eastAsia'), '微軟雅黑') #設置字體 run.font.size = Pt(18) #字體大小設置 p.paragraph_format.alignment = WD_ALIGN_PARAGRAPH.CENTER #段落文字居中設置 docx1.add_paragraph('【說明】') # 添加段落文字 docx1.add_paragraph('1.筆試時間為60分鐘。') docx1.add_paragraph('2.請將答案寫在答題卡上,且不允許在試題卷上做任何塗寫和標記。') q=docx2.add_paragraph() #添加段落 run = q.add_run('軟件測試(網絡通信)方向試題答案(%s)' % name) #使用add_run添加文字 run.font.name = '微軟雅黑' #設置字體 run._element.rPr.rFonts.set(qn('w:eastAsia'), '微軟雅黑') #設置字體 run.font.size = Pt(18) #字體大小設置 q.paragraph_format.alignment = WD_ALIGN_PARAGRAPH.CENTER #段落文字居中設置 p1 = docx1.add_paragraph() p1.paragraph_format.space_before = Pt(12) #設置段前間距 docx2.add_paragraph('一、選擇題') run = p1.add_run('一、選擇題(每題3分共45分)') run.bold = True # 字體加粗 list1=random.sample(range(0,len(choice1)-1),3) #len范圍內獲取指定的數量 x=1 for y in list1: docx1.add_paragraph(str(x)+'、'+choice1[y][1]) docx1.add_paragraph(choice1[y][2]) docx1.add_paragraph(choice1[y][3]) docx1.add_paragraph(choice1[y][4]) p11=docx1.add_paragraph(choice1[y][5]) p11.paragraph_format.space_after = Pt(12) #段後間距 docx2.add_paragraph(str(x)+'、'+choice1[y][6]) x+=1 list2=random.sample(range(0,len(choice2)-1),7) x=1 for y in list2: docx1.add_paragraph(str(x+3)+'、'+choice2[y][1]) docx1.add_paragraph(choice2[y][2]) docx1.add_paragraph(choice2[y][3]) docx1.add_paragraph(choice2[y][4]) p11=docx1.add_paragraph(choice2[y][5]) p11.paragraph_format.space_after = Pt(12) docx2.add_paragraph(str(x+3)+'、'+choice2[y][6]) x+=1 list3=random.sample(range(0,len(choice3)-1),5) x=1 for y in list3: docx1.add_paragraph(str(x+10)+'、'+choice3[y][1]) docx1.add_paragraph(choice3[y][2]) docx1.add_paragraph(choice3[y][3]) docx1.add_paragraph(choice3[y][4]) p11=docx1.add_paragraph(choice3[y][5]) p11.paragraph_format.space_after = Pt(12) docx2.add_paragraph(str(x+10)+'、'+choice3[y][6]) x+=1 p2 = docx1.add_paragraph() p2.paragraph_format.space_before = Pt(12) docx2.add_paragraph('二、填空題') run = p2.add_run('二、填空題(每題3分,共15分)') run.bold = True list2 = random.sample(range(0, len(choice5)-1), 5) i = 1 for j in list2: docx1.add_paragraph(str(i) + '、' + choice5[j][1]) docx2.add_paragraph(str(i) + '、' + str(choice5[j][2])) i += 1 p3 = docx1.add_paragraph() p3.paragraph_format.space_before = Pt(12) docx2.add_paragraph('三、簡答題') run = p3.add_run('三、簡答題(每題10分,共20分)') run.bold = True list3 = random.sample(range(0, len(choice6)-1), 2) n = 1 for m in list3: docx1.add_paragraph(str(n) + '、' + choice6[m][1]) docx1.add_paragraph('\r') docx2.add_paragraph(str(n) + '、' + choice6[m][2]) n += 1 p4 = docx1.add_paragraph() p4.paragraph_format.space_before = Pt(12) docx2.add_paragraph('四、綜合題') run = p4.add_run('四、綜合題(共20分)') run.bold = True list4 = random.randint(0, len(choice7)-1) docx1.add_paragraph('1、' + choice7[list4][1]) docx2.add_paragraph(choice7[list4][2]) docx1.save(os.path.join(paper_path, '網絡通信試題(%s).docx' % name)) #保存試題 docx2.save(os.path.join(paper_path, '網絡通信試題答案(%s).docx' % name)) #保存答案 def android_docx(choice1, choice2, choice4, choice5, choice6, choice8,paper_path,name): """生成智能終端方向的試題""" docx1 = Document() docx2 = Document() docx1.styles['Normal'].font.name = '宋體' #選擇字體 docx1.styles['Normal']._element.rPr.rFonts.set(qn('w:eastAsia'), '宋體') #默認字體 docx1.styles['Normal'].font.size = Pt(11) #默認字號大小 docx1.styles['Normal'].paragraph_format.space_before = Pt(0) #默認段前間距 docx1.styles['Normal'].paragraph_format.space_after = Pt(0) #默認段後間距 docx1.styles['Normal'].paragraph_format.line_spacing_rule = WD_LINE_SPACING.ONE_POINT_FIVE #默認單倍行距 sec = docx1.sections[0] # sections對應文檔中的“節” sec.left_margin = Inches(1) # 設置左頁面邊距 sec.right_margin = Inches(1) #設置右頁面邊距 sec.top_margin = Inches(0.5) # 設置上頁面邊距 sec.bottom_margin = Inches(0.5) #設置下頁面邊距 p=docx1.add_paragraph() #添加段落 run = p.add_run('軟件測試(智能終端)方向試題(%s)' % name) #使用add_run添加文字 run.font.name = '微軟雅黑' #設置字體 run._element.rPr.rFonts.set(qn('w:eastAsia'), '微軟雅黑') #設置字體 run.font.size = Pt(18) #字體大小設置 p.paragraph_format.alignment = WD_ALIGN_PARAGRAPH.CENTER #段落文字居中設置 docx1.add_paragraph('【說明】') # 添加段落文字 docx1.add_paragraph('1.筆試時間為60分鐘。') docx1.add_paragraph('2.請將答案寫在答題卡上,且不允許在試題卷上做任何塗寫和標記。') q = docx2.add_paragraph() # 添加段落 run = q.add_run('軟件測試(智能終端)方向試題答案(%s)' % name) # 使用add_run添加文字 run.font.name = '微軟雅黑' # 設置字體 run._element.rPr.rFonts.set(qn('w:eastAsia'), '微軟雅黑') # 設置字體 run.font.size = Pt(18) # 字體大小設置 q.paragraph_format.alignment = WD_ALIGN_PARAGRAPH.CENTER # 段落文字居中設置 p1 = docx1.add_paragraph() p1.paragraph_format.space_before = Pt(12) #設置段前間距 docx2.add_paragraph('一、選擇題') run = p1.add_run('一、選擇題(每題3分共45分)') run.bold = True # 字體加粗 list1=random.sample(range(0,len(choice1)-1),3) x=1 for y in list1: docx1.add_paragraph(str(x)+'、'+choice1[y][1]) docx1.add_paragraph(choice1[y][2]) docx1.add_paragraph(choice1[y][3]) docx1.add_paragraph(choice1[y][4]) p11=docx1.add_paragraph(choice1[y][5]) p11.paragraph_format.space_after = Pt(12) #段後間距 docx2.add_paragraph(str(x)+'、'+choice1[y][6]) x+=1 list2=random.sample(range(0,len(choice2)-1),7) x=1 for y in list2: docx1.add_paragraph(str(x+3)+'、'+choice2[y][1]) docx1.add_paragraph(choice2[y][2]) docx1.add_paragraph(choice2[y][3]) docx1.add_paragraph(choice2[y][4]) p11=docx1.add_paragraph(choice2[y][5]) p11.paragraph_format.space_after = Pt(12) docx2.add_paragraph(str(x+3)+'、'+choice2[y][6]) x+=1 list3=random.sample(range(0,len(choice4)-1),5) x=1 for y in list3: docx1.add_paragraph(str(x+10)+'、'+choice4[y][1]) docx1.add_paragraph(choice4[y][2]) docx1.add_paragraph(choice4[y][3]) docx1.add_paragraph(choice4[y][4]) p11=docx1.add_paragraph(choice4[y][5]) p11.paragraph_format.space_after = Pt(12) docx2.add_paragraph(str(x+10)+'、'+choice4[y][6]) x+=1 p2 = docx1.add_paragraph() p2.paragraph_format.space_before = Pt(12) docx2.add_paragraph('二、填空題') run = p2.add_run('二、填空題(每題3分,共15分)') run.bold = True list2 = random.sample(range(0, len(choice5)-1), 5) i = 1 for j in list2: docx1.add_paragraph(str(i) + '、' + choice5[j][1]) docx2.add_paragraph(str(i) + '、' + str(choice5[j][2])) i += 1 p3 = docx1.add_paragraph() p3.paragraph_format.space_before = Pt(12) docx2.add_paragraph('三、簡答題') run = p3.add_run('三、簡答題(每題10分,共20分)') run.bold = True list3 = random.sample(range(0, len(choice6)-1), 2) n = 1 for m in list3: docx1.add_paragraph(str(n) + '、' + choice6[m][1]) docx1.add_paragraph('\r') docx2.add_paragraph(str(n) + '、' + choice6[m][2]) n += 1 p4 = docx1.add_paragraph() p4.paragraph_format.space_before = Pt(12) docx2.add_paragraph('四、綜合題') run = p4.add_run('四、綜合題(共20分)') run.bold = True list4 = random.randint(0, len(choice8)-1) docx1.add_paragraph('1、' + choice8[list4][1]) docx2.add_paragraph(choice8[list4][2]) docx1.save(os.path.join(paper_path, '智能終端試題(%s).docx' % name)) docx2.save(os.path.join(paper_path, '智能終端試題答案(%s).docx' % name)) def main(ip,name,passwd,db_name): paper_path = os.path.join(os.path.dirname(os.path.abspath(__file__)), '試卷') #試卷存放路徑 if not os.path.exists(paper_path): os.mkdir(paper_path) #創建試卷文件夾 my = SunckSql(ip,name,passwd,db_name) #連接數據庫 choice1 = my.get_all("select * from %s" % '計算機基礎選擇題') #查詢數據庫中的試題 choice2 = my.get_all("select * from %s" % '測試基礎選擇題') choice3 = my.get_all("select * from %s" % '網絡通信選擇題') choice4 = my.get_all("select * from %s" % '智能終端選擇題') choice5 = my.get_all("select * from %s" % '填空題') choice6 = my.get_all("select * from %s" % '簡答題') choice7 = my.get_all("select * from %s" % '網絡通信綜合題') choice8 = my.get_all("select * from %s" % '智能終端綜合題') for i in range(1,4): #同時生成3份試卷及答案 router_docx(choice1, choice2, choice3, choice5, choice6, choice7, paper_path, i) android_docx(choice1, choice2, choice4, choice5, choice6, choice8, paper_path, i) if __name__ == "__main__": main(ip='數據庫ip地址', name='mysql賬號', passwd='mysql密碼', db_name='軟件測試試題庫')
以上就是Python從MySQL數據庫中面抽取試題,生成試卷的詳細內容,更多關於python MySQL抽取題目生成試卷的資料請關註WalkonNet其它相關文章!
推薦閱讀:
- Python+MySQL隨機試卷及答案生成程序的示例代碼
- 詳解python-docx處理Word必備工具
- 使用python處理一萬份word表格簡歷操作
- Python操作word文檔的示例詳解
- Python docx庫代碼演示