Ubuntu 18.04 + VsCode + Python3 + mysql-connector-python から mariadb10.5 にアクセスしてみる

# 目的
・Ubuntu 18.04に mariadb10.5 をインストールする
・デモDB nation をインストールする
・python + mysql-connector-python を使ったサンプルコードを書いてみる

## Repositoriesを追加する
[https://mariadb.com/kb/en/mariadb-package-repository-setup-and-usage/](https://mariadb.com/kb/en/mariadb-package-repository-setup-and-usage/)より

<pre>
$ curl -LsS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash
[info] Repository file successfully written to /etc/apt/sources.list.d/mariadb.list
[info] Adding trusted package signing keys...
[info] Running apt-get update...
[info] Done adding trusted package signing keys

Repositoryに以下の３サイトが登録される
※ワーニング表示抑制のため [arch=amd64] を追加している
$ sudo cat /etc/apt/sources.list.d/mariadb.list

&#35; MariaDB Server
&#35; To use a different major version of the server, or to pin to a specific minor version, change URI below.
deb [arch=amd64] http://downloads.mariadb.com/MariaDB/mariadb-10.5/repo/ubuntu bionic main

&#35; MariaDB MaxScale
&#35; To use the latest stable release of MaxScale, use "latest" as the version
&#35; To use the latest beta (or stable if no current beta) release of MaxScale, use "beta" as the version
deb [arch=amd64] http://downloads.mariadb.com/MaxScale/2.4/ubuntu bionic main

&#35; MariaDB Tools
deb [arch=amd64] http://downloads.mariadb.com/Tools/ubuntu bionic main

</pre>

## mariadbをインストールする

<pre>
$ sudo apt update
$ sudo apt install mariadb-server mariadb-client
</pre>
※サービス名は /lib/systemd/system/mariadb.service

## 文字コードを設定する

<pre>
/etc/mysql/mariadb.conf.d/50-server.cnf

character-set-server  = utf8mb4
&#35;collation-server     = utf8mb4_general_ci  修正前
collation-server      = utf8mb4_bin         修正後

&#35;bind-address         = 127.0.0.1           修正前
bind-address          = 0.0.0.0             修正後

/etc/mysql/mariadb.conf.d/50-client.cnf
default-character-set = utf8mb4             コメントアウト

※修正後、再起動する
$ sudo systemctl restart mariadb
</pre>

<pre>
$ mysql -u root -p
Enter password:
～
MariaDB [(none)]> show variables like "chara%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.001 sec)

※アクセス用ユーザーを追加する
MariaDB [(none)]>GRANT ALL PRIVILEGES ON *.* TO demo IDENTIFIED BY 'passwd' WITH GRANT OPTION;
</pre>

## デモDB nation をインストールする
[MariaDB Sample Database](https://www.mariadbtutorial.com/getting-started/mariadb-sample-database/)よりDLした nation.zip を解凍する

<pre>
$ mysql -u root -p
Enter password:
～
MariaDB [(none)]> source /path/to/nation.sql
～
MariaDB [nation]> use nation;
Database changed

MariaDB [nation]> show tables;
+-------------------+
| Tables_in_nation  |
+-------------------+
| continents        |
| countries         |
| country_languages |
| country_stats     |
| guests            |
| languages         |
| region_areas      |
| regions           |
| vips              |
+-------------------+
</pre>

## mysql-connector-python をインストールする

<pre>
$ sudo pip3 install mysql-connector-python
</pre>

## サンプルコード

```python
# Windows Add env PYTHONIOENCODING = UTF-8 & restart vscode
# coding:utf-8

import mysql.connector as mydb

# コネクションの作成
conn = mydb.connect(
    host='192.168.5.xxx',
    port='3306',
    user='demo',
    password='passwd',
    database='nation'
)

cur = conn.cursor()

query = "SELECT * FROM guests"
cur.execute(query)

print("all:")
rows = cur.fetchall()
for row in rows:
    print(row)
    
cur.close()
conn.close()
```
# 参考にしたサイトはこちら
[Ubuntu 18.04 LTS に MariaDB 10.4 をインストール](https://qiita.com/cherubim1111/items/61cbc72673712431d06e)
[Ubuntu18.04にMariaDBを導入する](http://note.kurodigi.com/ubuntu1804-mariadb/)
[MySQLの文字コードとCollation](https://qiita.com/tfunato/items/e48ad0a37b8244a788f6)
[MariaDBのインストール/初期設定 [CentOS7]](https://qiita.com/ponsea/items/9578659009b434c60959)
[ユーザーの作成](https://www.dbonline.jp/mysql/user/)
[MySQLで「ERROR 2003 (HY000): Can’t connect to MySQL server」と怒られた時の対処法](https://casualdevelopers.com/tech-tips/how-to-fix-2003-error-when-connecting-to-mysql-server/)