Python： JSONデータを一括でSQLServerのあるテーブルにINSERTしたい

Question

色々文献をあさっているのですが、こちらの記事を見た限り　For文が登場していることから　JSONデータのレコードの分　INSERT文を発行しなければならないような作りなのかな？と思いました。

Pythonが稼働する筐体と、DBサーバの筐体は別であり、レコード数に左右してこの筐体間のトラフィックが発生するのは最良ではないような気がしました。

一回のSQL文の発行で、JSONデータを取り込む方法があれば　教えてください。
こちらのMSの記事を見たのですが、今度はDBMS側に特化した記事になっており、Python内で生成されるJSONデータを　どうDBMSに適用すれば良いのだろう（ファイルとして保存されていない）、と悩んでいます。

参考になる記事のご紹介でも結構です、ご見解をよろしくお願いします。

---

14:25追記

def jsonINSERT(_cn, _cur, jdata):
    SQL = """
        INSERT INTO TSTTBL VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    """
    _cur.executemany(SQL, jdata)
    _cn.commit()
    return

取込もうとしているJSONデータ

上記executemanyメソッド部分から発せられているだろうエラー

'The SQL contains 56 parameter markers, but 1 parameters were supplied', 'HY000'

2020/03/03 19:26追記
columns = tuple(fetch_dat[0].keys())　の行で　'str' object has no attribute 'keys'　となります...

Comments

[yoorwm]

JSON文字列をそのままINSERTという方法もあるので、そのJSONデータを「どのように使用するのか？」という所を考えて質問すると良いかもしれません。
（ユーザIDだけ拾って、ユーザIDとデータだけのレコードでもいい場合がありますし。）

[saya24]

yoorwmさん
SQLServer上のテーブルは　既に業務で運用されているもので、辞書形式の内容は各列に収まる必要があります。現在のステータスを本文に追加します。executemanyメソッドを実行するとエラーが現れています。

Answer 1

sqliteでのコード例を示します。ポイントは

• jsonデータ（に含まれている列名）から動的にSQLクエリ文字列を作成
• jsonデータからexecutemanyに渡すべき値をタプルにしたリストを作成

です。

import sqlite3
import json

# テストテーブルを作成
con = sqlite3.connect('test.db')
cur = con.cursor()
cur.execute('drop table if exists t_test;')
cur.execute('create table if not exists t_test(num int, str text);')
cur.executemany("insert into t_test(num, str) values(?,?)",[(1,'a'),(2,'b')])
con.commit()
con.close()

# テストデータ。fetchallなりで取り出してきたもの
fetch_dat = [('{"num": 3, "str": "c"}',), ('{"num": 4, "str": "d"}',)]

# タプルで包まれているので、包みを取り除く
fetch_dat = [json.loads(t[0]) for t in fetch_dat]
print(fetch_dat) # [{'num': 3, 'str': 'c'}, {'num': 4, 'str': 'd'}]

# クエリ文字列を作成
columns = tuple(fetch_dat[0].keys())
params = ['?' for _ in range(len(columns))]
sql = 'insert into t_test({}) values({})'.format(','.join(columns), ','.join(params))
print(sql) # insert into t_test(num,str) values(?,?)

# クエリに渡すデータ。値をタプルにしたリスト
datas = [tuple(e.values()) for e in fetch_dat]

# テーブルに複数レコード追加
con = sqlite3.connect('test.db')
cur = con.cursor()
cur.executemany("insert into t_test(num, str) values(?,?)",datas)
con.commit()
con.close()

# 確認表示
con = sqlite3.connect('test.db')
cur = con.cursor()
cur.execute("select * from t_test;")
rows = cur.fetchall()
for row in rows:
    print( row)
con.close()
#(1, 'a')
#(2, 'b')
#(3, 'c')
#(4, 'd')

Comments

[saya24]

can110さん　当方の為に貴重なお時間を頂き有り難うございます。
早速確認して、改めて結果を報告させて頂きます。ちょっと、開発環境の前にいないもので。

まずは御礼まで、また後ほど。

[saya24]

報告が遅れており申し訳ございません、時間が経過してしまっている理由は
ご提示を頂きました　json_dat = json.loads(XXXX)　のステップをうまく自分のコードに適用できずにいるためです。「the JSON object must be str, bytes or bytearray, not 'list」というエラーを招いてしまっています。察するに自分が手配したデータがJSON形式に相応しくないためと思われます。

