数据库-实验2-通过嵌入式SQL进行MIS系统开发

1、实验二 通过嵌入式SQL进行MIS系统开发1、实验目的：熟悉通过高级语言连接SQL Server 数据库的过程，通过嵌入式SQL对数据库进行操作，实现简单的MIS系统2、实验环境： 1）使用SQL Server数据库 2）自选高级语言开发工具 如Powerbuider，VC+等 3）举例介绍Powerbuilder连接SQL Server ODBC数据库的过程：打开SQL Server 2008的配置管理SQL Server Configuration Manager，查看右边的服务（如：SQL Server Browser）是否启动，如果没有则手动启动。 打开Powerbuilder 选择t

2、ools菜单下Database Profile 选择ODB ODBC 的Utilities下的ODBC Administrator，双击后选择添加数据源，选择SQL Server Native Client 配置数据源名，服务器选择local选择认证方式修改默认链接数据库添加完成后，在Database Profiles对话框中，选中ODB ODBC，右键添加New ProfileData Source选则刚刚建立的数据库源完成后，即可连接新建的ODBC数据库3、实验内容：1） MIS系统的题目和内容自选(如学生学籍管理系统，医疗档案管理系统，图书管理系统等等)。2） 至少包含4个以上的库表。3

3、） 有交互式界面，能通过界面插入、修改和删除数据，能够实现一些简单的查询操作。 4、实验报告Ø 列出所设计的数据库表结构。Ø 截屏给出程序界面，操作界面和操作结果其中quit为退出，而hello与SQLcall是内部测试。点击Insert：点击Select：四个按钮分别对应选择四个表的内容：选择Update：Ø 附上主要源代码#-File: dialog_SQL.pyimport mysql.connectorimport sys, osfrom tkinter import *class MyDialog: def _init_(self, root): fra

4、me = Frame(root) self.root = root frame.pack() # SQL related initialization # user = 'root' pwd = 'harbin141421' host = '127.0.0.1' db = 'courtDB' data_file = 'mysql-courtDB.dat' create_database = "CRATE DATABASE courtDB" create_table_sql1 = " C

5、REATE TABLE IF NOT EXISTS cases ( case_id int AUTO_INCREMENT PRIMARY KEY, case_describe varchar(100) CHARACTER SET utf8 " create_table_sql2 = " CREATE TABLE IF NOT EXISTS lawsuits ( lawsuit_id int AUTO_INCREMENT PRIMARY KEY, case_id int, judge_id int, plaintiff varchar(20), defendant varch

6、ar(20) ) CHARACTER SET utf8 " create_table_sql3 = " CREATE TABLE IF NOT EXISTS judges ( judge_id int AUTO_INCREMENT PRIMARY KEY, name varchar(20), age int ) CHARACTER SET utf8 " create_table_sql4 = " CREATE TABLE IF NOT EXISTS outcomes ( outcome_id int AUTO_INCREMENT PRIMARY KEY,

7、 lawsuit_id int UNIQUE, lawsuit_winner varchar(20) CHARACTER SET utf8 " # SQL hello function # self.hi_there = Button(frame, text="Hello", command=self.say_hi, activebackground = 'green',activeforeground = 'white') # SQL goodbye function # self.quit = Button(frame, tex

8、t="Quit", fg="red", underline = 0, command=self.bye_bye, activebackground = 'green',activeforeground = 'white') # SQL test function # self.sql_test_call = Button(frame, text="SQL call", command=self.sql_test_call, activebackground = 'green',activ

9、eforeground = 'white') # SQL call function # SQL_inserter = Button(frame, text="insert", command=self.SQL_insert) SQL_selecter = Button(frame, text="select", command=self.SQL_select) SQL_updateer = Button(frame, text="update", command=self.SQL_update) # grid SQL

10、_inserter.grid(row=0,column=1,padx=10,pady=3) SQL_selecter.grid(row=1,column=1,padx=10,pady=3) SQL_updateer.grid(row=2,column=1,padx=10,pady=3) self.quit.grid(row=0,column=2,rowspan=3,padx=10,pady=9) self.hi_there.grid(row=4,column=1) self.sql_test_call.grid(row=4,column=2) # SQL table definition te

