【MySQL】データベース使ってみた & クエリメモ

こんにちはRcatです。
唐突ですがデータベース使います。
なぜ使いたいかというのは最近出してるAI系の記事に関連します。
とりあえずセットアップなどを行っていくので、メモ書きとして残しておこうと思います。

本記事は完全初心者が初めてデータベースを導入する記事です。
内容を過信しないように注意してください。

DIfy系目次記事

データベースって何?

私も初めて使うので具体的な説明は避けますが、その名の通りデータを保管しておく場所です。データベースには様々な種類があり、今回は関係データベースを使います。関係データベースは簡単に言えばエクセルです。2次元の表があり、その中でデータを管理していくものです。
そして、今回使っていく関係データベースはオープンソースのMySQLです。

導入する

インストールする

まずはインストールを行います。
下記コマンドを実行してください。

sudo apt install mysql-server

インストールが完了したらデータベースを起動します。
再起動時に必ず起動するようにenableも行います。

sudo systemctl start mysql
sudo systemctl enable mysql

初期セットアップをする

初期セットアップ用のコマンドがあるようなので、それを使用してセットアップしていきます。

sudo mysql_secure_installation

色々聞かれるのですが基本y。念のため翻訳しながら進めましょう。
ルートのパスワードはこの時点では空らしいので、終わったらログインして設定します。

sudo mysql
#ログイン後パスワードを設定
ALTER USER 'root'@'localhost' identified BY 'パスワード'

使ってみる

データベースを作成

下記のように作成と表示ができます

mysql> CREATE DATABASE New1;
Query OK, 1 row affected (0.03 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| New1               |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

テーブルの作成

データベースはエクセルで言うとブックみたいなものでしょうか？
まだ1枚もシートがないので、新しくシートを作る作業がテーブルの作成のようです。
ただし、テーブルを作成する時点で、各カラムにどのようなデータが入るのか、どういう名前を割り当てるのかなど決めることができるみたいです。
今回は関係データベースなので必ずキーとなる行が必要なので、お決まりのID用と単純に文字列を入力できるようにしてみました。

mysql> use New1
Database changed
mysql> CREATE TABLE test (
    -> id  INT AUTO_INCREMENT PRIMARY KEY,
    -> val TEXT
    -> );
Query OK, 0 rows affected (0.11 sec)

データの書き込み

次のように名前を指定して新しい行を追加することができます。

mysql> INSERT INTO test (val) VALUES ("にゃーん");
Query OK, 1 row affected (0.02 sec)

データの確認

入力したデータは次のように確認できます。
うまくいったようですね。

mysql> SELECT * FROM test;
+----+--------------+
| id | val          |
+----+--------------+
|  1 | にゃーん     |
+----+--------------+
1 row in set (0.00 sec)

ユーザーを作成する

root以外の一般ユーザーや外部接続用のリモートユーザーを作ります

ユーザの作成

@の後ろは許可するIPアドレス。安全のためローカルにした方がいいでしょう。%はなんでもという意味です。

CREATE USER 'remote'@'192.168.0.%' IDENTIFIED BY 'PASSWD';

権限付与

ユーザーを作ったら権限を与えます。
今回はリモート作業用なので全権限です。

GRANT ALL PRIVILEGES ON *.* TO 'remote'@'192.168.0.%';

外部アクセスを許可する

初期だとリモートからのアクセスができないので許可します

sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

# If MySQL is running as a replication slave, this should be
# changed. Ref https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_tmpdir
# tmpdir                = /tmp
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address           = 127.0.0.1  <-ここをコメント化する 
mysqlx-bind-address     = 127.0.0.1

バックアップからリストアする

mysqldumpで作ったバックアップがある場合はリストアすることができます。

リストアコマンド

$ mysql -h 192.168.0.250 -u remote -pPASSWD DB名 < dump.sql

先ほど作ったリモートユーザーを使ってリストアします。
-hを使うことでリモートログイン、-pに直接パスワードを繋げることで、パスワード入力無しでログイン、その後ろにdb名を指定してuseを行ったことにしたうえで、入力リダイレクトでdumpしたSQLファイルをそのまま入力します。

Pythonからアクセスする

さて、ここからが本番です。
Pythonから使うのが目的なので早速試していきます。

ライブラリをインストールする

ライブラリがあるみたいなので、そちらを使っていきます。

pip install mysql-connector-python

Pythonからアクセスする

アクセスと表示

>>> import mysql.connector
>>> c = {'user':"root","password":"pass","host":"localhost","databese":"new1"}
>>> conn = mysql.connector.connect(**c)
>>> cursor = conn.cursor()
>>> cursor.execute('SELECT * FROM test')
>>> rows = cursor.fetchall()
>>> rows
[(1, 'にゃーん')]

とりあえず参考手順通りやってみました。
まず、最初にコネクションを確立してコネクションオブジェクトを取得する。
次に操作を行うためのカーソルオブジェクトを取得。
そこからSQLのコマンドを実行という感じのようです。先ほど表示に使ったコマンドと同じものを入力しています。
実行した結果を取得。
最後に表示ですね。

データの入力

次に入力です。
先ほど入力したのと同じコマンドをexecuteします。
ただ、その後コミットという行が追加されています。
これはデータベース内で矛盾を発生させないためのもので、コミットを実行した瞬間にデータの変更を反映させます。
もし他の操作が割り込んでうまくいかなかった場合はこれが失敗し、データの整合性が保たれるというわけですね。
そしてもう一度取得し直すとデータが増えていることがわかります。

>>> cursor.execute('INSERT INTO test (val) VALUES ("Pythonから")');
>>> conn.commit()
>>> cursor.execute('SELECT * FROM test')
>>> cursor.fetchall()
[(1, 'にゃーん'), (2, 'Pythonから')]

まとめ

メモ書き程度なのでこのくらいにしておきます。
今回はデータベースの導入とPythonからのアクセスをやりました。
これを使って、現在進行中のものでデータの管理をしていこうと思います。なので、具体的な話はそっちでやっていこうと思います。
それではまたお会いしましょう。

クエリメモ

なんか覚えられないのでメモ。コピペ用。

データベースを作成

CREATE DATABASE データベース名;

データベースを削除

DROP DATABASE データベース名;

データベース一覧

show databases;

カレントデータベース変更

use データベース名;

テーブル作成

CREATE table テーブル名 (カラム名 型 デフォルト値,…)

CREATE table テーブル名 (
id INT AUTO_INCREMENT PRIMARY KEY,
date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
content TEXT,
);

テーブル一覧

show tables;

内容を表示

select * from テーブル名;

カラムを指定する

select カラム名,カラム名2… from テーブル名;

行数制限で表示

select * from テーブル名 limit 制限;

最新N件取得

select * from テーブル名 order by date desc limit 制限;
データを新しい順に並び替えてから先頭N件取得という意味

特定の値で絞り込む

select * from テーブル名 where カラム名 = 値
#候補が複数の時 (1個でもOK)
select * from テーブル名 where カラム名 in (値1,値2)

行を削除

delete from テーブル名 where カラム名 = 値

最新10件で"flag"カラムが"1または2"のデータの"id"を取得

select id from テーブル名 where flag in (1,2)  order by date desc limit 10;

mysql> select id from posts where aiflag in (1,2) order by date desc limit 10;
+-------+
| id    |
+-------+
| 18624 |
| 18623 |
| 18621 |
| 18620 |
| 18622 |
| 18618 |
| 18616 |
| 18615 |
| 18610 |
| 18608 |
+-------+