Synology NAS DS218を使ってmysqlを使ってみる

# mysqlを使ってみる
synology nasが家にあるのでSQLサーバーというものを立ててみたいのでやってみた

## sqlサーバーを有効にする
Diskstationで使えるアプリケーションにMYSQLのMARIA DBがあったのでインストールする
ついでにPhyMyAdminをインストールしてGUIでデータベースの設定を行えるようにする

## ユーザーの登録
PhyMyAdminにrootでログインした後、ユーザーアカウントとデータベースを作成する

<img width="438" alt="スクリーンショット 2020-04-05 16.55.03.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/181375/88e177db-d5ec-c507-0fba-c71a1cc4ebc9.png">

ユーザーアカウントを登録して、全ての権限を有効化
rootアカウントの権限を無効にしておく。

## データーベースの作成
登録したユーザーアカウントでログインし直して、次はデーターベースを作成する

<img width="510" alt="スクリーンショット 2020-04-05 16.56.44.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/181375/d28f5fb3-0164-1d5e-41a6-8b5412e205f9.png">

## テーブルの作成
データベースを作成したら、左端のデータベースリストに登録したデータベースが存在しているはずなので、リストを開いてみると「新規作成」という項目がある。それをクリックするとテーブルの作成メニューになる。
このメニューから好きなテーブルを登録する
<img width="616" alt="スクリーンショット 2020-04-05 16.57.55.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/181375/f9a61a47-f1d5-c515-e9c5-036421a82b2e.png">

mysqlでテーブルを作成に成功したら、サーバー上のデータベースへクライアントPCからアクセスしてみる  

```sh
mysql -u user_id -p -h server_addr -P port_num
Enter password:*******
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| mydatabase         |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
mysql> use mydatabase;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-------------------+
| Tables_in_finance |
+-------------------+
| date              |
+-------------------+
1 row in set (0.01 sec)

mysql> 
```
このようにデータベースにアクセスできる

## テーブルの作成
sqlサーバーへのアクセスとデータベースの確認ができたので、テーブルを作成してみる

```sh
mysql > use mydatabase
mysql > create table sample_data (
    -> id int auto_increment not null primary key,
    -> str char(8) not null,
    -> date timestamp not null);
mysql > show tables;
+-------------------+
| Tables_in_finance |
+-------------------+
| date              |
| sample_data       |
+-------------------+

mysql > describe sample_data;
+-------+-----------+------+-----+---------------------+-------------------------------+
| Field | Type      | Null | Key | Default             | Extra                         |
+-------+-----------+------+-----+---------------------+-------------------------------+
| id    | int(11)   | NO   | PRI | NULL                | auto_increment                |
| str   | char(8)   | NO   |     | NULL                |                               |
| date  | timestamp | NO   |     | current_timestamp() | on update current_timestamp() |
+-------+-----------+------+-----+---------------------+-------------------------------+
3 rows in set (0.00 sec)
```
この方法で、テーブルデータの作成とその確認ができる

## データの追加
データの追加には``` INSERT ```を使う
https://dev.mysql.com/doc/refman/5.6/ja/insert.html
構文は
``` INSERT INTO table名 (列1,列2,列3,...) VALUES (値1,値2,値3,...) ```
となっている。

```sh
mysql> INSERT INTO sample_data (str,date) VALUES ("お菓子",NOW());
Query OK, 1 row affected (0.86 sec)

mysql> INSERT INTO sample_data (str,date) VALUES ("ご飯",NOW());
Query OK, 1 row affected (0.25 sec)
```
``` id```は``` auto_increment ```設定にしているので列指定では省略した。
``` NOW() ```はtimestamp型のデータで現在時刻を取得する関数

## データを取得
先ほど登録したデータをみたい場合は``` SELECT ```を使う
https://dev.mysql.com/doc/refman/5.6/ja/select.html
構文は、
``` SELECT 取得するカラム FROM テーブル名 ```
他にもWHEREで取ってきたりいろいろなやり方はあるので、マニュアル参照

```sh
SELECT * FROM sample_data;
+----+-----------+---------------------+
| id | str       | date                |
+----+-----------+---------------------+
|  1 | お菓子    | 2020-04-16 10:55:25 |
|  2 | ご飯      | 2020-04-16 10:55:47 |
+----+-----------+-------

```

