python操作数据库的增删改查

import mysql.connector
class mysql_conn:
  def __init__(self,table='') -> None:
    self.pre='**'
    self.db="**"
    self.conn = mysql.connector.connect(
    host="**5",
    user="**",
    passwd="**",
    database=self.db
    )
    # 创建游标对象
    self.cur = self.conn.cursor(dictionary=True)
    self.table_con=' '+self.pre+table
    self.field_con='*'
    self.limit_con=self.where_con=self.order_con=''
  # 切换数据表
  def table(self,con):
    self.table_con=' '+self.pre+con
    return self
  def columns(self):
    sql='show COLUMNS FROM '+self.table_con
    query=self.query_fetchall(sql)
    fields=[]
    for i in query:
      tmp={}
      type=i['Type']
      if type.find('int')!=-1 or type.find('double')!=-1 or type.find('float')!=-1 or type.find('decimal')!=-1:
        tmp['type']='int'
      else:
        tmp['type']='str'
      tmp['name']=i['Field']
      fields.append(tmp)
    return fields
  # 字段
  def field(self,con):
    self.field_con=str(con)
    return self
  # 参数
  def where(self,con):
    self.where_con=' where '+str(con)  if con else self.where_con
    return self
  # 排序
  def order(self,con):
    self.order_con=' '+con if con else self.order_con
    return self
  # 限制个数
  def limit(self,con):
    self.limit_con=' limit '+str(con) if con else self.limit_con
    return self
  # 增加
  def add(self,con):
    sql,val=self.sql_zuhe_add(con)
    sql="insert into "+self.table_con+sql
    return self.add_one(sql,val)
  # 修改
  def update(self,con):
    sql0,val=self.sql_zuhe_update(con)
    sql="update "+self.table_con+" set "+sql0+' '+self.where_con
    return self.update_one(sql,val)
  # 查找
  def find(self,con=''):
    self.where_con=' where id='+str(con) if con else self.where_con
    sql="select "+self.field_con+" from" + self.table_con+self.where_con+self.order_con+' limit 1'
    # return sql
    return self.query_one(sql)
  # 统计个数
  def count(self):
    sql="select count("+self.field_con+") from" + self.table_con+self.where_con+self.order_con
    count=self.query_fetchall(sql)
    return count
  # 查找多个  
  def select(self):
    sql="select "+self.field_con+" from" + self.table_con+self.where_con+self.order_con+self.limit_con
    return self.query_fetchall(sql)
  # 删除
  def delete(self):
    sql="delete from" + self.table_con+self.where_con+self.order_con+self.limit_con
    query=self.query_execute(sql)
    return query
  # add update用的sql
  def sql_zuhe_add(self,con):
    columns=self.columns()
    if not con.get('id'):
      del columns[0]
    index=''
    vals=''
    tmp_list=[]
    count=0
    for key in columns:
      if con.get(key['name']):
        va=con.get(key['name'])
      else:
        if key['type']=='int':
          va=0
        else:
          va=''
      tmp_list.append(va)
      if(count==0):
        vals='%s'
        index=key['name']
      else:
        index+=','+key['name']
        vals+=','+'%s'
      count=count+1
    sql="("+index+")value("+vals+")"
    tmp_tuple=tuple(tmp_list)
    return sql,tmp_tuple
  
  def sql_zuhe_update(self,con):
    columns=self.columns()
    if not con.get('id'):
      del columns[0]
    index=''
    tmp_list=[]
    count=0
    for key in columns:
      if key['name'] not in con:
        continue
      if con.get(key['name']):
        va=con.get(key['name'])
      else:
        if key['type']=='int':
          va=0
        else:
          va=''
      # va =str(va)
      tmp_list.append(va)
      if(count==0):
        index=key['name']+'='+'%s'
      else:
        index+=','+key['name']+'='+'%s'
      count=count+1
    sql=index
    tmp_tuple=tuple(tmp_list)
    return sql,tmp_tuple
  def query(self,sql,params=''):
    self.cur.execute(sql,params)
  def add_one(self,sql,params=''):
    self.query(sql,params)
    self.conn.commit()
    lastrowid=self.cur.lastrowid
    return lastrowid
  def update_one(self,sql,params=''):
    self.query(sql,params)
    return self.conn.commit()
  def query_fetchall(self,sql,params=''):
    self.query(sql,params)
    all=self.cur.fetchall()
    return all
  def query_one(self,sql,params=''):
    self.query(sql,params)
    one=self.cur.fetchone()
    return one