python学习-函数模拟SQL

具体需求:

员工信息表程序，实现增删改查操作：

可进行模糊查询，语法支持下面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             continue
100         if tag:
101             sql_dic[key].append(item)
102     # print('before 33[33;1m%s33[0m' %sql_dic)
103     if sql_dic.get('where'):
104         sql_dic['where']=where_parse(sql_dic.get('where'))
105 
106     # print('after 33[33;1m%s33[0m' %sql_dic)
107     return sql_dic
108 
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:continue
120         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+=i
128     else:
129         char=three_parse(char)
130         res.append(char)
131     return res
132 
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 33[34;1m%s33[0m' %exp_str)
140     key=['>','=','<']
141     res=[]
142     char=''
143     opt=''
144     tag=False
145     for i in exp_str:
146         if i in key:
147             tag=True
148             if len(char) !=0:
149                 res.append(char)
150                 char=''
151             opt+=i
152         if not tag:
153             char+=i
154         if tag and i not in key:
155             tag=False
156             res.append(opt)
157             opt=''
158             char+=i
159     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 res
167 
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 = -100
183         while True:
184             fh.seek(offs,2)
185             lines = fh.readlines()
186             if len(lines)>1:
187                 last = lines[-1]
188                 break
189             offs *= 2
190         last=last.decode(encoding='utf-8')
191         last_id=int(last.split(',')[0])
192         new_id=last_id+1
193         #insert into db1.emp values 张国辉,30,18500841678,运维,2007-8-1
194         record=sql_dic.get('values')[0].split(',')
195         record.insert(0,str(new_id))
196         #['26', 'alex', '18', '13120378203', '运维', '2013-3-1
']
197         record_str=','.join(record)+'
'
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=0
208         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+=1
216         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 alex
223     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=0
232         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]=v
242                 print('change dic is %s ' %dic)
243                 line=[]
244                 for i in title.split(','):
245                     line.append(dic[i])
246                 update_count+=1
247                 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_res
270 
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 res
284 
285 def logic_action(dic,where_l):
286     res=[]
287     # print('==33[45;1m%s33[0m==33[48;1m%s33[0m' %(dic,where_l))
288     for exp in where_l:
289         if type(exp) is list:
290             exp_k,opt,exp_v=exp
291             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('==33[45;1m%s33[0m' %(res))
308     return res
309 
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_res
317 
318     return res
319 
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_res
326         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':break
346         if len(sql) == 0:continue
347 
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)

SQL

上课笔记

一、函数：

函数是第一类的对象：指的是函数可以被当做数据传递

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)

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()

stop_time=time.time()

print('run time is %s' %(stop_time-start_time))

wrapper('egon',age=18)

命名关键字参数: 在*后面定义的形参称为命名关键字参数，必须是被以关键字实参的形式传值