python操作数据库常用工具:lzl

import pandas as pd
import numpy as np
import datetime
import pymysql
from sqlalchemy import create_engine
import math
'''
作者：LIZHILONG
程序写于：2023年11月4日
pip install sqlalchemy
pip install pymysql
#如果安装慢的话可以使用清华源
pip install sqlalchemy -i https://pypi.tuna.tsinghua.edu.cn/simple
pip install pymysql -i https://pypi.tuna.tsinghua.edu.cn/simple
如果运行报错，pandas和sqlalchemy不兼容，则：
pip install sqlalchemy==1.4.46 -i https://pypi.tuna.tsinghua.edu.cn/simple
pip install pandas -i https://pypi.tuna.tsinghua.edu.cn/simple
pip install pymysql -i https://pypi.tuna.tsinghua.edu.cn/simple
'''

#---------------数据库配置-------
database_name=''
user='t'
pwd=''
port=''
url=''
#--------------------------------


'''
开发好的函数：
get_pandas_BySql(sql)
get_dictList_BySql(sql)
get_dictList_FromPandas(df)
get_pandas_FromDictList(dict_datas)

get_dictList_ByTableNameAndCondition(tablename,select_item_list,select_condition_dict={})
get_pandas_ByTableNameAndCondition(tablename,select_item_list,select_condition_dict={})
get_distinctColumn_ByTableNameAndCondition(table_name,select_item,select_condition_dict={})
get_dictList_ByInColumnValueList(table_name,select_item_list,by_column_name,column_value_list)

insert_toTable_ByManyList(table_name,insert_column_list,insert_datas_list):#批量插入数据库
insert_ToTable_ByDict(table_name,Dict_Datas)
insert_ToTable_ByPandas(table_name,pandas)

delete_fromTableByColumn(table_name,byColumns_name,byColumn_value)
delete_fromTableByDict(table_name,Dict_Datas)

update_tableByDicData(table_name,where_Dictdatas,update_Dictdatas)
update_tableByDicData_addLog(table_name,where_Dictdatas,update_Dictdatas,cmd_userid,cmd_username)
db_cmd_bySql(sql)

'''
def get_db_connect():#获取数据库的连接
    db = pymysql.connect(host=url, port=int(port), user=user, passwd=pwd, db=database_name, charset='utf8')
    return db

def get_db_engine():
    db_url = "mysql+pymysql://" + user + ":" + pwd + "@" + url + ":" + port + "/" + database_name + "?charset=utf8"
    dataBase_url = db_url
    engine =create_engine(dataBase_url)
    return engine

def get_log_datas(dict_datas,user_id,user_name,table_name,cmd_type,id_name,id_value):
    cmd_time=datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    insert_datas_list=[]
    for key in dict_datas:
        insert_datas=[user_id,user_name,table_name,cmd_type,id_name,id_value,key,dict_datas[key]['before_value'],dict_datas[key]['after_value'],cmd_time]
        insert_datas_list.append(insert_datas)
    return insert_datas_list


def DictDataVmdTime(dict_data):
    for key, value in dict_data.items():
        if isinstance(value, pd._libs.tslibs.nattype.NaTType):
            dict_data[key]=None
        elif isinstance(value, datetime.datetime):
            dict_data[key]=value.strftime('%Y-%m-%d %H:%M:%S')
        elif isinstance(value,datetime.date):
            dict_data[key] = value.strftime("%Y-%m-%d")
        elif isinstance(value, float) and math.isnan(value):
            dict_data[key]=None
    return dict_data

def db_cmd_bySql(sql):
    res={}
    try:
        db =get_db_connect()
        db.ping(reconnect=True)
        cur = db.cursor()
        cur.execute(sql)
        db.commit()
        rowcount=cur.rowcount
        db.close()
        res['rowcount']=rowcount
        res['success'] = 1
        res['msg'] = '操作成功'
        return res
    except Exception as e:
        res['success'] = 0
        res['rowcount'] =0
        res['msg'] = '操作失败:' + str(e)
        return res


def list_join(data_list):#列表合并
    return ','.join(data_list)

