Python连接MySQL的一些不同姿势

业务需要,计划写个自动化部署集群的脚本,一点点记录吧

python连接数据库的方式有很多,大概可以分为使用连接池的,和不使用连接池的,说到连接池,血压就上来了,druid有一些大坑,关于druid,后面再讲吧,都是泪

一、不使用连接池

 

这个写的蛮好的:传送门

 

[root@localhost ~]# pip install mysql

 

#!/usr/bin/env python
# -*- coding:utf-8 -*-

import MySQLdb

conninfo = {
    'host':"",
    'user':"",
    'passwd':"",
    'db':"",
    'port':
}

db = MySQLdb.connect(**conninfo)
# 打开数据库连接  url,username,password,database
# 使用cursor()方法获取操作游标
cursor = db.cursor()
# 使用execute方法执行SQL语句
cursor.execute("SELECT VERSION()")
# 使用 fetchone() 方法获取一条数据
data = cursor.fetchone()
print "Database version : %s " % data
# 关闭数据库连接
db.close()

大概就是这样子了,交流切磋点到为止

 

二、使用连接池

关于DBUtils的介绍,请点这里

官网

[root@localhost ~]# pip install DBUtils

#!/bin/env python
# -*- coding:utf-8 -*-

import MySQLdb
from dbutils.pooled_db import PooledDB

# mysql配置信息
conninfo = {
    'host':"",
    'user':"",
    'passwd':"",
    'db':"",
    'port':
}

#5为连接池里的最少连接数
pool = PooledDB(MySQLdb,5,**conninfo)
#以后每次需要数据库连接就是用connection（）函数获取连接就好了
conn = pool.connection()
cur = conn.cursor()
SQL = "select version()"
r = cur.execute(SQL)
r = cur.fetchone()
print r
cur.close()
conn.close()

 

面向对象实现python与数据库的交互

#!/bin/env python
# -*- coding:utf-8 -*-
#@Time  : 2020/11/19 10:00
#@Author: wangbo
#@File  : mysql_DBUtils.py
import json
import datetime
from dbutils.pooled_db import PooledDB
import pymysql

class MysqlClient(object):
    __pool = None;

    def __init__(self, mincached=3, maxcached=5, maxshared=5, maxconnections=5, blocking=True,
                 maxusage=100, setsession=None, reset=True,
                 host='192.168.1.7', port=33064, db='deploy',
                 user='depUsr', passwd='deploY123', charset='utf8mb4'):
        """

        :param mincached:连接池中空闲连接的初始数量
        :param maxcached:连接池中空闲连接的最大数量
        :param maxshared:共享连接的最大数量
        :param maxconnections:创建连接池的最大数量
        :param blocking:超过最大连接数量时候的表现，为True等待连接数量下降，为false直接报错处理
        :param maxusage:单个连接的最大重复使用次数
        :param setsession:optional list of SQL commands that may serve to prepare
            the session, e.g. ["set datestyle to ...", "set time zone ..."]
        :param reset:how connections should be reset when returned to the pool
            (False or None to rollback transcations started with begin(),
            True to always issue a rollback for safety's sake)
        :param host:数据库ip地址
        :param port:数据库端口
        :param db:库名
        :param user:用户名
        :param passwd:密码
        :param charset:字符编码
        """

        if not self.__pool:
            self.__class__.__pool = PooledDB(pymysql,
                                             mincached, maxcached,
                                             maxshared, maxconnections, blocking,
                                             maxusage, setsession, reset,
                                             host=host, port=port, db=db,
                                             user=user, passwd=passwd,
                                             charset=charset,
                                             cursorclass=pymysql.cursors.DictCursor
                                             )
        self._conn = None
        self._cursor = None
        self.__get_conn()

    def __get_conn(self):
        self._conn = self.__pool.connection();
        self._cursor = self._conn.cursor();

    def close(self):
        try:
            self._cursor.close()
            self._conn.close()
        except Exception as e:
            print e

    def __execute(self, sql, param=()):
        count = self._cursor.execute(sql, param)
        print count
        return count

    @staticmethod
    def __dict_datetime_obj_to_str(result_dict):
        """把字典里面的datatime对象转成字符串，使json转换不出错"""
        if result_dict:
            result_replace = {k: v.__str__() for k, v in result_dict.items() if isinstance(v, datetime.datetime)}
            result_dict.update(result_replace)
        return result_dict

    def select_one(self, sql, param=()):
        """查询单个结果"""
        count = self.__execute(sql, param)
        result = self._cursor.fetchone()
        """:type result:dict"""
        result = self.__dict_datetime_obj_to_str(result)
        return count, result

    def select_many(self, sql, param=()):
        """
        查询多个结果
        :param sql: qsl语句
        :param param: sql参数
        :return: 结果数量和查询结果集
        """
        count = self.__execute(sql, param)
        result = self._cursor.fetchall()
        """:type result:list"""
        [self.__dict_datetime_obj_to_str(row_dict) for row_dict in result]
        return count, result

    def execute(self, sql, param=()):
        count = self.__execute(sql, param)
        return count

    def begin(self):
        """开启事务"""
        self._conn.autocommit(0)

    def end(self, option='commit'):
        """结束事务"""
        if option == 'commit':
            self._conn.autocommit()
        else:
            self._conn.rollback()

if __name__ == "__main__":
    mc = MysqlClient()
    sql1 = 'SELECT * FROM dbauto_server_token  WHERE  id = 50'
    result1 = mc.select_one(sql1)
    print json.dumps(result1[1], ensure_ascii=False)

    sql2 = 'SELECT * FROM dbauto_server_token  WHERE  id IN (%s,%s,%s)'
    param = (49, 50, 51)
    print json.dumps(mc.select_many(sql2, param)[1], ensure_ascii=False)

 

数据库的操作大概就是这样的,具体的使用,后续慢慢填坑