mysqlを使ってみる

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

sqlサーバーを有効にする

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

ユーザーの登録

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

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

データーベースの作成

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

テーブルの作成

データベースを作成したら、左端のデータベースリストに登録したデータベースが存在しているはずなので、リストを開いてみると「新規作成」という項目がある。それをクリックするとテーブルの作成メニューになる。
このメニューから好きなテーブルを登録する

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

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サーバーへのアクセスとデータベースの確認ができたので、テーブルを作成してみる

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,...)
となっている。

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で取ってきたりいろいろなやり方はあるので、マニュアル参照

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

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文でデータベース一覧を取得してみる

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

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

   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ライブラリを使用して時刻データを挿入する

   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文で書き込めているか確認

   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)

出力結果

{'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型にしないとダメだった。

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変換するとデータを一括で型変換してくれるが時間がかかる