def insert_toTable_ByManyList(table_name,insert_column_list,insert_datas_list):#批量插入数据库
    '''
    table_name:数据库表名称
    insert_column_list:列明
    insert_datas_list:插入数据库的列表值,例如：[['李之龙7','66666'],['李之龙8','66666']]或[['李之龙7','66666']]
    '''
    res={}
    try:
        sql = 'insert into ' + table_name
        s_zw_list = []  # 占位符
        for key in insert_column_list:
            s_zw_list.append('%s')
        sql = sql + ' (' + list_join(insert_column_list) + ') values (' + list_join(s_zw_list) + ')'
        db =get_db_connect()
        cursor = db.cursor()
        cursor.executemany(sql,insert_datas_list)
        rowcount=cursor.rowcount
        db.commit()
        cursor.close()
        db.close()
        res['success'] = 1
        res['rowcount']=rowcount
        res['msg'] = '操作成功'
        return res
    except Exception as e:
        res['success'] =0
        res['msg'] = '操作失败:'+str(e)
        res['rowcount'] =0
        return res
    
def insert_ToTable_ByDict(table_name,Dict_Datas):
    res={}
    try:
        sql="insert into "+table_name
        key_list=[]
        value_list=[]
        s_zw_list=[]#占位符
        for key,value in Dict_Datas.items():
            key_list.append(key)
            s_zw_list.append('%s')
            value_list.append(value)
        sql= sql+"("+list_join(key_list)+") values ("+list_join(s_zw_list)+")"
        db=get_db_connect()
        db.ping(reconnect=True)
        cur = db.cursor()
        cur.execute(query=sql,args=value_list)
        rowcount = cur.rowcount
        db.commit()
        db.close()
        res['success']=1
        res['rowcount']=rowcount
        res['msg']='操作成功'
        return res
    except Exception as e:
        res['success'] =0
        res['msg'] = '操作失败:'+str(e)
        res['rowcount'] =0
        return res

def insert_ToTable_ByPandas(table_name,df):
    res={}
    try:
        engine=get_db_engine()
        df.to_sql(table_name, con=engine, if_exists='append', index=False)
        engine.dispose()
        res['success'] = 1
        res['msg'] = '操作成功:'
        return res
    except Exception as e:
        res['success'] = 0
        res['msg'] = '操作失败:' + str(e)
        return res

def delete_fromTableByColumn(table_name,byColumns_name,byColumn_value):
    res = {}
    try:
        db = get_db_connect()
        db.ping(reconnect=True)
        cur = db.cursor()
        if byColumn_value is None:
            sql = "delete from " + table_name + " where " + byColumns_name + " is NULL "
            cur.execute(sql)
        else:
            sql = "delete from " + table_name+" where "+byColumns_name+"=%s"
            cur.execute(sql, args=[byColumn_value])
        rowcount=cur.rowcount
        db.commit()
        db.close()
        res['success'] = 1
        res['rowcount']=rowcount
        res['msg'] = '操作成功'
        return res
    except Exception as e:
        res['success'] = 0
        res['rowcount'] = 0
        res['msg'] = '操作失败:' + str(e)
        return res

def delete_fromTableByDict(table_name,Dict_Datas):
    res={}
    try:
        sql=" delete from "+table_name+" where 1=1"
        where_str=""
        values_list=[]
        for key, value in Dict_Datas.items():
            if value is None:
                where_str=where_str + " and " + key + " is %s"
            else:
                where_str = where_str + " and " + key + "=%s"
            values_list.append(value)
        sql=sql+where_str
        db = get_db_connect()
        db.ping(reconnect=True)
        cur = db.cursor()
        cur.execute(sql,args=values_list)
        db.commit()
        rowcount = cur.rowcount
        db.close()
        res['rowcount'] = rowcount
        res['success'] = 1
        res['msg'] = '操作成功'
        return res
    except Exception as e:
        res['success'] = 0
        res['rowcount'] = 0
        res['msg'] = '操作失败:' + str(e)
        return res


def get_pandas_BySql(sql):
    db = get_db_connect()
    db.ping(reconnect=True)
    cur = db.cursor()
    cur.execute(sql)
    datas = cur.fetchall()
    titles_info=cur.description
    title_list=[]
    for title in titles_info:
        title_list.append(title[0])
    res_data= list(datas)
    df= pd.DataFrame(res_data, columns=title_list)
    db.close()
    return df


def get_dictList_FromPandas(df):
    dict_list=df.to_dict(orient='records')
    for data in dict_list:
       data = DictDataVmdTime(data)
    return dict_list

def get_dictList_BySql(sql):
    df=get_pandas_BySql(sql)
    dict_list=get_dictList_FromPandas(df)
    return dict_list

