python连接mysql-success-20170906

1.以下代码实现的功能：查询单条数据和所有数据
2.前期准备工作：
（1）window本地安装mysql数据库---连接公司服务器，报错，百度原因，必须为root用户
（2）pymysql包，网上下的，粘贴到lib包下---这块逻辑还得理理
（3）对缩进的使用，tab和空格要合理使用---小技巧 选中要缩进的代码，按tab键

以下代码基于Python3.6.1

import pymysql
import logging
import os
class OperationDbInterface(object):

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


    # 定义单条数据操作，增删改
    def op_sql(self, params):
        try:
            self.cur.execute(params) # 执行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

if __name__ == "__main__":
     test = OperationDbInterface() # 实例化类
     result_1 = test.select_one('select*from D_COM_RG_PERN') # 查询一条数据
     print(result_1)
     result_2 = test.select_all('select*from D_COM_RG_PERN') # 查询所有数据
     print(result_2)

总结：
1.对代码的初步理解，首先导入所需包，定义类及方法，程序入口。
2.需要理解---init、self、游标、捕获异常、内置方法
3.pycharm的使用技巧
4.python连接mysql数据库
5.python3.X和2.X的区别
6.python3.X的参数使用