python数据库操作以及简单的封装

封装成类方法使用

class cxt_connetMyql(object):
    def __init__(self, host, user, password, db, port):
        self.host = host
        self.user = user
        self.password = password
        self.db = db
        self.port = port
        self.__getCursor()

    def __getCursor(self):
        try:
            self.conn = pymysql.connect(self.host, self.user, self.password, self.db, self.port, charset='utf8',  use_unicode=True)
        except Exception as e:
            print('数据库链接失败:', e)
        finally:
            self.cursor = self.conn.cursor()
            # 设置游标的编码
            self.cursor.execute('SET NAMES utf8;')
            self.cursor.execute('SET CHARACTER SET utf8;')
            self.cursor.execute('SET character_set_connection=utf8;')

    def executeSelectSql(self, sql):
        try:
            self.cursor.execute(sql)
        except Exception as e:
            print('执行查询SQL失败,错误信息:', e)
        finally:
            return self.cursor.fetchall()

    def executeOtherSql(self, sql):
        try:
            self.cursor.execute(sql)
        except Exception as e:
            print('执行SQL语句失败,错误信息:', e)
        finally:
            self.conn.commit()

    def __del__(self):

        self.cursor.close()
        self.conn.close()

类方法调用

selectUserInfoSql='xxx'
updateCreditSql='xxx'
updateUserStatusSql='xxx'

con = cxt_connetMyql('xxx', 'xxx', 'xxx', 'xxx', xxx)
result1 = con.executeSelectSql(selectUserInfoSql)
con.executeOtherSql(updateUserStatusSql)
result3 = con.executeSelectSql(selectUserInfoSql)
print(result1, result3)

python操作mysql数据库

import pymysql
# 连接
try:
    conn = pymysql.connect(host='xxx', user='xxx', password='xxx', db='xxx', port=xxx)
except Exception as e:
    print('数据库连接出错,报错信息:%s',e)
finally:
    # 游标
    cursor = conn.cursor()

# SQL语句
# 查询用户信息SQL
selectUserInfoSql = "xxx"
# 修改用户信用额度SQL
updateCreditSql = 'xxx'
# 修改用户认证状态SQL
updateUserStatusSql = 'xxx'
# 执行SQL
try:
    cursor.execute(selectUserInfoSql)
    cursor.execute(updateCreditSql)
    cursor.execute(selectUserInfoSql)
except Exception as e:
    print('执行sql语句报错,报错信息:%s',e)
finally:
    # 获取查询的所有记录
    result = cursor.fetchall()
    print(result)

# 关闭游标关闭连接
cursor.close()
conn.close()

import pymysql
from pymysql.cursors import DictCursor
# coon = pymysql.connect(host='211.149.218.16',port=3306,user='jxz',passwd='123456',db='jxz',charset='utf8')
# cur = coon.cursor(DictCursor)#建立游标,仓库管理员,指定游标类型，返回字典
# sql='select * from stu limit 2,9;'
# # insert_sql = 'insert into stu VALUE (89,"nhy");'
# # cur.execute(insert_sql)
# cur.execute(sql)#执行sql语句
# for c in cur:#直接循环游标，每次循环的时候就是每一列的数据
#     print(c)
# # res = cur.fetchall()#获取sql语句执行的结果,一次性全部获取
# # coon.commit()#提交
# # print(res[0][1])
# cur.close()
# coon.close()
def op_mysql(host,user,passwd,db,sql,charset='utf8',port=3306):
    import pymysql
    from pymysql.cursors import DictCursor
    coon = pymysql.connect(host=host,user=user,passwd=passwd,db=db,charset=charset,port=port)
    cur = coon.cursor(DictCursor)#指订返回数据的类型是字典
    cur.execute(sql)
    if sql.strip().startswith('select'):
        res = cur.fetchall()
    else:
        coon.commit()
        res = 'ok'
    cur.close()
    coon.close()
    print(res)
    return res