基于python的oracle操作程序

零、Oracle依赖安装：

1、Linux环境下Oracle客户端安装cx_oracle:https://blog.csdn.net/jiaoyangdetian/article/details/81116741?spm=1001.2014.3001.5501

2、Linux环境下包安装

apt-get install  libaio* -y

参考连接：
https://www.cnblogs.com/Dev0ps/p/11128514.html

一、创建表

# -*- coding: utf-8 -*-
import cx_Oracle
import pandas as pd
from collections import OrderedDict
import os
os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.ZHS16GBK'
# os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'

mydb =  cx_Oracle.connect("oracle账号","oracle密码","云服务器ip:oracle端口/orcl",encoding='utf-8')
mycursor = mydb.cursor()

tables = OrderedDict(
    {
        "表名": [
            ("output_id int PRIMARY KEY",'form_type int','id int','nws_code varchar(30)','nws_tit clob','nws_content clob','entities clob','type varchar(256)','sent varchar(100)','ent_time date','upd_time date','grd_time date')]
    })


# 创建表程序
def create_tables():

    # 遍历所有表初始化结构
    for name in tables.keys():
        # 获得表结构
        table_e = tables[name]
        # 获得表结构长度
        length = len(table_e[0])
        # 载入创建表代码
        sentence = "CREATE TABLE "+name+"  ("
        # id varchar(255) PRIMARY KEY, name text, address text
        for i in range(length):
            if i ==0:
                sentence+=table_e[0][i]
            else:
                sentence +=','+table_e[0][i]
        sentence+=") tablespace 表空间名"

        try:
            # 如果重复建表，需删除
            mycursor.execute('drop table 表名')
            print("删除成功")
        except:
            pass

        # 执行创表语句
        print(sentence)
        mycursor.execute(sentence)

create_tables()

二、数据插入

# -*- coding: utf-8 -*-
# 创建者：旺达
#---------------------------------
import cx_Oracle
import pandas as pd
from collections import OrderedDict
import os
from tqdm import tqdm

os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.ZHS16GBK'


mydb =  cx_Oracle.connect("oracle账号","oracle密码","云服务器ip:oracle端口/orcl",encoding='utf-8')
mycursor = mydb.cursor()

emo_dict = {'-2':0,'-1':2,'0':0,'1':1}

# 导入要插入的数据
with open(r'11388_for_DB.json',encoding='utf-8') as f:
   df = pd.read_json(f)

   print("数据条数:",len(df))

   for i in tqdm(range(len(df))):
       #-------------------------------------
       # 数据初始化
       #---------------------------------------------
       info = df.iloc[i]
       id = info['id']
       note_text = eval(info['note_text'])
       nws_tit = note_text['content']
       new_content = note_text['content']
       label = info['label']
       Name = ""
       Type = ""
       Emo = ""
       for _la in label:
           Name+=_la['name']+','
           Type+=_la['type']+','
           Emo+=str(emo_dict[_la['emotion_input']])+','
      #----------------------------------------------
     
      # 插入oracle语句      

       sent1 = "INSERT INTO 表名 values({0},0,{0},'{1}','{2}','{3}','{4}','{5}','{6}',sysdate,sysdate,sysdate)".format(id,str(id),nws_tit.replace("'","''"),new_content.replace("'","''"),Name[:-1].replace("'","''"),Type[:-1],Emo[:-1])

       # 执行置入语句
       mycursor.execute(sent1)
       mydb.commit()

       pass

注意：会遇到单引号转义问题。通过.replace("'","''")进行解决。即用''代替'即可。（两个双引号，代替，一个双引号）

三、表查询

# -*- coding: utf-8 -*-
# 创建者：旺达
#---------------------------------

import cx_Oracle
import pandas as pd
import os
# os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.ZHS16GBK'
os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'

mydb =  cx_Oracle.connect("oracle账号","oracle密码","云服务器ip:oracle端口/orcl",encoding='utf-8')

cursor = mydb.cursor()

s = 'select * from 表名'

cursor.execute(s)

results = cursor.fetchall()
print("数据数量：",len(results))
for i in range(len(results)):

    print(results[i])
    break
    # results[i][4],[5]可以查看文本内容
    # print(results[i][1],repr(results[i][1].encode('gbk').decode('utf-8')))