python操作mysql

利用pymysql操作mysql

import pymysql


class Get_DB(object):
    def __init__(self):
        self.host_env = '127.0.0.1'
        self.user = "root"
        self.passwd = "root"
        self.port = 3306

    def get_connect(self):
        try:
            conn = pymysql.connect(self.host_env, user=self.user, password=self.passwd, port=self.port, charset='utf8')
            return conn
        except Exception as e:
            raise e

    # 查询数据库，
    def get_query_result(self, sql):
        """
        将结果放到一个空List中
        [(1, zl, '2017')(2,zll,'2017')]
        :param sql:
        :return:
        """
        ss = []
        try:
            conn = self.get_connect()
            cursor = conn.cursor()
            cursor.execute(sql)
            rs = cursor.fetchall()
            cursor.close()
            self.get_connect().close()
        except Exception as e:
            raise e

        if rs is not None:
            for i in range(rs.__len__()):
                ss.append(rs[i])
            return ss
        else:
            raise Exception("null")

    # 查询数据库，获取字典格式
    def get_query_dict_result(self, sql):
        """
        获取字典格式数据
        [{"id":"1"}, {"id":"2"}, {"id":"3"}]
        :param sql:
        :return:
        """
        try:
            conn = self.get_connect()
            cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
            cursor.execute(sql)
            rs = cursor.fetchall()
            cursor.close()
            self.get_connect().close()
            return rs
        except Exception as e:
            raise e


if __name__ == "__main__":
    get_db = Get_DB()
    result = get_db.get_query_result("select * from test.user where id='1' ")
    print(result)