EXcelファイルを読んでmysqlに接続する

brewのインストールhttps://brew.sh/index_zh-cn.html

brew install python-pip

pip install xlrd

pip install pymysql

pip install psycopg2

pip install redis

codeは以下の

である.

# -*- coding: UTF-8 -*-

import xlrd
import pymysql.cursors
import psycopg2
import redis

myaql_config = {
    'host': '127.0.0.1',
    'port': 3306,
    'password': '',
    'user': 'root',
    'cursorclass': pymysql.cursors.DictCursor,
}

pq_config = {
    'host': '127.0.0.1',
    'port': 5432,
    'password': '',
    'user': 'post',
}

redis_config = {
    'host': '127.0.0.1',
    'port': 6379,
    'socket_timeout': 5
}

db_mode = {'mysql': myaql_config,
           'pq': pq_config,
           'redis': redis_config}


class Config(object):
    def __init__(self, host, port, user):
        self.host = host
        self.port = port
        self.user = user


def open_excel(file='file.xls'):
    try:
        data = xlrd.open_workbook(file)
        return data
    except Exception as e:
        print(str(e))


# filepath     
# by_index sheet   
# column_index        
def get_excel_datas(filepath='file.xls', by_index=0, column_index=0):
    data = open_excel(filepath)
    table = data.sheets()[by_index]
    nrows = table.nrows  #   
    list = []
    for rownum in range(0, nrows):
        row = table.row_values(rownum)
        if row and len(row) > column_index:
            list.append(row[column_index])
    return list


def get_ips(file):
    return get_excel_datas(file, column_index=1)


def get_ports(file):
    return get_excel_datas(file, column_index=2)


def get_users(file):
    return get_excel_datas(file, column_index=3)


def get_passwords(file):
    return get_excel_datas(file, column_index=4)


if __name__ == '__main__':
    input_mode = raw_input('please input db type: [mysql / pq / redis]
')
    if db_mode.has_key(input_mode):
        config = db_mode[input_mode]
        ports = get_ports('demo.xlsx')
        users = get_users('demo.xlsx')
        passwords = get_passwords('demo.xlsx')
        index = 0
        port = ''
        user = ''
        password = ''
        for ip in get_ips('demo.xlsx'):
            if index < len(ports):
                port = ports[index]
            if index < len(users):
                user = users[index]
            if index < len(passwords):
                password = passwords[index]
            try:
                # Connect to the database=
                config['host'] = ip
                if input_mode == 'mysql':
                    connection = pymysql.connect(**config)
                elif input_mode == 'pq':
                    if user:
                        config['user'] = user
                    if port:
                        config['port'] = (int)(port)
                    if password:
                        config['password'] = password
                    connection = psycopg2.connect(**config)
                elif input_mode == 'redis':
                    connection = redis.Connection(**config)
                    connection.connect()
                print connection
            except Exception, e:
                print ip, e
            finally:
                index += 1
    else:
        print 'input wrong db type'