import MySQLdb #在python3点几的版本中MySQLdb下载的包为mysqlclient
#输入连接数据库的信息并且将信息保存在数据库和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('{} '.format(hosts))
f.write('{} '.format(ports))
f.write('{} '.format(users))
f.write('{} '.format(passwds))
f.write('{} '.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 xiugaishujuku(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)+' '
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("***您的输入有误*** ")
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("***您的主机为{}，端口为{}，用户名为{}，密码为{}，数据库名为{}*** ".format(res2[0],res2[1],res2[2],res2[3],res2[4]))
except Exception:
print("***连接错误*** ")
#创建一个方法对数据库进行操作
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" )#host：主机名；port：数据库端口；user：用户名；passwd：密码；db：数据库名
cur = db.cursor()#创建游标对数据库进行操作
cur.execute(sql) #使用excute方法执行sql语句
result = cur.fetchall()# 获取所有记录列表
cur.close()#关闭游标
db.commit()#提交到数据库执行
db.close()#关闭数据库连接
return result
#python建立数据库表
def jianbiao_chuku(self):
mysql.link("CREATE TABLE 出库 "#出库是库名称
"(xuhao varchar(255) DEFAULT NULL,"#xuhao是一个列名，vaechar是字符属性，255是字符长度
"mingcheng varchar(255) DEFAULT NULL,"
"fahuodi varchar(255) DEFAULT NULL,"
"fahuoshang varchar(255) DEFAULT NULL,"
"jinjia varchar(255) DEFAULT NULL,"
"danjia varchar(255) DEFAULT NULL,"
"chajia varchar(255) DEFAULT NULL,"
"zhongliang varchar(255) DEFAULT NULL,"
"zongjia varchar(255) DEFAULT NULL,"
"huoli varchar(255) DEFAULT NULL,"
"weizhi varchar(255) DEFAULT NULL,"
"chukuriqi varchar(255) DEFAULT NULL,"
"dingdanbianhao varchar(255) DEFAULT NULL,"
"lishidingdanbianhao 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("***表格({})创建成功*** ".format(name))
else:
print("***表格({})创建失败*** ".format(name))
#数据库在python中的增删改查语句
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))