[python] sqlite3 database 생성

너무도 많이 사용하게 될 Sqlite3 데이터베이스다. 작고 가볍고 사용하기 편하다.  
아래의 예제는 database가 없으면 만들고, database 내에 table 없으면 만든다.
table이 존재할 경우에는 table 내의 데이터 개수를 프린트하고 끝낸다.
 

import sqlite3

class NewsMailDB:

    def __init__(self):
        # database check?
        # if not exist, db have to be created
        # db가 없다면 db를 자동생성한다.
        self.connectDB = sqlite3.connect("db/NewsMailer.db")
        self.checkNCreate_tables()

    def checkNCreate_tables(self):
        print("checkNcreate_tables")
        db = self.connectDB.cursor()
        if not self.isTableExist("main"):
            print("main table create")
            sql = "CREATE TABLE main (idx INTEGER PRIMARY KEY, keyword TEXT, duration INTEGER)"
            db.execute(sql)
        else:
            sql = "SELECT count(*) from main"
            cursor = db.execute(sql)
            print(f"main table content count : {cursor.fetchone()[0]} EA")

        if not self.isTableExist("mail"):
            print("mail table create")
            sql = "CREATE TABLE mail (idx INTEGER, email TEXT)"
            db.execute(sql)
        else:
            sql = "SELECT count(*) from mail"
            cursor = db.execute(sql)
            print(f"mail table content count : {cursor.fetchone()[0]} EA")

        if not self.isTableExist("sent"):
            print("sent table create")
            sql = "CREATE TABLE sent (idx INTEGER PRIMARY KEY, mainIdx INTEGER, title TEXT, link TEXT, summary TEXT, dtime DATETIME)"
            db.execute(sql)
        else:
            sql = "SELECT count(*) from sent"
            cursor = db.execute(sql)
            print(f"sent table content count : {cursor.fetchone()[0]} EA")

        db.close()


    def isTableExist(self, tbl_name):
        db = self.connectDB.cursor()
        cursor = db.execute("SELECT * FROM sqlite_master WHERE name = ?", (tbl_name,))

        if cursor.fetchone():
            # print(f"{tbl_name} 테이블이 있어요")
            self.connectDB.commit()
            db.close()
            return True

        # print(f"{tbl_name} 테이블이 없어요")
        self.connectDB.commit()
        db.close()
        return False