自分で整理したpythonのmysqlに対する常用操作を、関数にカプセル化して直接呼び出す

自分が普段よく使うmysql操作の関数を整理して、主にdataframeを使って操作して、同時にデータベースの接続、提出などの操作をカプセル化して、このようにパラメータとデータを伝えるだけでいいです.必要な友达に分かち合い、車輪を作る時間を節約します.

from sqlalchemy import create_engine
import pymysql
import pandas as pd

'''
author:LancerWu
email:[email protected]
'''


def df_to_sql(engine_conn, data, table_name):
    '''
    datafrme            
    :param engine_conn:       
    :param data:             
    :param table_name:     
    :return:
    '''
    try:
        # list       dataframe  
        df = pd.DataFrame(data)
        #           
        engine = create_engine('%s' % engine_conn)
        #     
        df.to_sql(name=table_name, con=engine, if_exists='append', index=False, index_label=False)
        #   engine   
        engine.dispose()
        print('       ')

    except Exception as e:
        print('dataframe       ：%s' % e)
        return None


def list_to_df_to_sql(tb_name, cols, rows, engine_conn):
    '''
    list dict df
    :param tb_name:  
    :param cols:   
    :param rows:   ，    
    :param engine_conn:       
    :return:
    '''
    try:
        #          
        dict_temp = dict(zip(cols, rows))
        #        list
        list_temp = [dict_temp]
        #     dataframe
        df = pd.DataFrame(list_temp)
        #           
        engine = create_engine('%s' % engine_conn)
        # df     
        df.to_sql(name=tb_name, con=engine, if_exists='append', index=False, index_label=False)
        #       
        engine.dispose()
        print('       ')

    except Exception as e:
        print('list       ：%s' % e)
        return None


def sql_to_df(engine_info, sql):
    '''
          dataframe  
    :param engine_info:       
    :param sql:   sql  
    :return:
    '''
    try:
        #          
        engine = create_engine(engine_info)
        #          dataframe
        df_query = pd.read_sql(sql, engine)
        #        
        engine.dispose()
        print('       ', df_query.head())
        #     list
        data = df_query.to_dict(orient='dict')
        return data

    except Exception as e:
        print('       df  :%s' % e)
        return None


def sql_caozuo(sql, conn):
    '''
      sql            
    :param sql:    sql  
    :param conn:        
    :return:
    '''
    try:
        #      
        conn = pymysql.connect(**conn)
        cur = conn.cursor()
        #   sql  
        cur.execute(sql)
        #       
        results = cur.fetchall()
        #       
        conn.commit()
        #     
        cur.close()
        conn.close()

        return results

    except Exception as e:
        print('       ：%s' % e)
        return None


if __name__=="__main__":
    #          
    engine_conn = 'mysql+pymysql://(mysql  ):(    )@(ip):(   )/(table)?charset=utf8'
    pymysql_conn = {
        'host': "",  # ip  
        'port': 3306,  #    ，  3306
        'user': "",  # mysql  
        'password': "",  #     
        'db': "",  #      
        'charset': 'utf8'  #     
    }
    sql = "select * from table;"

    # 1、           
    query_result = sql_caozuo(sql, pymysql_conn)
    print('    ', query_result)

    # 2、             dataframe
    data = sql_to_df(engine_conn, sql)  # data df   list，        
    print('data', data)

    # 3、list      
    tb_name = 'test'  #           
    cols = ['col1', 'col2', 'col3']  #    ，              
    rows = ['data1', 'data2', 'data3']  #    ，  cols    ，                  
    list_to_df_to_sql(tb_name, cols, rows, engine_conn)

    # 4、dataframe      
    data = [{
        'col1': 'data1',
        'col2': 'data2',
        'col3': 'data3'

    }]  # list       
    df_to_sql(engine_conn, data, tb_name)

ソースアドレス:https://github.com/wuxs231/python_mysql_operation