Python2操作MySQL数据库封装通用方法

男娘i 2023-01-11 09:13 147阅读 0赞

Python2操作MySQL数据库示例如下：

# coding=utf-8 #

import pymysql

class Database():  
def **init**(self, host, port, db, user, passwd, charset=‘utf8’):  
self.host = host  
self.port = port  
self.db = db  
self.user = user  
self.password = passwd  
self.charset = charset

def open(self):
        # 打开MySQL数据库连接
        self.conn = pymysql.connect(host=self.host, port=self.port, db=self.db,
                                    user=self.user, passwd=self.password, charset=self.charset)
        # 创建游标对象
        self.cursor = self.conn.cursor()
        print '打开MySQL数据库连接成功'
    
    def close(self):
        # 关闭mysql数据库
        self.cursor.close()
        self.conn.close()
        print '关闭MySQL数据库连接成功'
    
    def execute(self, sql, params, msg="操作成功"):
        # 增加、修改、删除命令语句
        try:
            self.open()
            # 处理逻辑数据，传入sql语句以及参数化
            self.cursor.execute(sql, params)
            # 执行事务
            self.conn.commit()
            self.close()
            # 这样可以修改输出的操作成功信息提示
            print(msg)
        except Exception as e:
            self.conn.rollback()
            print("执行SQL语句错误回滚操作", e)
    
    def all(self, sql, params=()):
        # 查询所有数据,多个值
        try:
            self.open()
            self.cursor.execute(sql, params)
            data = self.cursor.fetchall()
            self.close()
            return data
        except Exception as e:
            print("执行SQL语句错误", e)

if **name** == “**main**”:  
host = ‘182.16.125.5’  
port = 3306  
db = ‘Testdb’  
user = ‘root’  
passwd = ‘Mysql123+’  
sql = ‘SELECT \* FROM userprofile WHERE fuserId = 8880002;’  
\# Database(host, port, db, user, passwd).open()  
Database(host, port, db, user, passwd).execute(sql, any)