Python脚本之连接MySQL【四】

本文为博主原创，未经授权，严禁转载及使用。
本文链接：https://blog.csdn.net/zyooooxie/article/details/124640412

之前写了篇 Python脚本之连接MySQL【三】，日常使用过程中，代码实际有很多改动，特此更新；

【实际这篇博客推迟发布N个月】

个人博客：https://blog.csdn.net/zyooooxie

【以下所有内容仅为个人项目经历，如有不同，纯属正常】

Cursor类的nextset()、_nextset()

https://peps.python.org/pep-0249/#nextset

Cursor.nextset()This method will make the cursor skip to the next available set, discarding any remaining rows from the current set.If there are no more sets, the method returns None. Otherwise, it returns a true value and subsequent calls to the .fetch*() methods will return rows from the next result set.

在这里插入图片描述

方法优化【2】

【读取ini配置文件 + 建立、关闭数据库连接】

@filename: db.ini

[zyooooxie_db]
user = user_zy
host = host_zy
passwd = passwd_zy
database = database_zy

"""
@blog: https://blog.csdn.net/zyooooxie
@qq: 153132336
@email: zyooooxie@gmail.com
"""from pymysql.connections import Connection
from pymysql.cursors import Cursor
from pymysql.constants import CLIENTdef read_ini(ini_path: str = 'db.ini'):"""读取配置文件:param ini_path:默认是数据库的配置文件:return:"""if os.path.isfile(ini_path):config = ConfigParser()config.read(ini_path, encoding='UTF-8')return {cf: config.items(cf) for cf in config.sections()}def connect_db(db_name: str) -> (Connection, Cursor):"""建立链接，传参为 具体的db:param db_name::return:"""ini_dict = read_ini()if db_name in ini_dict:res_list = ini_dict.get(db_name)res_dict = dict(res_list)else:raise Exception('传参不合法')db = pymysql.connect(port=3306, charset='utf8', autocommit=True, client_flag=CLIENT.MULTI_STATEMENTS,use_unicode=True, **res_dict)  # client_flag传值：默认可以同时执行多条sql语句的cur = db.cursor()Log.debug('{} connect'.format(db_name))return db, curdef close_db(db: Connection, cur: Cursor):"""断开连接:param db::param cur::return:"""cur.close()db.close()Log.debug('connect close')

exe_sql()、fetch_sql()、fetch_sqls()

"""
@blog: https://blog.csdn.net/zyooooxie
@qq: 153132336
@email: zyooooxie@gmail.com
"""def exe_sqls(sql: str, db_name: str, db: Connection = None, cur: Cursor = None):"""多条sql语句-execute():param sql::param db_name::param db::param cur::return:"""exe_sql(sql=sql, db_name=db_name, db=db, cur=cur)def exe_sql(sql: str, db_name: str, exe_mode: str = None, data_list: list or tuple = None,db: Connection = None, cur: Cursor = None):"""1条sql语句-execute()、executemany():param sql::param db_name::param exe_mode::param data_list::param db::param cur::return:"""if not bool(cur):db_use, cur_use = connect_db(db_name=db_name)else:db_use, cur_use = db, curLog.info(sql)if data_list is None and exe_mode is None:try_str = """cur_use.execute(sql)"""elif exe_mode == 'execute' and data_list is not None:assert sql.find('(%s') != -1try_str = """cur_use.execute(sql, data_list)"""elif exe_mode == 'executemany' and data_list is not None:assert sql.find('(%s') != -1try_str = """cur_use.executemany(sql, data_list)"""else:Log.error('{}--{}'.format(exe_mode, data_list))raise Exception('Execute Error')try:result = eval(try_str, locals())except Exception as e:db_use.rollback()Log.error(e.args)Log.info(traceback.format_exc())result = FalseLog.error('sql执行有问题')else:execute_sql_result_check(result)finally:if not bool(cur):close_db(db=db_use, cur=cur_use)def execute_sql_result_check(result):"""SQL Execute结果检查:param result::return:"""if not result:Log.error('{}-Number of affected rows'.format(result))else:Log.debug('Execute Succeed:{}'.format(result))def fetch_sqls(sql: str, db_name: str, db: Connection = None, cur: Cursor = None) -> List[tuple]:"""多条sql语句-fetchall():param sql::param db_name::param db::param cur::return:"""if not bool(cur):db_use, cur_use = connect_db(db_name=db_name)else:db_use, cur_use = db, curLog.info(sql)try:data = list()cur_use.execute(sql)data.append(cur_use.fetchall())# while True:##     if cur_use.nextset():#         data.append(cur_use.fetchall())##     else:#         breakwhile cur_use.nextset():data.append(cur_use.fetchall())except Exception as e:db_use.rollback()Log.debug(e.args)Log.info(traceback.format_exc())data = FalseLog.error('sql执行有问题')else:for d in data:fetch_sql_result_check(d)finally:if not bool(cur):close_db(db=db_use, cur=cur_use)return datadef fetch_sql(sql: str, db_name: str, fetch_mode: str = 'fetchall', db: Connection = None, cur: Cursor = None):"""1条sql语句-fetchone()、fetchall():param sql::param db_name::param fetch_mode::param db::param cur::return:"""if not bool(cur):db_use, cur_use = connect_db(db_name=db_name)else:db_use, cur_use = db, curLog.info(sql)if fetch_mode == 'fetchall':try_str = """cur_use.fetchall()"""elif fetch_mode == 'fetchone':  # 很少用到try_str = """cur_use.fetchone()"""else:Log.error('fetch_mode: {}'.format(fetch_mode))raise Exception(fetch_mode)try:cur_use.execute(sql)data = eval(try_str, locals())except Exception as e:db_use.rollback()Log.debug(e.args)Log.info(traceback.format_exc())data = FalseLog.error('sql执行有问题')else:fetch_sql_result_check(data)finally:if not bool(cur):close_db(db=db_use, cur=cur_use)return datadef fetch_sql_result_check(result):"""SQL Fetch结果检查:param result::return:"""if not result:Log.error('{}-Fetch Nothing'.format(result))else:Log.debug('Fetch Succeed')