def get_dictList_ByTableNameAndCondition(tablename,select_item_list,select_condition_dict={}):
    '''
    :param tablename: 查询数据库表名
    :param select_item_list: 查询需要的列名
    :param select_condition_dict: 查询条件字典
    :return:
    '''
    select_items=','.join(select_item_list)
    sql="select "+select_items+" from "+tablename+" where 1=1"
    where_str=''
    values_list=[]
    for item,value in select_condition_dict.items():
        if value is None:
            where_str=where_str+" and "+item+" is %s"
        else:
            where_str=where_str+" and "+item+"=%s"
        values_list.append(value)
    sql = sql + where_str
    db = get_db_connect()
    db.ping(reconnect=True)
    cur = db.cursor()
    cur.execute(sql, args=values_list)
    datas = cur.fetchall()
    titles_info = cur.description
    title_list = []
    for title in titles_info:
        title_list.append(title[0])
    res_data = list(datas)
    df = pd.DataFrame(res_data, columns=title_list)
    db.close()
    dict_list=get_dictList_FromPandas(df)
    return dict_list

def get_pandas_ByTableNameAndCondition(tablename,select_item_list,select_condition_dict={}):
    '''
       :param tablename: 查询数据库表名
       :param select_item_list: 查询需要的列名
       :param select_condition_dict: 查询条件字典
       :return:
       '''
    select_items = ','.join(select_item_list)
    sql = "select " + select_items + " from " + tablename + " where 1=1"
    where_str = ''
    values_list = []
    for item, value in select_condition_dict.items():
        if value is None:
            where_str = where_str + " and " + item + " is %s"
        else:
            where_str = where_str + " and " + item + "=%s"
        values_list.append(value)
    sql = sql + where_str
    db = get_db_connect()
    db.ping(reconnect=True)
    cur = db.cursor()
    cur.execute(sql, args=values_list)
    datas = cur.fetchall()
    titles_info = cur.description
    title_list = []
    for title in titles_info:
        title_list.append(title[0])
    res_data = list(datas)
    df = pd.DataFrame(res_data, columns=title_list)
    db.close()
    return df

def get_pandas_FromDictList(dict_datas):
    df=pd.DataFrame(data=dict_datas)
    return df

def get_dictList_ByInColumnValueList(table_name,select_item_list,by_column_name,column_value_list):
    select_items = ','.join(select_item_list)
    sql = "select " + select_items + " from " + table_name + " where "+by_column_name+" in"
    zw_s_list=[]#占位符
    for i in range(len(column_value_list)):
        zw_s_list.append('%s')
    sql=sql+'('+','.join(zw_s_list)+')'
    db = get_db_connect()
    db.ping(reconnect=True)
    cur = db.cursor()
    cur.execute(sql, args=column_value_list)
    datas = cur.fetchall()
    titles_info = cur.description
    title_list = []
    for title in titles_info:
        title_list.append(title[0])
    res_data = list(datas)
    df = pd.DataFrame(res_data, columns=title_list)
    db.close()
    res_list=get_dictList_FromPandas(df)
    return res_list

def convert_dictList_toPandas(dict_list):
    df=pd.DataFrame(dict_list)
    return df

def update_tableByDicData(table_name,where_Dictdatas,update_Dictdatas):
    res={}
    try:
        key_list = []
        value_list = []
        for key, value in update_Dictdatas.items():
            key_list.append(key+"=%s")
            value_list.append(value)
        sql='update '+table_name+" set "+list_join(key_list)+" where 1=1"

        where_value_list = []
        where_str=''
        for key, value in where_Dictdatas.items():
            if value is None:
                where_str=where_str+' and '+key+' is %s'
            else:
                where_str = where_str + ' and ' + key + '=%s'
            where_value_list.append(value)
        sql=sql+where_str
        db = get_db_connect()
        db.ping(reconnect=True)
        cur = db.cursor()
        cur.execute(query=sql, args=value_list+where_value_list)
        rowcount=cur.rowcount
        db.commit()
        db.close()
        res['success'] = 1
        res['rowcount']=rowcount
        res['msg'] = '操作成功'
        return res
    except Exception as e:
        res['success'] = 0
        res['msg'] = '操作失败:' + str(e)
        res['rowcount'] =0
        return res

def get_two_dict_diffrence(dict1,dict2):
    dict1=DictDataVmdTime(dict1)
    dict2= DictDataVmdTime(dict2)
    keys_list=list(dict1.keys())+ list(dict2.keys())
    res={}
    for key in keys_list:
        if key in dict1.keys() and key in dict2.keys():
            if dict1[key]!=dict2[key]:
                res_dif={}
                res_dif['before_value']=dict1[key]
                res_dif['after_value'] = dict2[key]
                res[key]=res_dif
    return res

