工作过程中，经常需要用到与MySQL交互，初步封装Mysql类，可直接进行增删查改操作

0.编写MySQLconn.py

# -*- coding:utf-8 -*-
import os
import sys
import MySQLdb

class MySQLConnect(object):
    """ mysqldb 操作类"""
    def __init__(self, params):
        """ 数据库初始化 """
        self.host = str(params.get("host", ""))
        self.username = str(params.get("username", ""))
        self.password = str(params.get("password", ""))
        self.dbname = str(params.get("dbname", ""))
        self.port = str(params.get("port", ""))

    def connect(self):
        """ 链接数据库 """
        try:
            self.conn = MySQLdb.connect(host=self.host, user=self.username, passwd=self.password, db=self.dbname, port=int(self.port), charset='utf8', connect_timeout=1000)
        except MySQLdb.Error as e:
            print "conn mysql error: %s" % e
        self.cursor = self.conn.cursor() # 使用cursor方法获取操作游标

    def close(self):
        """ 关闭数据库 """
        self.cursor.close()
        self.conn.close()

    def select(self, cmd):
        """ 用于查询返回所有结果 """
        results = []
        try:
            self.connect()
            self.cursor.execute(cmd)
            results = self.cursor.fetchall()
        except MySQLdb.Error as e:
            print "mysql selct error: %s" % e
        return results

    def select_one(self, cmd):
        """ 查询一条结果 """
        try:
            self.connect()
            self.cursor.execute(cmd)
            result = self.cursor.fetchone()
        except MySQLdb.Error as e:
            print "mysql select one error: %s" % e
        return result

    def inner_execute(self, cmd):
        """ 进行修改，插入，更新基本操作 """
        try:
            self.connect()
            self.cursor.execute(cmd)
            self.commit()
        except MySQLdb.Error as e:
            print "mysql insert error: %s" % e

    def insert(self, cmd):
        """ 执行插入mysql 操作 """
        self.inner_execute(cmd)

    def update(self, cmd):
        """ 执行更新mysql操作 """
        self.inner_execute(cmd)

    def delete(self, cmd):
        """ 执行删除mysql操作 """
        self.inner_execute(cmd)

    def commit(self):
        """ 事务提交操作 """
        self.conn.commit()

    def rollback(self):
        """ 事务回滚操作 """
        self.conn.rollback()

def test():
    """ 测试case """
    pass # 详见测试例子
if __name__ == '__main__':
    test()

1. 测试

(1) 测试数据表non_weekday_info 用来存放非工作日期

CREATE TABLE `non_weekday_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `year` varchar(256) DEFAULT NULL,
  `month` varchar(256) DEFAULT NULL,
  `holiday` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=469 DEFAULT CHARSET=utf8

(2) 查询fetchall操作

def test():
    """ 测试case """
    params = {"host": "xxxxxx", "username": "xxx", "password": "xxx", "dbname": "xxx", "port": "xxxx"}
    myconn = MySQLConnect(params)
    select_sql = "select * from non_weekday_info where year='2019' and month='12';"
    query_results = myconn.select(select_sql)
    print "query 2019-12: ", query_results

• 控制台输出结果

query 2019-12:  ((455L, u'2019', u'12', datetime.date(2019, 12, 1)), (456L, u'2019', u'12', datetime.date(2019, 12, 7)), (457L, u'2019', u'12', datetime.date(2019, 12, 8)), (458L, u'2019', u'12', datetime.date(2019, 12, 14)), (459L, u'2019', u'12', datetime.date(2019, 12, 15)), (460L, u'2019', u'12', datetime.date(2019, 12, 21)), (461L, u'2019', u'12', datetime.date(2019, 12, 22)), (462L, u'2019', u'12', datetime.date(2019, 12, 28)), (463L, u'2019', u'12', datetime.date(2019, 12, 29)))

• 实际查询数据库效果：

  
  

(3) fetchone vs fetchall 区别

def test():
    """ 测试case """
    params = {"host": "xxxxxx", "username": "xxx", "password": "xxx", "dbname": "xxx", "port": "xxxx"}
    myconn = MySQLConnect(params)
    # 测试select_one
    select_sql = "select * from non_weekday_info where year='2019';"
    query_results = myconn.select_one(select_sql)
    print "[select one] 2019: ", query_results
   # 测试后select_all
    select_sql = "select * from non_weekday_info where year='2019';"
    query_all_results = myconn.select(select_sql)
    print "[select all] 2019: ", query_all_results

测试结果

注意： 2019年12月份节假日的日期非单一，用select_one(相当于使用fetchone) 仅能查询2019-1-1，而使用select(相当于fetchall) 可以查到2019年全年节假日日期。这是因为fetchone仅能获取单条数据，而fetchall 可以获取多条数据

(4) insert插入操作

def test():
    """ 测试case """
    params = {"host": "xxxxxx", "username": "xxx", "password": "xxx", "dbname": "xxx", "port": "xxxx"}
    myconn = MySQLConnect(params)
    print myconn, type(myconn)
    select_sql = 'select * from non_weekday_info order by id desc limit 10;'
    query_results = myconn.select(select_sql)
    print "before insert 2020-01-01: ", query_results

    insert_sql = "insert non_weekday_info(year, month, holiday) values('2020', '01', '2020-01-01');"
    myconn.insert(insert_sql)

    select_sql = 'select * from non_weekday_info order by id desc limit 10;'
    query_results = myconn.select(select_sql)
    print "after insert 2020-01-01: ", query_results
if __name__ == '__main__':
    test()

• 控制台输出结果：

  
  

• 实际查询数据库效果：

  
  
  

  由测试结果可知，本次插入2020-01-01生效

同理，delete删除和update修改与insert插入相同，请自己尝试