11、st # sql_cnx = mysql.connector.connect(user=user, password=pwd, host=host, database=db) cursor = sql_cnx.cursor() try: cursor.execute(create_table_sql1) except mysql.connector.Error as err: print("create table 'cases' failed.") print("Error: ".format(err.msg) sys.exit() t

12、ry: cursor.execute(create_table_sql2) except mysql.connector.Error as err: print("create table 'cases' failed.") print("Error: ".format(err.msg) sys.exit() try: cursor.execute(create_table_sql3) except mysql.connector.Error as err: print("create table 'cases'

13、 failed.") print("Error: ".format(err.msg) sys.exit() try: cursor.execute(create_table_sql4) except mysql.connector.Error as err: print("create table 'cases' failed.") print("Error: ".format(err.msg) sys.exit() sql_mit() cursor.close() sql_cnx.close() def s

14、ql_test_call(self): # SQL related initialization # user = 'root' pwd = 'harbin141421' host = '127.0.0.1' db = 'courtDB' data_file = 'mysql-court.dat' create_table_sql = " CREATE TABLE IF NOT EXISTS std_test ( id int(10) AUTO_INCREMENT PRIMARY KEY, name va

15、rchar(20), age int(4) ) CHARACTER SET utf8 " insert_sql = " INSERT INTO std_test(name, age) VALUES ('Jay', 22 ), ('杰', 26) " select_sql = " SELECT id, name, age FROM std_test " # SQL related definition # sql_cnx = mysql.connector.connect(user=user, password=p

16、wd, host=host, database=db) cursor = sql_cnx.cursor() # SQL standard create # try: cursor.execute(create_table_sql) except mysql.connector.Error as err: print("create table 'std_test' failed.") print("Error: ".format(err.msg) sys.exit() # SQL standard insert # try: cursor

