美文网首页
python3 读取、写入、操作Mysql

python3 读取、写入、操作Mysql

作者: Ziger丶 | 来源:发表于2019-09-27 15:04 被阅读0次

    本文介绍如何在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)
    
    

    相关文章

      网友评论

          本文标题:python3 读取、写入、操作Mysql

          本文链接:https://www.haomeiwen.com/subject/kowouctx.html