SQLとSolrの情報を削除する方法

簡単な処理をバッチ化した。MySQLにアクセスする方法がわかったことが大きな収穫。

# -*- coding: utf-8 -*-
# vim:tabstop=4:shiftwidth=4:expandtab

import sys
import MySQLdb
import json
import urllib2
import argparse
from datetime import datetime


def check_jan_sql(jancode, conn, cond):
    print 'the jancode check(SQL) start', datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    cur = conn.cursor()
    try:
        sql = '''
            SELECT janCd,name FROM [table名] WHERE janCd = %s
        '''
        cur.execute(sql, [jancode, ])
        rows = cur.fetchall()
        if len(rows) == cond:
            for row in rows:
                janCd, name = row
                print '%s\t%s' % (janCd, name)
            return True
    except Exception as e1:
        raise e1
    finally:
        cur.close()
        return False


def check_jan_solr(jancode, cond):
    print 'the jancode check(Solr) start', datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    url_check = 'http://localhost:8983/solr/[core名]/select?q=(janCd:{jancode})'.format(
        jancode=jancode
    ) + '&fl=janCd&wt=json&indent=true'
    json_string = urllib2.urlopen(url_check).read()
    parsed = json.loads(json_string)
    response = parsed.get(u'response')
    print json_string
    if response and response.get(u'numFound') == cond:
        return True
    return False


def delete_jan_sql(jancode, conn):
    cur = conn.cursor()
    try:
        sql = '''
            DELETE FROM [table名] WHERE janCd = %s
        '''
        cur.execute(sql, [jancode, ])
    except Exception as e1:
        raise e1
    finally:
        cur.close()


def delete_jan_solr(jancode):
    request = urllib2.Request(
        'http://localhost:8983/solr/[core名]/update?wt=json&indent=true',
        data="<delete><query>janCd:%s</query></delete>" % jancode,
        headers={
            "Content-Type": "text/xml; charset=utf-8",
        },
    )
    json_string = urllib2.urlopen(request).read()
    parsed = json.loads(json_string)
    print parsed


def commit_delete_jan_solr():
    request = urllib2.Request(
        'http://localhost:8983/solr/[core名]/update?wt=json&indent=true',
        data="<commit />",
        headers={
            "Content-Type": "text/xml; charset=utf-8",
        },
    )
    json_string = urllib2.urlopen(request).read()
    parsed = json.loads(json_string)
    print parsed


def execute(jancode, checkonly):
    conn = MySQLdb.connect(host="localhost", db=[db名], user=[user名], passwd=[パスワード], charset="utf8")
    if check_jan_sql(jancode, conn, 1) and check_jan_solr(jancode, 1):
        print 'precheck OK'
    else:
        print 'precheck NG! %s is not in db' % jancode
        sys.exit(1)

    if checkonly:
        conn.close()
        sys.exit(0)

    print 'JanCd: %s OK???' % jancode
    check = raw_input('(yes/no): ')
    if check != "yes":
        sys.exit(0)

    delete_jan_sql(jancode, conn)
    delete_jan_solr(jancode)
    commit_delete_jan_solr()

    if check_jan_sql(jancode, conn, 0) and check_jan_solr(jancode, 0):
        print 'Delete check OK'
    else:
        print 'Delete check NG! %s is in db' % jancode
        sys.exit(1)

    conn.close()


if __name__ == '__main__':
    print 'the jancode delete operation start', datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    parser = argparse.ArgumentParser()
    parser.add_argument('-j', action='store', dest='jan_code',
                        help='Delete the jan code')
    parser.add_argument('--check', action='store_true', dest='checkonly', default=False,
                        help='Check only')
    jancode = parser.parse_args().jan_code
    checkonly = parser.parse_args().checkonly
    if jancode:
        execute(jancode, checkonly)
    else:
        print 'No jan code!'
    print 'the jancode delete operation finish', datetime.now().strftime("%Y-%m-%d %H:%M:%S")