mysql_comm.py
# -*- coding:utf8 -*-
import pymysql
from pymysql.cursors import DictCursor
from dbutils.pooled_db import PooledDB
MYSQL_HOST=""
MYSQL_PORT=3306
MYSQL_USER="crawler"
MYSQL_DATABASE="crawler_deliver"
MYSQL_PASSWORD=""
# Parent connection pool , Used to initialize the database connection
class BasePymysqlPool(object):
def __init__(self):
self.db_host = MYSQL_HOST
self.db_port = int(MYSQL_PORT)
self.user = MYSQL_USER
self.password = str(MYSQL_PASSWORD)
self.db = MYSQL_DATABASE
self.conn = None
self.cursor = None
class PymysqlPool(BasePymysqlPool):
""" MYSQL Database objects , Responsible for generating database connection , The connection in this class uses the connection pool to obtain the connection object ：conn = Mysql.getConn() Release connection object ;conn.close() or del conn """
# Connect pool objects
__pool = None
def __init__(self):
super(PymysqlPool, self).__init__()
# Database constructors , Remove the connection from the connection pool , And generate the operation cursor
self._conn = self.__getConn()
self._cursor = self._conn.cursor()
def __getConn(self):
""" @summary: Static methods , Remove the connection from the connection pool @return MySQLdb.connection """
if PymysqlPool.__pool is None:
__pool = PooledDB(creator=pymysql,
mincached=1,
maxcached=20,
host=self.db_host,
port=self.db_port,
user=self.user,
passwd=self.password,
db=self.db,
use_unicode=True,# This should be set to True, Otherwise, the queried data will become bytes type
charset="utf8",
cursorclass=DictCursor,
)
return __pool.connection()
def getAll(self, sql, param=None):
""" @summary: Execute the query , And take out all the result sets @param sql: Inquire about ＳＱＬ, If there are query conditions , Please specify only the condition list , And use the condition value as a parameter [param] Pass it on @param param: Optional parameters , Condition list values （ Tuples / list ） @return: result list( A dictionary object )/boolean The result set of the query """
if param is None:
count = self._cursor.execute(sql)
else:
count = self._cursor.execute(sql, param)
if count > 0:
result = self._cursor.fetchall()
else:
result = False
return result
def getOne(self, sql, param=None):
""" @summary: Execute the query , And take out the first @param sql: Inquire about ＳＱＬ, If there are query conditions , Please specify only the condition list , And use the condition value as a parameter [param] Pass it on @param param: Optional parameters , Condition list values （ Tuples / list ） @return: result list/boolean The result set of the query """
if param is None:
count = self._cursor.execute(sql)
else:
count = self._cursor.execute(sql, param)
if count > 0:
result = self._cursor.fetchone()
else:
result = False
return result
def getMany(self, sql, num, param=None):
""" @summary: Execute the query , And take out num Bar result @param sql: Inquire about ＳＱＬ, If there are query conditions , Please specify only the condition list , And use the condition value as a parameter [param] Pass it on @param num: The number of results obtained @param param: Optional parameters , Condition list values （ Tuples / list ） @return: result list/boolean The result set of the query """
if param is None:
count = self._cursor.execute(sql)
else:
count = self._cursor.execute(sql, param)
if count > 0:
result = self._cursor.fetchmany(num)
else:
result = False
return result
def insertMany(self, sql, values):
""" @summary: Insert multiple records into the data table @param sql: To insert ＳＱＬ Format @param values: Record data to insert tuple(tuple)/list[list] @return: count Rows affected """
count = self._cursor.executemany(sql, values)
return count
def __query(self, sql, param=None):
if param is None:
count = self._cursor.execute(sql)
else:
count = self._cursor.execute(sql, param)
return count
def update(self, sql, param=None):
""" @summary: Update data table records @param sql: ＳＱＬ Format and conditions , Use (%s,%s) @param param: To update the value tuple/list @return: count Rows affected """
return self.__query(sql, param)
def insert(self, sql, param=None):
""" @summary: Update data table records @param sql: ＳＱＬ Format and conditions , Use (%s,%s) @param param: To update the value tuple/list @return: count Rows affected """
return self.__query(sql, param)
''' Execute table creation statement '''
def create_table(self,sql):
return self._cursor.execute(sql)
def insert_data(self, table_name: str, data) -> bool:
''' insert data , Judge according to the incoming data type , Automatic selector insertion mode @:param table_name Table name @:param data The data to be inserted '''
try:
count = 0
if isinstance(data, list):
for item in data:
keys = ",".join(list(item.keys()))
values = ",".join([f"'{
x}'" for x in list(item.values())])
sql = f"INSERT IGNORE INTO {
table_name} ({
keys}) VALUES ({
values});"
count+=self._cursor.execute(sql)
elif isinstance(data, dict):
keys = ",".join(list(data.keys()))
values = ",".join([f"'{
x}'" for x in list(data.values())])
sql = f"INSERT IGNORE INTO {
table_name} ({
keys}) VALUES ({
values});"
print(sql)
count+=self._cursor.execute(sql)
return count
except Exception as ex:
raise Exception(ex)
def delete(self, sql, param=None):
""" @summary: Delete data table records @param sql: ＳＱＬ Format and conditions , Use (%s,%s) @param param: Conditions to delete value tuple/list @return: count Rows affected """
return self.__query(sql, param)
def begin(self):
""" @summary: Open transaction """
self._conn.autocommit(0)
def end(self, option='commit'):
""" @summary: End the business """
if option == 'commit':
self._conn.commit()
else:
self._conn.rollback()
def dispose(self, isEnd=1):
""" @summary: Release connection pool resources """
if isEnd == 1:
self.end('commit')
else:
self.end('rollback')
self._cursor.close()
self._conn.close()
