【mysql】关于python建立mysql相关操作

1.安装

用pip安装指令

pip install pymysql

查看安装成功

#cmd
pip show mysql
#cmd  找list中有该软件
pip list
#python 中不报错
import pymysql

2.操作流程

3.封装代码

#encoding=utf8
#author: blanset

from pymysql import *
import time
class MysqlHelper(object):

    conn_params1 = {'host':'localhost','port':3306,'user':'root','password':'2401','db':'test','charset':'utf8'}
    logfilename = 'log.txt'
    local_time = time.strftime("%Y-%m-%d %H:%M:%S",time.localtime(time.time()))

    def __init__(self,conn_params):
        self.__host = conn_params['host']
        self.__port = conn_params['port']
        self.__user = conn_params['user']
        self.__password = conn_params['password']
        self.__db = conn_params['db']
        self.__charset = conn_params['charset']

    def __connect(self):
        self.__conn = connect(host=self.__host,port=self.__port,user=self.__user,password=self.__password,db=self.__db,charset=self.__charset)
        self.__cursor = self.__conn.cursor()

    def __close(self):
        self.__cursor.close()
        self.__conn.close()

    def __write_log(self,optype,status,sql):
        with open(self.logfilename,'a') as file:
            s = f'user:{self.__user}|optype:{optype}|opstatus:{status}|optime:{self.local_time}|sql:{sql}'
            file.write(s+'\n')

    def __write_log_p(self,optype,status,sql,params):
        with open(self.logfilename,'a') as file:
            s = f'user:{self.__user}|optype:{optype}|opstatus:{status}|optime:{self.local_time}|sql:{sql}|params:{params}'
            file.write(s+'\n')

    def __write_err_log(self,optype,status,sql,err):
        with open(self.logfilename,'a') as file:
            s = f'user:{self.__user}|optype:{optype}|opstatus:{status}|optime:{self.local_time}|sql:{sql}|err:{err}'
            file.write(s+'\n')

    def __write_err_log_p(self,optype,status,sql,params,err):
        with open(self.logfilename,'a') as file:
            s = f'user:{self.__user}|optype:{optype}|opstatus:{status}|optime:{self.local_time}|sql:{sql}|params:{params}|err:{err}'
            file.write(s+'\n')

    def __edit(self,sql,params,type):
        count = 0
        try:
            self.__connect()
            count = self.__cursor.execute(sql,params)
            self.__conn.commit()
            self.__write_log(type,1,sql)
        except Exception as e:
            print(e)
            self.__conn.rollback()
            self.__write_err_log(type,0,sql,e)
        finally:
            self.__close()
        return count

    def select_one(self,sql,params):
        result = None
        try:
            self.__connect()
            self.__cursor.execute(sql,params)
            result = self.__cursor.fetchone()
            self.__conn.commit()
            self.__write_log('selectone',1,sql)
        except Exception as e:
            print(e)
            self.__conn.rollback()
            self.__write_err_log('selectone',0,sql,e)
        finally:
            self.__close()
        return result

    def select_all(self,sql,params):
        result = ()
        try:
            self.__connect()
            self.__cursor.execute(sql,params)
            result = self.__cursor.fetchall()
            self.__conn.commit()
            self.__writelog('selectall',1,sql)
        except Exception as e:
            print(e)
            self.__conn.rollback()
            self.__write_err_log('selectall',0,sql,e)
        finally:
            self.__close()
        return result

    def insert(self,sql,params):
        return self.__edit(sql,params,'insert')

    def update(self,sql,params):
        return self.__edit(sql,params,'update')

    def delete(self,sql,params):
        return self.__edit(sql,params,'delete')

    def insertmany(self,sql,params):
        count = 0
        try:
            self.__connect()
            count = self.__cursor.executemany(sql,params)
            print(count)
            self.__conn.commit()
            self.__write_log('insertmany',1,sql)
        except Exception as e:
            print('exception:',e)
            self.__conn.rollback()
            self.__write_err_log('insertmany',0,sql,e)
        finally:
            self.__close()
        return count

    def update_many(self,sql,params):
        count = 0
        try:
            self.__connect()
            count = self.__cursor.executemany(sql,params)
            self.__conn.commit()
            self.__write_log('updatemany',1,sql)
        except Exception as e:
            print(e)
            self.__conn.rollback()
            self.__write_err_log('updatemany',0,sql,e)
        finally:
            self.__close()
        return count

    def delete_many(self,sql,params):
        count = 0
        try:
            self.__connect()
            count = self.__cursor.executemany(sql,params)
            self.__conn.commit()
            self.__write_log('deletemany',1,sql)
        except Exception as e:
            print(e)
            self.__conn.rollback()
            self.__write_err_log('deletemany',0,sql,e)
        finally:
            self.__close()
        return count