def get_dictCal(dict_datas):
    res_item_list=[]
    res_value_list=[]
    for item,value in dict_datas.items():
        res_item_list.append(item)
        res_value_list.append(value)
    return [','.join(res_item_list),','.join(res_value_list)]


def update_tableByDicData_addLog(table_name,where_Dictdatas,update_Dictdatas,cmd_userid,cmd_username):
    res={}
    try:
        old_dict_value =get_dictList_ByTableNameAndCondition(table_name,list(update_Dictdatas.keys()),where_Dictdatas)#获得数据库内当前的数据
        if len(old_dict_value)==0:
            res['success']=0
            res['message']='操作失败，在数据库内未能按照查询条件查询到待修改数据的记录!'
            return res
        diffrence_dicts =get_two_dict_diffrence(old_dict_value[0], update_Dictdatas)
        new_update_Dictdatas={}
        for item,value in diffrence_dicts.items():
            new_update_Dictdatas[item]=value['after_value']
        if len(new_update_Dictdatas)==0:
            res['success'] = 0
            res['message'] = '操作失败,发送的数据和数据库内数据保持一致，无需修改!'
            return res
        res=update_tableByDicData(table_name,where_Dictdatas,new_update_Dictdatas)
        if res['success']==1:
            cmd_ids=get_dictCal(where_Dictdatas)
            insert_log_datas=get_log_datas(diffrence_dicts,cmd_userid,cmd_username,table_name,'update',cmd_ids[0],cmd_ids[1])
            title=['cmd_userid','cmd_username','cmd_table','cmd_type','cmd_table_idname','cmd_table_idvalue','column_name','before_value','after_value','cmd_time']
            df_log=pd.DataFrame(insert_log_datas,columns=title)
            res2=insert_ToTable_ByPandas('log_table',df_log)
            res['log_count']=len(df_log)
            res['addLog_flag']=res2['success']
        return res
    except Exception as e:
        res['success'] = 0
        res['msg'] = '操作失败:' + str(e)
        res['rowcount'] = 0
        return res

def get_distinctColumn_ByTableNameAndCondition(table_name,select_item,select_condition_dict={}):
    sql = "select distinct " + select_item + " from " + table_name + " where 1=1"
    where_str = ''
    values_list = []
    for item, value in select_condition_dict.items():
        if value is None:
            where_str = where_str + " and " + item + " is %s"
        else:
            where_str = where_str + " and " + item + "=%s"
        values_list.append(value)
    sql = sql + where_str
    db = get_db_connect()
    db.ping(reconnect=True)
    cur = db.cursor()
    cur.execute(sql, args=values_list)
    datas = cur.fetchall()
    res_list=[]
    for data in datas:
        res_list.append(data[0])
    db.close()
    return res_list


def get_updateSql_ByDictAndId(table_name,id_name,id_value,update_Dict_Datas):#获取update语句和参数
    key_list = []
    value_list = []
    for key, value in update_Dict_Datas.items():
        key_list.append(key + "=%s")
        value_list.append(value)
    sql = 'update '+table_name+" set "+ list_join(key_list) + " where " +id_name+ "='"+str(id_value)+"'"
    parama = value_list
    return [sql,parama]

def get_insertSql_ByDict(table_name,insert_Dict_Datas):#获取插入语句和参数
    sql = "insert into " + table_name
    key_list = []
    value_list = []
    s_zw_list = []  # 占位符
    for key, value in insert_Dict_Datas.items():
        key_list.append(key)
        s_zw_list.append('%s')
        value_list.append(value)
    sql = sql + "(" + list_join(key_list) + ") values (" + list_join(s_zw_list) + ")"
    return [sql,value_list]

def command_transaction(sqlAndargs):#执行数据库事务
    db = get_db_connect()
    db.ping(reconnect=True)
    cursor = db.cursor()
    res={}
    try:
        for sql in sqlAndargs:
            cursor.execute(sql[0],sql[1])
        db.commit()
        db.close()
        res['success']=1
        res['msg']='操作成功'
        return res
    except Exception as e:
        db.rollback()
        cursor.close()
        db.close()
        res['success'] = 0
        res['msg'] = '操作失败:'+str(e)
        return res