こちらは　res = cur.fetchall() 　というように、別データベースから取得したもので、このresを　json.loadsの引数に与えている状況です。
ちなみに　変数 res をprint文で確認すると　以下結果を得ています。現在2レコードだけ得るようにしています。
[('{"ITEMCODE":"A001234","DISPLAYREVISIONNO":1,"ITEMNAME":"ABCDEFGHIJKLM","ITEMNAME_ID":"~0000PCJSU"}',),
('{"ITEMCODE":"A002234","DISPLAYREVISIONNO":1,"ITEMNAME":"BCDEFGHIJKLMN","ITEMNAME_ID":"~0000PCPAT"}',)]

この 変数resから (' と　',)　を除去すれば　ご提示されているサンプルのJSONデータに近づけると思うのですが。

すみません、Pythonを始めたばかりで、辞書・タプルの扱いが不慣れで。

差し支えなければ　自分がJSONデータとして認識している　ここに示したデータの集団を、どういった関数・加工を施した上で 　json.loadsの引数に与えればよいのか　教えてくださいませんか

[can110]

元のデータの形に合わせて回答コードを修正、(' と　',)を取り除くようにしました。

[saya24]

すみません、何から何まで。確認致します。

[saya24]

ご支援をありがとうございます。
columns = tuple(fetch_dat[0].keys())　の部分で　'str' object has no attribute 'keys'
というエラーが現われております。まだJSONとして認識されない問題が含まれている、ということでしょうか？？　本文に加工を経たあとのデータの状況、エラー状況を添付致しました、ご確認頂ければ幸いです。

[can110]

よく見たら辞書{～}自体が文字列になっているようですね。コード修正しました。

[saya24]

can110さん、完璧です。この度は至れり尽くせりの対応をありがとうございました。
無事　JSONとして取得したデータベースからのデータを、たった１度のSQL発行で別データベースサーバのテーブルへ　データ登録することができました。

頂きましたコード、正直まだよく理解できていないので　今から解読したいと思います。絶対将来にわたっても汎用的に使えるコードに違いありません！　重ねてお礼を申し上げます。

[saya24]

先ほどまで別データベースからJSON形式に格納しているデータは　一先ず2レコードに制限して試していたのですが、この制限を解いたら（WHERE句を外した）、ちょっと困ったことになりました。

cur.executemanyの第二引数に与えるタプルが　1244項目を超えると動作しない様子です。
タプルもしくはリストの限度、ということなのかも知れません。

というと、やはり　何回かにSQL発行を分けるしかない、という結末なのでしょうかね...
一回のSQL発行で、ズドンとJSONデータが所定テーブルの各列に収まることは非常に画期的だと思ったのですが

解決済としながら　コメントを書き続けすみません

[can110]

回答コードを修正し、10000タプル追加するようにしてみましたが問題なく動作しました。
そのような結論に至った経緯が不明なので何ともいえませんが、DBサーバ側での制限があるのかもしれません。たとえばMySQLでは以下のようなケースもあるようです。
https://stackoverflow.com/questions/13291700/executemany-for-mysqldb-error-for-large-number-of-rows

[saya24]

ご見解をありがとうございます。今は環境が手元になく、発生したエラーメッセージを貼り付けられないのですが、後ほど貼り付けようと思います、すみません。

確かに利用のDBMS•ドライバに依存する部分かも知れませんね。変更設定出来ると良いのですが。

エラーメッセージでググれば 解決できるかな。

ではまた後ほど

[saya24]

fetch_dat = [json.loads(t[0]) for t in fetch_dat]
の部分で、
「Expecting value: line 1 column 1244 (char 1243)」
というエラーメッセージが出ていることを確認しました。DBMS云々のはなしではないようでした、妙なことを言ってすみません。

これからググってみますが、現段階で検討がつきません

[saya24]

fetch_dat = [json.loads(t[0]) for t in fetch_dat]　のステップで得られるリスト内の辞書ですが、
keyと値の括りをシングルクォートからダブルクォートへ　変更することはできますか？

現在fetch_datのprint結果は　[{'ITEMCODE': 'A001234', 'DISPLAYREVISIONNO': 1, 'ITEMNAME': 'ABCDEFGHIJKL', 'ITEMNAME_ID': '~0000PCJSU',....................................
となっています。
http://nekoyukimmm.hatenablog.com/entry/2016/07/14/213140
の記事に　その事象を要因に記載されているのですが、別データベースから取得するデータ群の中に意図せずシングルクォートが含まれているのでは？？　と疑いだしています。