一天写完了。。。。。挂这里就当是造福后人了，虽然写的这么乱估计也没什么人看，就当是见笑了
选题：
¨CREATE TABLE <表名> （<列名><数据类型>[<列完整性约束条件>][，<列名><数据类型>[<列完整性约束条件>]…][，<表完整性约束条件>]）
¨ALTER TABLE <表名> [ADD <新列名><数据类型>[<列完整性约束>]] [DROP<列完整性约束名>][MODIFY <列名><数据类型>]
¨要求：
（1）能够以命令方式执行；
（2）能够以程序方式执行a
（3）提供课程设计报告。
效果如下：
代码如下：
#! usr/bin/python #coding=utf-8
import re,os
print '''
```````````````````````````*******``.............................```**`*`*******
``````````````````````````*`********`..........................````**```********
`````````````````````````****`*``**````````..................``*`***************
````````````*********```**```````````````````````````````````*******************
******************``*```````````````****``````````````*``***********************
******************````````````````*`*******```````````******************@*******
***************````````````````````******`````````````*````*@*******************
******```````````````````````````****````*``*`*`````````````**********@@********
******``````````````````````````****```````````````````````````*****@@@@****`***
*```````````````````````````````***`*````````````*``*````````````**@*@@*********
``````````````````````````````******```````````````***`````````````**********@*`
````````````````**@@*````````*`****```````````````****````````````````**********
````````....```****@@**```````*`*``````````````````**``**```````````````********
...```........`.@**@@@@``````````````````````````````**`**```````````````*******
.````........`*.***@@*````````````````````*@@@@@@*`*``*````````````````````***@*
..``.......````*****```````````````*****@@`.**@*@@@***``````````````````````****
.``........````````````````````````****@@```@@@@@@@@**``````````````````````****
.`.........````````````````````````************@@`*```````````````````````````**
...........```.`````````````````````*````*****`**```````````````````````````````
.............````````````````````````````````````````````..`````````````````````
`.........``*********``````.```````````````````````````````.````````````````````
`.`.....`@@*@@@@@@@@****``````````````````````````````````......````````````````
````....`@@@*@@@@@@@@@**`....`````````````````````````````.......```````````````
........****@@@@@@@@@@@*````````````````````````..``````````````````````````````
......```@**@@@@@@@@@***`````````````````````.```.`````````````````````````````*
....`````***@@@@@@*****`````````````````````````````.``````````````````````````*
....````*****@********``.````````````````````````````````````````````````````***
....````**************`````````````````````````````````````````````````````*****
....`````**@@@@@@*******`*```````````*``````````````````````````````````````****
....`.`.`******@@@@@@@@*```````````****``````````````````````````````````````***
......````***********@@@@@@`@@@@@*@***`````````````````````````````````````**`**
......```````************@**********```````````````````````````````````````*****
.......``````*`******```*****`*****``````````````````````````````````````*``****
.....````````````````````````````````````````````````````````````````````*******
...````````````````````````````````````````````````````````````````````*`*******
....```````````````````````````````````````````````````````````````````****`****
...`````````````````````````````````````````````````````````````````````**`*****
..``````````````````````````````````````````````````````````````````````********
.``````````````````````````````````````````````````````````````````*````********
..````````````````````````````````````````````````````````````````````**********
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 id
6.修改列
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=0
while(1):
print "doge>",
if (flag_exit==1):
break
stopword = ';'#读取到;时结束输入
operation = ''
for line in iter(raw_input, stopword):
if line =="":
pass
else:
if(line[-1]==";"):
operation+=line[:-1]
break
operation += 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)#强转为list
list.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,"",False
patterns_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_key
break
else:
pattern_name=patterns_describe[j].replace('`','').replace("(","")
#print "pattern_name "+pattern_name
break
sample_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_type
sample_notnull = r"[ ]*?not[ ]*?null"#匹配not null
if (len(re.findall(sample_notnull,list_patterns[i][1:],re.M))!=0):
notnull=True
if (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_key
if (list_this_sql[sql_name][num][0]==primary_key):
list_this_sql[sql_name][num][3]=True
check_flag=0
for i in range(len(list)):
if (i==0):
pass
else :
if (sql_name==eval(str(list[i]))[0][0]):
check_flag=1
if (check_flag==0):
list_first =eval(str(list[0]))
list_first [sql_name]=list_this_sql
list[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=0
sql_name_delete= operation.split("(")[0].replace(" ","").replace("\n","")[9:]#以'('为界分割,去空格，删除数据库往后的字符，从而取出要删的数据库名
for i in range(len(list)):
if (i==0):
pass
else:
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_delete
delete_flag=1
file_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=0
for i in range(len(list)):
if (i==0):
pass
else :
if (sql_name_alter==eval(str(list[i]))[0][0]):
check_flag_fix=1
if(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_add1
for i in range (len(list)):
if (i==0):
pass
else:
list_cookie=eval(str(list[i]))
if (list_cookie[0][0]==sql_name_alter):
list_cookie[0].append(column_name)
list[i]=list_cookie
file_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=0
for i in range(len(list_add1)):
if(list_add1[i][0]==column_name):
drop_flag=1
if(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_drop1
for i in range (len(list)):
if (i==0):
pass
else:
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_cookie
file_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=0
for i in range(len(list_add1)):
if(list_add1[i][0]==column_name):
drop_flag=1
if(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_drop1
for i in range (len(list)):
if (i==0):
pass
else:
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_cookie
file_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=0
for i in range(len(list_add1)):
if(list_add1[i][0]==column_name):
add_flag=1
if(add_flag==1):
pass
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_add1
for i in range (len(list)):
if (i==0):
pass
else:
list_cookie=eval(str(list[i]))
if (list_cookie[0][0]==sql_name_alter):
list_cookie[0].append(column_name)
list[i]=list_cookie
file_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=0
desc_name=operation.replace("\n","").replace(" ","").lower()[4:]
for i in range(len(list)):
if (i==0):
pass
else:
if (desc_name==eval(str(list[i]))[0][0]):
desc_flag=1
if(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=1
i=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 ,再判定下一个http://blog.youkuaiyun.com/qq_32545287/article/details/73292264
扫一扫
155
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
