山东科技大学小学期数据库大作业

一天写完了。。。。。挂这里就当是造福后人了，虽然写的这么乱估计也没什么人看，就当是见笑了

选题：

¨CREATE TABLE <表名>　（<列名><数据类型>[<列完整性约束条件>][，<列名><数据类型>[<列完整性约束条件>]…][，<表完整性约束条件>]）

¨ALTER TABLE <表名> [ADD <新列名><数据类型>[<列完整性约束>]] [DROP<列完整性约束名>][MODIFY <列名><数据类型>]

¨要求：

   （1）能够以命令方式执行；

   （2）能够以程序方式执行a

   （3）提供课程设计报告。

效果如下：

代码如下：

#! usr/bin/python #coding=utf-8import re,osprint '''```````````````````````````*******``.............................```**`*`*******``````````````````````````*`********`..........................````**```********`````````````````````````****`*``**````````..................``*`***************````````````*********```**```````````````````````````````````*************************************``*```````````````****``````````````*``*****************************************````````````````*`*******```````````******************@**********************````````````````````******`````````````*````*@*************************```````````````````````````****````*``*`*`````````````**********@@**************``````````````````````````****```````````````````````````*****@@@@****`****```````````````````````````````***`*````````````*``*````````````**@*@@*********``````````````````````````````******```````````````***`````````````**********@*`````````````````**@@*````````*`****```````````````****````````````````**********````````....```****@@**```````*`*``````````````````**``**```````````````********...```........`.@**@@@@``````````````````````````````**`**```````````````*******.````........`*.***@@*````````````````````*@@@@@@*`*``*````````````````````***@*..``.......````*****```````````````*****@@`.**@*@@@***``````````````````````****.``........````````````````````````****@@```@@@@@@@@**``````````````````````****.`.........````````````````````````************@@`*```````````````````````````**...........```.`````````````````````*````*****`**```````````````````````````````.............````````````````````````````````````````````..``````````````````````.........``*********``````.```````````````````````````````.`````````````````````.`.....`@@*@@@@@@@@****``````````````````````````````````......````````````````````....`@@@*@@@@@@@@@**`....`````````````````````````````.......```````````````........****@@@@@@@@@@@*````````````````````````..``````````````````````````````......```@**@@@@@@@@@***`````````````````````.```.`````````````````````````````*....`````***@@@@@@*****`````````````````````````````.``````````````````````````*....````*****@********``.````````````````````````````````````````````````````***....````**************`````````````````````````````````````````````````````*****....`````**@@@@@@*******`*```````````*``````````````````````````````````````****....`.`.`******@@@@@@@@*```````````****``````````````````````````````````````***......````***********@@@@@@`@@@@@*@***`````````````````````````````````````**`**......```````************@**********```````````````````````````````````````*****.......``````*`******```*****`*****``````````````````````````````````````*``****.....````````````````````````````````````````````````````````````````````*******...````````````````````````````````````````````````````````````````````*`*******....```````````````````````````````````````````````````````````````````****`****...`````````````````````````````````````````````````````````````````````**`*****..``````````````````````````````````````````````````````````````````````********.``````````````````````````````````````````````````````````````````*````********..````````````````````````````````````````````````````````````````````**********author:sdust-iot-15-2-朱虹霖下面请开始你的表演：示例：1.创建表create table information (  `account` char(10) not null, `password` char(10) not null,`tem` char(32)  , `wet`    char(32)  ,`lit`    char(32),     primary key( `account`)     );2.查看所有表名show tables;3.查看表结构desc information;4.增加列alter table information add id int(10);5.删除列alter table information drop id6.修改列alter table information modify id int(10)7.删除表drop table information;'''.decode('utf-8').encode('gbk')if(os.path.isfile('data.txt')!=True ):#初始化，如果不存在文件就创建file_ready =open('data.txt','w+')file_ready.close()flag_exit=0while(1):print "doge>",if (flag_exit==1):breakstopword = ';'#读取到;时结束输入operation = ''for line in iter(raw_input, stopword):if line =="":passelse:if(line[-1]==";"):operation+=line[:-1]breakoperation += line + '\n'operation=operation.decode('gbk').encode('utf-8')file_object_r =open('data.txt')try:data = file_object_r.read( )if (data==""):#文件为空，就进行以下初始化操作data="[]"list= eval(data)#强转为listlist.append('{\'name\':\'strcture\'}')#list的第一个对象为数据库名和数据库结构，list[0].append(list_thislist)即可添加当前数据库进去else:list= eval(data)if(operation.replace(" ","").replace("\n","").lower()=="showtables"):for key in eval(str(list[0])):print key.decode('utf-8').encode('gbk')else:if (operation.replace(" ","").replace("\n","").lower()[:11]=="createtable"): #创建数据库sql_name= operation.split("(")[0].replace(" ","").replace("\n","")[11:]#以'('为界分割,去空格，createtable往后的字符，从而取出要创的数据库名if (sql_name!=""):list_this_sql= {sql_name:[]}#用于添加到list[0]中管理整个数据库结构else:print "数据库名为空，请重新编写数据库语句".decode('utf-8').encode('gbk')create_information = operation.replace(operation.split("(")[0],"").replace("\n","")list_patterns = create_information.split(',')primary_key=""for i in range (len(list_patterns)):#循环读取每一组创建元素的限制pattern_name,pattern_length,pattern_type,notnull="",0,"",Falsepatterns_describe=re.split(" " "{1,}",list_patterns[i])#以一至多个空格为界，进行划分for j in range (len(patterns_describe)):if (patterns_describe[j]!=""and patterns_describe[j]!="(" and patterns_describe[j]!=")"and patterns_describe[j].replace(" ","").replace("`","")!=""):#第一个非空且不为特殊符号的变量即为元素名if (patterns_describe[j]=="primary"):sample_primarykey = r"\(.*?\)"primary_key=re.findall(sample_primarykey,list_patterns[i] ,re.M)[0].replace(')','').replace('(','').replace('`','').replace(" ","")#print "primary_key  "+primary_keybreakelse:pattern_name=patterns_describe[j].replace('`','').replace("(","")#print "pattern_name  "+pattern_namebreaksample_length = r"\([0-9]*?\)"#匹配数据长度if (i==0):pattern_length=int(re.findall(sample_length,list_patterns[i][1:],re.M)[0].replace(')','').replace('(',''))#如果是第一行就要去掉第一个(else:if (pattern_name!=""):if (len(re.findall(sample_length,list_patterns[i],re.M))!=0):#排除uniquekey 的情况pattern_length=int(re.findall(sample_length,list_patterns[i],re.M)[0].replace(')','').replace('(',''))#print "length  "+str(pattern_length)sample_type = r"[^ ]*?\(" #匹配数据类型if (i==0):pattern_type=re.findall(sample_type,list_patterns[i][1:],re.M)[0].replace(')','').replace('(','')else:pattern_type=re.findall(sample_type,list_patterns[i],re.M)[0].replace(')','').replace('(','')#print "type  "+pattern_typesample_notnull = r"[ ]*?not[ ]*?null"#匹配not nullif (len(re.findall(sample_notnull,list_patterns[i][1:],re.M))!=0):notnull=Trueif (pattern_name!="" and pattern_type!="" ):#最后如果名字和数据类型都非空，就加入该条目list_this_sql[sql_name].append([pattern_name,pattern_type,pattern_length,False,notnull])else:if (pattern_name!="" and pattern_type=="")or(pattern_type.lower()!="char"and pattern_type.lower()!=("int")and pattern_type.lower()!="boolen"and pattern_type.lower()!="key"):print ("第"+str(i+1)+"组数据有误请重写数据库语句").decode('utf-8').encode('gbk')#break#break#跳出整个循环if (primary_key!=""):#最后设置主键for num in range (len(list_this_sql[sql_name])):#print   "key             "+list_this_sql[sql_name][num][0]+primary_keyif (list_this_sql[sql_name][num][0]==primary_key):list_this_sql[sql_name][num][3]=Truecheck_flag=0for i in range(len(list)):if (i==0):passelse :if (sql_name==eval(str(list[i]))[0][0]):check_flag=1if (check_flag==0):list_first=eval(str(list[0]))list_first [sql_name]=list_this_sqllist[0]= list_first#存进第一个管理所有的数据库的表里list_this_sql_foruse=[sql_name]for num in range (len(list_this_sql[sql_name])):list_this_sql_foruse.append(list_this_sql[sql_name][num][0])list.append("["+str(list_this_sql_foruse)+"]")file_object = open('data.txt',"w")file_object.write(str(list))file_object .close()print "创建成功".decode('utf-8').encode('gbk')else:print "已存在数据表，请重新创建".decode('utf-8').encode('gbk')else:if (operation.replace(" ","").replace("\n","").lower()[:9]=="droptable"): #删除数据库delete_flag=0sql_name_delete= operation.split("(")[0].replace(" ","").replace("\n","")[9:]#以'('为界分割,去空格，删除数据库往后的字符，从而取出要删的数据库名for i in range(len(list)):if (i==0):passelse:if (sql_name_delete==eval(str(list[i]))[0][0]):del list[i]dict_delete=eval(str(list[0]))del dict_delete[sql_name_delete]list[0]=dict_deletedelete_flag=1file_object = open('data.txt',"w")file_object.write(str(list))file_object .close()if  (delete_flag==1):print "删除完成".decode('utf-8').encode('gbk')else :print "没有该表".decode('utf-8').encode('gbk')else :if (operation.replace(" ","").replace("\n","").lower()[:10]=="altertable"): #更改数据库operation_split=re.split(" " "{1,}",operation)#以一至多个空格为界，进行划分sql_name_alter=operation_split[2]order=operation_split[3]column_name=operation_split[4].replace("\n","")check_flag_fix=0for i in range(len(list)):if (i==0):passelse :if (sql_name_alter==eval(str(list[i]))[0][0]):check_flag_fix=1if(check_flag_fix==0):print ("不存在该表"+sql_name_alter+"，请重新填写语句").decode('utf-8').encode('gbk')else:if (order.lower()=="add"): column_ty=operation_split[5].replace("\n","") column_type=column_ty.split("(")[0].replace("\n","") column_length=column_ty.split("(")[1].replace(")","").replace(" ","").replace("\n","") dict_add1=eval(str(list[0])) list_add1=dict_add1[sql_name_alter][sql_name_alter] add_flag=0 for i in range(len(list_add1)): if(list_add1[i][0]==column_name):add_flag=1 if(add_flag==1): print (sql_name_alter+"已存在对应栏目"+column_name+"，无法添加").decode('utf-8').encode('gbk') else:list_for_add=[]list_for_add.append(column_name)list_for_add.append(column_type)list_for_add.append(column_length)list_for_add.append(False)list_for_add.append(False)dict_add1[sql_name_alter][sql_name_alter].append(list_for_add)list[0]=dict_add1for i in range (len(list)):if (i==0):passelse:list_cookie=eval(str(list[i]))if (list_cookie[0][0]==sql_name_alter):list_cookie[0].append(column_name)list[i]=list_cookiefile_object = open('data.txt',"w")file_object.write(str(list))file_object .close()print (sql_name_alter+"增加新栏目"+column_name+"成功").decode('utf-8').encode('gbk')else :if  (order.lower()=="drop"):dict_drop1=eval(str(list[0]))list_add1=dict_drop1[sql_name_alter][sql_name_alter]drop_flag=0for i in range(len(list_add1)):if(list_add1[i][0]==column_name):drop_flag=1if(drop_flag==0):print (sql_name_alter+"不存在对应栏目"+column_name+"，无法删除").decode('utf-8').encode('gbk')else:list_for_add=[]list_for_add.append(False)list_for_add.append(False)for i in range (len(dict_drop1[sql_name_alter][sql_name_alter])):if(eval(str(dict_drop1[sql_name_alter][sql_name_alter][i]))[0]==column_name):del dict_drop1[sql_name_alter][sql_name_alter][i]list[0]=dict_drop1for i in range (len(list)):if (i==0):passelse:list_cookie=eval(str(list[i]))if (list_cookie[0][0]==sql_name_alter):for j in range (len(list_cookie[0])):if(list_cookie[0][j]==column_name):del list_cookie[0][j]list[i]=list_cookiefile_object = open('data.txt',"w")file_object.write(str(list))file_object .close()print (sql_name_alter+"删除"+column_name+"完成").decode('utf-8').encode('gbk')else:if(order.lower()=="modify"):dict_drop1=eval(str(list[0]))list_add1=dict_drop1[sql_name_alter][sql_name_alter]drop_flag=0for i in range(len(list_add1)):if(list_add1[i][0]==column_name):drop_flag=1if(drop_flag==0):print (sql_name_alter+"不存在对应栏目"+column_name+"，将直接添加").decode('utf-8').encode('gbk')else:list_for_add=[]list_for_add.append(False)list_for_add.append(False)for i in range (len(dict_drop1[sql_name_alter][sql_name_alter])):if(eval(str(dict_drop1[sql_name_alter][sql_name_alter][i]))[0]==column_name):del dict_drop1[sql_name_alter][sql_name_alter][i]list[0]=dict_drop1for i in range (len(list)):if (i==0):passelse:list_cookie=eval(str(list[i]))if (list_cookie[0][0]==sql_name_alter):for j in range (len(list_cookie[0])):if(list_cookie[0][j]==column_name):del list_cookie[0][j]list[i]=list_cookiefile_object = open('data.txt',"w")file_object.write(str(list))file_object .close()print (sql_name_alter+"删除"+column_name+"完成").decode('utf-8').encode('gbk')column_ty=operation_split[5].replace("\n","")column_type=column_ty.split("(")[0].replace("\n","")column_length=column_ty.split("(")[1].replace(")","").replace(" ","").replace("\n","")dict_add1=eval(str(list[0]))list_add1=dict_add1[sql_name_alter][sql_name_alter]add_flag=0for i in range(len(list_add1)):if(list_add1[i][0]==column_name):add_flag=1if(add_flag==1):passelse:list_for_add=[]list_for_add.append(column_name)list_for_add.append(column_type)list_for_add.append(column_length)list_for_add.append(False)list_for_add.append(False)dict_add1[sql_name_alter][sql_name_alter].append(list_for_add)list[0]=dict_add1for i in range (len(list)):if (i==0):passelse:list_cookie=eval(str(list[i]))if (list_cookie[0][0]==sql_name_alter):list_cookie[0].append(column_name)list[i]=list_cookiefile_object = open('data.txt',"w")file_object.write(str(list))file_object .close()print (sql_name_alter+"增加新栏目"+column_name+"成功").decode('utf-8').encode('gbk')else:if (operation.replace("\n","").replace(" ","").lower()[:4]=="desc"):desc_flag=0desc_name=operation.replace("\n","").replace(" ","").lower()[4:]for i in range(len(list)):if (i==0):passelse:if (desc_name==eval(str(list[i]))[0][0]):desc_flag=1if(desc_flag==1):print ("   元素名    数据类型   数据长度  是否主键   是否非空").decode('utf-8').encode('gbk')desc_list=eval(str(list[0]))[desc_name][desc_name]for i in range (len(desc_list)):print ("%09s %09s  %09s  %09s %09s" % (desc_list[i][0],desc_list[i][1],desc_list[i][2],desc_list[i][3],desc_list[i][4])).decode('utf-8').encode('gbk')else:print  (desc_name+"表名错误，请重新填写").decode('utf-8').encode('gbk')else:if(operation.replace("\n","").replace(" ","").lower()=="exit"):print  "按任意键退出程序".decode('utf-8').encode('gbk')flag_exit=1i=input()else:print '数据库语言错误，请重新填写'.decode('utf-8').encode('gbk')# except Exception:#    print '数据库语言错误，请重新填写'.decode('utf-8').encode('gbk')finally: file_object_r.close()#pyinstaller -F# sql_name  数据库名  ，pattern_name 元素名，pattern_type 数据类型，pattern_length 长度,isEmpty 是否能为空 ,primary_key 主键#                      元素名  数据类型   数据长度  primary_key是否主键   ,是否必须not null# {表名 tablename,[ [ "account" , "int"， 0    ，     true  ,                  false                                                ]]}#  表名  tablename      值  值  值  值# [        tablename     id accout name value ],# [                     0,0,0,0]# create table information (#   `account` char(10) not null,#  ‘password’ char(10) not null,# ‘tem’ char(32)  ,#  ‘wet’    char(32)  ,# ‘lit’    char(32),#      primary key( `account`)#      );#alter table information add id int(10) not null#alter table information drop id#alter table information modify id int(10) not null# 先切开 alter table  ,然后判定informaition ,再判定下一个

关于如何打包成exe可执行文件参照我上一篇博客

http://blog.csdn.net/qq_32545287/article/details/73292264