SQL命令解析与操作
本文介绍了一种在Python中解析并执行SQL命令的方法，包括增删改查等基本操作。通过对SQL命令进行分析和分割，利用列表和字典处理数据表，实现了简单的数据库管理功能。
环境：win7系统、anaconda3 和 pycharm
要想实现增删改查的命令，首先对命令进行分析，对命令进行分析，提取有用的信息
比如：数据表、where条件、要显示的字段
对命令进行分割（split(' ')）
select * from user where name = '李云龙'
sql = "select * from user where name = '李云龙'"
sql_list = sql.strip().split(' ') #以空格为分隔符
print(sql_list)
res = []
for i in sql_list: #遍历列表，去除空白元素
if i:
res.append(i)
print(res)
sql_list = res
sql_list = ['select', '*', 'from', 'user', 'where', 'name', '=', "'李云龙'"]
if 'from' in sql_list:
from_id = sql_list('from'） #得到from 在sql_list 中的索引号
table = sql_list[from_id + 1]
if 'where' in sql_list:
where_list = sql_list[-3:] # 得到where_list
def where(table,where_list):
"""
:param table: 数据表
:param where_list: 筛选条件列表
:return:
"""
title = "id,name,age,phone,dept,enroll_date"
fh = open(table,'r',encoding='utf8')
res = []
for line in fh:
dic = dict(zip(title.split(','),line.split(','))) #利用zip 函数 定义字典
exp_k,opt,exp_v = where_list
# print(opt == 'like')
if opt == "=":
opt = '%s='%where_list[1]
if dic[exp_k].isdigit():
dic_v = int(dic[exp_k])
exp_v = int(exp_v)
else:
dic_v = "'%s'"%dic[exp_k]
if opt != 'like':
exp = eval('%s%s%s'%(dic_v,opt,exp_v))
if exp:
res.append(line)
else:
if exp_v in dic_v:
print('exp_v %s,dic_v: %s'%(exp_v,dic_v))
res.append(line)
# print('符合条件的数据行：%s'%res)
return res
需要什么，就想法利用字典或列表的属性得到他，然后测试是否可行！
这些都是按照正常思路设计的，如果你要剑走偏锋报错了，我就不负责了，嘿嘿
下面是完整的实例：
import os
def rename_file(table,newfile):# 实现旧表的删除，新表的重命名
os.remove(table)
os.renames(newfile,table)
def insert(sql_list):
# insert into user values 李云龙,35,1889922334,2017-05-05
table = sql_list[2]
info = sql_list[-1]
with open(table,'r+',encoding='utf8') as fh:
res = []
for line in fh:
if len(line) != 0:
res.append(line)
last_line = res[-1]
id = int(last_line.strip().split(',')[0])
new_id = id + 1
new_line = '%s,%s\n'%(str(new_id),info)
print(new_line)
fh.write(new_line)
fh.close()
print('成功写入：%s'%info)
def delete(sql_list):
#delete from user where name like '李'
table = sql_list[2]
if 'where' not in sql_list:
with open(table,'r',encoding='utf8') as f:
result = f.readlines()
else:
where_list = sql_list[-3:]
result = where(table,where_list)
print('即将删除：%s'%result)
new_file = 'new_file'
with open(table,'r',encoding='utf8') as f,\
open(new_file,'w',encoding='utf8') as new_f:
for line in f:
if line not in result:
new_f.write(line)
new_f.flush()
def update(sql_list):
# update user set dept = 'sa' where age < 18
title = "id,name,age,phone,dept,enroll_date"
up_set = sql_list[3:6]
print(up_set)
table = sql_list[1]
new_file = 'new_file'
if 'where' not in sql_list:
print('update 语法错误！')
exit()
else:
where_list = sql_list[-3:]
with open(table,'r',encoding='utf8') as f,\
open(new_file,'w',encoding='utf8') as new_f:
res = where(table,where_list)
print(res)
if res:
for line in f:
if line in res:
dic = dict(zip(title.strip().split(','),line.strip().split(',')))
dic[up_set[0]] = up_set[-1]
new_line = '%s,%s,%s,%s,%s,%s\n'%(dic["id"],dic["name"],dic["age"],dic['phone'],dic["dept"],dic["enroll_date"])
print("newline'",new_line)
new_f.write(new_line)
new_f.flush()
else:
new_f.write(line)
new_f.flush()
def select(sql_list):
#select * from user where dept = IT
from_id = sql_list.index('from')
fields = sql_list[1:from_id] # 得到需要筛选的字段
table = sql_list[from_id+1]
if 'where' not in sql_list:
fh = open(table,'r',encoding='utf8')
res = fh.readlines()
if res:
res = search(fields,res)
for i in res:
print(i)
else:
print('没有结果')
else:
where_list = sql_list[-3:]
res = where(table,where_list)
if res:
res = search(fields,res)
for i in res:
print(i)
else:
print('没有结果')
def search(field,res):
title = "id,name,age,phone,dept,enroll_date"
sea_res = []
for line in res:
#利用 zip 函数 得到 数据行的字典
dic = dict(zip(title.strip().split(','),line.strip().split(',')))
if len(field) == 1:
if field[0] == '*':
return res
else:
sea_res.append(dic[field[0]])
else:
r_l = []
for i in field:
r_l.append(dic[i].strip())
sea_res.append(r_l)
return sea_res
def where(table,where_list):
"""
:param table: 数据表
:param where_list: 筛选条件列表
:return:
"""
title = "id,name,age,phone,dept,enroll_date"
fh = open(table,'r',encoding='utf8')
res = []
for line in fh:
dic = dict(zip(title.split(','),line.split(',')))
exp_k,opt,exp_v = where_list
# print(opt == 'like')
if opt == "=":
opt = '%s='%where_list[1]
if dic[exp_k].isdigit():
dic_v = int(dic[exp_k])
exp_v = int(exp_v)
else:
dic_v = "'%s'"%dic[exp_k]
if opt != 'like':
exp = eval('%s%s%s'%(dic_v,opt,exp_v))
if exp:
res.append(line)
else:
if exp_v in dic_v:
print('exp_v %s,dic_v: %s'%(exp_v,dic_v))
res.append(line)
# print('符合条件的数据行：%s'%res)
return res
test_sql = """
# insert into user values 李云龙,32,1999883771,IT,2017-07-08
# delete from user where name = '李云龙'
# delete from user where name like '李'
# update user set dept = 运维 where name = '李云龙'
# select * from user where dept = 'IT'
# select name age from user where dept = 'IT'
# select * from user where enroll_date like 2013
# select name age from user where age > 22
# select id name age phone from user where age > 20
# select * from user name like '李'
"""
if __name__ == "__main__":
print(test_sql)
print('测试命令如上：')
while True:
print('字段名称：\033[33mid,name,age,phone,dept,enroll_date\033[0m')
sql = input('sql command >').strip()
if sql =='exit':
exit()
if len(sql) == 0:
continue
sql_list = sql.strip().split(' ')
print(sql_list)
res = []
for i in sql_list:
if i:
res.append(i)
sql_list = res
func = sql_list[0]
dic_sql = {'insert':insert,
'delete':delete,
'update':update,
'select':select,}
if func in dic_sql:
res = dic_sql[func](sql_list)
print(res)
else:
print('不能识别的sql 命令')
continue
慢慢来，整理思路，回头再看看视频，会有柳暗花明的感觉，加油！
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
