python连接oracle使用cx_Oracle模糊查询报错的处理方法

一、倒述python连接oracle的最后取数困难：

1、Oracle区分大小写，单引号统一变大写处理；
2、模糊查询不能直接将平时用SQL查询工具软件能用的贴。
例如：SELECT *
  FROM DLYB_REC
 WHERE SDT >= TRUNC(SYSDATE)
   AND SNAME = 'ModBus.S08-DTU.DLYB2_2_Ps' （或者LIKE '%DLYB2_2_Ps%'）
 ORDER BY SNAME, SDT

import cx_Oracle
 
# 连接Oracle数据库
conn = cx_Oracle.connect('SENSOR/SENSOR@172.16.30.166:1521/orcl')

# 创建游标
cur = conn.cursor()

# 执行模糊查询
query = "SELECT * FROM DLYB_REC WHERE SDT >= TRUNC(SYSDATE) AND SNAME LIKE :name_pattern ORDER BY SNAME, SDT"
name_pattern = '%ModBus.S08-DTU.DLYB2_2_Ps%'  # 使用通配符进行模糊匹配
cur.execute(query, name_pattern=name_pattern)
 
# 获取查询结果
result = cur.fetchall()
 
# 打印查询结果
for row in result:
    print(row)
 
# 关闭游标和连接
cur.close()
conn.close()

二、安装python第三方库报错处理

import cx_Oracle

# 连接Oracle数据库
conn = cx_Oracle.connect('USR/PWD@172.16.30.1:1521/orcl')

# 创建游标
cur = conn.cursor()

# 执行模糊查询
query = []
name_pattern = []
query = ["SELECT SDT,SPV FROM DLYB_REC WHERE SDT >= TRUNC(SYSDATE)-1 AND SNAME LIKE :name_pattern ORDER BY SNAME, SDT"]
name_pattern = ['%ModBus.S08-DTU.DLYB2_2_Ps%']  # 使用通配符进行模糊匹配
query.append("SELECT SDT,SPV FROM DLYB_REC WHERE SDT >= TRUNC(SYSDATE)-1 AND SNAME LIKE :name_pattern ORDER BY SNAME, SDT")
name_pattern.append('%ModBus.S08-DTU.DLYB1_1_Ps%')  # 使用通配符进行模糊匹配

cur.execute(query[0], name_pattern=name_pattern[0]) 
result = cur.fetchall()
x = []
y = []
for row in result:
    x.append(row[0])
    y.append(row[1])

cur.execute(query[1], name_pattern=name_pattern[1]) 
result = cur.fetchall()
u = []
v = []
for row in result:
    u.append(row[0])
    v.append(row[1])

# 关闭游标和连接
cur.close()
conn.close()

import matplotlib.pyplot as plt
import numpy as np
from matplotlib.gridspec import GridSpec
import matplotlib.dates as mdates

# 创建图形和轴
#fig, ax = plt.subplots()
fig = plt.figure(figsize=(15, 10)) # 设置画布尺寸
gs = GridSpec(2, 2, figure=fig, wspace=0.3, hspace=0.3)  # 创建2x2的GridSpec布局
ax1 = fig.add_subplot(gs[0, :])  # 第1行全宽子图
ax2 = fig.add_subplot(gs[1, :])  # 第2行第2列子图

# 使用线性回归拟合趋势线
slope, intercept = np.polyfit( mdates.date2num(x), y, 1)
trendline = slope * mdates.date2num(x) + intercept
# 绘制趋势线
ax1.plot(mdates.date2num(x), trendline, color='red', linestyle='--', label='Trendline')

# 使用线性回归拟合趋势线
slope, intercept = np.polyfit( mdates.date2num(u), v, 1)
trendline = slope * mdates.date2num(u) + intercept
# 绘制趋势线
ax2.plot(mdates.date2num(u), trendline, color='red', linestyle='--', label='Trendline')

# 绘制线图
ax1.plot(x, y)
ax2.plot(u, v)

# 设置x轴为日期格式并自定义间隔
ax1.xaxis.set_major_locator(mdates.HourLocator(interval=1))  # 每小时一个刻度
ax1.xaxis.set_major_formatter(mdates.DateFormatter('%H'))  # 设置时间格式为小时

# 添加标题和标签
ax1.set_title("S08 FP") # 分变所用电功率监测曲线
ax1.set_xlabel("Hour")
ax1.set_ylabel("active power W")

ax2.set_title("S08 XP") # 分变所用电功率监测曲线
ax2.set_xlabel("datetime YY-MM Hour")
ax2.set_ylabel("active power W")

# 显示图形
plt.show()