无法使用python cx_oracle连接到表

发布于 2025-01-17 09:38:21 字数 960 浏览 2 评论 0原文

当我在CX_oracle中使用某个条件时，我会收到上述错误。但是，当我获取所有行时，没有错误。请在下面找到我的代码。输入-OLS是我要获取的所有列的列表。同样，我也想将其中的条件作为变量传递，其中我要在循环中传递值。

为了进行测试，让我们保持静态。

谢谢

 cols = [
        'ID',
        'CODE',
        'LOGINDEX_CODE',
        'IS_ACTIVE',
        'IS_PORT_GROUP'
        ]
table_name = 'PORT'

os.chdir(os.path.dirname(__file__))
main_dir = os.getcwd()

import cx_Oracle

try:
    cx_Oracle.init_oracle_client(lib_dir= main_dir + "\\instantclient_21_3\\")
except:
    pass

dsn_tns = cx_Oracle.makedsn(r'some_db', 1521, service_name=r'some_service')
conn = cx_Oracle.connect(user='abcdef', password=r'ghijkl', dsn=dsn_tns).cursor()

query_cols = ', '.join(cols)
named_params = {
                'varx'  :   query_cols,
                'vary'  :   'LEG',
                'varz'  :   242713
                }


sql_query = 'SELECT :varx FROM :vary WHERE START_PORT_ID IN :varz'
conn.prepare(sql_query)
conn.execute(sql_query, named_params)

原文

I'm getting the mentioned error while I'm using a WHERE condition in cx_oracle.
However, there is no error while I fetch all rows.
Please find my code below. The input - cols is a list of all the columns I want to fetch. Similarly, I want to pass the where condition as variable too, wherein I'm passing on the values in a loop.

For testing, lets keep it static.

Thanks

 cols = [
        'ID',
        'CODE',
        'LOGINDEX_CODE',
        'IS_ACTIVE',
        'IS_PORT_GROUP'
        ]
table_name = 'PORT'

os.chdir(os.path.dirname(__file__))
main_dir = os.getcwd()

import cx_Oracle

try:
    cx_Oracle.init_oracle_client(lib_dir= main_dir + "\\instantclient_21_3\\")
except:
    pass

dsn_tns = cx_Oracle.makedsn(r'some_db', 1521, service_name=r'some_service')
conn = cx_Oracle.connect(user='abcdef', password=r'ghijkl', dsn=dsn_tns).cursor()

query_cols = ', '.join(cols)
named_params = {
                'varx'  :   query_cols,
                'vary'  :   'LEG',
                'varz'  :   242713
                }


sql_query = 'SELECT :varx FROM :vary WHERE START_PORT_ID IN :varz'
conn.prepare(sql_query)
conn.execute(sql_query, named_params)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

美人迟暮 2025-01-24 09:38:21

绑定变量不能用于替换 SQL 语句本身的部分内容。它们只能用于提供发送到数据库的数据。所以你需要做这样的事情：

sql_query = f"select {', '.join(cols)} from LEG where start_port_id = :varz"
with conn.cursor() as cursor:
    for row in cursor.execute(sql_query, varz=242713):
        print(row)

Bind variables cannot be used to replace parts of the SQL statement itself. They can only be used to supply data that is sent to the database. So you would need to do something like this instead:

sql_query = f"select {', '.join(cols)} from LEG where start_port_id = :varz"
with conn.cursor() as cursor:
    for row in cursor.execute(sql_query, varz=242713):
        print(row)

回复收藏 0 原文

~没有更多了~