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