import pymysql


class MysqlHelper(object):
    conn = None

    def __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 = port

    def connect(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()

    def close(self):
        self.cursor.close()
        self.conn.close()

    def get_one(self, sql, params=()):
        result = None
        try:
            self.connect()
            self.cursor.execute(sql, params)
            result = self.cursor.fetchone()
            self.close()
        except Exception as e:
            print(e)
        return result

    def get_all(self, sql, params=()):
        list_data = ()
        try:
            self.connect()
            self.cursor.execute(sql, params)
            list_data = self.cursor.fetchall()
            self.close()
        except Exception as e:
            print(e)
        return list_data

    def 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 = 0
        try:
            self.connect()
            count = self.cursor.execute(sql, params)
            self.conn.commit()
            self.close()
        except Exception as e:
            print(e)
        return count
    def du(self,aa):#打开图片读入内存给变量
        fp = open(aa, ‘rb‘)
        try:
            img = fp.read()
        except:
            print("图片打开出错")
        fp.close()
        return img
    def 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)