目的
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ファイルをいれ、プログラムを実行します。
### インポート
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に流し込む