python (Oracle数据库操作)

数据库的创建与上传数据（可上传小写的表名和字段名）

import pandas as pd
import cx_Oracle
from sqlalchemy.dialects.oracle import \
            BFILE, BLOB, CHAR, CLOB, DATE, \
            DOUBLE_PRECISION, FLOAT, INTERVAL, LONG, NCLOB, \
            NUMBER, NVARCHAR, NVARCHAR2, RAW, TIMESTAMP, VARCHAR, \
            VARCHAR2
def mapping_df_types(p_data: pd.DataFrame):
    """
    定义数据对应的类型dict
    :param p_data: pd数据
    :return:
    """
    dtypedict = {}
    for i, j in zip(p_data.columns, p_data.dtypes):
        if "object" in str(j):
            dtypedict.update({i: VARCHAR(256)})
        if "float" in str(j):
            dtypedict.update({i: NUMBER(19, 8)})
        if "int" in str(j):
            dtypedict.update({i: VARCHAR(19)})
    return dtypedict

def get_key_type_old(p_dtypedict: dict):
    """
    返回orical数据库创建表格所需 字段_类型 的格式
    :param p_dtypedict: {字段：类型}
    :return:
    """
    data = ""
    data_k = list(p_dtypedict.keys())
    data_v = list(p_dtypedict.values())
    for i in range(len(p_dtypedict)):
        if data == '':
            data = data + '(' + '"' + str(data_k[i]) + '"' + ' ' + str(data_v[i]) + ','
        elif i != len(p_dtypedict) - 1:
            data = data + '"' + str(data_k[i]) + '"' + ' ' + str(data_v[i]) + ','
        else:
            data = data + '"' + str(data_k[i]) + '"' + ' ' + str(data_v[i]) + ')'
    return data

def get_key_type(p_dtypedict: dict):
    """
    返回orical数据库创建表格所需 字段_类型 的格式
    :param p_dtypedict: {字段：类型}
    :return:
    """
    data_k = list(p_dtypedict.keys())
    data_v = list(p_dtypedict.values())
    data = '(' + ','.join(list(map(lambda x, y: '"' + str(x) + '" ' + str(y), data_k, data_v)))+ ')'
    return data

def orical_creat_table(p_tablename: str, p_dtypedict: dict):
    """
    orical中创建一个表
    :param tablename: 表名
    :param dtypedict: {key：类型}
    :return:
    """
    p_tablename = '"' + p_tablename + '"'
    sql_createTable = "create table " + p_tablename + get_key_type(p_dtypedict)
    # 执行sql语句
    cur = conn.cursor()
    cur.execute(sql_createTable)
    conn.commit()
    cur.close()


def judge_if_have_this_table(p_tablename: str):
    """
    判断当前数据库是否有对应的表名
    :param p_tablename:
    :return:
    """
    p_tablename = "'" + p_tablename + "'"
    judge_sql = "SELECT COUNT(*) FROM User_Tables WHERE table_name = " + p_tablename
    have_table = False
    # 执行sql语句
    cur = conn.cursor()
    cur.execute(judge_sql)
    result = cur.fetchone()
    if result[0] == 1:
        have_table = True
    # result=cur.fetchmany(2)
    conn.commit()
    cur.close()
    return have_table


def insert_sql_by_row(p_tablename: str):
    """
    按行插入数据
    :param p_tablename: 表名
    :return:
    """
    p_tablename = '"' + p_tablename + '"'
    cur = conn.cursor()
    keys_data = "(" + ','.join(list(map(lambda x: '"'+x+'"', list(Data.columns))))+")"
    for i in range(len(Data)):
        insert_sql = "INSERT INTO" + p_tablename + keys_data+"values"+str(tuple(list(Data.iloc[i])))
        print("insert_sql", insert_sql)
        cur.execute(insert_sql)
    conn.commit()
    cur.close()

