pythonのsqlite3でsqlを使いデータベースからデータをカラムごとに取り出す関数作ってみた[sqlite3 , sql ,pandas]

モチベーション

普段機械学習系ばかり独学してますが、twitter等ではsqlの知識も必要といわれる方をよく見ます。なので少しずつ並行して学ぼうということで、最初にはデータを取り出すところから関数化していきたいと思います。

この記事では以下をやります.

• pandasのdataframeをdbに格納
• 作ったdbからcolumn指定でデータを取り出す

すでにある場合はごめんなさい。
SQLは完全初心者なので多めに見てください(-_-;)

今回使うもののimport

import sqlite3
import pandas as pd
import pandas.io.sql as sql

DBを作る

pandasを使いお試し用のDBを作成します。
作るdataframeは以下のようにしたいと思います。値は適当です。

city = ["tokyo","kyoto","oosaka"]
num = [30,20,28]
df_1 = pd.DataFrame({"city":city,"number_of_city":num})

dataframe -> databaseへ

path = "tamesi.db"
conn = sqlite3.connect(path)
cur = conn.cursor()
sql.to_sql(df_1,"study",conn,if_exists="replace",index=None)
cur.close()
con.close()

今回はtamesiというデータベースにstudyというtableで作成しました。

指定カラムを取り出す関数を作る

作りました。

def ret_column(db,table,column=""):
    assert bool(column), "no column is spcified"
    conn= sqlite3.connect(db)
    cur = conn.cursor()
    cur.execute("select {} from {}".format(column,table))
    val = cur.fetchall()
    cur.close()
    conn.close()
    return val

columnに取り出したいものを入れて取り出します。一応columnに何もないとエラーを返すようにしてみました。
やってることは単純にdbとtable指定してsql文呼び出しているだけです。

全データを取り出す関数を作る

せっかくなんで全データを取り出す関数を作成してみます。
つくりました。

def ret_all(db,table):
    conn = sqlite3.connect(db)
    cur = conn.cursor()
    cur.execute("select * from {}".format(table))
    val = cur.fetchall()
    cur.close()
    conn.close()
    return val

カラム指定がないのでこちらの方が簡単でしたね。

使ってみる

path = "tamesi.db"
table="study"
val_1 = ret_column(path,table,column="city")
val_2 = ret_all(path,table)
print(val_1)
print(val_2)

# 以下出力
[('tokyo',), ('kyoto',), ('oosaka',)]
[('tokyo', 30), ('kyoto', 20), ('oosaka', 28)]

ちゃんと取り出せていることが確認できました。

ついでにインスタンス化してみる

簡単にclassで実装してみました。

class ret_from_db():
    def __init__(self,db):
        self.db = db

    def ret_column(self,table,column=""):
        assert bool(column), "no column is spcified"
        conn= sqlite3.connect(self.db)
        cur = conn.cursor()
        cur.execute("select {} from {}".format(column,table))
        val = cur.fetchall()
        cur.close()
        conn.close()
        return val

    def ret_all(self,table):
        conn = sqlite3.connect(self.db)
        cur = conn.cursor()
        cur.execute("select * from {}".format(table))
        val = cur.fetchall()
        cur.close()
        conn.close()
        return val

上二つをくっ付けただけです、特筆はなし。

使ってみた

get_db = ret_from_db(db="tamesi.db")
val_1 = get_db.ret_column(table="study",column="city")
val_2 = get_db.ret_all(table="study")
print(val_1)
print(val_2)

# 以下出力
[('tokyo',), ('kyoto',), ('oosaka',)]
[('tokyo', 30), ('kyoto', 20), ('oosaka', 28)]

ちゃんとできました。

終わり

これが実用性があるかどうかは知りません使ってことないので(-_-;)
次はpandasで読み取ることができればsklean等に使いやすくなるのでそれを作りたいですね。