SQlite と Pandas(to_sql)を使用して、プライマリーキーのあるデータベースの作成

Question

to_sqlは非常に便利で、csvなどのデータから簡単にデータベースへ保存できるのですが、
プライマリーキーを付け加えたく思います。

あらかじめデータベースは最初から作っております。

import pandas as pd
import sqlite3

#CSV保存先
z1=r"-----"
r1="f.csv"
df = pd.read_csv(z1 + "\\" + r1,encoding="CP932")

#データ保存先
z2=r""
db_="example.db"
table_="ist"

conn = sqlite3.connect(z2 + '\\' + db_)

# カーソルを取得
c = conn.cursor()

df.to_sql(table_,conn,if_exists='append',index_label='id', dtype={'id': 'INTEGER PRIMARY KEY AUTOINCREMENT'})

# コミット
conn.commit()
 
# コネクションをクローズ
conn.close()

以上で、簡単にテーブルを作成できます。indexも付与されます。

この次に、indexを指定せず(index=False)追加しようとしてもIDが追記されません。
何もID列が追加されないか、indexがまた０から追記されます。

df.to_sql(table_,conn,if_exists='append',index_label='id', dtype={'id': 'INTEGER PRIMARY KEY AUTOINCREMENT'})
df.to_sql(table_,conn,if_exists='append',index=False, dtype={'id': 'INTEGER PRIMARY KEY AUTOINCREMENT'})

参考サイト

こちらを参考にしてみたのですが、

id = db.Column(db.Integer, primary_key=True,autoincrement=True)

上記のdbがないことや、pip install dbで取得しても動作しません。
SyntaxError: Missing parentheses in call to 'print'. Did you mean print("var", var)?
のエラーが出て、そもそもdbなしでできないのか、相談させてください。

SQliteとPandasを使用して、最初のテーブル作成にCREATE文を使用せず、簡易的にto_csvからテーブル作成を
行いたく思います。

宜しくお願いいたします。

Answer 1

できあがったテーブルのスキーマを確認すると、index_label='id' でカラムidを作ったときに、dtype={'id': 'INTEGER PRIMARY KEY AUTOINCREMENT'}がきちんと反映されていないように見えます。(バグなのか仕様なのかは不明)
インデックスの名前を明示的に id に変更してから to_sql() してやると、うまく動きそうです。

なお、参考にされているページは、sqlalchemy を使っている場合です。
このためだけに sqlalchemy を入れるのは、少し大げさな気がしますが、sqlalchemy も使えるようになっていると便利な場面もあるので、sqlalchemy coreの部分(EngineとかSchemaの部分)だけでも勉強されてもいいかもしれません。(pandasだけならormの部分は使いません)

import sqlite3
import pandas as pd

conn = sqlite3.connect('example.db')

df1 = pd.DataFrame({
    'foo': [1, 2, 3],
    'bar': [1.1, 2.2, 3.3],
})

# 初回
# インデックスを id に名前変更して index=True で書き込む
df1.rename_axis(index='id').to_sql('tbl', conn, index=True, dtype={'id': 'INTEGER PRIMARY KEY AUTOINCREMENT'})


df2 = pd.DataFrame({
    'foo': [11, 22, 33],
    'bar': [11.1, 22.2, 33.3],
})

# 追加は index=False
df2.to_sql('tbl', conn, index=False, if_exists='append')

conn.commit()
conn.close()