MySQL に Python から配列を格納する

2020-11-26

software

MySQLに配列を格納したい

Windows10 + MySQL8.0 + Python3.8.5

MySQLに配列を入れたかったがMySQLには配列型がないらしい。
配列を何かしらの形に変換して入れざるを得ない。
JSON型を扱えるようなので配列をJSON形式に変換することにする。

• 1. 実装
  • 1.1. 事前準備
  • 1.2. テーブルを作る
  • 1.3. データを保存する
  • 1.4. データを参照する

1. 実装

1.1. 事前準備

前回の記事を参考に最低限必要なコードを書く。

MySQLインストールしてPythonからアクセスするまで

MySQLインストールしてPythonからアクセスするまでWindows10 + MySQL8.0 + Python3.8.5MySQLをPythonから使いた…

1 2 3 4 5 6 7 8 9 10 11 12 13

import mysql.connector as mydb # コネクションの作成 cnx= mydb.connect( host='localhost', port='3306', user='root', password='password', database='new_db' ) # DB操作用にカーソルを作成 cur = cnx.cursor()

さらに今回はJSON形式のファイルを扱うのでjsonライブラリを追加する。

1	import json

1.2. テーブルを作る

json_testという名前のテーブルを作る。
json_testにはint型で自動的に番号が振られるidと、json型のdataが含まれる。
auto_incrementを使うために必要らしいのでprimary key(id)としている。
同じ名前のテーブルがすでにある時に何もしないためにif not existsとしている。

1	cur.execute("create table if not exists json_test(id integer auto_increment, data json, primary key (id))")

1.3. データを保存する

json.dumps()でリストをJSON形式に変換する。
これを文字列としてSQL文の中に入れる。
ここで、%sで展開するだけだと文字列として扱われないので前後に\'としてシングルクォーテーションを付ける。

1 2 3 4

data = [1.23, 4.56, 7.89] json_data = json.dumps(data) sql = "insert into json_test (data) values (\'%s\')" % json_data cur.execute(sql)

ここでndarrayをjson.dumps()に入れるとObject of type ndarray is not JSON serializableとエラーが出る。
単純にjson.dumps(array.tolist())というようにtolist()でリストに変換したら良い。

1.4. データを参照する

単純にselect * from json_testとcur.fetchall()で取得したデータはレコードが1行ごとタプルに入ったリストになっている。

今回のテーブルは2列目にJSONデータが入っているため、任意の行でrow[1]を取り出す。
取り出したJSONデータをjson.loads()で読み込むことで元のリストが取得できる。

1 2 3 4 5 6

# 全てのデータを取得・表示 cur.execute("select * from json_test") rows = cur.fetchall() for row in rows: data = json.loads(row[1]) print(data)

タグ MySQL, Python