Python SQLite3 データベースへの登録 （行ごと/データフレーム全体、書込み/更新）

Question

実現したいこと

・Python　SQLite3 を使用してデータベースを作成し、運用したいと考えています
・データフレーム df で用意した値をデータベースに書き込み、ユーザーが変更できる仕組みを作りたい
※やりたいこと※
①行ごと( df内の各idごと) にまとめて書込む
②データフレームの内容を一括で書き込む

前提

Python　SQLite3 を学習し日が浅く、独学で少しづつ理解を深めている状況です。
特にデータベースについては今回初めて扱います。

id	date	item	price	quantity
1	2000-01-01	さくらんぼ	100	10
2	2000-01-01	いちご	200	20
3	2000-01-01	ぶどう	300	30
4	2000-01-01	みかん	400	40
5	2000-01-01	かき	500	50
6	2000-01-01	りんご	600	60
7	2000-01-01	なし	700	70

上記が使用するデータの初期値で、データフレームとして用意しました
idは固有の値です
1.空のデータフレームを作成
2.初期値を書き込む

ということをやりたいのですが、
[2.初期値を書き込む] の所で躓いています。
個別のデータとして　execute('INSERT INTO　を使用して書き込みはできたのですが、
例えば(1, '2000-01-01', 'さくらんぼ', 100, 10)をまとめて書込んだり、
データフレーム全体を書き込んだり、ということができていません。

困っている内容は、
①行ごと(1, '2000-01-01', 'さくらんぼ', 100, 10)で初期値を書き込む方法(INSERT INTO)
②データフレーム全体を初期値として書き込む方法(INSERT INTO)

全く知識の無いところからのスタートで、何日も手が止まってしまい困り果てています。
Python sqlite3 pandas　その他もろもろ知識が不足していることは重々承知しております。
先輩方のお力添えを頂ければ幸いです<m(__)m>

発生している問題・エラーメッセージ

38行目にて下記エラー

例外が発生しました: ProgrammingError
parameters are of unsupported type
  File "C:\python\webapp\codetest02.py", line 38, in <module>
    cur.executemany('INSERT INTO fruit_data VALUES(?,?,?,?,?)' , index_0)
sqlite3.ProgrammingError: parameters are of unsupported type

該当のソースコード

import pandas as pd 
import sqlite3 

#初期値をデータフレームとして用意
list_df = pd.DataFrame({'id': [1 , 2 , 3 , 4 , 5 , 6 , 7],
                   'date': ['2000-01-01' , '2000-01-01' , '2000-01-01', '2000-01-01', '2000-01-01', '2000-01-01', '2000-01-01'],
                   'item': ['さくらんぼ', 'いちご' , 'ぶどう', 'みかん','かき','りんご' , 'なし'],
                   'price': [100 , 200 , 300 , 400 , 500 , 600 , 700],
                   'quantity': [10 , 20 , 30 , 40 , 50 , 60 , 70]})

# SQLiteに接続する
conn = sqlite3.connect('fruit_data.db')
# SQLiteを操作するためのカーソルを作成
cur = conn.cursor()
#変数query:の宣言
query = "SELECT * FROM fruit_data"

#デバッグの為、dbを一度初期化
conn.execute('DROP TABLE IF EXISTS fruit_data')

# テーブルを作成する
cur.execute("CREATE TABLE IF NOT EXISTS fruit_data( \
    id INTEGER PRIMARY KEY, \
    date TEXT NOT NULL, \
    item TEXT NOT NULL, \
    price INTEGER NOT NULL, \
    quantity INTEGER NOT NULL)")

#個別のデータではdb登録可能
#   cur.execute('''INSERT INTO fruit_data VALUES (1, '2000-01-01', 'さくらんぼ', 100, 10)''')

#0行目index[0]をリストで取得・・・取得結果[1, '2000-01-01', 'さくらんぼ', 100, 10]
index_0 = list_df.iloc[0].to_list()
#0行目をタプルに変換・・・変換結果(1, '2000-01-01', 'さくらんぼ', 100, 10)
tuple_index_0=tuple(index_0)

#取得した行をリストで登録
cur.executemany('INSERT INTO fruit_data VALUES(?,?,?,?,?)' , index_0)
#取得した行をタプルで登録
#   cur.executemany('INSERT INTO fruit_data VALUES(?)' , tuple_index_0)

# commitする
conn.commit()
# データベースをdfに書き出し表示する
df = pd.read_sql_query(query,conn)
print(df)

試したこと

個別のデータではdb登録可能でした…3０行目
cur.execute('''INSERT INTO fruit_data VALUES (1, '2000-01-01', 'さくらんぼ', 100, 10)''')

行ごと登録の為、リストやタプルで取り出し、
executemany('INSERT INTO　の所を色々と修正しましたがうまくいきませんでした。
32～38行目が試した内容です

補足情報（FW/ツールのバージョンなど）

python3.11
win11

Answer 1

sqlite3.ProgrammingError: parameters are of unsupported type

既に他の方の回答にあります通り、sqlite3.register_adapter(type, callable) を利用して、事前に変換器を登録しておきます。

import numpy as np

sqlite3.register_adapter(np.int64, int)

cur.executemany('INSERT INTO fruit_data VALUES(?,?,?,?,?)' , [index_0]) # リストのリストを指定

②データフレーム全体を初期値として書き込む方法(INSERT INTO)

以下の場合、sqlite3.register_adapter(np.int64, int) は不要になります。(pandas.DataFrame.to_sql() の内部で自動変換されます)

#変数query:の宣言
query = "SELECT * FROM fruit_data"

#デバッグの為、dbを一度初期化
conn.execute('DROP TABLE IF EXISTS fruit_data')

# テーブルを作成する
cur.execute("CREATE TABLE IF NOT EXISTS fruit_data( \
    id INTEGER PRIMARY KEY, \
    date TEXT NOT NULL, \
    item TEXT NOT NULL, \
    price INTEGER NOT NULL, \
    quantity INTEGER NOT NULL)")

list_df.to_sql(name='fruit_data', con=conn, if_exists='append', index=False)

# データベースをdfに書き出し表示する
df = pd.read_sql_query(query,conn)
print(df)

#    id        date       item  price  quantity
# 0   1  2000-01-01   さくらんぼ    100        10
# 1   2  2000-01-01      いちご    200        20
# 2   3  2000-01-01      ぶどう    300        30
# 3   4  2000-01-01      みかん    400        40
# 4   5  2000-01-01        かき    500        50
# 5   6  2000-01-01      りんご    600        60
# 6   7  2000-01-01        なし    700        70

①行ごと(1, '2000-01-01', 'さくらんぼ', 100, 10)で初期値を書き込む方法(INSERT INTO)

データフレーム全体を sqlite3 の既存テーブルに追加する場合と同様にできます。

# list_df.iloc[[0]] はデータフレーム(pandas.DataFrame 型インスタンス)で、list_df.iloc[0] は pandas.Series 型インスタンスです
list_df.iloc[[0]].to_sql(name='fruit_data', con=conn, if_exists='append', index=False)

Comments

[conan_edg]

お忙しい中、①、②両方の回答を頂きましてありがとうございます。
回答頂いた、両方の動作が実現できました！

pandas.DataFrame.to_sql()を使うという知識がありませんでしたので、
引数の詳細含めて今回勉強させて頂きます。

「list_df.iloc[[0]] はデータフレーム(pandas.DataFrame 型インスタンス)で、list_df.iloc[0] は pandas.Series 型インスタンスです」
とのコメントも参考になります。

Answer 2

質問にあるような、更新や追加・削除が必要になるようなデータを、RDBとDFで扱うのは相性が悪いのでやめたほうがいいと思います。

たとえば、新たなデータを追加する場合、DFを更新して、そのデータをDBでも更新するわけですから、単なる二度手間です。
DBというのは、メモリに乗りきらなかったり、永続させたかったりするデータをプログラムの外で管理保存するための仕組みです。なので、更新や追加・削除は直接行ない、必要なデータは都度問い合わせで取得するのが正しいやりたかだと思います。

Comments

[conan_edg]

お忙しい中、回答頂きましてありがとうございます。
”更新や追加・削除が必要になるようなデータを、RDBとDFで扱うのは相性が悪いのでやめたほうがいい”
というご意見、大変参考になります。
システム全体の構成を見直しながら、DB,DFの使いどころを見直したいと思います。
ありがとうございました。

Answer 3

全部答えると丸投げに対応したことになってしまうので、ヒントとして①だけ答えます。

index_0 = list_df.iloc[0].to_list()

ここで、index_0 の id, price, quantity は int型ではなく、
numpy.int64型になっています。
ですのでそのままではsqliteに挿入しようとしても、型の不一致でエラーになります。

np.int64型のデータを自動的にintに変換するには
sqlite3.register_adapter を使えばできます。

また、executemany メソッドの第2引数は、タプルのリストでなければなりません。

したがって、以下のようになります。

import pandas as pd 
import sqlite3 

# numpy.int64 を int に変換して読み込む設定
import numpy
sqlite3.register_adapter(numpy.int64, lambda val: int(val))

#初期値をデータフレームとして用意
list_df = pd.DataFrame({'id': [1 , 2 , 3 , 4 , 5 , 6 , 7],
                   'date': ['2000-01-01' , '2000-01-01' , '2000-01-01', '2000-01-01', '2000-01-01', '2000-01-01', '2000-01-01'],
                   'item': ['さくらんぼ', 'いちご' , 'ぶどう', 'みかん','かき','りんご' , 'なし'],
                   'price': [100 , 200 , 300 , 400 , 500 , 600 , 700],
                   'quantity': [10 , 20 , 30 , 40 , 50 , 60 , 70]})

# SQLiteに接続する
conn = sqlite3.connect('fruit_data.db')
# SQLiteを操作するためのカーソルを作成
cur = conn.cursor()
#変数query:の宣言
query = "SELECT * FROM fruit_data"

#デバッグの為、dbを一度初期化
conn.execute('DROP TABLE IF EXISTS fruit_data')

# テーブルを作成する
cur.execute("CREATE TABLE IF NOT EXISTS fruit_data( \
    id INTEGER PRIMARY KEY, \
    date TEXT NOT NULL, \
    item TEXT NOT NULL, \
    price INTEGER NOT NULL, \
    quantity INTEGER NOT NULL)")


#0行目index[0]をリストで取得・・・取得結果[1, '2000-01-01', 'さくらんぼ', 100, 10]
index_0 = list_df.iloc[0].to_list()
#0行目をタプルに変換・・・変換結果(1, '2000-01-01', 'さくらんぼ', 100, 10)
tuple_index_0=tuple(index_0)

#取得した行をタプルで登録
print(tuple_index_0)
# cur.executemany('INSERT INTO fruit_data VALUES(?,?,?,?,?)' , tuple_index_0)  # <-リストでないのでエラー
cur.executemany('INSERT INTO fruit_data VALUES(?,?,?,?,?)' , [tuple_index_0]) # <-リストにして渡す

# commitする
conn.commit()
# データベースをdfに書き出し表示する
df = pd.read_sql_query(query,conn)
print(df)

Comments

[conan_edg]

お忙しい中、回答頂きましてありがとうございます。
丸投げ質問のような印象を与えてしまい、申し訳ありません。
現状の知識レベルと進捗を記載するつもりで、困っている内容を①～③まで記載してしまいました。
以後、気を付けます。（質問内容修正します）

頂いた回答にて確認させて頂き、意図する動作が得られました！ありがとうございます。
型についての意識が低く、numpy.int64型であることに気づきませんでした。
また、sqlite3.register_adapterを知りませんでしたので、今回勉強させて頂きます。

ありがとうございました。