import oracledb

un = 'esun'
pw = 'Oraclebice502'
cs = 'www.xxxx.net:1527/esun'


def test_select() -> None:
    with oracledb.connect(user=un, password=pw, dsn=cs) as connection:
        with connection.cursor() as cursor:
            sql = """select deptno, dname, loc from dept"""
            cursor.execute(sql)
            # 获取表结构，用于组装JSON
            columns = [col[0] for col in cursor.description]
            cursor.rowfactory = lambda *args: dict(zip(columns, args))

            result = cursor.fetchall()  # 获取全部返回值
            print(result)
            print(result[1]["LOC"])


def test_insert() -> None:
    with oracledb.connect(user=un, password=pw, dsn=cs) as connection:
        with connection.cursor() as cursor:
            sql = """insert into dept(DNAME, LOC) values (:dname, :loc)"""
            try:
                ret = cursor.execute(sql, ['MANAGER2', 'BEIJING2'])
            except:
                print("程序异常")

            connection.commit()  # 提交操作


def test_delete() -> None:
    with oracledb.connect(user=un, password=pw, dsn=cs) as connection:
        with connection.cursor() as cursor:
            sql = """delete from dept where DEPTNO > 40"""
            try:
                ret = cursor.execute(sql)
            except:
                print("程序异常")

            connection.commit()  # 提交操作


test_select()
test_delete()
test_select()