欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2022-05-30 10:23:08
...
#!/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)