python 读取数据库中数据到csv文件 ansible下发到各个agent上

1.创建数据库连接

def get_postgres_connect():
    import psycopg2
    import psycopg2.extensions
    psycopg2.extensions.register_type(psycopg2.extensions.UNICODE)
    u"""从配置文件获得数据库 connect 对象."""
    config = get_file_config_from_type('install')
    return psycopg2.connect(database=config.get('postgreSQL', 'dbname'),
                            user=config.get('postgreSQL', 'username'),
                            password=config.get('postgreSQL', 'password'),
                            host=config.get('postgreSQL', 'host'),
                            port=config.get('postgreSQL', 'port'),
                            connect_timeout=CONNECT_TIMEOUT)


def get_sql_select_data(sql):
    u"""从配置中读取数据库配置，执行 sql 并返回全部结果."""
    with get_postgres_connect() as connect:
        with connect.cursor() as cur:
            cur.execute(sql)
            all_data = cur.fetchall()
    connect.close()
    return all_data

 

2.写入数据到.csv并下发文件到各个agent上

#!/usr/bin/python
# encoding:utf-8
import sys
import psycopg2
import os
import datetime
from ConfigParser import ConfigParser
import commands
import csv
import glob
import socket
config_file = os.getenv('INSPECTOR_HOME')+'/conf/install.conf'
sys.path.append(os.getenv('INSPECTOR_HOME')+'/plugins/common')
import tools
#csv存储路径
csv_path= os.getenv('INSPECTOR_HOME')+'/conf'    
def remove_csv():
        for infile in glob.glob(os.path.join(csv_path, '*.csv')):
             os.remove(infile)
def get_softwareName_list():
    sql="SELECT a.server_ip,b.software_name from resource.server_software a left join resource.software_manage b on a.software_manage_id=b.id"
    sql_data = tools.get_sql_select_data(sql)
    return sql_data
def get_agent():
    server_ip = socket.gethostbyname(socket.gethostname())
    sql=" select ip from resource.deployed_equipment where server_ip ='"+server_ip+"'"
    sql_data = tools.get_sql_select_data(sql)
    result_list = []
    for row in sql_data:
        result_list.append(row[0])
    agent_str=','.join(result_list)
    print(agent_str)
    return agent_str
def create_csv():
    nowTime=datetime.datetime.now().strftime('%Y%m%d%H%M%S')
    csv_name= ''.join(["softwareName", '_',nowTime,'.csv'])
    cvs_full_name=csv_path+"/"+csv_name  
    return cvs_full_name
def software_to_csv(cvs_full_name,rows):
       with open(cvs_full_name,"w") as f:
            write = csv.writer(f, dialect='excel')
            csv_head = ['IP', 'softwareName']
            write.writerow(csv_head)
            for row in rows:
                print(row)
                write.writerow(row)
def send_csv(agent):
    cmd ='ansible "'+ agent+'" -m copy -a'+'"src='+cvs_full_name+' dest='+csv_path+'"'
    ansibleStatus=commands.getoutput(cmd)
    print(ansibleStatus)

if __name__ == '__main__':
        remove_csv()
        cvs_full_name=create_csv()
        rows=get_softwareName_list()
        software_to_csv(cvs_full_name,rows)
        agent_str=get_agent()
        send_csv(agent_str)