if __name__ == '__main__':
    #################定义数据
    Data = [{"projPickId": 'b4cc5099-a510-11ec-9b80-08d23efcc353', "projPickName": "N1s2",
            "botDepth": 2, "symbolBotDepth": 1},
           {"projPickId": 'b4cc509b-a510-11ec-ae34-08d23efcc352', "projPickName": "N1s2",
            "botDepth": 4, "symbolBotDepth": 1},
           {"projPickId": 'b4cc509b-a510-11ec-ae34-08d23efcc354', "projPickName": "N1s2",
            "botDepth": 3, "symbolBotDepth": 1},
           {"projPickId": 'b4cc509b-a510-11ec-ae34-08d23efcc355', "projPickName": "N1s2",
            "botDepth": 1, "symbolBotDepth": 1}]
    
    Data = pd.DataFrame(Data)
    
    ##################获取key对应的类型
    dtypedict = mapping_df_types(Data)
    
    ##############定义数据库与表名
    conn = cx_Oracle.connect('cloud/cloud@10.1.1.76:1521/yxsd')
    tablename_ori = 'r_test_table'
    
    #################判断是否有一个表
    if_have_table = judge_if_have_this_table(tablename_ori)
    
    #####################创建一个表格（需要提前判断是否有这个表格）
    if if_have_table==False:
        orical_creat_table(tablename_ori, dtypedict)
    
    #######################按行上传数据
    insert_sql_by_row(tablename_ori)

创建数据库(create_engine和cx_Oracle)

from sqlalchemy import create_engine
self.engine = create_engine("oracle+cx_oracle://用户名:密码@主机号:1521/服务名")

如果密码里含有@，可以使用URL编码来解决此问题。URL编码将特殊字符转换为URL安全的表示形式，以避免与URL语法冲突。在Python中，可以使用urllib.parse模块中的quote函数对密码进行URL编码

from sqlalchemy import create_engine
from urllib.parse import quote
sql_password = 'password@123.0'
sql_password = quote(sql_password)
dsn = '10.1.2.191:1521/yxsd'
username = 'zncc'
# 构建连接字符串
connection_string = f"oracle+cx_oracle://{username}:{sql_password}@{dsn}"
# 创建引擎
engine = create_engine(connection_string)

def connect_oracle_database():
    sql_password = 'Yxsd@123.0'
    dsn = cx_Oracle.makedsn('10.200.0.22', 1521, service_name='cloud')
    username = 'zncc'
    conn = cx_Oracle.connect(username, sql_password, dsn)
    # 或者
    # conn = cx_Oracle.connect('zncc/"Yxsd@123.0"@10.200.0.22/cloud')

    cur = conn.cursor()
    return conn, cur

cx_oracle的demo

import cx_Oracle
class oracle_demo:
    def __init__(self):
        self.conn = self.conn_ora()
        self.tablename = '"MODULE_USAGE"'
     
    def conn_ora(self):
  		conn = cx_Oracle.connect('用户名/密码@主机号:1521/服务号')
        return conn

    def close_ora(self):
        self.conn.close()

    def insert_key(self):#增加一列
        cur = self.conn.cursor()
        sql = "alter table" + self.tablename + "add (occupyType  VARCHAR2(100))"
        cur.execute(sql)
        cur.close()

    def deldata(self, exist_id):
        cur = self.conn.cursor()
        sql = "delete from "+self.tablename + 'where "LIC_SERVICE_ID" in('+ exist_id +')'
        try:
           cur.execute(sql)
           self.conn.commit()
           cur.close()
           print("数据删除成功")
        except:
            self.conn.rollback()
            print("语句执行错误")
            cur.close()

sql语句

sql = "select * from " + db_name  # 从数据库对应文件中进行查找操作
#执行sql
data = pd.read_sql(sql, self.engine)

注：pd.read_sql要用create_engine创建数据库

读取数据库某文件的数据类型

select_db_filename1 = "'" + "DIRECTIONALSURVEY"+ "'"
sql1 = "select A.COLUMN_NAME, A.DATA_TYPE from user_tab_columns A where TABLE_NAME = " + select_db_filename1 + " order by COLUMN_ID"

读取字段名和对应的数据

select_db_filename2 = "POSITIONLOG"
sql2 = "select * from " + select_db_filename2  # 从数据库对应文件中进行查找操作
db_data2 = pd.read_sql(sql2, self.engine)  # 在读取的库中执行sql语句
db_Fieldname2 = np.array(list(map(lambda x: x.upper(), db_data2.columns)))  # 读取数据库中对应文件的字段名（第一行），数据库字段名变大写
db_values2 = db_data2.values  # 数据库中数据

json存入redis

import redis
import json

redisClient = redis.StrictRedis(host='10.1.1.xx', port=6379, db=8,decode_responses=True, charset='UTF-8', encoding='UTF-8')

json_return_data = json.dumps(pd_data)
print('从json读出的数据：\n', json.loads(json_return_data))

redisClient.set(filename, json_return_data)#存入redis
redisClient.delete(filename)#从redis删除
load = redisClient.get(filename)#从redis中获取
print("从redis读出的数据：\n", load)