pythonとmysqlを使ってデータベースを作ってみる

はじめに

mysqlを触り始めた初心者がpythonとMySQLでデータベースを構築するための備忘録。
細かな用語の違いなど多々あると思いますがご容赦ください。随時更新予定。

python環境構築

mysqlをpythonで管理するにはconnectorなるものが必要。
conecctorには公式配布のものから、サードパーティ製？のものなどいろいろあるが、
どれもpython3.9に対応していないので、anacondaでpython3.7の環境を構築する。

以下構築コマンド

anaconda prompt

$ conda create -n py37 python=3.7
$ conda activate py37
$ conda install ipykernel
$ conda install panads
$ conda install numpy
$ conda install mysql-connector-python

mysqlでデータベースを作る

コネクタの接続はデータベース単位で行うようなので、あらかじめデータベースを作っておく。
作成方法cuiでもguiでも。なんでも可。
cuiコマンド例

mysql command line client

CREATE DATABASE test_db;

mysqlサーバーに接続する

cursorのbufferd=Tureにする理由は調査中。

import mysql.connector

config = {
    "user"     : "root",
    "password" : "yourpassword",
    "host"     : "localhost",
    "database" : "test_db"
}
cnx    = mysql.connector.connect(**config)
cursor = cnx.cursor(buffered=True)

新しくテーブルを定義する

cursor.execuite()の引数にSQL文を与えると実行してくれるので、命令文を作って渡すだけ。
try, except処理は実行時のエラー対応。

# テーブル名とカラムを定義
table_name = "table1"
columns    = [
    "id INT AUTO_INCREMENT NOT NULL PRIMARY KEY",
    "path VARCHAR(255) NOT NULL",
    "dt DATETIME NOT NULL",
    "label VARCHAR(255) NOT NULL"
]

# SQL文作成
buf = ""
for i, column in enumerate(columns):
    if not i == len(columns)-1:
        buf += column + ", "
    else:
        buf += column
sentence = f"CREATE TABLE {table_name}({buf})"

# 実行
try:
    cursor.execute(sentence)
except mysql.connector.Error as err:
    print(sentence)
    print("==========")
    print(err.msg)

テーブルにレコードを挿入する

テーブル作成時と同様にSQL文を作ってcursor.execute()で実行する。
cnx.commit()を行わないと反映しない点に注意。

table_name = "table1" 
columns    = ["path", "dt", "label"]
for iter in iterable:
    
    # iterに応じてレコードをつくる
    path    = ~~~
    dt      = ~~~
    label   = ~~~
    values  = (path, dt, label)

    # SQL文作成
    buf1 = ""
    for i, column in enumerate(columns):
        if not i == len(columns)-1:
            buf1 += column + ", "
        else:
            buf1 += column
    buf2 = ""
    for i, value in enumerate(values):
        if not i == len(values)-1:
            buf2 += r"%s, "
        else:
            buf2 += r"%s"
    sentence = f"INSERT INTO {table_name} ({buf1}) VALUES ({buf2})"

    # 実行
    try:
        cursor.execute(sentence, values)
    except mysql.connector.Error as err:
        print(sentence)
        print("==========")
        print(err.msg)
cnx.commit()

既存のテーブルのレコードを参照する

SELECT文の結果がiterableなのでforループで使いやすい形に変換する.
また、下記forループ内でcnx.commit()を実行すると"unread result found"というエラーを吐くので注意

# SELECT分でレコードを取得
cursor.execute("SELECT id, path FROM test_db")

# 結果をリストに変換
ids   = []
paths = []
for id, path in cursor:
    ids.append(id)
    paths.append(srcpath)

参照