importpymysqlclassMysqlHelper(object):
conn=Nonedef __init__(self, host, username, password, db, charset='utf8', port=3306):
self.host=host
self.username=username
self.password=password
self.db=db
self.charset=charset
self.port=portdefconnect(self):
self.conn= pymysql.connect(host=self.host, port=self.port, user=self.username, password=self.password, db=self.db,
charset=self.charset)
self.cursor=self.conn.cursor()defclose(self):
self.cursor.close()
self.conn.close()def get_one(self, sql, params=()):
result=Nonetry:
self.connect()
self.cursor.execute(sql, params)
result=self.cursor.fetchone()
self.close()exceptException as e:print(e)returnresultdef get_all(self, sql, params=()):
list_data=()try:
self.connect()
self.cursor.execute(sql, params)
list_data=self.cursor.fetchall()
self.close()exceptException as e:print(e)returnlist_datadef insert(self, sql, params=()):return self.__edit(sql, params)def update(self, sql, params=()):return self.__edit(sql, params)def delete(self, sql, params=()):return self.__edit(sql, params)def __edit(self, sql, params):
count=0try:
self.connect()
count=self.cursor.execute(sql, params)
self.conn.commit()
self.close()exceptException as e:print(e)returncountdef du(self,aa):#打开图片读入内存给变量
fp = open(aa, 'rb')try:
img=fp.read()except:print("图片打开出错")
fp.close()returnimgdef bu(self,aa,bname,id):#从数据表bname中读取对应ID图片并保存为文件名为aa
sql=f"select tu from {bname} where id={id}"fp= open(aa, 'wb')try:
self.connect()
self.cursor.execute(sql)
self.conn.commit()
fp.write(self.cursor.fetchone()[0])
self.close()except:print("图片读写出错")#aa=MysqlHelper("db4free.net","myabcd","******","数据库名称")
aa=MysqlHelper("localhost","root","**********","testa")
sql="select sjh from sjh limit 10"sql1="select sjh from sjh where sjh='13000290804'"sql2="delete from sjh where sjh='13000290804'"sql3="select table_name from information_schema.tables where table_schema='mys'"sql4="SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'mys'"sql5="select table_name,column_name,column_comment from information_schema.columns where table_schema ='mys' and table_name ='sjh'"
#显示指定数据库指定表的字段名称
sql6="desc sjh"images=aa.du("222.jpg")
sql7=f"insert into xsxx6 (xm,tu) values ('张三',%s);"sql8=f"update xsxx6 set xm='李四',tu =%s where id=1;"args=(images)#显示指定的详细结构(字段名称 类型 长度)#aa.insert(sql8,args)#利用插入将图片存入表中#aa.update(sql)#利用更新的方法将图片存入表中
aa.bu('1234.jpg','xsxx',5)