17、.execute(insert_sql) except mysql.connector.Error as err: print("insert table 'std_test' failed.") print("Error: ".format(err.msg) sys.exit() if os.path.exists(data_file): myfile = open(data_file) lines = myfile.readlines() myfile.close() for line in lines: myset = line.s

18、plit() sql = "INSERT INTO std_test (name, age) VALUES ('', )".format(myset0, myset1) try: cursor.execute(sql) except mysql.connector.Error as err: print("insert table 'std_test' from file 'mysql-test.dat' - failed.") print("Error: ".format(err.ms

19、g) sys.exit() # SQL standard select # try: cursor.execute(select_sql) for (id, name, age) in cursor: print("ID: Name: Age:".format(id, name, age) except mysql.connector.Error as err: print("query table 'mytable' failed.") print("Error: ".format(err.msg) sys.exit

20、() sql_mit() cursor.close() sql_cnx.close() def SQL_insert(self): # topwindow1 # top = self.top = Toplevel(root) self.insert_table = Entry(top) self.insert_attri = Entry(top) self.insert_value = Entry(top) self.insert_op = Button(top, text="INSERT",command=lambda:self.SQL_insert_operate(TA

21、B=self.insert_table.get(),ATR=self.insert_attri.get(),VAL=self.insert_value.get(), activebackground = 'green',activeforeground = 'white') Label(top, text="insert").grid(row = 0,column=2) Label(top, text="TABLE ").grid(row=1,column=1) Label(top, text="ATTRIBUT

22、E").grid(row=1,column=2) Label(top, text="VALUE ").grid(row=1,column=3) self.insert_table.grid(row=2,column=1) self.insert_attri.grid(row=2,column=2) self.insert_value.grid(row=2,column=3) self.insert_op.grid(row=3,column=3) # Entry # def SQL_insert_operate(self,TAB,ATR,VAL): print(TA

23、B,ATR,VAL) # SQL related argument user = 'root' pwd = 'harbin141421' host = '127.0.0.1' db = 'courtDB' data_file = 'mysql-court.dat' insert_sql = "INSERT INTO "+TAB+"("+ATR+") VALUES ("+VAL+")" print(insert_sql); sql_cnx

24、 = mysql.connector.connect(user=user, password=pwd, host=host, database=db) cursor = sql_cnx.cursor() try: cursor.execute(insert_sql) except mysql.connector.Error as err: print("insert table "+TAB+" failed.") print("Error: ".format(err.msg) sys.exit() if os.path.exists(

25、data_file): myfile = open(data_file) lines = myfile.readlines() myfile.close() for line in lines: myset = line.split() sql = "INSERT INTO "+TAB+" ("+ATR+") VALUES ('', )".format(myset0, myset1) try: cursor.execute(sql) except mysql.connector.Error as err: print(

26、"insert table "+TAB+" from file 'mysql-test.dat' - failed.") print("Error: ".format(err.msg) sys.exit() sql_mit() cursor.close() sql_cnx.close() def SQL_select(self): # topwindow1 # top = self.top = Toplevel(root) self.select_cases = Button(top,text="CASES&

27、quot;,command=self.SQL_select_cases,activebackground = 'green',activeforeground = 'white') self.select_lawsuits = Button(top,text="LAWSUITS",command=self.SQL_select_lawsuits,activebackground = 'green',activeforeground = 'white') self.select_judges = Button(t

28、op,text="JUDGES",command=self.SQL_select_judges,activebackground = 'green',activeforeground = 'white') self.select_outcome = Button(top,text="OUTCOMES",command=self.SQL_select_outcomes,activebackground = 'green',activeforeground = 'white') self.sel

29、ect_cases.pack(padx=10,pady=5) self.select_lawsuits.pack(padx=10,pady=5) self.select_judges.pack(padx=10,pady=5) self.select_outcome.pack(padx=10,pady=5) # Entry # def SQL_select_cases(self): # topwindow1 # top = self.top = Toplevel(root) self.S_C_ID = Entry(top) Label(top, text="ID of the Case

30、").pack() self.S_C_ID.pack(padx=20,pady=40) self.select_cases_op = Button(top, text="SELECT FROM CASES",command=lambda:self.SQL_S_C_op(ID=self.S_C_ID.get(), activebackground = 'green',activeforeground = 'white').pack() def SQL_S_C_op(self,ID): # SQL related argument us

31、er = 'root' pwd = 'harbin141421' host = '127.0.0.1' db = 'courtDB' data_file = 'mysql-court.dat' # Entry # select_sql = " SELECT case_id, case_describe FROM cases WHERE case_id = "+str(ID)+" " sql_cnx = mysql.connector.connect(user=user, pa

32、ssword=pwd, host=host, database=db) cursor = sql_cnx.cursor() try: cursor.execute(select_sql) for (id, describe) in cursor: print("ID:ndescribe:".format(id, describe) except mysql.connector.Error as err: print("query table 'cases' failed.") print("Error: ".forma

33、t(err.msg) sys.exit() sql_mit() cursor.close() sql_cnx.close() def SQL_select_lawsuits(self): # topwindow1 # top = self.top = Toplevel(root) # case_id self.S_L_CID = Entry(top) Label(top, text="ID of the Case").grid(row=0,column=1) self.S_L_CID.grid(row=1,column=1) self.select_lawsuits_op

34、= Button(top, text="SELECT",command=lambda:self.SQL_S_L_CID(CID=self.S_L_CID.get(), activebackground = 'green',activeforeground = 'white').grid(row=2,column=1) # lawsuit_id self.S_L_LID = Entry(top) Label(top, text="ID of the Lawsuit").grid(row=0,column=2) self.S_

35、L_LID.grid(row=1,column=2) self.select_lawsuits_op = Button(top, text="SELECT",command=lambda:self.SQL_S_L_LID(LID=self.S_L_LID.get(), activebackground = 'green',activeforeground = 'white').grid(row=2,column=2) def SQL_S_L_CID(self,CID): # SQL related argument user = 'r

36、oot' pwd = 'harbin141421' host = '127.0.0.1' db = 'courtDB' data_file = 'mysql-court.dat' # Entry # select_sql = " SELECT * FROM lawsuits WHERE case_id = "+str(CID)+" " sql_cnx = mysql.connector.connect(user=user, password=pwd, host=host, datab

37、ase=db) cursor = sql_cnx.cursor() try: cursor.execute(select_sql) for (lawsuit_id,case_id,judge_id,plaintiff,defendant) in cursor: print("Lawsuit_ID: Case_ID: Judge_ID: plaintiff: defendant:".format(lawsuit_id,case_id,judge_id,plaintiff,defendant) except mysql.connector.Error as err: print

38、("query table 'lawsuit' failed.") print("Error: ".format(err.msg) sys.exit() sql_mit() cursor.close() sql_cnx.close() def SQL_S_L_LID(self,LID): # SQL related argument user = 'root' pwd = 'harbin141421' host = '127.0.0.1' db = 'courtDB' dat

39、a_file = 'mysql-court.dat' # Entry # select_sql = " SELECT * FROM lawsuits WHERE lawsuit_id = "+str(LID)+" " sql_cnx = mysql.connector.connect(user=user, password=pwd, host=host, database=db) cursor = sql_cnx.cursor() try: cursor.execute(select_sql) for (lawsuit_id,case_i

40、d,judge_id,plaintiff,defendant) in cursor: print("Lawsuit_ID: Case_ID: Judge_ID: plaintiff: defendant:".format(lawsuit_id,case_id,judge_id,plaintiff,defendant) except mysql.connector.Error as err: print("query table 'lawsuit' failed.") print("Error: ".format(err

41、.msg) sys.exit() sql_mit() cursor.close() sql_cnx.close() def SQL_select_judges(self): # topwindow1 # top = self.top = Toplevel(root) # judge_id self.S_J_JID = Entry(top) Label(top, text="ID of the Judge").grid(row=0,column=1) self.S_J_JID.grid(row=1,column=1) self.select_judges_CID = Butt

42、on(top, text="SELECT",command=lambda:self.SQL_S_J_JID(JID=self.S_J_JID.get(), activebackground = 'green',activeforeground = 'white').grid(row=2,column=1) # age self.S_J_AGE = Entry(top) Label(top, text="Age of the Judge").grid(row=0,column=2) self.S_J_AGE.grid(row

43、=1,column=2) self.select_judges_op = Button(top, text="SELECT",command=lambda:self.SQL_S_J_AGE(AGE=self.S_J_AGE.get(), activebackground = 'green',activeforeground = 'white').grid(row=2,column=2) def SQL_S_J_JID(self,JID): # SQL related argument user = 'root' pwd = &

44、#39;harbin141421' host = '127.0.0.1' db = 'courtDB' data_file = 'mysql-court.dat' # Entry # select_sql = " SELECT * FROM judges WHERE judge_id = "+str(JID)+" " sql_cnx = mysql.connector.connect(user=user, password=pwd, host=host, database=db) cursor =

45、sql_cnx.cursor() try: cursor.execute(select_sql) for (judge_id,name,age) in cursor: print("Judge ID: name:,age:".format(judge_id,name,age) except mysql.connector.Error as err: print("query table 'judges' failed.") print("Error: ".format(err.msg) sys.exit() sql_m

46、it() cursor.close() sql_cnx.close() def SQL_S_J_AGE(self,AGE): # SQL related argument user = 'root' pwd = 'harbin141421' host = '127.0.0.1' db = 'courtDB' data_file = 'mysql-court.dat' # Entry # select_sql = " SELECT * FROM judges WHERE age = "+str(A

47、GE)+" " sql_cnx = mysql.connector.connect(user=user, password=pwd, host=host, database=db) cursor = sql_cnx.cursor() try: cursor.execute(select_sql) for (judge_id,name,age) in cursor: print("Judge ID: name:,age:".format(judge_id,name,age) except mysql.connector.Error as err: print("query table 'judges' failed.") print("Error: ".format(err.msg) sys.exit() sql_mit() cursor.close() sql_cnx.close() def SQL_select_outcomes(self): # topwindow1 # top = self.top = Top