具体需求:
员工信息表程序，实现增删改查操作：
可进行模糊查询，语法支持下面3种:
select name,age from staff_data where age > 22 多个查询参数name,age 用','分割
select * from staff_data where dept = 人事
select * from staff_data where enroll_date like 2013
查到的信息，打印后，最后面还要显示查到的条数
可创建新员工纪录，以phone做唯一键，phone存在即提示,staff_id需自增,添加多个记录record1/record2中间用'/'分割
insert into staff_data values record1/record2
可删除指定员工信息纪录，输入员工id，即可删除
delete from staff_data where staff_id>=5andstaff_id<=10
可修改员工信息，语法如下:
update staff_table set dept=Market,phone=13566677787 where dept = 运维 多个set值用','分割
1 #_*_coding:utf-8_*_ 2 #第一部分:sql解析 3 import os 4 def sql_parse(sql): 5 ''' 6 sql_parse--->insert_parse,delete_parse,update_parse,select_parse 7 sql解析总控 8 :param sql:用户输入的字符串 9 :return: 返回字典格式sql解析结果 10 ''''' 11 parse_func={ 12 'insert':insert_parse, 13 'delete':delete_parse, 14 'update':update_parse, 15 'select':select_parse, 16 } 17 sql_l=sql.split(' ') 18 func=sql_l[0] 19 res='' 20 if func in parse_func: 21 res=parse_func[func](sql_l) 22 return res 23 24 def insert_parse(sql_l): 25 # insert into db.t1 values 鲁海宝,35,13910015353,测试,2005-06-27 26 ''' 27 sql解析分支:insert 28 :param sql_l: sql按照空格分割的列表 29 :return: 返回字典格式的sql解析结果 30 ''''' 31 sql_dic={ 32 'func':insert, #函数名 33 'insert':[], #insert选项,留出扩展 34 'into':[], #表名 35 'values':[], #值 36 } 37 return handle_parse(sql_l,sql_dic) 38 39 def delete_parse(sql_l): 40 # delete from db.t1 where id=1 41 ''' 42 sql解析分支:delete 43 :param sql_l: sql按照空格分割的列表 44 :return: 返回字典格式的sql解析结果 45 ''''' 46 sql_dic={ 47 'func':delete, 48 'delete':[], #delete选项,留出扩展 49 'from':[], #表名 50 'where':[], #filter条件 51 } 52 return handle_parse(sql_l,sql_dic) 53 54 def update_parse(sql_l): 55 # update db.t1 set id=2 where name='alex' 56 ''' 57 sql解析分支:update 58 :param sql_l: sql按照空格分割的列表 59 :return: 返回字典格式的sql解析结果 60 ''''' 61 sql_dic={ 62 'func':update, 63 'update':[], #update选项,留出扩展 64 'set':[], #修改的值 65 'where':[], #filter条件 66 } 67 return handle_parse(sql_l,sql_dic) 68 69 def select_parse(sql_l): 70 # select * from db1.emp where not id= 1 and name = 'alex' or name= 'sb' limit 3 71 ''' 72 sql解析分支:select 73 :param sql_l: sql按照空格分割的列表 74 :return: 返回字典格式的sql解析结果 75 ''''' 76 sql_dic={ 77 'func':select, 78 'select':[], #查询字段 79 'from':[], #表 80 'where':[], #filter条件 81 'limit':[], #limit条件 82 } 83 return handle_parse(sql_l,sql_dic) 84 85 def handle_parse(sql_l,sql_dic): 86 ''' 87 填充sql_dic 88 :param sql_l: sql按照空格分割的列表 89 :param sql_dic: 待填充的字典 90 :return: 返回字典格式的sql解析结果 91 ''''' 92 tag=False 93 for item in sql_l: 94 if tag and item in sql_dic: 95 tag=False 96 if not tag and item in sql_dic: 97 tag=True 98 key=item 99 continue100 if tag:101 sql_dic[key].append(item)102 # print('before \033[33;1m%s\033[0m' %sql_dic)103 if sql_dic.get('where'):104 sql_dic['where']=where_parse(sql_dic.get('where'))105 106 # print('after \033[33;1m%s\033[0m' %sql_dic)107 return sql_dic108 109 def where_parse(where_l):110 '''111 对用户输入的where子句后的条件格式化,每个子条件都改成列表形式112 :param where_l: 用户输入where后对应的过滤条件列表113 :return:114 '''''115 res=[]116 key=['and','or','not']117 char=''118 for i in where_l:119 if len(i) == 0:continue120 if i in key:121 if len(char) != 0:122 char=three_parse(char) #将每一个小的过滤条件如,name>=1转换成['name','>=','1']123 res.append(char)124 res.append(i)125 char=''126 else:127 char+=i128 else:129 char=three_parse(char)130 res.append(char)131 return res132 133 def three_parse(exp_str):134 '''135 将每一个小的过滤条件如,name>=1转换成['name','>=','1']136 :param exp_str:条件表达式的字符串形式,例如'name>=1'137 :return:138 '''''139 # print('three_opt before is \033[34;1m%s\033[0m' %exp_str)140 key=['>','=','<']141 res=[]142 char=''143 opt=''144 tag=False145 for i in exp_str:146 if i in key:147 tag=True148 if len(char) !=0:149 res.append(char)150 char=''151 opt+=i152 if not tag:153 char+=i154 if tag and i not in key:155 tag=False156 res.append(opt)157 opt=''158 char+=i159 else:160 res.append(char)161 # print('res is %s ' %res)162 #新增like功能163 if len(res) == 1:#['namelike_ale5']164 res=res[0].split('like')165 res.insert(1,'like')166 return res167 168 169 #第二部分:sql执行170 def sql_action(sql_dic):171 '''172 执行sql的统一接口,内部执行细节对用户完全透明173 :param sql:174 :return:175 '''''176 return sql_dic.get('func')(sql_dic)177 178 def insert(sql_dic):179 print('insert %s' %sql_dic)180 db,table=sql_dic.get('into')[0].split('.')181 with open('%s/%s' %(db,table),'ab+') as fh:182 offs = -100183 while True:184 fh.seek(offs,2)185 lines = fh.readlines()186 if len(lines)>1:187 last = lines[-1]188 break189 offs *= 2190 last=last.decode(encoding='utf-8')191 last_id=int(last.split(',')[0])192 new_id=last_id+1193 #insert into db1.emp values 张国辉,30,18500841678,运维,2007-8-1194 record=sql_dic.get('values')[0].split(',')195 record.insert(0,str(new_id))196 #['26', 'alex', '18', '13120378203', '运维', '2013-3-1\n']197 record_str=','.join(record)+'\n'198 fh.write(bytes(record_str,encoding='utf-8'))199 fh.flush()200 return [['insert successful']]201 202 def delete(sql_dic):203 db,table=sql_dic.get('from')[0].split('.')204 bak_file=table+'_bak'205 with open("%s/%s" %(db,table),'r',encoding='utf-8') as r_file,\206 open('%s/%s' %(db,bak_file),'w',encoding='utf-8') as w_file:207 del_count=0208 for line in r_file:209 title="id,name,age,phone,dept,enroll_date"210 dic=dict(zip(title.split(','),line.split(',')))211 filter_res=logic_action(dic,sql_dic.get('where'))212 if not filter_res:213 w_file.write(line)214 else:215 del_count+=1216 w_file.flush()217 os.remove("%s/%s" % (db, table))218 os.rename("%s/%s" %(db,bak_file),"%s/%s" %(db,table))219 return [[del_count],['delete successful']]220 221 def update(sql_dic):222 #update db1.emp set id='sb' where name like alex223 db,table=sql_dic.get('update')[0].split('.')224 set=sql_dic.get('set')[0].split(',')225 set_l=[]226 for i in set:227 set_l.append(i.split('='))228 bak_file=table+'_bak'229 with open("%s/%s" %(db,table),'r',encoding='utf-8') as r_file,\230 open('%s/%s' %(db,bak_file),'w',encoding='utf-8') as w_file:231 update_count=0232 for line in r_file:233 title="id,name,age,phone,dept,enroll_date"234 dic=dict(zip(title.split(','),line.split(',')))235 filter_res=logic_action(dic,sql_dic.get('where'))236 if filter_res:237 for i in set_l:238 k=i[0]239 v=i[-1].strip("'")240 print('k v %s %s' %(k,v))241 dic[k]=v242 print('change dic is %s ' %dic)243 line=[]244 for i in title.split(','):245 line.append(dic[i])246 update_count+=1247 line=','.join(line)248 w_file.write(line)249 250 w_file.flush()251 os.remove("%s/%s" % (db, table))252 os.rename("%s/%s" %(db,bak_file),"%s/%s" %(db,table))253 return [[update_count],['update successful']]254 255 def select(sql_dic):256 db,table=sql_dic.get('from')[0].split('.')257 fh=open("%s/%s" %(db,table),'r',encoding='utf-8')258 259 filter_res=where_action(fh,sql_dic.get('where'))260 # print('filter_res is ====>',filter_res)261 fh.close()262 263 limit_res=limit_action(filter_res,sql_dic.get('limit'))264 # print('limit_res is ====>',limit_res)265 266 search_res=search_action(limit_res,sql_dic.get('select'))267 # print('select_res is ====>',search_res)268 269 return search_res270 271 def where_action(fh,where_l):272 res=[]273 logic_l=['and','or','not']274 title="id,name,age,phone,dept,enroll_date"275 if len(where_l) !=0:276 for line in fh:277 dic=dict(zip(title.split(','),line.split(',')))278 logic_res=logic_action(dic,where_l)279 if logic_res:280 res.append(line.split(','))281 else:282 res=fh.readlines()283 return res284 285 def logic_action(dic,where_l):286 res=[]287 # print('==\033[45;1m%s\033[0m==\033[48;1m%s\033[0m' %(dic,where_l))288 for exp in where_l:289 if type(exp) is list:290 exp_k,opt,exp_v=exp291 if exp[1] == '=':292 opt='%s=' %exp[1]293 if dic[exp_k].isdigit():294 dic_v=int(dic[exp_k])295 exp_v=int(exp_v)296 else:297 dic_v="'%s'" %dic[exp_k]298 if opt != 'like':299 exp=str(eval("%s%s%s" %(dic_v,opt,exp_v)))300 else:301 if exp_v in dic_v:302 exp='True'303 else:304 exp='False'305 res.append(exp)306 res=eval(' '.join(res))307 # print('==\033[45;1m%s\033[0m' %(res))308 return res309 310 def limit_action(filter_res,limit_l):311 res=[]312 if len(limit_l) !=0:313 index=int(limit_l[0])314 res=filter_res[0:index]315 else:316 res=filter_res317 318 return res319 320 def search_action(limit_res,select_l):321 res=[]322 fileds_l=[]323 title="id,name,age,phone,dept,enroll_date"324 if select_l[0] == '*':325 res=limit_res326 fileds_l=title.split(',')327 else:328 329 for record in limit_res:330 dic=dict(zip(title.split(','),record))331 # print("dic is %s " %dic)332 fileds_l=select_l[0].split(',')333 r_l=[]334 for i in fileds_l:335 r_l.append(dic[i].strip())336 res.append(r_l)337 338 return [fileds_l,res]339 340 341 342 if __name__ == '__main__':343 while True:344 sql=input("sql> ").strip()345 if sql == 'exit':break346 if len(sql) == 0:continue347 348 sql_dic=sql_parse(sql)349 350 if len(sql_dic) == 0:continue #输入命令非法351 res=sql_action(sql_dic)352 353 for i in res[-1]:354 print(i)
上课笔记
一、 函数：
函数是第一类的对象：指的是函数可以被当做数据传递
def foo():
print('from foo')
1 被赋值
f=foo
print(f)
f()
2 可以当做参数传入
def wrapper(func):
print(func)
func()
wrapper(foo)
3 可以当做函数的返回
def wrapper(func):
return func
res=wrapper(foo)
print(res)
4 可以当做容器类型的元素
cmd_dic={
'func':foo
}
print(cmd_dic)
cmd_dic['func']()
def select(sql):
'''select function'''
print('select----->',sql)
sql=['select', '*', 'from', 'mysql.user;']
def insert(sql):
'''insert function'''
print('insert---->',sql)
def update(sql):
'''update function'''
print('update----->',sql)
def delete(sql):
'''delete function'''
print('delete---->',sql)
def alter(sql):
print('alter===>',sql)
cmd_dic = {
'insert': insert,
'update': update,
'delete': delete,
'select': select,
'alter':alter,
}
select * from mysql.user;
def main():
while True:
sql=input('>>: ').strip()
if not sql:continue
cmd_info=sql.split()
cmd=cmd_info[0]
if cmd in cmd_dic:
cmd_dic[cmd](cmd_info)
else:
print('cmd not exists')
形参：在定义函数时，括号内的参数成为形参
特点：形参就是变量名
def foo(x,y): x=1,y=2
print(x)
print(y)
实参：在调用函数时，括号内的参数成为实参
特点：实参就是变量值
foo(1,2)
在调用阶段实参（变量值）才会绑定形参（变量名）
调用结束后，解除绑定
可变长参数指的是实参的个数多了
实参无非位置实参和关键字实参两种
形参必须要两种机制来分别处理按照位置定义的实参溢出的情况：*
跟按照关键字定义的实参溢出的情况：**
def foo(x,y,*args): nums=(3,4,5,6,7)
print(x)
print(y)
print(args)
foo(1,2,3,4,5,6,7) *
foo(1,2) *
*args的扩展用法
def foo(x,y,*args): *args=*(3,4,5,6,7)
print(x)
print(y)
print(args)
foo(1,2,3,4,5,6,7) *
foo(1,2,*(3,4,5,6,7)) foo(1,2,3,4,5,6,7)
def foo(x,y=1,*args):
print(x)
print(y)
print(args)
foo('a','b',*(1,2,3,4,5,6,7)) foo('a','b',1,2,3,4,5,6,7)
foo('egon',10,2,3,4,5,6,9,y=2) 报错
foo('egon',10,2,3,4,5,6,9)
def foo(x,y,**kwargs): nums={'z':3,'b':2,'a':1}
print(x)
print(y)
print(kwargs)
foo(1,2,z=3,a=1,b=2) **
def foo(x,y,**kwargs): kwargs={'z':3,'b':2,'a':1}
print(x)
print(y)
print(kwargs)
foo(1,2,**{'z':3,'b':2,'a':1}) foo(1,2,a=1,z=3,b=2)
def foo(x, y):
print(x)
print(y)
foo(**{'y':1,'x':2}) foo(y=1,x=2)
def foo(x,*args,**kwargs):args=(2,3,4,5) kwargs={'b':1,'a':2}
print(x)
print(args)
print(kwargs)
foo(1,2,3,4,5,b=1,a=2)
这俩东西*args，**kwargs干甚用？？？
def register(name,age,sex='male'):
print(name)
print(age)
print(sex)
def wrapper(*args,**kwargs): args=(1,2,3) kwargs={'a':1,'b':2}
print(args)
print(kwargs)
register(*args,**kwargs)
register(*(1, 2, 3),**{'a': 1, 'b': 2})
register(1, 2, 3,a=1,b=2)
wrapper(1,2,3,a=1,b=2)
import time
def register(name,age,sex='male'):
start_time=time.time()
print(name)
print(age)
print(sex)
time.sleep(3)
stop_time=time.time()
print('run time is %s' %(stop_time-start_time))
def wrapper(*args, **kwargs): args=('egon',) kwargs={'age':18}
start_time=time.time()
register(*args, **kwargs)
stop_time=time.time()
print('run time is %s' %(stop_time-start_time))
wrapper('egon',age=18)
register('egon',18)
命名关键字参数: 在*后面定义的形参称为命名关键字参数，必须是被以关键字实参的形式传值
def foo(name,age,*args,sex='male',group):
print(name)
print(age)
print(args)
print(sex)
print(group)
foo('alex',18,19,20,300,group='group1')
def foo(name,age=18,*args,sex='male',group,**kwargs):
pass