目的

pythonでcsvのデータをデータベースへ書き込みをします。

環境

MariaDB version　 10.3.31-MariaDB-0+deb10u1
Python version: 3.7.3
OS: Raspbian 10.0

イメージ

ラズパイ内にmySQLを入れ、データベース、テーブル、要素の作成をします。そのあとpythonからcsvファイルの読み込みを行い、新規に作成したデータベースへ書き込みを行っていきます。データベースの操作は、コマンドラインから、phpmyAdminからの2種類があり、今回は、コマンドラインを使用しました。コマンドの説明については、別の記事にしましたので、そちらを参考にしてください。

手順

下記のような手順で、操作を確認しつつ、進めていきました。

mySQL(mariaDB)のログイン
データベースの作成
データベースの確認
テーブルの作成
テーブルの確認
要素の作成
要素の確認
pythonでcsvファイルの読み込みとデータベースへの書き込みをする。

1. データベースの作成

データベースを作成するため、mySQLへログインし、データを入れるための箱を作っていきます。

1.1 mySQL(mariaDB)のログイン

ラズパイ上で、MySQLにroot権限でログインします。初期設定では、パスワードはせって入れていないため、passwordを聞かれてもエンターでよいです。

mysql -u root -p;

1.2 新規データベースの作成

ラズパイには、SQLは、MariaDBをインストールしています。データベース名は、bme280dbにしました。

CREATE DATABASE bme280db;

次に、データベースが作成できているか確認します。

SHOW DATABASES;

1.3 データベースの使用

使用するbme280dbへ移動します。Database changedと出れば、OKです。

USE bme280db;

1.4 テーブルの作成

テーブル名をweather_forecastとして、カラムを設定しました。後ほど使用するファイルに書き込まれているデータは、下記のように設定されています。

create table bme280db.weather_forecast(id int not null auto_increment primary key, date_time timestamp, pressure float NOT NULL, temperature float NOT NULL, humidity float NOT NULL);

テーブル名とカラムの設定を確認します。

SHOW CREATE TABLE weather_forecast;

デフォルトでは、date_timeの値が今の時間に更新される設定になっています。timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
今回、csvファイルを読み込んだ値を使用します。読みだしたときの値をそのまま使ってほしいので、アップデートしないように設定しなおします。

ALTER TABLE weather_forecast CHANGE date_time date_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

1.5 mySQL　サーバの接続を切断する。

データベースに必要な情報が入力できたので、サーバの接続を切ります。

exit;

2. python環境構築

2.1 ライブラリのインストール

下記ライブラリをインストールします。

pip install mysql-connector-python

2.2 プログラム作成

下記のようにプログラムを作成しました。csvファイル名は、bme280_db.csvとしています。DBに必要な情報(host, user, password)ですが、最初は初期設定のままなので、そのままになっています。
上記で作成した、データベース名、テーブル名、要素の名前に合わせて書き換える必要があります。作成したプログラムと同じディレクトリにcsvファイルをいれ、プログラムを実行します。

update_sql_from_csv.py

### インポート
import csv
import mysql.connector
 
### ファイルオープン
file = open("bme280_db.csv", "r")
 
### ファイル読み込み
members = csv.reader(file)
 
### DB接続
cnx = mysql.connector.connect(host='localhost', user='root', password='', database='bme280db')
 
### カーソル作成
cursor = cnx.cursor()
 
### INSERT文作成
sql = "INSERT INTO weather_forecast ( date_time, pressure, temperature, humidity) VALUES (%s, %s, %s, %s)"
 
### データ挿入
data_count = 0
for value in members:
        if not data_count == 0:
        ### データ挿入実行
            cursor.execute(sql, value)
        data_count += 1

### コミット
cnx.commit()
 
### カーソルクローズ
cursor.close()
 
### DB切断
cnx.close()
 
### ファイルクローズ
file.close()

2.3. 挿入した要素の確認。

再度、mySQLにアクセスし、データベースへ移動します。

mysql -u root -p;
USE bme280db;

その後、下記コマンドで、テーブルに追加されたデータを確認します。

#  格納データの確認
select * from weather_forecast;

3. リンク

参考にしたリンク集です。
Pythonスクリプト【MySQLにリモート接続しよう⑩～CSVデータ挿入】
Pythonを使ってCSVファイルのデータをMySQLに流し込む