python操作mysql的简单类（pymysql模块）

 1、pymysql实现步骤

• 建立连接通道connect
• 创建游标cursor，操作设置为字典类型，返回结果为字典格式！不写默认是元组格式！
• 向数据库发送操作指令execute

2、sql防注入实现

#构建sql以下两种均可
sql = "select * from userinfo where username=%s and password=%s"
# sql = "select * from userinfo where username=%(u)s and password=%(p)s"
#传入数据
cursor.execute(sql,user,pwd)                 #直接传值
# cursor.execute(sql,[user,pwd])             #列表形式
# cursor.execute(sql,{'u':user,'p':pwd})     #字典格式

 3、简单封装类

import pymysql

class DbHandle:
    def __init__(self):
        self._dbhost = "localhost"
        self._dbuser = "root"
        self._dbpassword = "123456"
        self._dbname = "shop"
        self._dbcharset = 'utf8'
        self._dbport = int(3306)
        self._conn = self.connectMySQL()

        if (self._conn):
            self._cursor = self._conn.cursor(cursor=pymysql.cursors.DictCursor)

    def connectMySQL(self):
        try:
            conn = pymysql.connect(host=self._dbhost,
                                   user=self._dbuser,
                                   passwd=self._dbpassword,
                                   db=self._dbname,
                                   port=self._dbport,
                                   cursorclass=pymysql.cursors.DictCursor,
                                   charset=self._dbcharset,
                                   )
        except Exception:
            print("connect database failed")
            conn = False
        return conn

    def close(self):
        if (self._conn):
            try:
                if (type(self._cursor) == 'object'):
                    self._cursor.close()
                if (type(self._conn) == 'object'):
                    self._conn.close()
            except Exception:
                print("close database exception")

    def select_all(self, sql):
        res = ''
        if (self._conn):
            try:
                self._cursor.execute(sql)
                res = self._cursor.fetchall()
            except Exception:
                res = False
                print("query database exception")
            self.close()
        return res

    def select_one(self, sql):
        res = ''
        if (self._conn):
            try:
                self._cursor.execute(sql)
                res = self._cursor.fetchone()
            except Exception:
                res = False
                print("query database exception")
            self.close()
        return res

    def update(self, sql):
        flag = False
        if (self._conn):
            try:
                self._cursor.execute(sql)
                self._conn.commit()
                flag = True
            except Exception:
                self._conn.rollback()
                flag = False
                print("update database exception")
            self.close()
        return flag