珍藏版 _ python pandas 与 mysql 通用 交互/查询/修改 代码 开箱即用

示例

查询：df=get_DataFrame("select 1 from dual")

新建mysql表：save_DataFrame("new_table",df) #表存在时默认追加，可换成if_exists='replace'

根据主键更新mysql表:update_DataFrame("new_table",df)

删除mysql: query('delete from xxxx')

调用存储过程：call('xxxx')

# -*- coding: utf-8 -*-
"""
Created on Thu May  6 16:32:22 2021

@author: Admin
"""

import pandas as pd
import pymysql
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker
import datetime





def sc():
    global dz
    dz="mysql+pymysql://账号:密码@IP地址:端口/数据库?charset=utf8mb4"


#开局选择数据库
sc()


def SetAddr(url):
    
    global dz
    dz = url


def connect():
    conn = create_engine(dz)
    return conn


def get_DataFrame(sqlstr, retry=10):
    conn = connect()

    for i in range(retry):
        try:
            df = pd.read_sql(sqlalchemy.text(sqlstr), conn)
            break
        except Exception as e:
            if e.args[0].find("(2003,") > 0:
                print(datetime.datetime.now(), '重连中', i + 1)
            raise e
            break
    conn.dispose()

    return df

import traceback

def save_DataFrame(sqlstr, df, index=False, if_exists='append', retry=10,send_err=True):

    conn = connect()

    for i in range(retry):
        try:
            df.to_sql(sqlstr, conn, index=index, if_exists=if_exists)
            break
        except Exception as e:
            print(e)
            if e.args[0].find("(2003,") > 0:
                print(datetime.datetime.now(), '重连中', i + 1)
            # print(traceback.extract_stack(limit=3))
            raise e
            break

    conn.dispose()



def query(sqlstr, retry=10):

    engine = connect()

    for i in range(retry):
        try:
            session = scoped_session(sessionmaker(autocommit=True, autoflush=False, bind=engine))
            result = session.execute(sqlstr)
            
            session.close()
            break
        except Exception as e:
            if e.args[0].find("(2003,") > 0:
                print(datetime.datetime.now(), 'sql重连中', i + 1)
                continue
            raise e
            break

    engine.dispose()
    try:
        return result.fetchall()
    except:
        return None


def call(T,p=None):
    #调用存储过程
    engine = connect()
    Connection =engine.raw_connection()
    cursor = Connection.cursor()
    if p is None:
        result = cursor.callproc(T)
    else:
        result = cursor.callproc(T, p)
    
    Connection.commit()
    Connection.close()
    engine.dispose()
    return result

def update_DataFrame(sqlstr, df,retry=1):
    if (len(df)==0) | (retry==0):
        return

    #记得设置主键！！！！！
    try:
        cols = get_DataFrame('select * from %s limit 1' % sqlstr).columns

        if len(df.columns)>len(cols):
            print('\t\t',sqlstr,"sqlupdate列新增：",set(df.columns)-set(cols))

        save_DataFrame('tmp',df[cols],if_exists='replace')
        query('REPLACE into %s (select * from tmp)'%sqlstr)
        print(sqlstr,'\t','已更新')
    except Exception as e:
            if e.args[0].find("(1146,") > 0:
                save_DataFrame(sqlstr, df)
            elif e.args[0].find("(1050,") > 0:
                # (1050, "Table 'tmp' already exists")
                update_DataFrame(sqlstr, df,retry-1)
            else:
                raise e
    

if __name__ == '__main__':
    pass
    df=get_DataFrame("select 1 from dual")

---------------------------------------------------------