python数据库连接

一，单例模式

from dbutils import pooled_db
import pymysql
class SqlHelper(object):
    def __init__(self):
        self.pool = pooled_db.PooledDB(creator=pymysql,
                                  maxcached=2,
                                  mincached=1,
                                  blocking=True,
                                  ping=0,
                                  host='127.0.0.1',
                                  port=3306,
                                  user='root',
                                  password='123456',
                                  database='test')
    def open(self):
        conn = self.pool.connection()
        cursor = conn.cursor()
        return conn,cursor

    def close(self,conn,cursor):
        conn.close()
        cursor.close()

    def fetchall(self,sql, *args):
        conn,cursor = self.open()
        # conn = self.pool.connection()
        # cursor = conn.cursor()
        cursor.execute(sql, args)
        result = cursor.fetchall()
        # cursor.close()
        # conn.close()
        self.close(conn, cursor)
        return result

    def fetchone(self,sql, *args):
        conn = self.pool.connection()
        cursor = conn.cursor()
        cursor.execute(sql, args)
        result = cursor.fetchone()
        # cursor.close()
        # conn.close()
        self.close(conn, cursor)
        return result

db = SqlHelper()

二、方法

import pymysql
from dbutils import pooled_db

pool = pooled_db.PooledDB(creator=pymysql,
                          maxcached=2,
                          mincached=1,
                          blocking=True,
                          ping=0,
                          host='127.0.0.1',
                          port=3306,
                          user='root',
                          password='123456',
                          database='test')

def fetchall(sql,*args):
    conn = pool.connection()
    cursor = conn.cursor()
    cursor.execute(sql,args)
    result = cursor.fetchall()
    cursor.close()
    conn.close()
    return  result

def fetchone(sql,*args):
    conn = pool.connection()
    cursor = conn.cursor()
    cursor.execute(sql,args)
    result = cursor.fetchone()
    cursor.close()
    conn.close()
    return result
 def insert(sql,*args):
    conn = pool.connection()
    cursor = conn.cursor()
    cursor.execute(sql,args)
    conn.commit()
    result = cursor.fetchall()
    cursor.close()
    conn.close()
    print(result)
    return  result