PyMysql简单的记录一下
用了两天时间，用手机写的，不想多说啥，就这点水平了。注释也懒得写了。。自己懂就行了。
import pymysql,json
from prettytable import PrettyTable
class My():
ID = 'id'
def __init__(self):
try:
#{'host': 'x.xxxx.vip', 'user': 'db', 'password': 'zxc6xxxxxx3', 'database': 'db'}
self.db = pymysql.connect(**self.ad())
self.youbiao = self.db.cursor(pymysql.cursors.DictCursor)
self.youbiao.execute('SELECT VERSION()')
data = self.youbiao.fetchone().get('VERSION()')
print('----------------\n数据库连接成功\n版本：%s\n----------------'%data)
except:
print('数据库连接失败')
def rk(self):
while True:
print('欢迎使用数据库管理系统')
print('----------------------')
a = input('1.数据操作\n2.插入数据\n3.初始化表单\n4.删除表单\n0.退出系统\n')
a1 = ['查询全部','关键字查询','自定义查询']
print('')
if a == '1':
b = self.b()
n = self.xz(a1)
try:
if n == '查询全部':
print('选择需要操作的数据编号:')
li = self.pt(self.all(b),kg=1)
self.cz(b,li)
elif n == '关键字查询':
print('选择要查询的数据')
key = self.xz(self.head(b))
use = input('查询 %s = '%key)
li = self.pt(self.sel(b,key,use),kg=1)
self.cz(b,li)
except:
print('表内无数据')
print('')
self.rk()
elif a == '2':
b = self.b()
while True:
li = []
head = self.head(b)
for i in head:
a2 = input('%s = '%i)
li.append(a2)
self.add(b,tuple(head),tuple(li))
a3 = input('继续添加按确定,键入ok退出添加')
if a3 == 'ok':
break
else:
pass
elif a == '3':
self.sr()
elif a == '4':
i = input('请输入需要删除的表单名称：\n')
self.my("DROP TABLE IF EXISTS %s"%i)
elif a == '0':
self.youbiao.close()
self.db.close()
print('感谢使用')
break
else:
print('请选择正确的编号')
def cz(self,b,li,ID=ID):
a2 = ['删除','修改']
n1 = self.xz(a2)
if n1 == '删除':
for i in li:
self.dele(b,i.get(ID))
else:
key = self.xz(list(li[0].keys()))
use = input('把 %s 的值修改为：'%key)
for i in li:
self.up(b,key,use,i.get(ID))
def my(self,sql):
try:
# 执行SQL语句
self.youbiao.execute(sql)
# 提交修改
self.db.commit()
print('操作成功')
except:
# 发生错误时回滚
self.db.rollback()
print('错误放弃执行')
def ad(self,a='./db.json'):
with open(a,'r') as f:
data = json.load(f)
return data
def xz(self,lis):
a = 1
li1 = ""
for i in lis:
li = "%d、%s "%(a,i)
a += 1
if a == 4:
li1 = li1 + li +"\n"
else:
li1 = li1 + li
print(li1)
n = input('选择：')
li11 = [str(i) for i in range(1,len(li1)+1)]
li11.insert(0,'')
while n not in li11:
print('没有%s选项'%n)
n = input('选择：')
if n == '':
self.rk()
else:
return lis[int(n)-1]
def sr(self):
a = input("请输入表单名称\n")
data = []
z = 1
while True:
print('')
b = input('第【%s】列\n输入表头名称。例：名称\n键入ok结束输入\n'%z)
print('')
if b == 'ok':
print('共【%s】列'%z)
break
else:
z+=1
pass
lx = ['CHAR(255)','TEXT','VARCHAR','BIGINT','INT','FLOAT','DATE','TIMESTAMP']
print('选择数据类型')
blx = "%s %s"%(b,self.xz(lx))
data.append(blx)
self.cjb(a,data)
def cjb(self,b,li):
a = ','.join(li)
self.my("DROP TABLE IF EXISTS %s"%b)
self.my("CREATE TABLE %s (%s)"%(b,a))
def b(self):
self.youbiao.execute('SHOW TABLES')
biao = [i.get('Tables_in_db') for i in self.youbiao.fetchall()]
if len(biao) > 1:
print('请选择表单')
return self.xz(biao)
else:
return biao[0]
def pt(self,li,kg=0):
a = 1
pt = PrettyTable()
names = list(li[0].keys())
names.insert(0,'序号')
pt.field_names = names
for i in li:
i1 = list(i.values())
i1.insert(0,a)
pt.add_row(i1)
a += 1
print(pt)
if kg == 0:
return li
else:
li11 = [str(ii) for ii in range(1,len(li)+1)]
li11.insert(0,'')
li11.insert(-1,'0')
n = input('选择：')
while n not in li11:
print('没有该选项')
n = input('选择：')
if n == '0':
return li
elif n == '':
self.rk()
else:
return [li[int(n)-1]]
def head(self,b):
self.youbiao.execute("select * from %s"%b)
return [i[0] for i in self.youbiao.description]
def all(self,b):
self.youbiao.execute("select * from %s"%b)
return self.youbiao.fetchall()
def sel(self,b,key,use,x='='):
self.youbiao.execute("SELECT * FROM %s WHERE %s %s '%s'"%(b,key,x,use))
return self.youbiao.fetchall()
def addl(self,b,li):
a = ','.join(li)
self.my("ALTER TABLE %s ADD %s;"%(b,li))
def add(self,b,head,li):
a = ','.join(head)
self.my("INSERT INTO %s(%s) VALUES %s"%(b,a,li))
def up(self,b,key,use,id1,ID=ID):
self.my("UPDATE %s SET %s = '%s' WHERE %s = '%s'"%(b,key,use,ID,id1))
def dele(self,b,id1,ID=ID):
self.my("DELETE FROM %s WHERE %s = %s" %(b,ID,id1))
if __name__ == "__main__":
a=My()
a.rk()