へっぽこエンジニアの覚え書き

主に、バッチとTeraTermマクロのことについて書きます。

Pythonでcsvファイルを読み書きする 

from tkinter import filedialog
typ = [('','*.csv')]
dir = r'C:\work'
csv_file_name = filedialog.askopenfilename(filetypes = typ , initialdir = dir)
print(csv_file_name)

import numpy as np
data = np.loadtxt(csv_file_name, delimiter = ",", dtype = "unicode")

H_Username = data[0][0]
print(H_Username)
H_Password = data[0][1]
print(H_Password)
H_AccesskeyID = data[0][2]
print(H_AccesskeyID)
H_Secretaccesskey = data[0][3]
print(H_Secretaccesskey)
H_Consoleloginlink = data[0][4]
print(H_Consoleloginlink)

B_Username = data[1][0]
print(B_Username)
B_Password = data[1][1]
print(B_Password)
B_AccesskeyID = data[1][2]
print(B_AccesskeyID)
B_Secretaccesskey = data[1][3]
print(B_Secretaccesskey)
B_Consoleloginlink = data[1][4]
print(B_Consoleloginlink)

import numpy as np 
list_rows = [[H_Username,H_Password,H_AccesskeyID,H_Secretaccesskey,H_Consoleloginlink],
[B_Username,B_Password,B_AccesskeyID,B_Secretaccesskey,B_Consoleloginlink]]
np.savetxt(csv_file_name, list_rows, delimiter =",",fmt ='%s')

PySimpleGUIのボタンの書き方に詳しいものが少なかったので自分なりにまとめた

最近、PySimpleGUIにハマってる。
だけど、どう書くか具体的に書いてあるものが少なかったので備忘としてまとめた。
かなりシンプルに書ける。
↓をコピペすれば動くはず。
あとは自分なりに工夫してください。
ここにないのはOKCancelくらいだけどYesNoと同じように書けるので省略。

import PySimpleGUI as sg
sg.theme('LightGrey1')
value = sg.PopupYesNo('実行しますか？YesかNoを選択してください。',title='Choice Yes or No')
if value == 'Yes':
    sg.PopupTimed('なーにーなーにーなーにーなーにー',title='ポコポコ')
    sg.Popup('やっちまたなぁやっちまたなぁやっちまたなぁ',title='ポコポコ')
    sg.PopupCancel('そういえばこのひとたち最近テレビで観ないね',title='ピークを知る人たちなんだね')
elif value == 'No':
    sg.PopupTimed('ヒロシです',title='ヒロシです')
    sg.Popup('ソロキャンプ動画面白いから観てね',title='ヒロシです')
    sg.PopupError('エラーが発生しました。titleを表示させるために意味のない文章を書いてみた',title='特に意味はない')

PySimpleGUIのテーマ一覧

PySimpleGUIのテーマ一覧を出力してみた

import PySimpleGUI as sg
    TL = sg.theme list()

'Black', 'BlueMono', 'BluePurple', 'BrightColors', 'BrownBlue', 'Dark', 'Dark2', 'DarkAmber', 'DarkBlack', 'DarkBlack1', 'DarkBlue', 'DarkBlue1', 'DarkBlue10', 'DarkBlue11', 'DarkBlue12', 'DarkBlue13', 'DarkBlue14', 'DarkBlue15', 'DarkBlue16', 'DarkBlue17', 'DarkBlue2', 'DarkBlue3', 'DarkBlue4', 'DarkBlue5', 'DarkBlue6', 'DarkBlue7', 'DarkBlue8', 'DarkBlue9', 'DarkBrown', 'DarkBrown1', 'DarkBrown2', 'DarkBrown3', 'DarkBrown4', 'DarkBrown5', 'DarkBrown6', 'DarkBrown7', 'DarkGreen', 'DarkGreen1', 'DarkGreen2', 'DarkGreen3', 'DarkGreen4', 'DarkGreen5', 'DarkGreen6', 'DarkGreen7', 'DarkGrey', 'DarkGrey1', 'DarkGrey10', 'DarkGrey11', 'DarkGrey12', 'DarkGrey13', 'DarkGrey14', 'DarkGrey2', 'DarkGrey3', 'DarkGrey4', 'DarkGrey5', 'DarkGrey6', 'DarkGrey7', 'DarkGrey8', 'DarkGrey9', 'DarkPurple', 'DarkPurple1', 'DarkPurple2', 'DarkPurple3', 'DarkPurple4', 'DarkPurple5', 'DarkPurple6', 'DarkPurple7', 'DarkRed', 'DarkRed1', 'DarkRed2', 'DarkTanBlue', 'DarkTeal', 'DarkTeal1', 'DarkTeal10', 'DarkTeal11', 'DarkTeal12', 'DarkTeal2', 'DarkTeal3', 'DarkTeal4', 'DarkTeal5', 'DarkTeal6', 'DarkTeal7', 'DarkTeal8', 'DarkTeal9', 'Default', 'Default1', 'DefaultNoMoreNagging', 'Green', 'GreenMono', 'GreenTan', 'HotDogStand', 'Kayak', 'LightBlue', 'LightBlue1', 'LightBlue2', 'LightBlue3', 'LightBlue4', 'LightBlue5', 'LightBlue6', 'LightBlue7', 'LightBrown', 'LightBrown1', 'LightBrown10', 'LightBrown11', 'LightBrown12', 'LightBrown13', 'LightBrown2', 'LightBrown3', 'LightBrown4', 'LightBrown5', 'LightBrown6', 'LightBrown7', 'LightBrown8', 'LightBrown9', 'LightGray1', 'LightGreen', 'LightGreen1', 'LightGreen10', 'LightGreen2', 'LightGreen3', 'LightGreen4', 'LightGreen5', 'LightGreen6', 'LightGreen7', 'LightGreen8', 'LightGreen9', 'LightGrey', 'LightGrey1', 'LightGrey2', 'LightGrey3', 'LightGrey4', 'LightGrey5', 'LightGrey6', 'LightPurple', 'LightTeal', 'LightYellow', 'Material1', 'Material2', 'NeutralBlue', 'Purple', 'Python', 'Reddit', 'Reds', 'SandyBeach', 'SystemDefault', 'SystemDefault1', 'SystemDefaultForReal', 'Tan', 'TanBlue', 'TealMono', 'Topanga'

