9rep - Pandas MySQL

Pandasを使ったデータSELECT INSERT(追記)

• MySQLへPandasを経由し取得
• PandsでTSVをMySQLへInsert

import pandas as pd
from sqlalchemy import create_engine
import sqlalchemy as sa
import sys
import datetime

class MySqlPandas():

    def __init__(self):
    		self.engine = create_engine('mysql+pymysql://{USER}:{PASSWORD}@{HOST}:{PORT}/{DB NAME}')

    def sesStoreData(self):
        sql = '''
				SELECT
				    tb.hoge1                       
				,   tb.hoge2                     
				,   tb.hoge3               
				FROM
				    table tb
				;
				'''

        df = pd.read_sql_query(sql, self.engine)

        df.to_csv('ses_table_' + str(datetime.date.today()) + '.tsv', sep='\t')

    def insStoreData(self):
        df_ins = pd.read_csv('all_table.tsv', delimiter='\t')

        df_ins_new = df_ins.rename(columns={'hoge1': 'col1', 'hoge2': 'col2', 'hoge3': 'col3'})

        df_ins_new.to_sql('store_master', self.engine, index=False, if_exists='append')　# フルリプレイスは　if_exists='replace'


if __name__ == '__main__':
    myp = MySqlPandas()
    # myp.sesStoreData() select実行
    myp.insStoreData()   Insert実行