PythonによるMySQLデータバックアップ:データ照会と追加
4577 ワード
実行前にMySQLdbモジュールをインストールする必要があります!!!
sudo yum install MySQL-python
#!/usr/bin/python
# -*- coding: UTF-8 -*-
import MySQLdb
import datetime
def backYestoday():
today = datetime.date.today()
yesterday = today - datetime.timedelta(days=1)
#
db_mat = MySQLdb.connect("hostname", "root", "password", "db", charset='utf8')
# cursor()
cursor = db_mat.cursor()
# execute SQL
sql_select = "select id, created, agency_name, app_name, match_type, campaign_id, campaign_name, country_name, currency_code, is_view_through, site_name, site_id, publisher_name, publisher_id, existing_user, region_name, session_datetime, session_ip, impression_created, stat_click_id, stat_impression_id, payout, referral_source, referral_url, ip, revenue, revenue_usd, status, status_code, tracking_id, os_id, wurfl_brand_name, debug_mode, device_brand, device_carrier, device_id, device_ip, device_model, device_type, os_version, google_aid, ios_ifa, ios_ifv, language, mac_address, odin, sdk, sdk_version, test_profile, unid, user_agent, windows_aid, publisher_sub_ad_name, publisher_sub_adgroup_name, publisher_sub_campaign_name, publisher_sub_campaign_id, publisher_sub_campaign_ref, publisher_sub_keyword_name, publisher_sub_placement_name, publisher_sub_placement_id, publisher_sub_publisher_id, publisher_sub_publisher_name, publisher_sub_site_name, publisher_ref_id, publisher_sub1, advertiser_ref_id, advertiser_sub_ad_name, advertiser_sub_adgroup_name, advertiser_sub_adgroup_id, advertiser_sub_ad_id, advertiser_sub_campaign_name, advertiser_sub_campaign_id, advertiser_sub_campaign_ref, advertiser_sub_keyword_name, advertiser_sub_publisher_name, advertiser_sub_publisher_id, advertiser_sub_placement_name, advertiser_sub_placement_id, advertiser_sub_site_name, user_id, currency_rate, ip_from, ip_to, os_jailbroke FROM install where created >= '"+str(yesterday)+" 00:00:00' and created <='"+str(yesterday)+" 23:59:59'"
cursor.execute(sql_select)
result = cursor.fetchall()
db_mat.close()
#
db_advertisement = MySQLdb.connect("127.0.0.1", "root", "password", "db",charset='utf8')
# cursor()
cursor = db_advertisement.cursor()
# SQL
sql_insert = "insert into install(id, created, agency_name, app_name, match_type, campaign_id, campaign_name, country_name, currency_code, is_view_through, site_name, site_id, publisher_name, publisher_id, existing_user, region_name, session_datetime, session_ip, impression_created, stat_click_id, stat_impression_id, payout, referral_source, referral_url, ip, revenue, revenue_usd, status, status_code, tracking_id, os_id, wurfl_brand_name, debug_mode, device_brand, device_carrier, device_id, device_ip, device_model, device_type, os_version, google_aid, ios_ifa, ios_ifv, language, mac_address, odin, sdk, sdk_version, test_profile, unid, user_agent, windows_aid, publisher_sub_ad_name, publisher_sub_adgroup_name, publisher_sub_campaign_name, publisher_sub_campaign_id, publisher_sub_campaign_ref, publisher_sub_keyword_name, publisher_sub_placement_name, publisher_sub_placement_id, publisher_sub_publisher_id, publisher_sub_publisher_name, publisher_sub_site_name, publisher_ref_id, publisher_sub1, advertiser_ref_id, advertiser_sub_ad_name, advertiser_sub_adgroup_name, advertiser_sub_adgroup_id, advertiser_sub_ad_id, advertiser_sub_campaign_name, advertiser_sub_campaign_id, advertiser_sub_campaign_ref, advertiser_sub_keyword_name, advertiser_sub_publisher_name, advertiser_sub_publisher_id, advertiser_sub_placement_name, advertiser_sub_placement_id, advertiser_sub_site_name, user_id, currency_rate, ip_from, ip_to, os_jailbroke) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
try:
# sql
cursor.executemany(sql_insert, result)
#
db_advertisement.commit()
except:
db_advertisement.rollback()
#
db_advertisement.close()
backYestoday()