すごい数あるから全部試すことはできない。
明るめのテーマに好きなものが多い

PySimpleGUIのOK Cancelボタンと動作の定義 

import PySimpleGUI as sg
#OKのときの処理を定義しておく
def submit_func():
    print ('OK_func')

layout = [[sg.Submit(button_text='OK'), sg.Cancel()]]
window = sg.Window('Sample', layout)

while True:
    event, value = window.read()
#例外処理とCanselのときの動作=break
    if event in [None, 'Cancel']:
        break
#OKのときの動作=submit_func()
    elif event == 'OK':
        submit_func()
        
window.close()

Outputsを短縮記法の組み込み関数でJoinする

短縮記法でOutputsで!Refで取得した値同士を!Joinする方法がわからなかったので自分メモ
論理IDはご自身の環境に合わせて適当に変えてください。

Outputs:
  AZ:
    Value: !GetAtt EC2Linux.AvailabilityZone
  PrivateIp:
    Value: !GetAtt EC2Linux.PrivateIp
  PrivateDnsName:
    Value: !GetAtt EC2Linux.PrivateDnsName
  PublicDnsName:
    Value: !GetAtt EC2Linux.PublicDnsName
  PublicIp:
    Value: !GetAtt EC2Linux.PublicIp
  Plus:
    Value: !Join [ "-", [ !GetAtt EC2Linux.AvailabilityZone, !GetAtt EC2Linux.PublicIp ] ]

AWS EC2+RDSでWordpressを公開できる直前までのCloudFormationテンプレートを公開する

ドメイン登録とhttps化までやろうと思いましたが思ったより時間がないのでテンプレートの公開までにしておきます。
インストールページでRDSのエンドポイントを使います。
出力欄に表示されています。
出力されたIPに/wp-admin/install.phpでアクセスすればWordPressのインストール画面が表示されるはずです。
ドメイン登録とhttps化は時間があればいつかやるかもしれません。
RDSのエンドポイントを取得してmysqlコマンドと組み合わせて[mysql -h {endpoint}(←ここ) -u root -p] userdata内でRDSにログインできれば完璧なんですが日々勉強です。

