#!/usr/bin/python
# -*- encoding:utf-8 -*-
import sqlite3, logging  
logging.basicConfig(format='%(asctime)s - %(levelname)s : %(message)s', level=logging.DEBUG)

class sqldb:  
    '''
    SQLite3 数据库操作模型
    '''
    def __init__(self):
        '''
        连接数据库
        '''
        try:
            self.conn = sqlite3.connect('./test.db')
            logging.info("Opened database successfully")
        except:
            logging.error("Opened database Failed")
            return -1

    def closeDB(self):
        '''
        关闭数据库连接
        :return: 0 关闭数据库成功  -1 关闭数据库失败
        '''
        try:
            self.conn.close()
            return 0
        except:
            return -1

    def createTable(self):
        '''
        创建数据表,此处数据表模型应该在配置文件里面预设
        :return:0 创建数据表成功 -1 创建数据表失败
        '''
        try:
            self.conn.execute('''CREATE TABLE COMPANY
                   (ID INTEGER PRIMARY KEY AUTOINCREMENT,
                   NAME           TEXT    NOT NULL,
                   AGE            INT     NOT NULL,
                   ADDRESS        CHAR(50),
                   SALARY         REAL);''')
            logging.info("Table created successfully")
            return 0
        except:
            logging.error("createTable Error maybe database is exits!")
            return -1

    def insertRecord(self, tableName, tableStrcture, tableValue):
        '''
        向数据表中插入记录
        :param tableName: 数据表名称
        :param tableStrcture: 数据表结构模型
        :param tableValue: 对应数据表模型的数据
        :return: 执行结果: 0 插入成功 -1 插入失败
        '''
        self.dbsql = "INSERT INTO " + tableName + " " + tableStrcture + " VALUES " + tableValue
        logging.info("DB sql :" + self.dbsql)
        result = self.execSql(self.dbsql)
        return result

    def seltctAll(self, tableName):
        '''
        列出数据表中所有数据
        :param tableName: 数据表名称
        :return: 数据表查询结果,返回数据序列
        '''
        self.dbsql = "SELECT * FROM " + tableName
        logging.info("DB sql :" + self.dbsql)
        record = self.execSqlWithReturn(self.dbsql)
        return record

    def getOne(self, tableName, racordToSearch):
        '''
        列出数据表中所有数据
        :param tableName: 数据表名称
        :param racordToSearch:用来作为查找根据的数据键值对
        :return: 数据表查询结果,返回数据序列
        '''
        self.dbsql = "SELECT * FROM " + tableName + " WHERE " + racordToSearch
        logging.info("DB sql :" + self.dbsql)
        record = self.execSqlWithReturn(self.dbsql)
        return record

    def updateRecord(self, tableName, recordToChange, racordToSearch):
        '''
        更新数据表
        :param tableName:数据表名称
        :param recordToChange:需要更改的记录
        :param racordToSearch:用来查询记录的数据键值对
        :return:执行结果: 0 更新成功 -1 更新失败
        '''
        self.dbsql = "UPDATE " + tableName + " SET " + recordToChange + " where " + racordToSearch
        logging.info("DB sql :" + self.dbsql)
        result = self.execSql(self.dbsql)
        return result

    def deleteRecord(self, tableName, recordToDelete):
        '''
        删除数据记录
        :param tableName:数据表名称
        :param recordToDelete:需要删除的数据表记录
        :return:执行结果: 0 删除成功 -1 删除失败
        '''
        if tableName is not None and tableName != '':
            self.dbsql = "DELETE from " + tableName + " where " + recordToDelete
            logging.info("DB sql :" + self.dbsql)
            result = self.execSql(self.dbsql)
            return result

    def execSql(self, dbsql):
        '''
        运行没有返回值的sql语句
        :param dbsql: sql语句
        :return: 执行结果 0 成功 -1 失败
        '''
        try:
            self.conn.execute(dbsql)
            self.conn.commit()
            return 0
        except:
            logging.error("Operate Dababase Error !")
            return -1

    def execSqlWithReturn(self, dbsql):
        '''
        运行具有返回值的sql语句
        :param dbsql: sql语句
        :return: 执行结果 返回单一序列 成功 -1 失败
        '''
        try:
            self.cursor = self.conn.execute(dbsql)
            return self.cursor.fetchall()
        except:
            logging.error("seltct Record Error !")
            return -1

if __name__ == "__main__":  
    '''
    测试函数
    '''
    db = sqldb()
    db.createTable()
    tableName = "COMPANY"
    tableStrcture = "( NAME, AGE, ADDRESS, SALARY )"
    selectTableStrcture = "ID, NAME, AGE, ADDRESS, SALARY"
    tableValue = "('Paul', 32, 'California', 20000.00 )"
    recordToChange = "SALARY = 25000.00"
    racordToSearch = "NAME='Paul'"
    racordToDelete = "ID=2"
    db.insertRecord(tableName, tableStrcture, tableValue)
    db.updateRecord(tableName, recordToChange, racordToSearch)
    db.deleteRecord(tableName, racordToDelete)
    record = db.getOne(tableName, racordToSearch)
    if record != -1 and record is not None:
        print(record)
    else:
        logging.error("Get Record Error !")
    record = db.seltctAll(tableName)
    if record != -1 and record is not None:
        print(record)
    else:
        logging.error("Get Record Error !")
    db.closeDB()