#事务操作例子
# a=get_updateSqls_ByDictAndId('test','id','14',{'name':'lzl0000000000000','age':'11111'})
# b=get_updateSqls_ByDictAndId('test','id','7',{'name':'lzl800000000','age':'55555'})
# c=get_insertSqls_ByDictAndId('test',{'name':'李连杰','birthday':'2023-11-05 23:25:12'})
# sqls=[]
# sqls.append(a)
# sqls.append(b)
# sqls.append(c)
# res=command_transaction(sqls)
# print(res)

def get_updateSql_ByConditionDictAndUpdateDict(table_name,conditionDict,update_Dict_Datas):#获取update语句和参数
    key_list = []
    value_list = []
    for key, value in update_Dict_Datas.items():
        key_list.append(key + "=%s")
        value_list.append(value)
    sql = 'update '+table_name+" set "+ list_join(key_list) + " where "

    where_list=[]
    where_value_list=[]
    for key,value in conditionDict.items():
        where_list.append(key+"=%s")
        where_value_list.append(value)
    sql=sql+' and '.join(where_list)
    parama = value_list+where_value_list
    return [sql,parama]

def statistics_word_count_fromList(list_datas):#从列表中统计出宣词汇的次数
    res={}
    for list_data in list_datas:
        if list_data in res:
            res[list_data]=res[list_data]+1
        else:
            res[list_data] = 1
    titles=['work_name','count']
    res_list=[]
    for key,value in res.items():
        res_list.append([key,value])
    df=pd.DataFrame(data=res_list,columns=titles)
    df.sort_values(by=['count'],ascending=False,inplace=True)
    dict_list=get_dictList_FromPandas(df)
    return dict_list


# list_datas=['李之龙','张晓峰','李之龙','张三丰','rrrrr','李之龙']
# res=statistics_word_count_fromList(list_datas)
# df=get_pandas_FromDictList(res)
# print(df)

def get_pandasPaging_bySql(select_sql,current_page,page_size):#根据SQL做数据库分页返回df
    sql = select_sql+" limit %s,%s"
    db = get_db_connect()
    db.ping(reconnect=True)
    cur = db.cursor()
    cur.execute(sql, args=[(current_page-1)*page_size,page_size])
    datas = cur.fetchall()
    titles_info = cur.description
    title_list = []
    for title in titles_info:
        title_list.append(title[0])
    res_data = list(datas)
    df = pd.DataFrame(res_data, columns=title_list)
    db.close()
    return df

def get_dictListPaging_bySql(select_sql,current_page,page_size):#根据SQL做数据库分页返回dictList
    df=get_pandasPaging_bySql(select_sql,current_page,page_size)
    dict_list=get_dictList_FromPandas(df)
    return dict_list

def get_complete_dataPaging_bySql(sql,summary_columns_dict,current_page,page_size):
    '''
    summary_columns_dict={'sum':['a','b','c'],'count':['c','d']}
    :param sql: 查询语句
    :param summary_columns_dict: 总体的汇总字段
    :param current_page: 当前页数
    :param page_size: 页面大小
    :return:
    sql="select * from dd_dz_datas where 1=1 and 数据获取时间='2023-04-27 12:19:18'"
    cc=db_base_curd.get_complete_dataPaging_bySql(sql,{'sum':['月租金','总实收额'],'count':['订单号']},1,10)
    print(cc)
    '''
    res={}
    try:
        page_data_list=get_dictListPaging_bySql(sql,current_page,page_size)
        all_sql2=sql.split('from')[1].split('where 1=1')[0]#将from后面和where 1=1之间的提取
        legal_summary_agg=['sum','count']
        agg_list=[]
        rename_list={}
        for key,values in summary_columns_dict.items():
            if key not in legal_summary_agg:
                res['success']=0
                res['message']='传递的聚合名称['+key+']不合法！'
                return res
            for value in values:
                agg_str=key+"("+value+")"
                rename_list[agg_str]=key+'_'+value
                agg_list.append(agg_str)
        all_sql="select "+','.join(agg_list)+" from "+all_sql2
        df_all=get_pandas_BySql(all_sql)
        df_all=round(df_all,2)
        df_all.rename(columns=rename_list,inplace=True)
        summary_dict_list=get_dictList_FromPandas(df_all)
        res['success']=1
        res['message']='查询成功'
        res['page_data_list']=page_data_list
        res['summary_info']=summary_dict_list
        res['summary_sql'] =all_sql
        return res
    except Exception as e:
        res['success'] =0
        res['message'] = '查询失败:'+str(e)
        res['page_data_list'] =None
        res['summary_info'] =None
        res['summary_sql'] = all_sql
        return res