AWSTemplateFormatVersion: 2010-09-09
Parameters:
  KeyPair:
    Type: AWS::EC2::KeyPair::KeyName

  LinuxLatestAmi:
    Type: AWS::SSM::Parameter::Value<String>
    Default: "/aws/service/ami-amazon-linux-latest/amzn2-ami-hvm-x86_64-gp2"

  InstanceType:
    Type: String
    AllowedValues: ["t2.nano", "t2.micro", "t2.small", "t2.medium", "t2.large", "t2.xlarge", "t2.2xlarge"]
    Default: "t2.micro"

  Prefix:
    Type: String

  DBInstanceName:
    Type: String
    Default: "rds"
    
  MySQLMajorVersion:
    Type: String
    Default: "5.7"
    AllowedValues: [ "5.6", "5.7" , "8.0" ]
    
  MySQLMinorVersion:
    Type: String
    Default: "22"
    
  DBInstanceClass:
    Type: String
    AllowedValues: ["db.t2.micro", "db.t2.small", "db.t2.medium", "db.t2.large", "db.t2.xlarge", "db.t2.2xlarge", "db.t3.micro", "db.t3.small", "db.t3.medium", "db.t3.large", "db.t3.xlarge", "db.t3.2xlarge"]
    Default: "db.t2.micro" 
    
  DBInstanceStorageSize:
    Type: String
    Default: "10"
    
  DBInstanceStorageType:
    Type: String
    Default: "gp2"
    
  DBName:
    Type: String
    Default: "wordpressdb"
    
  DBMasterUserName:
    Type: String
    Default: "root"
    MinLength: 1
    MaxLength: 16
    AllowedPattern: "[a-zA-Z][a-zA-Z0-9]*"
    ConstraintDescription: "must begin with a letter and contain only alphanumeric characters."
    
  DBPassword: 
    Default: "rdspassword"
    NoEcho: true
    Type: String
    MinLength: 8
    MaxLength: 41
    AllowedPattern: "[a-zA-Z0-9]*"
    ConstraintDescription: "must contain only alphanumeric characters."
    
  MultiAZ: 
    Default: "false"
    Type: String
    AllowedValues: [ "true", "false" ]
    
  VPCCIDR:
    Type: String
    Default: "10.0.0.0/16"

  PublicSubnetACIDR:
    Type: String
    Default: "10.0.1.0/24"

  PrivateSubnetACIDR:
    Type: String
    Default: "10.0.10.0/24"

  PrivateSubnetDCIDR:
    Type: String
    Default: "10.0.20.0/24"

  MainAvailabilityZone:
    Type: String
    AllowedValues: [ "ap-northeast-1a", "ap-northeast-1c" ,"ap-northeast-1d" ]
    Default: "ap-northeast-1a"
    ConstraintDescription: "EC2 Instance and RDS location"

  SubAvailabilityZone:
    Type: String
    AllowedValues: [ "ap-northeast-1a", "ap-northeast-1c" ,"ap-northeast-1d" ]
    Default: "ap-northeast-1d"
    ConstraintDescription: "Subnets that make up the DBSubnetGroup. Specify an MainAvailability Zone that is different from EC2 Instance."

