本文介绍如何在Python中使用Pandas库实现MySQL数据库的读写&操作。

数据库信息如下：

user_name = 'root'
password = '12345678'
address = 'localhost'
port = 3306
database_name = 'rkqy_wwsj'

一、python 读取Mysql

#读取表'modle_test_20190906'中的所有数据

def Read_database (user_name,password,address,port,database_name,sql):
    import pymysql
    import pandas as pd
    conn = pymysql.connect(host = address,user = user_name,passwd = password,\
                           db = database_name , port = int(port) ,charset = "utf8mb4")
    try:
        df = pd.read_sql (sql,con = conn)
    except:
        print ('\n Reading Error  \n')    
    finally:
        conn.close()
    print ('\n Completion of data reading \n')    
    return (df) 

sql = '''
SELECT * from modle_test_20190906;
'''    
df = Read_database (user_name,password,address,port,database_name,sql)

二、python 写入Mysql

#将数据导入'rkqy_wwsj'库，其中的'scs_rk_qc_all'表

def Write_to_database(df,user_name,password,address,port,database_name,table_name):
    import pandas as pd    
    import sqlalchemy    
    engine = sqlalchemy.create_engine('mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8'.format(user_name,password,address,int(port),database_name))
    try: 
        pd.io.sql.to_sql(frame = df,name = table_name,con=engine,if_exists='replace',index = False)
    except:
        print ('\n Writting Error  \n')
    finally:
        engine.dispose()
    print ('\n Data has been written \n')    
    
table_name = 'scs_rk_qc_all'
df = pd.read_excel(r'C:\Users\Administrator\Desktop\test.xlsx',sheet_name = 'test1')  
  
Write_to_database(df,user_name,password,address,port,database_name,table_name)    

三、python 操作Mysql

#操作数据库，删除'scs_rk_qc_all'表

def Operating_database (user_name,password,address,port,database_name,sql):
    import pymysql
    import pandas as pd
    conn = pymysql.connect(host = address,user = user_name,passwd = password,\
                           db = database_name , port = int(port) ,charset = "utf8mb4")    
    cur = conn.cursor()     # 使用cursor()方法获取操作游标    
    try:
        cur.execute(sql)    #像sql语句传递参数
        conn.commit()       #提交
        print ('\n Completion of database operation \n')
    except Exception as e:
        conn.rollback()     #错误回滚
        print ('\n Operation error \n')
    finally:
        conn.close()

sql = '''
drop table scs_rk_qc_all
'''
Operating_database (user_name,password,address,port,database_name,sql)