# -*- coding: utf-8 -*-
import cx_Oracle
import datetime


class MyOracle:

    SHOW_SQL = True

    def __init__(self, host='127.0.0.1', port=1521, user='system', password='oracle', sid='cndba'):
        self.host = host
        self.port = port
        self.user = user
        self.password = password
        self.sid = sid

    def get_con(self):
        try:
            dsn_tns = cx_Oracle.makedsn(self.host, self.port, self.sid)
            # 如果是Oracle 12c 数据库需要替换sid 为service_name
            dsn_tns = dsn_tns.replace('SID', 'SERVICE_NAME')
            conn = cx_Oracle.connect(self.user, self.password, dsn_tns)
            return conn
        except cx_Oracle.Error, e:
            print "cx_Oracle Error:%s" % e

    def select_all(self, sql):
        try:
            con = self.get_con()
            # print con
            cur = con.cursor()
            cur.execute(sql)
            fc = cur.fetchall()
            return fc
        except cx_Oracle.Error, e:
            print "cx_Oracle Error:%s" % e
        finally:
            cur.close()
            con.close()

    def select_by_where(self, sql, data):
        try:
            con = self.get_con()
            # print con
            d = (data,)
            cur = con.cursor()
            cur.execute(sql, d)
            fc = cur.fetchall()
            # if len(fc) > 0:
            #     for e in range(len(fc)):
            #         print(fc[e])
            return fc
        except cx_Oracle.Error, e:
            print "cx_Oracle Error:%s" % e
        finally:
            cur.close()
            con.close()

    def dml_by_where(self, sql, params):
        try:
            con = self.get_con()
            cur = con.cursor()

            for d in params:
                if self.SHOW_SQL:
                    print('执行sql:[{}],参数:[{}]'.format(sql, d))
                cur.execute(sql, d)
            con.commit()

        except cx_Oracle.Error, e:
            con.rollback()
            print "cx_Oracle Error:%s" % e
        finally:
            cur.close()
            con.close()

    # 不带参数的更新方法
    def dml_nowhere(self, sql):
        try:
            con = self.get_con()
            cur = con.cursor()
            count = cur.execute(sql)
            con.commit()
            return count
        except cx_Oracle.Error, e:
            con.rollback()
            print "cx_Oracle Error:%s" % e
        finally:
            cur.close()
            con.close()

# 开始测试函数


def select_all():
    sql = "select * from dave"
    fc = db.select_all(sql)
    for row in fc:
        print row


def select_by_where():
    sql = "select * from dave where USER_ID=:1"
    data = 0551
    fc = db.select_by_where(sql, data)

    for row in fc:
        print row


def ins_by_param():
    sql = "insert into dave(USERNAME,USER_ID,CREATED) values(:1,:2,:3)"
    date = datetime.datetime.now()
    data = [('http://www.cndba.cn', 0551, date), ('http://www.cndba.cn/dave', 0556, date)]
    db.dml_by_where(sql, data)


def del_by_where():
    sql = "delete from dave where USERNAME = :1 and USER_ID=:2"
    data = [('HR', 107)]
    db.dml_by_where(sql, data)


def update_by_where():
    sql = "update dave set USER_ID=:1 where USER_ID=:2"
    data = [(0551, 0556)]
    db.dml_by_where(sql, data)


def del_nowhere():
    sql = "delete from dave"
    print db.dml_nowhere(sql)


if __name__ == "__main__":
    db = MyOracle( '192.168.1.4', port=1521, user='system', password='www.cndba.cn', sid='cndba')

    # ins_by_param()
    # del_by_where()
    # update_by_where()
    # del_nowhere()
    # select_all()
    # del_by_where()
    # ins_by_param()
    del_nowhere()
    select_by_where()