pandasでsqlite3にテーブル作成・追加・読み出しでindexの取り扱い方教えてください

Question

質問ポイント
pandas DataFrameからSqlite３のIndexありテーブル（自動付与インクリメント）にデータ追加できない。
Index（自動付与）しなければIndex項目が自動的に発生するが追加データ内部連番が追加されてる。

当方のパソコン環境
Win10  python 3.10  64bit   pandas 1.4.2  Sqlite 3.39.4

pythonでpandasとsqlite3勉強中。
ファイルをpandasで読み込んで、
DBにテーブル作成が簡単にできました。
項目名対応も自動的にしてくれるすぐれもの。
ところが、データ追加するとindexが期待したようになりません。
テーブルにindexをユニーク・インクリメント自動に設定したところ
なってほしいのは、indexに連番でデータを自動追加してくれることなのに、
全データ削除後に１回だけできましたが、続けてのappendできません。
左側に管理番号らしき項目名のない連番が発生。

DB Browser for SQlite3のテーブル情報　indexは　PRIMARY KEY("index" AUTOINCREMENT)
CREATE TABLE "df_sqlite_model" (
	"index"	INTEGER NOT NULL,
	"id"	TEXT,
	"A"	TEXT,
	"B"	INTEGER,
	"C"	TEXT,
	PRIMARY KEY("index" AUTOINCREMENT)
);

このindexつけると、データ全削除後に、1回だけappendできるけど、次にappendはエラーに。
1回だけ成功したTable（データは３件）がこれです。
   index    id   A   B       C
0      0  100'  ab  10   black
1      1  200'  cd  20     red
2      2  300'  ef  30  yellow


pandas DataFrameを、既存テーブルある時はtableをreplaceにすると
indexの自動付与インクリメントがなくなり、そうなると
正常動作して、appendもできデータ数は全部で６件になりましたがindexは追加データ内部連番で、
追加するごとに　0  1  2　0  1  2　のようになって重複しまくり。

pythonプログラムは下記になります
--------------------------------------
import sqlite3
import pandas as pd

file_1="sample_df_001.csv"
file_2="sample_df_002.csv"