实际应用【2】

"""
@blog: https://blog.csdn.net/zyooooxie
@qq: 153132336
@email: zyooooxie@gmail.com
"""def test_0801():from XXX_use.common_mysql import fetch_sql, connect_db, close_db, fetch_sqls, exe_sql, exe_sqlsdb_, cur_ = connect_db(db_name='zy_db')sql = """SELECT  *  FROM `table_c_r`    ORDER BY `update_date` DESC ;"""data = fetch_sql(sql=sql, db_name='zy_db', fetch_mode='fetchall', db=db_, cur=cur_)Log.info(data)data = fetch_sql(sql=sql, db_name='zy_db', fetch_mode='fetchone', db=db_, cur=cur_)Log.info(data)data = fetch_sql(sql=sql, db_name='zy_db', db=db_, cur=cur_)Log.info(data)sql = """SELECT  *  FROM `table_c_r_g_m_s`    ORDER BY `update_date` DESC LIMIT 50 ;# 这条sql查不到数据SELECT  *  FROM `table_c_b_r_r`  WHERE   `delete_flag` = 1   ORDER BY `update_date` DESC  ; SELECT  *  FROM `table_c_r`    ORDER BY `update_date` DESC LIMIT 1;"""res = fetch_sqls(sql=sql, db_name='zy_db', db=db_, cur=cur_)Log.info(res)data_list = [('TEST0801' + 'ZYOOOOXIE_' + str(i), 'Name' + str(i), 'zyooooxie') for i inrandom.sample(range(500), 10)]Log.info(data_list)sql = """insert into `table_c_g_c`     (`c_i`, `name`, `owner`) VALUES   (%s, %s, %s);"""exe_sql(sql=sql, db_name='zy_db', exe_mode='executemany', data_list=data_list, db=db_, cur=cur_)exe_sql(sql=sql, db_name='zy_db', exe_mode='execute', data_list=data_list[-1], db=db_, cur=cur_)sql = """insert into `table_c_g_c`     (`c_i`, `name`, `owner`) VALUES     ('TEST0801ZYOOOOXIE_11','Name_11', 'zyooooxie') ; insert into table_c_y_a    (username, password)values    ('TEST0801_11', 'pwd_11');insert into `table_c_g_c`    (`c_i`, `name`, `owner`) VALUES     ('TEST0801ZYOOOOXIE_12','Name_12', 'zyooooxie') ;    """exe_sql(sql=sql, db_name='zy_db', db=db_, cur=cur_)exe_sqls(sql=sql, db_name='zy_db', db=db_, cur=cur_)sql_ = """DELETE FROM `table_c_g_c` WHERE c_i LIKE 'TEST0801%' ;DELETE FROM `table_c_y_a` WHERE username LIKE 'TEST0801%' ;DELETE FROM `table_c_y_a` WHERE username LIKE 'TEST080808%' ;"""exe_sql(sql=sql_, db_name='zy_db', db=db_, cur=cur_)exe_sqls(sql=sql_, db_name='zy_db', db=db_, cur=cur_)close_db(db_, cur_)