Linux学习笔记--Python操作mysql数据库（封装基本的增删改查）

#!/usr/bin/env python
#_*_ coding:utf-8 _*_

import MySQLdb

#类、静态字段、动态字段、静态方法、动态方法、特性
class MysqldbHelper(object):

    def __init__(self, host, user, passwd,db):
        self.Host = host
        self.User = user
        self.Passwd = passwd
        self.DB = db
        self.Port = 3306
        self.Charset = 'utf8'

    # 获取数据库连接
    def getCon(self):
        try:
            conn = MySQLdb.Connect(host=self.Host, user=self.User, passwd=self.Passwd, db=self.DB, port=self.Port,
                                   charset=self.Charset)
            return conn
        except MySQLdb.Error, e:
            print "Mysqldb Error:%s" % e

    # 查询方法，使用con.cursor(MySQLdb.cursors.DictCursor),返回结果为字典
    def select(self, sql):
        try:
            con = self.getCon()
            cur = con.cursor()
            reCount = cur.execute('select * from works')
            data = cur.fetchall()
            return data
        except MySQLdb.Error, e:
            print "Mysqldb Error:%s" % e
        finally:
            cur.close()
            con.close()

    #增加方法，eg:sql='insert into pythontest values(%s,%s,%s,now()',params=(6,'C#','good book')
    def insert(self, sql, params):
        try:
            con = self.getCon()
            cur = con.cursor()
            reCount = cur.execute(sql, params)
            con.commit()
            return reCount
        except MySQLdb.Error, e:
            con.rollback()
            print "Mysqldb Error:%s" % e
        finally:
            cur.close()
            con.close()

    #修改方法，
    def update(self, sql, params):
        try:
            con = self.getCon()
            cur = con.cursor()
            reCount = cur.execute(sql, params)
            con.commit()
            return reCount
        except MySQLdb.Error, e:
            con.rollback()
            print "Mysqldb Error:%s" % e
        finally:
            cur.close()
            con.close()

    #删除方法
    def delete(self, sql, params):
        try:
            con = self.getCon()
            cur = con.cursor()
            reCount = cur.execute(sql, params)
            con.commit()
            return reCount
        except MySQLdb.Error, e:
            con.rollback()
            print "Mysqldb Error:%s" % e
        finally:
            cur.close()
            con.close()





if __name__ == "__main__":
    # host = raw_input("请输入数据库所在主机地址：")
    # user = raw_input("请输入登陆账号：")
    # passwd = raw_input("请输入登陆密码：")
    # db = raw_input("请输入数据库名称：")
    # dbname = MysqldbHelper(host, user, passwd, db)
    dbname=MysqldbHelper('192.168.20.30','root','redhat','work')

    #查
    # sql = "select * from works"
    # fc = dbname.select(sql)
    # print "数据库信息如下："
    # print fc

    #增
    # sql = "insert into works values(%s,%s,%s)"
    # params = (2, 'lisi1', 'male')
    # fc = dbname.insert(sql,params)

    #改
    sql = "update works set name = %s where id = 2"
    params = ('sb',)
    fc = dbname.update(sql, params)

    #删
    sql = "delete from works where id = %s"
    params = (1,)
    fc = dbname.delete(sql, params)