df_1= pd.read_csv(file_1)
print("df_1= 
", df_1)     #入れたいsampleデータ１　表示
##    df_1= 
##          id   A   B       C
##    0  100'  ab  10   black
##    1  200'  cd  20     red
##    2  300'  ef  30  yellow

df_2= pd.read_csv(file_2)
print("df_2= 
", df_2)    #入れたいsampleデータ２　表示(項目順変更）
##    df_2= 
##          id   B       C   A
##    0  100'  10   black  ab
##    1  200'  20     red  cd
##    2  300'  30  yellow  ef

dbfile = "test.db"
tbl_name = "df_sqlite_model"
query = "select * from " + tbl_name     #query作成

with sqlite3.connect(dbfile) as con:    #データベースに接続　con
    #テーブル名が存在している場合:‘fail’, ‘replace’, ‘append’
    df_1.to_sql(tbl_name,con,if_exists='replace')
    df_2.to_sql(tbl_name,con,if_exists='append')
    q_to_df = pd.read_sql_query(query,con)
    print(q_to_df)

DBのテーブルをpandas DataFrameで読みだしたものがこれ
自動でついたIndexは重複していて元データのものがそのまま、さらに左側に連番項目あり
##       index    id   A   B       C
##    0      0  100'  ab  10   black
##    1      1  200'  cd  20     red
##    2      2  300'  ef  30  yellow
##    3      0  100'  ab  10   black
##    4      1  200'  cd  20     red
##    5      2  300'  ef  30  yellow

tatsumaru77 · Accepted Answer

＞自動インクリメント型のプライマリーキーが期待された動作をしないということのようです。
＞https://ytyaru.hatenablog.com/entry/2021/06/17/0 …

上記拝見しました。
pandas使用時、indexを未使用にして、indexの代わりにseqを項目名にします。そして、これを自動インクリメント型のプライマリーキーにします。
以下のような定義になります。
CREATE TABLE "df_sqlite_model" (
"seq" INTEGER NOT NULL,
"id" TEXT,
"A" TEXT,
"B" INTEGER,
"C" TEXT,
"D" TEXT,
"E" TEXT,
PRIMARY KEY("seq" AUTOINCREMENT)
);

上記の定義で、if_exists='append'のみを使えば、seqは期待した結果になります。（if_exists='replace'は使用しない）
スクリプトは、以下になります。
with sqlite3・・・より後の行はインデントがあります。
（このサイトに投稿するとインデントがなくなります。）
-----------------------------------
import sys
import sqlite3
import pandas as pd

file_1="sample_df_001.csv"
file_2="sample_df_002.csv"

df_1= pd.read_csv(file_1)
print("df_1= 
", df_1) #入れたいsampleデータ１　表示
df_2= pd.read_csv(file_2)
print("df_2= 
", df_2) #入れたいsampleデータ２　表示(項目順変更）

dbfile = "test.db"
tbl_name = "df_sqlite_model"
query = "select * from " + tbl_name #query作成

with sqlite3.connect(dbfile) as con: #データベースに接続　con
    df_1.to_sql(tbl_name,con,if_exists='append',index=False)
    df_2.to_sql(tbl_name,con,if_exists='append',index=False)
    q_to_df = pd.read_sql_query(query,con)
    print(q_to_df)

以下実行結果
df_1=
　　 id　 A　 B　　　 C
0　100　ab　10　 black
1　200　cd　20　　 red
2　300　ef　30　yellow
df_2=
　　 id　 B　　　 C　 A
0　100　10　 black　ab
1　200　20　　 red　cd
2　300　30　yellow　ab
　 seq　 id　 A　 B　　　 C　　 D　　 E
0　　1　100　ab　10　 black　None　None
1　　2　200　cd　20　　 red　None　None
2　　3　300　ef　30　yellow　None　None
3　　4　100　ab　10　 black　None　None
4　　5　200　cd　20　　 red　None　None
5　　6　300　ab　30　yellow　None　None

https://ytyaru.hatenablog.com/entry/2021/06/17/0 …
でいろいろ問題がかかれていますが、
実行結果をみると、seqはきちんと採番されています。
通常の操作をする分には、特に問題なく使えるのではないかと思います。
（pandasと自動採番項目は相性が悪いですが、それは他のDBでも同じことかと思います）

tatsumaru77 · Answer

>この経験から、使いやすいアプリからデータを扱うのが生産性高く効率的だと実感しています。

おっしゃる通りです。まずは、使いやすい、慣れたものを使うのが一番かと思います。

>pythonからだと、いろいろ扱えるということで、勉強中ですが、思いがけないことも多く、出来上がったら便利だけど、使えるようになるまでは面倒多いとも感じています。

同感です。
私は、年金暮らしのジジイですが、知識を吸収する余力がなくて困ってます。新しいことを覚えてもすぐに忘れるので、なかなか覚えられません。
pythonを書くときは、マニュアルをみながらでないと正しい構文が書けません。やはり、使えるようになるまでは面倒も多いですね。
といっても、まあ、暇つぶしにやっているところもあるので、特に切羽詰まっているわけではないのですが。

tatsumaru77 · Answer

>プロの皆様はどうしているのでしょう？
私は、プロではありませんが、下記のサイトをみると、Primary Keyの問題はプロの方もいろいろと手をやいているみたいですね。
https://qiita.com/mynkit/items/b329ce4f9d6c229382ff

まだ、pandasには実装されていませんが、upsertの機能を追加する予定だそうです。
if_exists='upsert_keep'
if_exists='upsert_overwrite'
のどちらかを指定して、行う方法です。
この機能が実装されれば、あなたが望んでいたことがすんなりとできるようになるかと思います。

tatsumaru77 · Answer

>通常のDBと同じように使えるかどうか、試してみているのですが、どうも期待通りというわけにはいかないようです。

これはpandas側の問題で、sqlite3は正常に動作していると考えられます。
CREATE TABLEで、index,id,A,B,Cの項目を作成していますが、これを
index,id,A,B,C,E,F,Gのように作成して、to_sql(if_exists='replace')
をおこなうと、項目E,F,Gは生成されません。

pandas側にしてみれば、テーブルを削除後、再作成するとき、以前のテーブルと全く同じものを作る意図は全くないように見えます。
pandasにとって,DBはCSVファイルとかexcelファイルとかのように、今pandasが格納しているデータを保存するための器であれば、それで良いと考えているように見えます。

tatsumaru77 · Answer

提示されたスクリプトの実行前と実行後のdf_sqlite_modelの構造を確認してみました。
実行前
sqlite> .schema
CREATE TABLE sqlite_sequence(name,seq);
CREATE TABLE IF NOT EXISTS "df_sqlite_model" (
"index" INTEGER NOT NULL,
"id" TEXT,
"A" TEXT,
"B" INTEGER,
"C" TEXT,
PRIMARY KEY("index" AUTOINCREMENT)
);
実行後
sqlite> .schema
CREATE TABLE sqlite_sequence(name,seq);
CREATE TABLE IF NOT EXISTS "df_sqlite_model" (
"index" INTEGER,
  "id" INTEGER,
  "A" TEXT,
  "B" INTEGER,
  "C" TEXT
);
CREATE INDEX "ix_df_sqlite_model_index"ON "df_sqlite_model" ("index");

indexのAUTOINCREMENT属性が失われています。
これが仕様なのかバグなのかわかりませんが、
if_exists='replace'を指定すると、テーブルを削除後、再作成したときに、
最初に作ったものと構造が変わることになります。
よって、AUTOINCREMENT属性を失いたくないなら、
「if_exists='replace'を使用しない」という選択肢しか残されていないかと思います。
どうしても、あなたがreplaceに期待する結果と同じことをやりたいなら、
その条件が発生したとき、SQLコマンドで
①当該テーブルの削除
②当該テーブルの生成
③to_sql(if_exists='append')での呼び出し
を行うしかないかと思います。

pandasでsqlite3にテーブル作成・追加・読み出しでindexの取り扱い方教えてください

＞自動インクリメント型のプライマリーキーが期待された動作をしないということのようです。

>この経験から、使いやすいアプリからデータを扱うのが生産性高く効率的だと実感しています。

>プロの皆様はどうしているのでしょう？

>通常のDBと同じように使えるかどうか、試してみているのですが、どうも期待通りというわけにはいかないようです。

提示されたスクリプトの実行前と実行後のdf_sqlite_modelの構造を確認してみました。

似たような質問が見つかりました

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング