pandas で sqlite3 の読み書き

sqlite3 データベースファイルの作成

sqlite3_create.py

#! /usr/bin/python
# -*- coding: utf-8 -*-
#
#	sqlite3_create.py
#
#					Oct/01/2018
# ------------------------------------------------------------------
import sys
import pandas as pd
#
import	sqlite3
# ------------------------------------------------------------------
sys.stderr.write("*** 開始 ***\n")
#
df = pd.DataFrame([
		["t1271","千葉",51476,"2003-9-25"],
		["t1272","勝浦",42573,"2003-3-16"],
		["t1273","市原",28471,"2003-6-21"],
		["t1274","流山",36872,"2003-8-27"],
		["t1275","八千代",24176,"2003-11-5"],
		["t1276","我孫子",13275,"2003-1-12"],
		["t1277","鴨川",85194,"2003-12-18"]])

print(df)
#
file_sqlite3 = "./cities.db"
conn = sqlite3.connect(file_sqlite3)
#
#
df.to_sql('cities',conn,if_exists='append',index=None)
#
conn.close()
#
sys.stderr.write("*** 終了 ***\n")
# ------------------------------------------------------------------

sqlite3 の読み込み

sqlite3_read.py

#! /usr/bin/python
#
#	sqlite3_read.py
#
#					Mar/05/2023
# ------------------------------------------------------------------
import sys
import pandas as pd
#
import sqlite3
# ------------------------------------------------------------------
sys.stderr.write("*** 開始 ***\n")
#
file_sqlite3 = sys.argv[1]
conn = sqlite3.connect(file_sqlite3)
#
df=pd.read_sql_query('SELECT * FROM cities', conn)
print(df)
# print(df.head())
#
conn.close()
df.to_csv("tmp001.csv",header=None,index=None)
sys.stderr.write("*** 終了 ***\n")
# ------------------------------------------------------------------

実行結果

$ ./sqlite3_read.py cities.db
*** 開始 ***
       0    1      2           3
0  t1271   千葉  51476   2003-9-25
1  t1272   勝浦  42573   2003-3-16
2  t1273   市原  28471   2003-6-21
3  t1274   流山  36872   2003-8-27
4  t1275  八千代  24176   2003-11-5
5  t1276  我孫子  13275   2003-1-12
6  t1277   鴨川  85194  2003-12-18
*** 終了 ***

確認したバージョン

$ python
Python 3.10.9 (main, Dec 19 2022, 17:35:49) [GCC 12.2.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import pandas
>>> pandas.__version__
'1.5.3'