[PYTHON] Automatically create Ansible configuration files (vars) from excel (Ansible)
Those who would be happy if you could read it
・ Those who are building servers or introducing MW with Ansible in the infrastructure department ・ Those who find it troublesome to create inventory files and playbooks
Execution environment
- python3.5
- jinja2
- vagrant(box:bento/centos-6.7)
- Contents of vagrant file
Vagrant.configure(2) do |config|
config.vm.box = "bento/centos-6.7"
config.vm.define "dev" do |node|
node.vm.network "private_network", ip: "192.168.10.10"
node.vm.synced_folder "/Users/takahirono7/projects/", "/root/project"
end
end
- Setting synced_folder is very convenient because you can sync directories on your local Mac and Vagrant.
- The method is summarized below
Synchronize files between guest OS running on Vagrant and Mac
Introduction
・ I am a person in the infrastructure department who often checks the requirements around MW, builds, tests, and delivers them according to the requirements of the business side.
・ In my department, I get requirements from development, and based on that, I drop the instance information into a vars file and build it with Ansible. -It is an image to deploy the information for each instance with a vars file as shown below.
The vars file looks like this:
yaml
apache_instance:
instance_name: 'SAMPLE_InstanceA'
server_name: 'sample.jp'
instance_user:
name: 'apache_user'
instance_settings:
chkconfig: false
add_encodings:
- 'x-compress Z'
- 'x-gzip gz'
add_languages:
- 'ja .ja'
- 'en .en'
- 'fr .fr'
start_servers: '5'
min_spare_servers: '5'
max_spare_servers: '10'
server_limit: '512'
max_request_workers: '150'
max_connections_perchild: '0'
As mentioned above, the above files are prepared for each instance.
This file is created based on the ** "requirement sheet" ** (excel) passed from the developer in the following format.
Aside from the fact that it is inefficient, isn't it a certain way of building infrastructure?
The actual files are longer and more complex, so creating Vars from them can be a daunting task.
I have created a Python app that ** automates this **.
Mounting method
The premise is that Excel is once replaced with a text file, which is loaded into the app and converted to yaml format vars.
In python, there is an xlrd module as a module that touches Excel. At first, I was trying to implement it with that, but since there was an internal problem such as increasing the columns and rows of Excel at the requester without permission, I chose the implementation to change it to text once.
If the application method is that the Excel sheet is fixed and the number of rows and columns does not increase, I think that it will be easier to implement using the above module.
** Basically, a text file (originally Excel) is stored in a dictionary with a split module for each parameter in python, and finally rendered in a jinja2 template and output **
Since the internal information is deleted from the script, it is impossible to execute it as it is, but I think that you can refer only to the essence m (_ _) m I'm sorry that python is still writing, so it's dirty.
Tool configuration
Directory structure
/root/project/convert_hearing_sheet_into_vars
|--README.md
|--hearing_sheets #Place the text file converted by the macro(See below)
| |--Apache.txt #Excel converted text file
|--main.py #Start each module
|--scripts
| |--__init__.py
| |--create_dict_from_apache_sheet.py
| |--common_method.py #Describe common processing
|--config.json
|--output
| |--apache_hogehoge_instance.yml #The vars file converted from txt is placed
|--vars_template
| |--[template]apache_vars.j2
main.py Call each module
#!/usr/bin/env python
# -*- coding: utf-8 -*-
# -*- mode: python;Encoding: utf8n -*-
### import
#-----------------------------------------------
import sys
import os
from scripts.create_dict_from_apache_sheet import CreateDictFromApacheSheet
from scripts.common_method import CommonMethod
#-----------------------------------------------
###Command line argument processing
#-----------------------------------------------
# $1 = run.py
# $2 =Executable file name
argvs = sys.argv
argc = len(argvs)
if (argc != 2):
print(
'Usage: # python %s filename {dest file name}' % argvs[0])
sys.exit(1)
excelfile = argvs[1]
#===============================================
#File path, common variable definition
#===============================================
script_dir = os.path.abspath(os.path.dirname(__file__))
#Reading a text file converted by a macro
apache_file = os.path.join(
script_dir, "hearing_sheets", excelfile)
#Instantiate a method class for common processing
method = CommonMethod()
config_lines = method.open_file(apache_file)
#Get the row containing the instance name and create a list of instances
inst_list_apache = []
for i in config_lines:
if i.find("Instance name") >= 0:
inst_list_apache.append(i)
target_instances = method.split_by_tab(inst_list_apache)
print("Number of instances")
print(len(target_instances) - 1)
print("Execute the process for the following instances")
for instance in target_instances[1:]:
print(instance)
#Perform processing for each instance
#Also get the index number using enumerate
print("Start processing")
for index, target_instance_name in enumerate(target_instances[1:]):
print(index, target_instance_name)
apache_dict = CreateDictFromApacheSheet(script_dir)
apache_dict.exec(config_lines, index, target_instance_name)
print("Processing Exit")
create_dict_from_apache_sheet.py
#!/usr/bin/env python
# -*- coding: utf-8 -*-
# -*- mode: python;Encoding: utf8n -*-
### importfile
#-------------------------------------------------------
import sys, os, copy, platform, codecs, re, traceback, json # @UnusedImport
from collections import defaultdict
from os.path import join, dirname, abspath, isfile
from jinja2 import Environment, FileSystemLoader
from scripts.common_method import CommonMethod
class CreateDictFromApacheSheet(CommonMethod):
def __init__(self, script_dir):
self.script_dir = script_dir
def exec(self, config_lines, index, target_instance_name):
###############File path, common variable definition#############
#Extract the parameters of instance
main_dict_apache = self.extract_instance_params(
config_lines,
index,
target_instance_name)
#Output vars file
self.output_vars_files(
index, target_instance_name, main_dict_apache)
def extract_instance_params(self,
config_lines,
index,
target_instance_name):
apache_dict = defaultdict(dict)
json_file = os.path.join(self.script_dir, "config.json")
#Read json file
with open(json_file) as f:
json_dict = json.load(f)
apache_dict["instance_name"] = target_instance_name
# config.Store the dictionary stored in json for each parameter
for parameter in json_dict["apache"]["APACHE_INSTANCE"]:
version_list = []
parameter_key = list(parameter.keys())
parameter_value = list(parameter.values())
#Convert to str.If you try to use it as a list, Can't convert error occurs
parameter_key_str = parameter_key[0]
parameter_value_str = parameter_value[0]
for i in config_lines:
if i.find(parameter_key_str) >= 0:
version_list.append(i)
version_params = self.split_by_tab(version_list)
apache_dict[parameter_value_str] = version_params[index + 1]
return apache_dict
#Get the jinja2 template and apache_Render and output the contents of the dict
def output_vars_files(self, index, target_instance_name, main_dict_apache):
template_dir = os.path.join(
self.script_dir, "vars_template", site)
jinja_env = Environment(
trim_blocks=True, loader=FileSystemLoader(template_dir))
vars_file = os.path.join(self.script_dir, "output", "instance_%s_%s.yml" %(target_instance_name))
tmpl = jinja_env.get_template("[template]apache_vars.j2")
with open(vars_file, "wb") as f:
f.write(tmpl.render(main_dict_apache).encode("utf-8"))
[template]apache_vars.j2 It becomes a template for vars. The value of the dictionary created by create_dict_from_apache_sheet.py can be applied to this template.
apache_instance:
instance_name: '{{ instance_name }}'
server_name: '{{ server_name }}'
instance_user:
name: '{{ instance_user }}'
instance_settings:
chkconfig: false
add_encodings:
- 'x-compress Z'
- 'x-gzip gz'
add_languages:
- 'ja .ja'
- 'en .en'
- 'fr .fr'
start_servers: '{{ start_servers }}'
min_spare_servers: '{{ min_spare_servers }}'
max_spare_servers: '{{ max_spare_servers }}'
server_limit: '{{ server_limit }}'
max_request_workers: '{{ max_request_workers }}'
max_connections_perchild: '{{ max_connections_perchild }}'
config.json
This file associates the Excel hearing sheet with the key of the dictionary held in the script.
json
{
"apache" : {
"APACHE_INSTANCE": [
{"ServerName" : "server_name"},
{"User" : "instance_user"},
{"AddEncoding" : "add_encodings"},
{"AddLanguage" : "add_languages"},
{"StartServers" : "start_servers"},
{"MinSpareServers" : "min_spare_servers"},
{"MaxSpareServers" : "max_spare_servers"},
{"ServerLimit" : "server_limit"},
{"MaxClients (MaxRequestWorkers)" : "max_request_workers"},
{"MaxRequestsPerChild(MaxConnectionsPerChild)" : "max_connections_perchild"}
]
}
}
Tool steps
The specific procedure is as follows.
- Convert Excel hearing sheet to text file using macro
- Place the created text file in the hearing_sheets directory
- Tool execution The vars file is spit out in the output directory It will be the flow
Convert Excel Hearing Sheet to Text File Using Macro
First, execute the following macro to convert the excel file to text for each sheet. Please note that there is a place in the macro where the path of the local directory is described.
The macro quotes the following article. Thank you m (_ _) m http://d.hatena.ne.jp/minamijoyo/20080124/p1
vba
Sub OutputTEXT()
'Save all active workbook sheets as tab-delimited text
Dim name As String
Dim outdir As String
Dim sheet As Worksheet
'Please specify the discharge destination folder
outdir = "D:\Users\hogehoge\Documents\"
'Hide overwrite warning if data already exists
Application.DisplayAlerts = False
For Each sheet In Worksheets
sheet.Select
name = outdir & ActiveSheet.name & ".txt"
ActiveWorkbook.SaveAs Filename:=name, FileFormat:=xlText, CreateBackup:=False
Next sheet
'Return warning display
Application.DisplayAlerts = True
End Sub
When you do this, the text will be spit out in the destination folder as below
Apache.txt
Instance name SAMPLE_InstanceA SAMPLE_InstanceB SAMPLE_InstanceC
Auto start setting No No No No
ServerName sample.jp sample.jp_B sample.jp_C
User apache_user apache_user apache_user
StartServers 5 5 5
MinSpareServers 5 5 5
MaxSpareServers 10 10 10
ServerLimit 512 512 512
MaxClients (MaxRequestWorkers) 150 150 150
MaxRequestsPerChild(MaxConnectionsPerChild) 0 0 0
Place the created text file in the hearing_sheets directory
Place the above file in the tool's hearing_sheets directory
Tool execution
[root@localhost convert_hearing_sheet_into_vars]# ./main.py apache
Number of Apache instances
3
Execute the process for the following Apache instance
SAMPLE_InstanceA
SAMPLE_InstanceB
SAMPLE_InstanceC
vars are spit out to the output directory
Recommended Posts