33
Help us understand the problem. What are the problem?

More than 1 year has passed since last update.

posted at

updated at

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
# -*- coding: utf-8 -*-
#
#   sqlite3_read.py
#
#                   Oct/01/2018
# ------------------------------------------------------------------
import sys
import pandas as pd
#
import sqlite3
# ------------------------------------------------------------------
sys.stderr.write("*** 開始 ***\n")
#
file_sqlite3 = "./cities.db"
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")
# ------------------------------------------------------------------

次のバージョンで動作を確認しました。

$ python
Python 3.8.2 (default, Jul 16 2020, 14:00:26) 
[GCC 9.3.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import sys
>>> print(sys.version)
3.8.2 (default, Jul 16 2020, 14:00:26) 
[GCC 9.3.0]
>>> import pandas as pd
>>> print(pd.__version__)
0.25.3
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Sign upLogin
33
Help us understand the problem. What are the problem?