Excelをpythonで操作めちゃ便利(unicodeに変換してくれる)

以下のように使えて、めちゃ便利。
ただし、cell(0, 0) がexcelのA1に対応しており、そこの注意のみ必要。
import csvのようにunicode変換に悩むこともない。

import xlrd
 
book = xlrd.open_workbook('test.xls')

print book.name
sheet1 = book.sheet_by_index(0)
 
cellA1 = sheet1.cell(0, 0) 
cellA2 = sheet1.cell(1, 0) 
cellB1 = sheet1.cell(0, 1) 
cellB2 = sheet1.cell(1, 1)
 
print cellA1.value
print cellA2.value
print cellB1.value
print cellB2.value

MySQL5.6から5.7に変更する際に起こったエラーについて

vagrantsで今までMySQL5.6を使ってたが、速度が3倍になるということで5.7に変更。
はじめ以下の手順で確かめていた。うまくインストールできない。

# エラーの出る手順
[root # ] yum remove mariadb-libs
[root # ] rm -rf /var/lib/mysql/
[root # ] yum localinstall http://dev.mysql.com/get/mysql57-community-release-el6-7.noarch.rpm

[root # ] yum -y install yum-utils
[root # ] yum-config-manager --disable mysql56-community
[root # ] yum-config-manager --enable mysql57-community
[root # ] yum repolist all | grep mysql
利用可能なパッケージ
名前                : mysql-community-server
アーキテクチャー    : x86_64
バージョン          : 5.7.17
リリース            : 1.el6
容量                : 151 M
リポジトリー        : mysql57-community/x86_64
要約                : A very fast and reliable SQL database server
URL                 : http://www.mysql.com/
ライセンス          : Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Under GPLv2 license as shown in the Description field.
説明                : The MySQL(TM) software delivers a very fast, multi-threaded, multi-user,
                    : and robust SQL (Structured Query Language) database server. MySQL Server
                    : is intended for mission-critical, heavy-load production systems as well
                    : as for embedding into mass-deployed software. MySQL is a trademark of
                    : Oracle and/or its affiliates
                    :
                    : The MySQL software has Dual Licensing, which means you can use the MySQL
                    : software free of charge under the GNU General Public License
                    : (http://www.gnu.org/licenses/). You can also purchase commercial MySQL
                    : licenses from Oracle and/or its affiliates if you do not wish to be bound by the terms of
                    : the GPL. See the chapter "Licensing and Support" in the manual for
                    : further info.
                    :
                    : The MySQL web site (http://www.mysql.com/) provides the latest news and
                    : information about the MySQL software.  Also please see the documentation
                    : and the manual for more information.
                    :
                    : This package includes the MySQL server binary as well as related utilities
                    : to run and administer a MySQL server.

[root # ] yum -y install mysql-community-server

...err...
エラー: パッケージ: perl-DBD-MySQL-4.023-5.el7.x86_64 (@base)
             要求: libmysqlclient.so.18()(64bit)
            削除中: mysql-community-libs-5.6.35-2.el6.x86_64 (@mysql56-community)
                libmysqlclient.so.18()(64bit)
            次のものにより更新された: : mysql-community-libs-5.7.17-1.el6.x86_64 (mysql57-community)
               ~libmysqlclient.so.20()(64bit)
            利用可能: 1:mariadb-libs-5.5.52-1.el7.x86_64 (base)
                libmysqlclient.so.18()(64bit)
 問題を回避するために --skip-broken を用いることができます。
 これらを試行できます: rpm -Va --nofiles --nodigest

weblabo.oscasierra.net

mysql関連のsoftwareを根絶やしにしないといけなかった。 その前にpythonを入れる。

AWS(Red Hat)にpython3.6をインストールしてpipやvirtualenvを作成する方法。 - 日に日に分からんことが増えていく…

[root # ] yum remove mariadb-libs
[root # ] rm -rf /var/lib/mysql/
[root # ] yum remove mysql-server mysql-libs mysql-devel mysql*

##URLは最新版をhttps://dev.mysql.com/downloads/repo/yum/にて確認する必要がある
[root # ] yum localinstall http://dev.mysql.com/get/mysql57-community-release-el6-7.noarch.rpm

[root # ] yum-config-manager --disable mysql56-community
[root # ] yum-config-manager --enable mysql57-community
[root # ] yum install mysql mysql-devel mysql-server
[root # ] yum install mysql mysql-utilities

[root # ] rpm -qa | grep mysql
mysql-community-client-5.7.17-1.el7.x86_64
mysql-community-devel-5.7.17-1.el7.x86_64
mysql57-community-release-el7-7.noarch
mysql-community-libs-5.7.17-1.el7.x86_64
mysql-connector-python-1.1.6-1.el7.noarch
mysql-community-server-5.7.17-1.el7.x86_64
mysql-community-libs-compat-5.7.17-1.el7.x86_64
mysql-community-common-5.7.17-1.el7.x86_64
mysql-utilities-1.3.6-1.el7.noarch

[root # ] yum install postfix
[root # ] systemctl enable mysqld.service
[root # ] systemctl start mysqld.service
[root # ] systemctl status mysqld.service

mysqld  Ver 5.7.17 for Linux on x86_64 (MySQL Community Server (GPL))
[root # ] cat /var/log/mysqld.log | grep 'password is generated'
[root # ] mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root: 初期パスワードを入力する

The existing password for the user account root has expired. Please set a new password.

New password: 新しいパスワードを入力する

Re-enter new password: 再度同じ新しいパスワードを入力する

VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?

Press y|Y for Yes, any other key for No: y

There are three levels of password validation policy:

LOW Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 0
Using existing password for root.

Estimated strength of the password: 100 
Change the password for root ? ((Press y|Y for Yes, any other key for No) : y

New password: ポリシーに沿った新しいパスワードを入力

Re-enter new password: 再度新しいパスワードを入力する

Estimated strength of the password: 50 
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
By default, a MySQL installation has an anonymous user, a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y Success.

- Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done! 

[root # ] vim /etc/my.cnf
# 以下を追加
character-set-server = utf8
default_password_lifetime = 0
[root # ] systemctl restart mysqld.service

serverfault.com

blog.ybbo.net

weblabo.oscasierra.net

また、mysql -u rootをパスワードなしで入るには

[root # ] vim /etc/my.cnf
[mysqld]
.
.
skip-grant-tables
.
.

qiita.com

SFTPのみ接続可。sshでのシェルは排除。

設定は以下のようにする。

# groupadd sftp_users

# usermod -G sftp_users mokyu

# vi /etc/ssh/sshd_config
147行目コメント化にしてその下に追記します


# override default of no subsystems
#Subsystem      sftp    /usr/libexec/openssh/sftp-server
Subsystem sftp internal-sftp
#SFTP Chroot化専用設定(最終行に追記)
Match Group sftp_users
   X11Forwarding no
   AllowTcpForwarding no
   ChrootDirectory /home
   ForceCommand internal-sftp

pcmemorin.blog.fc2.com

また、rsshを利用すると、scpやsftpなどコマンドを制約した使い方できるようになる。

rsshによるセキュアなファイル転送

references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use themが出た

このメッセージが出た場合は最初のcreatetableの際に変な設定が入っていることがある。
具体的に言うと後で全部の権限をuserに付与しても中のテーブルは特定のユーザしか見れなくなっている。
dumpしてloadした後にこのようなメッセージが出れば、以下の動作を行えばいい。

CREATE ALGORITHM=UNDEFINED DEFINER=`hoge1`@`%` SQL SECURITY DEFINER VIEW `view_count` AS ...

↓

CREATE ALGORITHM=UNDEFINED DEFINER=`hoge2`@`%` SQL SECURITY DEFINER VIEW `view_count` AS ...

qiita.com