mysql_example.py

#!/usr/bin/env python3.6
import argparse
import logging
from mysql import connector

MYSQL_DB = "XXX"
MYSQL_HOST = "XXX"
MYSQL_TCP_PORT = 3306
BATCH_SIZE = 100
CONNECTION_TIMEOUT_S = 1000


def get_args():
    parser = argparse.ArgumentParser()
    parser.add_argument('--mysql-user', required=True)
    parser.add_argument('--mysql-password', required=True)

    args = parser.parse_args()

    return args


def exec_sql(connection, sql_query):
    sql = "{} LIMIT {}".format(sql_query, BATCH_SIZE)
    logging.info("Executing '{}'".format(sql))
    mycursor = connection.cursor()
    try:
        mycursor.execute(sql)
        connection.commit()
        logging.info("{} rows affected".format(mycursor.rowcount))
        row_count = mycursor.rowcount
    finally:
        mycursor.close()
    return row_count


def exec_sql_for_all(connection, sql_query):
    affected = None
    logging.info("Executing '{}' for all records".format(sql_query))
    while affected != 0:
        affected = exec_sql(connection, sql_query)
    logging.info("All records that could be affected by '{}' have been processed".format(sql_query))


#
#
# def exec_query(connection, sql_query):
#     sql = "{} LIMIT {}".format(sql_query, BATCH_SIZE)
#     logging.info("Executing '{}'".format(sql))
#     mycursor = connection.cursor()
#     mycursor.execute(sql)
#     for row in mycursor:
#         logging.info("Result row: {}".format(row))
#     return mycursor.rowcount


def main(args):
    logging.basicConfig(level=logging.INFO)
    logging.info("Connecting to mysql db {} on {} / {}...".format(MYSQL_DB, MYSQL_HOST, MYSQL_TCP_PORT))

    connection = connector.connect(
        host=MYSQL_HOST,
        port=MYSQL_TCP_PORT,
        database=MYSQL_DB,
        user=args.mysql_user,
        passwd=args.mysql_password,
        connection_timeout=CONNECTION_TIMEOUT_S
    )
    logging.info("Connected!")

    # exec_query(connection, "select * FROM zipkin_annotations")
    exec_sql_for_all(connection, "DELETE FROM zipkin_annotations WHERE 1=1")
    exec_sql_for_all(connection, "DELETE FROM zipkin_spans WHERE 1=1")


if __name__ == '__main__':
    main(get_args())