用python开发mysql小工具_python 封装Mysql工具类

工作过程中，经常需要用到与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插入相同，请自己尝试