mysql py

__init__.py

#####

config.ini

[mysql]
MYSQL_USER   = root
MYSQL_PWD    = pwd
MYSQL_DB     = my_db
MYSQL_TABLE  = my_tb
MYSQL_IP     = 1.1.1.1
MYSQL_PORT   = 11111

log.py

import datetime, sys

__all__ = [
    'log_info',
    ]

GREEN = '\033[32m'
YELLOW = '\033[33m'
RED = '\033[31m'
LIGHT_BLUE = '\033[36m'
BLUE = '\033[34m'
RED_BLACK = '\033[41m'

COLOR_END = '\033[0m'

# directly use to print log
def log_info(str_trace, level='DEFAULT'):
    LOG_LEVEL = GREEN
    LOG_END = COLOR_END
    if level == 'WARNING':
        LOG_LEVEL = YELLOW
    elif level == 'ERROR':
        LOG_LEVEL = RED
    elif level == 'DEBUG':
        LOG_LEVEL = LIGHT_BLUE
    elif level == 'HIGHLIGHT':
        LOG_LEVEL = RED_BLACK
    print (LOG_LEVEL + r"[%s][%s]:%03d %s" + LOG_END) %(datetime.datetime.now().strftime('%y%m%d-%H:%M:%S'),
        sys._getframe().f_back.f_code.co_name,
        sys._getframe().f_back.f_lineno,
        str_trace)

mysql_unity.py

import MySQLdb
import log



class mysql_unity(object):
    def __init__(self, Host, Port, User, PWD, DB):
        self.host = Host
        self.port = Port
        self.user = User
        self.pwd = PWD
        self.db = DB
        self.conn = self.__conn__(Host, Port, User, PWD, DB)
        log.log_info(">>>>>>>>conn %s, %d, %s, %s, %s" % (Host, Port, User, PWD, DB), "DEBUG")


    def __del__(self):
        log.log_info("<<<<<<<<close conn %s, %d, %s, %s, %s" % (self.host, self.port, self.user, self.pwd, self.db), "DEBUG")
        self.conn.close()


    def __conn__(self, Host, Port, User, PWD, DB):
        try:
            conn = MySQLdb.connect(host = Host, port = Port, user = User, passwd = PWD, db = DB)
        except:
            log.log_info("conn err", "ERROR")
        return conn

    def get_conn(self):
        return self.conn

    def check_table_exists(self, table):
        with self.conn:
            datas = self.query("select count(*) from information_schema.TABLES where (TABLE_SCHEMA = '%s') AND (TABLE_NAME = '%s')" %\
                (self.db, table))
            if datas[0][0] > 0:
                return True
            else:
                return False

    def query(self, select):
        with self.conn:
            cur = self.conn.cursor()
            try:
                cur.execute(select)
                rows = cur.fetchall()
                desc = cur.description
                return rows
            except:
                log.log_info("select err", "ERROR")

    def exec_sql(self, sql_str):
        with self.conn:
            cur = self.conn.cursor()
            try:
                cur.execute(sql_str)
                self.conn.commit()
            except:
                self.conn.rollback()


def main():
    conn_handle = mysql_unity("", 3306, "", "", "")
    datas = conn_handle.query("select * from tb")
    print datas


if __name__=="__main__":
    main()

prepare_tables.py

import datetime
import mysql_unity
import log
import read_config


class prepare_tables(read_config.read_config):
    def __init__(self):
        read_config.read_config.__init__(self)
        self.raw_handle = mysql_unity.mysql_unity(self.mysql_ip, self.mysql_port, self.mysql_user, self.mysql_pwd, self.mysql_db)
        self.raw_conn = self.raw_handle.get_conn()
        self.raw_cur = self.raw_conn.cursor()
        self.today = datetime.date.today()

    def __del__(self):
        pass


    def clean_hit_table(self):
        for i in range(0, self.mysql_split):
            table = "%s_%04d%02d%02d" % (self.mysql_table, self.today.year, self.today.month, self.today.day)
            if self.raw_handle.check_table_exists(table):
                self.raw_cur.execute("DROP TABLE IF EXISTS %s" % table)


    def create_hit_table(self):
        for i in range(0, self.mysql_split):
            table = "%s_%04d%02d%02d" % (self.mysql_table, self.today.year, self.today.month, self.today.day)
            if not self.raw_handle.check_table_exists(table):
                self.raw_cur.execute("CREATE TABLE %s(id INT PRIMARY KEY AUTO_INCREMENT, number INT, \
                        last_update TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT NOW())" % table)
                self.raw_cur.execute("ALTER TABLE %s add index my_id(number)" % table)
                log.log_info("create table %s" % table, "DEBUG")



def main():
    obj = prepare_tables()
    obj.clean_table()
    obj.create_table()



if __name__ == "__main__":
    main()

read_config.py

import ConfigParser
import log


class read_config(object):
    def __init__(self, config_file="config.ini"):
        log.log_info("read config %s" % (config_file), "DEBUG")
        self.config = ConfigParser.ConfigParser()
        self.config.read(config_file)
        self.mysql_user   = self.config.get('mysql', 'MYSQL_USER')
        self.mysql_pwd    = self.config.get('mysql', 'MYSQL_PWD')
        self.mysql_db     = self.config.get('mysql', 'MYSQL_DB')
        self.mysql_table  = self.config.get('mysql', 'MYSQL_TABLE')
        self.mysql_ip     = self.config.get('mysql', 'MYSQL_IP')
        self.mysql_port   = int(self.config.get('mysql', 'MYSQL_PORT'))


    def __del__(self):
        pass



def main():
    obj = read_config("config.ini")


if __name__=="__main__":
    main()