import MySQLdb #在python3点几的版本中MySQLdb下载的包为mysqlclient
class mysql:
#输入连接数据库的信息并且将信息保存在数据库和txt文件中，这样就只需要输入一次数据库连接信息
def jianshujuku(self):
datas=[]
print("***请先连接数据库***")
hosts=str(input("请输入数据库连接主机："))
ports=int(input("请输入数据库连接端口："))
users=str(input("请输入数据库用户名："))
passwds=str(input("请输入数据库密码："))
dbs1 = input("请输入数据库名称：")
dbs=str(dbs1)
datas.append(hosts)
datas.append(ports)
datas.append(users)
datas.append(passwds)
datas.append(dbs)
db=MySQLdb.connect(host=hosts,port =ports ,user=users,passwd=passwds,charset="utf8" )
cur = db.cursor()
sql=("create database if not exists {}".format(dbs1))
cur.execute(sql)
db = MySQLdb.connect(host=hosts, port=ports, user=users, passwd=passwds,db=dbs1, charset="utf8")
cur = db.cursor()
sql1 = ("CREATE TABLE 数据库连接参数 "
"(xuhao varchar(255) DEFAULT NULL,"
"hosts varchar(255) DEFAULT NULL,"
"ports varchar(255) DEFAULT NULL,"
"users varchar(255) DEFAULT NULL,"
"passwds varchar(255) DEFAULT NULL,"
"dbs varchar(255) DEFAULT NULL)"
" ENGINE=InnoDB DEFAULT CHARSET=utf8;")
sql2 = ("insert into 数据库连接参数 (xuhao,hosts,ports,users,passwds,dbs)values({},‘{}‘,{},‘{}‘,‘{}‘,‘{}‘)".format(1,hosts, ports ,users,passwds,dbs1))
cur.execute(sql1)
cur.execute(sql2)
cur.close()
db.commit()
db.close()
with open(‘data.txt‘, ‘w‘, encoding=‘utf8‘) as f:
f.write(‘{}\n‘.format(hosts))
f.write(‘{}\n‘.format(ports))
f.write(‘{}\n‘.format(users))
f.write(‘{}\n‘.format(passwds))
f.write(‘{}\n‘.format(dbs))
f.flush()
f.close()
#程序启动时判断是否有连接数据
def indexs(self):
try:
with open(‘data.txt‘, ‘r‘, encoding=‘utf8‘) as f:
f.seek(0)
ress = []
for i in range(5):
res1 = f.readline()
ress.append(res1)
return 2000
except Exception:
return 1000
#更新数据库连接信息
def updata_mysql(self):
try:
xiugaiming=input("请输入想修改的地方（可选择主机、端口、用户名、密码、数据库名）：")
value=input("请输入修改的值：")
with open(‘data.txt‘, ‘r‘, encoding=‘utf8‘) as f:
f.seek(0)
ress = []
for i in range(5):
res1 = f.readline()
ress.append(res1)
datas=ress
values=‘{}‘.format(value)+‘\n‘
if xiugaiming==‘主机‘:
datas[0]=values
elif xiugaiming==‘端口‘:
datas[1]=values
elif xiugaiming == ‘用户名‘:
datas[2] = values
elif xiugaiming==‘密码‘:
datas[3]=values
elif xiugaiming==‘数据库名‘:
datas[4]=values
else:
print("***您的输入有误***\n")
with open(‘data.txt‘, ‘w‘, encoding=‘utf8‘) as f:
f.writelines(datas)
f.flush()
with open(‘data.txt‘, ‘r‘, encoding=‘utf8‘) as f:
f.seek(0)
res2 = []
for i in range(5):
res1 = f.readline()
res = res1[0:len(res1) - 1]
res2.append(res)
f.close()
mysql.link("update 数据库连接参数 set hosts=‘{}‘,ports={},users=‘{}‘,passwds=‘{}‘,dbs=‘{}‘where xuhao=1".format(res2[0],res2[1] ,res2[2],res2[3],res2[4]))
print("***您的主机为{}，端口为{}，用户名为{}，密码为{}，数据库名为{}***\n".format(res2[0],res2[1],res2[2],res2[3],res2[4]))
except Exception:
print("***连接错误***\n")
#程序进行增删改查等操作时调用这个方法连接数据库进行操作，用户只需要将数据库语句参数传入
def link(sql):
with open(‘data.txt‘, ‘r‘, encoding=‘utf8‘) as f:
f.seek(0)
ress = []
for i in range(5):
res1 = f.readline()
res = res1[0:len(res1) - 1]
ress.append(res)
f.close()
datas=ress
db=MySQLdb.connect(host=datas[0],port =int(datas[1]) ,user=datas[2],passwd=datas[3],db =datas[4],charset="utf8" )
cur = db.cursor()
cur.execute(sql) # 获取所有记录列表
result = cur.fetchall()
cur.close()
db.commit()
db.close()
return result#建数据库表
def create_table(self):
mysql.link("CREATE TABLE 出库 "
"(xuhao varchar(255) DEFAULT NULL,"
"mingcheng varchar(255) DEFAULT NULL,"
"fahuodi varchar(255) DEFAULT NULL,"
)"
" ENGINE=InnoDB DEFAULT CHARSET=utf8;")
#创建一个方法使用户可以手动输入表格信息程序自动建立表格
def jianbiao_jiandan(self):
name = input("请输入表名：")
data = []
datas = []
leibiaoshu = int(input("请输入列表名的数量:"))
n = 0
for i in range(0, leibiaoshu):
n += 1
leibiaoming = input("请输入第{}个列表名：".format(n))
data.append(leibiaoming)
for x in data:
data1 = "{} varchar(255) DEFAULT NULL".format(x)
datas.append(data1)
a="{},".format(datas[0])
for y in range(1, leibiaoshu-1):
a=a+ ‘{},‘.format(datas[y])
a=‘{}{}‘.format(a,datas[leibiaoshu-1])
try:
result = mysql.link("select * from {}".format(name))
if len(result) > 0:
print("***该表格已存在，请重新创建表格***")
except Exception:
mysql.link("CREATE TABLE {} ({}) ENGINE=InnoDB DEFAULT CHARSET=utf8;".format(name,a))
result=mysql.link("select * from {}".format(name))
if result==():
print("***表格({})创建成功***\n".format(name))
else:
print("***表格({})创建失败***\n".format(name))
class outbound:
def add_outbound(xuhao,mingcheng,fahuodi):#增加
mysql.link("insert into 出库 (xuhao,mingcheng,fahuodi)values({},‘{}‘,‘{}‘)".format(xuhao,mingcheng,fahuodi))
def del_outbound(xiugaineirong,value):#删除
mysql.link("delete from 出库 where {} = ‘{}‘".format(xiugaineirong,value))
def cha_outbound(beixiugaiduixiangming,value,xuhao):#修改
mysql.link("update 出库 set {}= ‘{}‘where xuhao ={}".format(beixiugaiduixiangming,value,xuhao))
def che_outbound(biechazhaoduixiang, value):#查找
result = mysql.link("SELECT * FROM 出库 where {}=‘{}‘".format(biechazhaoduixiang,value))