#coding=utf-8

import sqlite3
import os

import sys
reload(sys)
sys.setdefaultencoding("utf-8")

class DataBase(object):

    host= os.path.join(os.getcwd(), 'db.sqlite3')

    def create(self):
        self.conn = sqlite3.connect(self.host)
        # if (os.path.exists(self.host) and os.path.isfile(self.host)):
        # ID CHAR(10) PRIMARY KEY NOT NULL,
        self.conn.execute('''CREATE TABLE ZHIBOQUERY(
                            ID CHAR(20) NOT NULL,
                            TITLE CHAR(30) NOT NULL,
                            NAME CHAR(10) NOT NULL,
                            NUM CHAR(20) NOT NULL);''')
        print 'Create Table Successfully!'
        self.conn.close()
        # else:
        #     print 'Table is already exists'

    def insert(self, lsts):
        self.conn= sqlite3.connect(self.host)
        count=1
        for item in lsts:
            id, title, name , num = item.id, item.title, item.name, item.num
            # self.conn.execute('''INSERT INTO ZHIBOQUERY VALUES
            # ('%s','%s','%s','%s')''' % (unicode(id), unicode(title), unicode(name), unicode(num)))
            # self.conn.execute('''INSERT INTO ZHIBOQUERY VALUES
            # ('{}','{}','{}','{}')'''.format(unicode(id), unicode(title), unicode(name), unicode(num)))
            self.conn.execute('''INSERT INTO ZHIBOQUERY VALUES
            (?,?,?,?)''', (unicode(id), unicode(title), unicode(name), unicode(num)))
            # print "<ID {}> insert successfully".format(item.id)
            sys.stdout.write(u'\rinsert {} items complete'.format(count))
            count+=1
        self.conn.commit()
        self.conn.close()
        print u'insert complete!'

    def update(self, lsts):
        self.conn=sqlite3.connect(self.host)
        count = 1
        for item in lsts:
            id, title, name , num = item.id, item.title, item.name, item.num
            # self.conn.execute('''UPDATE ZHIBOQUERY SET NUM="{}", TITLE="{}" where ID="{}"'''.format(id, title, name, num))
            self.conn.execute('''UPDATE ZHIBOQUERY SET NUM=?, TITLE=? , NAME=? where ID=?''', (num , title, name ,id))
            # print "<ID {}> update successfully".format(item.id)
            sys.stdout.write(u'\rupdate {} items complete'.format(count))
            count += 1
        self.conn.commit()
        self.conn.close()
        print u'update complete!'

    def insert_or_update(self,lsts):
        self.conn= sqlite3.connect(self.host)
        id_set= set(self.select_id())
        # self.conn.commit()
        self.conn.close()

        insert_list=[]
        update_list=[]
        for item in lsts:
            if item.id in id_set:
                update_list.append(item)
            else:
                insert_list.append(item)
                id_set.add(item.id)
                
        self.update(update_list)
        self.insert(insert_list)


    def select_all(self):
        self.conn=sqlite3.connect(self.host)
        cur= self.conn.execute('''SELECT * FROM ZHIBOQUERY''')
        res=cur.fetchall()
        # cur= self.conn.execute('''SELECT ID, TITLE, NAME, NUM FROM ZHIBOQUERY''')
        self.conn.close()
        return res

    def select_id(self):
        self.conn= sqlite3.connect(self.host)
        cur = self.conn.execute('''SELECT ID FROM ZHIBOQUERY''')
        res= cur.fetchall()
        res=[item[0] for item in res]
        # print res
        return res

db=DataBase()

if not os.path.exists(db.host):
    db.create()
else:
    print 'db is alwready exists'

# unique = set(db.select_id())
# print unique