关于python数据库操作的学习记录

python

浏览数:443

2019-1-8

AD:资源代下载服务


operate_mysql.py

# -*- coding:utf-8 -*-
'''
定义对mysql数据库基本操作的封装
1.包括基本的单条语句操作,删除、修改、更新
2.独立查询单条、查询多条数据
3.独立添加多条数据
'''

import pymysql
import logging
import osclass OperationDbInterface(object):

    def __init__(self):
        self.conn = pymysql.connect(host='localhost',
                                    user='root',
                                    password='root',
                                    db='test',
                                    port=3306,
                                    charset='utf8',
                                    cursorclass=pymysql.cursors.DictCursor)  # 创建数据库连接
        self.cur = self.conn.cursor()  # 创建游标

    # 定义单条数据操作,增删改
    def op_sql(self, param):
        try:
            self.cur.execute(param)  # 执行sql语句
            self.conn.commit()
            return True
        except pymysql.Error as e:
            print("MySQL Error %d: %s" % (e.args[0], e.args[1]))
            logging.basicConfig(filename=os.path.join(os.getcwd(), './log.txt'),
                                level=logging.DEBUG,
                                format='%(asctime)s %(filename)s[line:%(lineno)d] %(levekname)s %(message)s')
            logger = logging.getLogger(__name__)
            logger.exception(e)
            return False

    # 查询表中单条数据
    def select_one(self, condition):
        try:
            self.cur.execute(condition)
            results = self.cur.fetchone()  # 获取一条结果
        except pymysql.Error as e:
            results = 'sql0001'  # 数据库执行失败
            print("MySQL Error %d: %s" % (e.args[0], e.args[1]))
            logging.basicConfig(filename=os.path.join(os.getcwd(), './log.txt'),
                                level=logging.DEBUG,
                                format='%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s')
            logger = logging.getLogger(__name__)
            logger.exception(e)
        finally:
            return results

    # 查询表中所有数据
    def select_all(self, condition):
        try:
            self.cur.execute(condition)
            self.cur.scroll(0,  mode='absolute')  # 光标回到初始位置
            results = self.cur.fetchall()  # 返回游标中所有结果
        except pymysql.Error as e:
            results = 'sql0001'  # 数据库执行失败
            print("MySQL Error %d: %s" % (e.args[0], e.args[1]))
            logging.basicConfig(filename=os.path.join(os.getcwd(), './log.txt'),
                                level=logging.DEBUG,
                                format='%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s')
            logger = logging.getLogger(__name__)
            logger.exception(e)
        finally:
            return results

    # 定义更多数据操作(插入数据,更新数据,删除数据)
    def operate_more(self, condition, param):
        try:
            self.cur.execute(condition, param)
            self.conn.commit()
            return True
        except pymysql.Error as e:
            results = 'sql0001'  # 数据库执行失败
            print("MySQL Error %d: %s" % (e.args[0], e.args[1]))
            logging.basicConfig(filename=os.path.join(os.getcwd(), './log.txt'),
                                level=logging.DEBUG,
                                format='%(asctime)s %(filename)s[line:%(lineno)d] %(levekname)s %(message)s')
            logger = logging.getLogger(__name__)
            logger.exception(e)
            return results

    # 数据库关闭
    def __del__(self):
        if self.cur is not None:
            self.cur.close()
        if self.conn is not None:
            self.conn.close()if __name__ == "__main__":
    test = OperationDbInterface()  # 实例化类
    result_1 = test.select_one('select*from people')  # 查询一条数据
    print(result_1)
    result_2 = test.select_all('select*from people')  # 查询所有数据
    print(result_2)
    result_3 = test.operate_more('insert into people  values (%s, %s, %s)', ('4', '付千', '兰州'))  # 插入一条数据
    print(result_3)
    # result_4 = test.operate_more("insert into people  values (%s, %s, %s)", [('4', '付千', '兰州'),
                                                                         #  ('5', '韩以', '温州')])  插入两条数据
    result_5 = test.operate_more('delete from people where NAME = %s', ('付千'))  # 删除一条数据
    print(result_5)
    result_6 = test.operate_more('update people set NAME = %s where NAME = %s', ('陈浩', '陈昊'))  # 修改一条数据
    print(result_6)