Resources:
  VPC:
    Type: AWS::EC2::VPC
    Properties:
      CidrBlock: !Sub ${VPCCIDR}
      EnableDnsSupport: true
      Tags:
        - Key: Name
          Value: !Sub "VPC-${Prefix}"
  InternetGateway:
    Type: AWS::EC2::InternetGateway
    Properties:
      Tags:
        - Key: Name
          Value: !Sub "InternetGateway-${Prefix}"
  AttachGateway:
    Type: AWS::EC2::VPCGatewayAttachment
    Properties:
      VpcId: !Ref VPC
      InternetGatewayId: !Ref InternetGateway
  Route:
    Type: AWS::EC2::Route
    Properties:
      RouteTableId: !Ref RouteTable
      DestinationCidrBlock: 0.0.0.0/0
      GatewayId: !Ref InternetGateway
  RouteTable:
    Type: AWS::EC2::RouteTable
    Properties:
      VpcId: !Ref VPC
      Tags:
        - Key: Name
          Value: !Sub "RouteTable-${Prefix}"
  routeTableAssocName:
    Type: AWS::EC2::SubnetRouteTableAssociation
    Properties:
      SubnetId: !Ref PublicSubnetA
      RouteTableId: !Ref RouteTable
  PublicSubnetA:
    Type: AWS::EC2::Subnet
    Properties:
      AvailabilityZone: !Sub ${MainAvailabilityZone}
      VpcId: !Ref VPC
      CidrBlock: !Sub ${PublicSubnetACIDR}
      MapPublicIpOnLaunch: 'true'
      Tags:
        - Key: Name
          Value: !Sub "PublicSubnetA-${Prefix}"
  PrivateSubnetA:
    Type: AWS::EC2::Subnet
    Properties:
      AvailabilityZone: !Sub ${MainAvailabilityZone}
      VpcId: !Ref VPC
      CidrBlock: !Sub ${PrivateSubnetACIDR}
      MapPublicIpOnLaunch: 'false'
      Tags:
        - Key: Name
          Value: !Sub "PrivateSubnetA-${Prefix}"
  PrivateSubnetD:
    Type: AWS::EC2::Subnet
    Properties:
      AvailabilityZone: !Sub ${SubAvailabilityZone}
      VpcId: !Ref VPC
      CidrBlock:  !Sub ${PrivateSubnetDCIDR}
      MapPublicIpOnLaunch: 'false'
      Tags:
        - Key: Name
          Value: !Sub "PrivateSubnetD-${Prefix}"
  EC2Linux:
    Type: AWS::EC2::Instance
    Properties:
      ImageId: !Ref LinuxLatestAmi
      AvailabilityZone: !Sub ${MainAvailabilityZone}
      InstanceType: !Ref InstanceType
      KeyName: !Ref KeyPair
      SubnetId: !Ref PublicSubnetA
      SecurityGroupIds:
        -  !GetAtt SecurityGroupLinux.GroupId
      UserData: !Base64 |
          #!/bin/bash
              sudo yum -y update
              sudo yum -y install httpd
              sudo systemctl start httpd
              sudo systemctl enable httpd
              chkconfig httpd on
              sudo yum -y install mysql
              sudo amazon-linux-extras install php7.3
              sudo yum -y install php-mbstring php-gd
              sudo systemctl restart httpd
              sudo timedatectl set-timezone Asia/Tokyo
              sudo localectl set-locale LANG=ja_JP.utf8
              mkdir /home/wordpress
              cd /home/wordpress
              wget https://wordpress.org/latest.tar.gz
              tar xzvf latest.tar.gz
              mv wordpress/* /var/www/html
              chown apache.apache -R /var/www/html
              chmod +w -R /var/www/html
      Tags:
      - Key: Name
        Value: !Sub "EC2Linux-${Prefix}"
  eipName:
    Type: AWS::EC2::EIP
    Properties:
      Domain: vpc
      InstanceId: !Ref EC2Linux
  SecurityGroupLinux:
    Type: AWS::EC2::SecurityGroup
    Properties:
      GroupDescription: SGlinux
      VpcId: !Ref VPC
      SecurityGroupIngress:
      - IpProtocol: tcp
        FromPort: '22'
        ToPort: '22'
        CidrIp: 0.0.0.0/0
      - IpProtocol: tcp
        FromPort: '80'
        ToPort: '80'
        CidrIp: 0.0.0.0/0
      - IpProtocol: tcp
        FromPort: '443'
        ToPort: '443'
        CidrIp: 0.0.0.0/0
      Tags:
        - Key: Name
          Value: !Sub "SecurityGroupLinux-${Prefix}"
  rdsdbinstance:
    Type: AWS::RDS::DBInstance
    Properties: 
      AllocatedStorage: !Sub ${DBInstanceStorageSize}
      AllowMajorVersionUpgrade: false
      AutoMinorVersionUpgrade: false
      AvailabilityZone: !Sub ${MainAvailabilityZone}
      BackupRetentionPeriod: 1
      DBInstanceClass: !Sub ${DBInstanceClass}
      DBInstanceIdentifier: !Sub ${DBName}
      DBName: !Sub ${DBName}
      Engine: mysql
      EngineVersion: !Sub ${MySQLMajorVersion}
      MasterUsername: !Sub ${DBMasterUserName}
      MasterUserPassword: !Sub ${DBPassword}
      PubliclyAccessible: false
      StorageType: standard
      DBSubnetGroupName: !Ref DBSubnetGroup
      DBParameterGroupName: !Ref DBParameterGroup
      VPCSecurityGroups:
        - !Ref SecurityGroupRDS
      Tags: 
        - Key: Name
          Value: !Sub "RDS-${Prefix}"
  SecurityGroupRDS:
    Type: AWS::EC2::SecurityGroup
    Properties:
      GroupDescription: SGRDS
      VpcId: !Ref VPC
      SecurityGroupIngress:
      - IpProtocol: tcp
        FromPort: '3306'
        ToPort: '3306'
        SourceSecurityGroupId: !Ref SecurityGroupLinux
      Tags:
        - Key: Name
          Value: !Sub "SecurityGroupRDS-${Prefix}"
  DBSubnetGroup: 
    Type: "AWS::RDS::DBSubnetGroup"
    Properties: 
      DBSubnetGroupName: !Sub "rds-subnet-${Prefix}"
      DBSubnetGroupDescription: "-"
      SubnetIds: 
        - !Ref 'PrivateSubnetA'
        - !Ref 'PrivateSubnetD'
  DBParameterGroup:
    Type: "AWS::RDS::DBParameterGroup"
    Properties:
      Family: !Sub "MySQL${MySQLMajorVersion}"
      Description: !Sub "rds-parm-${Prefix}"

Outputs:
  DBInstanceEndpoint:
    Description: RDS Endpoint Address
    Value: !GetAtt rdsdbinstance.Endpoint.Address
    Export:
      Name: !Sub "endpoint"
  EIP:
    Description: Elastic IP Address
    Value: !Ref eipName
    Export:
      Name: !Sub "EIP"
  ReplicaJDBCConnectionString:
    Description: JDBC connection string for the replica database
    Value: !Join 
      - ''
      - - 'http://'
        - !GetAtt 
          - rdsdbinstance
          - Endpoint.Address