【DB作成】PythonのSqlite3を使ってデータベースを操作する方法を見ていこう！

ウマたん

当サイト【スタビジ】の本記事では、PythonのSqlite3を使ってデータベース操作をおこなっていきます。PythonでSQL扱えると便利！もちろんPythonだけでなくSQLの知識も必要になるので注意してくださいねー！

こんにちは！

データサイエンティストでありながら最近はWeb開発に没頭しているウマたん(@statistics1012)です。

Pythonでデータベースを作成して操作することができるのをご存知でしょうか？

もちろんSQLの知識もある程度必要ですが、Pythonのsqlite3というライブラリを使えば簡易的にデータベース使えちゃいます。

是非、マスターしておきましょう！

ウマたん

PythonとSQLを用いてデータベース作成・操作していこう！

この記事ではPythonのsqlite3を用いてデータベースを作成する方法について見ていきます。

Pythonでデータベースを扱えるsqlite3とは

sqlite3はpythonでデータベースを操作するライブラリです。

基本的な記述方法はSQLが分かっていれば問題ありません。ちなみにMySQLをPythonで使う方法もあります。

sqlite3のリファレンスに関して詳細は以下のURLをご覧ください！

https://docs.python.org/ja/3.5/library/sqlite3.html

Python　Sqlite3の使用方法

Sqlite3で簡単にデータベースを作成し、テーブルにデータを格納し抽出してみます。

コードは以下のようになっています。

import sqlite3

dbname = 'trains.db'

conn=sqlite3.connect(dbname)
c = conn.cursor()

# executeメソッドでSQL文を実行する
create_table = 'create table toukeilab (id value,name verchar)'
c.execute(create_table)

# SQL文に値をセットする場合は，Pythonのformatメソッドなどは使わずに，
# セットしたい場所に?を記述し，executeメソッドの第2引数に?に当てはめる値をタプルで渡す．
sql = 'insert into toukeilab (id, name) values (?,?)'
namelist = (1, "uma")
c.execute(sql, namelist)

conn.commit()

select_sql = 'select * from toukeilab'

c.execute(select_sql)
result=c.fetchone()

conn.close()

print(result)

ロボたん

うわ！たくさんコードがあって理解が難しそう、、、

ウマたん

大丈夫！順番に説明していくよ！

データベースを作成

import sqlite3

dbname = 'trains.db'

conn=sqlite3.connect(dbname)
c = conn.cursor()

こちらの部分でデータベースを作成しています。conn.cursor()はデータベースに接続するためのおまじないだと思っていただければ問題ございません。

テーブルを作成

# executeメソッドでSQL文を実行する
create_table = '''create table toukeilab (id value,name verchar)'''
c.execute(create_table)

こちらで、テーブルを作成しています。テーブル上の列名を型と同時に定義します。

この時、テーブルが既に存在する場合エラーを起こしてしまうので、もしテーブルが存在する場合はテーブルを作成しないという処理を入れる必要があります。

また複数のSQL文処理を行う場合は、executeではなくexecutescriptを使う必要があります。

これらに関しては後述します。

テーブルにデータを格納

sql = 'insert into toukeilab (id, name) values (?,?)'
namelist = (1, "uma")
c.execute(sql, namelist)

conn.commit()

こちらでテーブルにデータを格納していきます。

conn.commit()ではデータの格納を保存します。

データを抽出

select_sql = 'select * from toukeilab'

c.execute(select_sql)
result=c.fetchone()

conn.close()

print(result)

こちらの部分で格納したデータを抽出していきます。

fetchoneではデータを1行抽出します。

conn.close()では、データベースへの接続を止めます。

これで簡単なデータベース作成・操作方法は終了。

続いて他のコードと組みあわせて、少し応用した実装を行っていきましょう！

Pythonで応用的なコードを実装

今回実装したいのは、ある記事情報をスクレイピングで抽出しそれらの類似度を計算した後、抽出に使ったURLと類似度をデータベースに格納して抽出するというもの。

実際にはデータベースを経由する必要はありませんが、勉強のためSqlite3を使ってみましょう。

ちなみにスクレイピングと類似度算出に関しては以下の記事でまとめているので参考にしてみてください。

コードは以下のようになっています。

import requests
from bs4 import BeautifulSoup
import sys
import MeCab
from time import sleep
import numpy as np
from sklearn.feature_extraction.text import TfidfVectorizer
import sqlite3


def geturl(urls):
    all_text=[]
    for url in urls:
        r=requests.get(url)
        c=r.content
        soup=BeautifulSoup(c,"html.parser")
        article1_content=soup.find_all("p")
        temp=[]
        for con in article1_content:
            out=con.text
            temp.append(out)
        text=''.join(temp)
        all_text.append(text)
        sleep(1)
    return all_text

def mplg(article):
    word_list = ""
    m=MeCab.Tagger()
    m1=m.parse (text)
    for row in m1.split("\n"):
        word =row.split("\t")[0]#タブ区切りになっている１つ目を取り出す。ここには形態素が格納されている
        if word == "EOS":
            break
        else:
            pos = row.split("\t")[1]#タブ区切りになっている2つ目を取り出す。ここには品詞が格納されている
            slice = pos[:2]
            if slice == "名詞":
                word_list = word_list +" "+ word
    return word_list

def tfidf(word_list):
    docs = np.array(word_list)#Numpyの配列に変換する
    #単語を配列ベクトル化して、TF-IDFを計算する
    vecs = TfidfVectorizer(
                token_pattern=u'(?u)\\b\\w+\\b'#文字列長が 1 の単語を処理対象に含めることを意味します。
                ).fit_transform(docs)
    vecs = vecs.toarray()
    return vecs


def cossim(v1,v2):
    return np.dot(v1, v2) / (np.linalg.norm(v1) * np.linalg.norm(v2))

##データベースの
class Db():
    def __init__(self,dbname):
        self.db=dbname

    def db_input(self,article):
        #値をデータベースに格納
        conn=sqlite3.connect(self.db)
        c = conn.cursor()
        # executeメソッドでSQL文を実行する
        create_table = '''DROP TABLE IF EXISTS article_match;
                        create table article_match (text_1 verchar,text_2 verchar,match_rate double)'''
        c.executescript(create_table)
        # セットしたい場所に?を記述し，executeメソッドの第2引数に?に当てはめる値を
        # タプルで渡す．
        sql = 'insert into article_match (text_1, text_2, match_rate) values (?,?,?)'
        c.execute(sql, article)
        conn.commit()
        c.close()
        conn.close()

    def db_output(self):
        #データベースから値を抽出
        conn=sqlite3.connect(self.db)
        c = conn.cursor()
        select_sql = 'select * from article_match'
        c.execute(select_sql)
        match_rate=c.fetchone()
        conn.commit()
        c.close()
        conn.close()
        return match_rate

word_list=[]
url=["https://toukei-lab.com/conjoint","https://toukei-lab.com/correspondence"]
texts=geturl(url)
for text in texts:
    word_list.append(mplg(text))
    
vecs=tfidf(word_list)
match_rate=cossim(vecs[1],vecs[0])
article = (url[0], url[1], match_rate)
dbname = 'article.db'
db=Db(dbname)
db.db_input(article)
article=db.db_output()

article[2]