上篇看了SQLite 数据库的封装，这篇看下MySQL的封装.http://www.cndba.cn/dave/article/2171http://www.cndba.cn/dave/article/2171

Python 封装 SQLite 数据库操作（使用类封装基本的增删改查）

http://www.cndba.cn/dave/article/2154

# -*- coding: utf-8 -*-
import pymysql

class MysqlClass:

    SHOW_SQL = False
    def __init__(self, host='127.0.0.1', port=3306, user='root', password='http://www.cndba.cn/dave', db='cndba', charset='utf8'):
        self.host = host
        self.port = port
        self.user = user
        self.password = password
        self.db = db
        self.charset = charset

    # 建立连接
    def get_con(self):
        try:  
            conn = pymysql.connect(host=self.host, user=self.user, passwd=self.password, db=self.db, port=self.port, charset=self.charset)
            return conn  
        except pymysql.Error, e:
            print "pymysql Error:%s" % e

    # 查询方法，使用con.cursor(MySQLdb.cursors.DictCursor),返回结果为字典
    def select_all(self, sql):
        try:  
            con = self.get_con()
            print con  
            cur = con.cursor(pymysql.cursors.DictCursor)
            count = cur.execute(sql)
            fc = cur.fetchall()
            return fc  
        except pymysql.Error, e:
            print "pymysql Error:%s" % e
        finally:  
            cur.close()  
            con.close()

    def select_by_where(self, sql, data):
        try:
            con = self.get_con()
            # print con
            d = (data,)
            cur = con.cursor(pymysql.cursors.DictCursor)
            count = cur.execute(sql, d)
            fc = cur.fetchall()
            # if len(fc) > 0:
            #     for e in range(len(fc)):
            #         print(fc[e])
            return fc
        except pymysql.Error, e:
            print "pymysql Error:%s" % e
        finally:
            cur.close()
            con.close()

    # 带参数的更新方法,eg:sql='insert into pythontest values(%s,%s,%s,now()',params=(6,'C#','good book')
    def dml_by_where(self, sql, params):
        try:  
            con = self.get_con()
            cur = con.cursor()

            for d in params:
                if self.SHOW_SQL:
                    print('执行sql:[{}],参数:[{}]'.format(sql, d))
                cur.execute(sql, d)
            con.commit()

        except pymysql.Error, e:
            con.rollback()  
            print "pymysql Error:%s" % e
        finally:  
            cur.close()  
            con.close()

    # 不带参数的更新方法
    def dml_nowhere(self, sql):
        try:  
            con = self.get_con()
            cur = con.cursor()
            count = cur.execute(sql)
            con.commit()  
            return count  
        except pymysql.Error,e:
            con.rollback()  
            print "pymysql Error:%s" % e
        finally:  
            cur.close()  
            con.close()  

# 开始测试函数


def select_all():
    sql = "select * from dave"
    fc = db.select_all(sql)
    for row in fc:
        print row['id'], row['url']

def select_by_where():
    sql = "select * from dave where id=%s"
    data = [1]
    fc = db.select_by_where(sql, data)

    for row in fc:
        print row['id'], row['url']

def ins_by_param():
    sql = "insert into dave values(%s,%s)"
    data = [(1, 'http://www.cndba.cn'), (2, 'http://www.cndba.cn/dave')]
    db.dml_by_where(sql, data)


def del_by_where():
    sql = "delete from dave where id=%s"
    data = [1, 2]
    db.dml_by_where(sql, data)


def update_by_where():
    sql = "update dave set url=%s where id=%s"
    data = [('http://www.zhixintech.cc', 2)]
    db.dml_by_where(sql, data)


def del_nowhere():
    sql = "delete from dave"
    print db.dml_nowhere(sql)


if __name__ == "__main__":  
    db = MysqlClass( '192.168.1.4', port=3306, user='root', password='http://www.cndba.cn/dave', db='cndba', charset='utf8')

    # ins_by_param()
    # del_by_where()
    # update_by_where()
    del_nowhere()
    select_all()
    # select_by_where()

版权声明：本文为博主原创文章，未经博主允许不得转载。