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

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

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

DIfy系目次記事

データベースって何?

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

導入する

インストールする

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

sudo apt install mysql-server

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

sudo systemctl start mysql
sudo systemctl enable mysql

初期セットアップをする

初期セットアップ用のコマンドがあるようなので、それを使用してセットアップしていきます。
ただ、私の場合は少々やらかしまして、ルートのパスワードの設定を行うことができませんでした。

sudo mysql_secure_installation

ちなみにそういう方はルートのパスワードが空になってるみたいなので、以下のように実行するとログインすることができます。
私も初期にこれでログインを行いました。
その後"ALTER USER"コマンドを使ってパスワードを設定します。どうやら規則があるみたいで、あまり簡単なパスワードだとけられます。

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)

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 |
+-------+