# pythonからのアクセス
pythonからデータベースにアクセスできた方が便利なので、その方法を記載する
pythonではmysqlアクセスするライブラリ``` pymysql ```を使用する。

## login

```py
import pymysql.cursors

def main():
    # my sqlに接続する
    connection = pymysql.connect(
        user='user_id',
        passwd='password',#適宜自分で設定したパスワードに書き換えてください。
        host='server_addr',#接続先DBのホスト名或いはIPに書き換えてください。
        port = port_num,
        db='mydatabase',
        charset='utf8mb4',
        cursorclass=pymysql.cursors.DictCursor)
main()
```

## data base内一覧を取得する
mysqlのselect文でデータベース一覧を取得してみる

```python
   cursor = connection.cursor()
   sql_state = "describe {}".format("sample_data")
   cursor.execute(sql_state)
   result = cursor.fetchall()
   for i in result:
       print(i)
```

## 登録してあるデータを取得する


```python
   cursor = connection.cursor()
   sql_state = "SELECT * FROM {}".format("sample_data")
   cursor.execute(sql_state)
   result = cursor.fetchall()
   for i in result:
       print(i)
```

## データの追加
データの追加はINSERT文で可能
SQLで現在時刻など時刻を入力したい場合は、Tableの時刻のフォーマットに合わせる
sample_dataテーブルは```年-月-日 時:分:秒```の形式なので``` time ```ライブラリを使用して時刻データを挿入する

```python
   import time    
   now = time.strftime('%Y-%m-%d %H:%M:%S')
   cursor = connection.cursor()
   table_name = "sample_data"
   sql_state = "INSERT INTO {} (str,date) VALUES (%s, %s )".format("sample_data")
   cursor.execute(sql_state,("お菓子",now))
   connection.commit()
```
これを``` SELECT ```文で書き込めているか確認

```python
   sql_state = "SELECT * FROM {}".format("sample_data")
   new_table_name = "sample_data"
   cursor.execute(sql_state)
   result = cursor.fetchall()
   for i in result:
       print(i)
```

出力結果  

```py
{'id': 1, 'str': 'お菓子', 'date': datetime.datetime(2020, 4, 16, 10, 55, 25)}
{'id': 2, 'str': 'ご飯', 'date': datetime.datetime(2020, 4, 16, 10, 55, 47)}
{'id': 3, 'str': 'パン', 'date': datetime.datetime(2020, 4, 20, 10, 43, 17)}
```

基本的なmysqlサーバーへのアクセスとテーブルの登録、データの追加がこれでできた。

## ２０２1.3 追記
### MySQLで型の変更を行う
データ構造の設計でfloat型出ないといけないところを間違えてint型で設計してしまって、かつデータを登録した場合の対象方法
priceの値が整数型で良いつもりが、端数がありえたため本来はfloat型にしないとダメだった。

```sh
mysql> use database_name;
mysql> show tables;
+-------------------+
| Tables_in_finance |
+-------------------+
| sql_data          |
+-------------------+
mysql> describe sql_data
+------------------------+---------+------+-----+---------+----------------+
| Field                  | Type    | Null | Key | Default | Extra          |
+------------------------+---------+------+-----+---------+----------------+
| id                     | int(11) | NO   | PRI | NULL    | auto_increment |
| date                   | date    | NO   |     | NULL    |                |
| code                   | int(11) | NO   |     | NULL    |                |
| price                  | int(11) | YES  |     | NULL    |                |
| high_price             | int(11) | YES  |     | NULL    |                |
+------------------------+---------+------+-----+---------+----------------+
mysql> ALTER TABLE sql_data MODIFY price float
mysql> describe sql_data
+------------------------+---------+------+-----+---------+----------------+
| Field                  | Type    | Null | Key | Default | Extra          |
+------------------------+---------+------+-----+---------+----------------+
| id                     | int(11) | NO   | PRI | NULL    | auto_increment |
| date                   | date    | NO   |     | NULL    |                |
| code                   | int(11) | NO   |     | NULL    |                |
| price                  | float   | YES  |     | NULL    |                |
| high_price             | int(11) | YES  |     | NULL    |                |
+------------------------+---------+------+-----+---------+----------------+

```
float変換するとデータを一括で型変換してくれるが時間がかかる