Python with sqlite3

不擅長寫字，不如畫畫吧！

import logging
import os
import sqlite3

class IndicatorDao():
    
    def __init__(self):
        self._dbfile = './db/indicator.db'
        self._schedule = 'Schedule'
        self._release_info = 'ReleaseInfo'
        self._code = 'Code'
        self._logger = logging.getLogger()
        
    def InsertSchedule(self, indicator, nation, dates):
        conn = sqlite3.connect(self._dbfile)
        cursor = conn.cursor()
        cursor.execute('''insert or ignore into
            %s (name, nation) values ('%s', '%s')
            ''' % (self._code, indicator, nation))
        cursor.execute('''select id from %s 
            where name="%s" and nation="%s"
            ''' % (self._code, indicator, nation))
        code = cursor.fetchone()[0]
        for date in dates:
            cursor.execute('''insert into
                %s (code_id, release_date) values ('%s', '%s')
                ''' % (self._schedule, code, date))
        conn.commit()
        cursor.close()
        conn.close()                             

    def UpdateSchedule(self, indicator, nation, old_date, new_date):
        conn = sqlite3.connect(self._dbfile)
        cursor = conn.cursor()
        cursor.execute('''select id from %s 
            where name="%s" and nation="%s"
            ''' % (self._code, indicator, nation))
        code_id = cursor.fetchone()[0]
        cursor.execute('''update %s set release_date="%s"
            where code_id=%d and nation="%s" and release_date="%s"
            ''' % (self._schedule, new_date, code_id, nation, old_date))
        conn.commit()
        cursor.close()
        conn.close()

    def DeleteSchedule(self, indicator, date):
        conn = sqlite3.connect(self._dbfile)
        cursor = conn.cursor()
        cursor.execute('''select id from %s 
            where name="%s" and nation="%s"
            ''' % (self._code, indicator, nation))
        code_id = cursor.fetchone()[0]
        cursor.execute('''delete from %s
            where code_id=%d and nation="%s" and release_date="%s"
            ''' % (self._schedule, code_id, nation, date))
        conn.commit()
        cursor.close()
        conn.close()    
    
    def GetSchedule(self, begin_date, end_date):
        sql_cmd = '''select %s.release_date, %s.name, %s.nation
            from %s inner join %s on %s.code_id = %s.id where 1=1
            ''' % (self._schedule, self._code, self._code,
                   self._schedule, self._code,
                   self._schedule, self._code)
        if begin_date:
            sql_cmd += ''' and release_date >= "%s"''' % begin_date
        if end_date:
            sql_cmd += ''' and release_date <= "%s"''' % end_date
        sql_cmd += ''' order by release_date'''
        self._logger.debug(sql_cmd)
        
        conn = sqlite3.connect(self._dbfile)
        cursor = conn.cursor()
        rv = [ _ for _ in cursor.execute(sql_cmd)]
        cursor.close()
        conn.close() 

        return rv

    def InsertReleaseInfo(self,
                          indicator,
                          nation,
                          period,
                          coverage,
                          url,
                          description):
        conn = sqlite3.connect(self._dbfile)
        cursor = conn.cursor()
        cursor.execute('''insert or ignore into
            %s (name, nation) values ('%s', '%s')
            ''' % (self._code, indicator, nation))
        cursor.execute('''select id from %s 
            where name="%s" and nation="%s"
            ''' % (self._code, indicator, nation))
        code = cursor.fetchone()[0]
        cursor.execute('''insert or replace into
                %s (code_id, period, coverage, url, description)
                values ('%s', '%s', '%s', '%s', '%s')
                ''' % (self._release_info,
                       code,
                       period,
                       coverage,
                       url,
                       description))
        conn.commit()
        cursor.close()
        conn.close()