Python – PySimpleGUI – OracleDB – 将输入文本传递到Oracle语句中

huangapple go评论61阅读模式
英文:

Python - PySimpleGUI - OracleDB - Input text passed into Oracle statement

问题

我创建了一个简单的Python应用程序,在这个应用程序中,我想要将两个文本字段 'ID' 和 'TEXT' 传递到 Oracle 插入语句中。应用程序正确打开,并且函数似乎工作正常,但是这些字段的输入文本没有传递到 Oracle 语句中。

Python 代码如下:

英文:

I've made a simple Python app where I want two text fields 'ID' and 'TEXT' to be passed into the Oracle insert statement. The app opens correctly and the functions appear to work however the inputtext from those fields are not being passed into the Oracle statement.

Python Code below;

import PySimpleGUI as sg
import oracledb

oracledb.init_oracle_client(lib_dir=r"C:\oracle\instantclient_11_2")

layout =[
           [sg.Text('Text',size=(10,1)),sg.InputText("", key='-TEXT-')],
           [sg.Button('Submit'),sg.Button('Quit')] ]
         
sg.theme('dark grey 6')
window = sg.Window('Oracle - Test Insert',layout)

event,values = window.read()
if event == 'Submit':
    with oracledb.connect(user="blinx", password="ComCraft8",
                          dsn="10.10.2.25:1521/ORCL") as connection:
        sql = """
            insert into danaos.z_blinx_notes (TEXT)
            values (:1)"""
        bind_values = ['-TEXT-']
        with connection.cursor() as cur:
            cur.execute(sql, bind_values)
    
elif event == 'Quit':
    window.close()

Tried this, the app loads/builds but it throws no error but doesn't insert the record.

答案1

得分: 2

尝试使用这个代码(使用Python的oracledb库编写,它是cx_Oracle的升级版本,但你可以在下面的代码中将oracledb替换为cx_Oracle,它仍然可以工作):

import PySimpleGUI as sg
import oracledb

layout = [
           [sg.Text('Text', size=(10,1)), sg.InputText("", key='-TEXT-')],
           [sg.Button('Submit'), sg.Button('Quit')]
         ]

sg.theme('dark grey 6')
window = sg.Window('Oracle - Test Insert', layout)

event, values = window.read()
if event == 'Submit':
    with oracledb.connect(user="blinx", password="ComCraft8", dsn="10.10.2.25:1521/ORCL") as connection:
        sql = """
            insert into schema.table (ID, TEXT)
            values (some_sequence_name.nextval, :1)
            """
        bind_values = ['-TEXT-']
        with connection.cursor() as cur:
            cur.execute(sql, bind_values)
    
elif event == 'Quit':
    window.close()

换句话说:

  • 使用绑定变量以确保安全性、性能和易用性。
  • 你必须实际调用cursor.execute()才能使数据库执行操作!
  • nextval 不是有效的,你必须在其前加上序列名称。

错误ORA-00936表示SQL语句无效,因为存在"missing expression"(缺少表达式)。

英文:

Try this (written with python-oracledb, the upgraded version of cx_Oracle, but you can replace oracledb with cx_Oracle in the code below and it should still work):

import PySimpleGUI as sg
import oracledb

layout =[
           [sg.Text('Text',size=(10,1)),sg.InputText("", key='-TEXT-')],
           [sg.Button('Submit'),sg.Button('Quit')] ]
         
sg.theme('dark grey 6')
window = sg.Window('Oracle - Test Insert',layout)

event,values = window.read()
if event == 'Submit':
    with oracledb.connect(user="blinx", password="ComCraft8",
                          dsn="10.10.2.25:1521/ORCL" as connection:
        sql = """
            insert into schema.table (ID, TEXT)
            values (some_sequence_name.nextval, :1)"""
        bind_values = ['-TEXT-']
        with connection.cursor() as cur:
            cur.execute(sql, bind_values)
    
elif event == 'Quit':
    window.close()

In other words:

  • use bind variables for safety, performance, ease of use
  • you have to actually call cursor.execute() to have the database do something!
  • nextval isn't valid; you must prefix that with the sequence name

The error ORA-00936 indicates an invalid SQL statement as there is a "missing expression".

答案2

得分: 0

代码部分不需要翻译,以下是已翻译的内容:

"Better to set the keys for all Input elements, like 'UserName', 'Password', 'Hostname', 'Port', 'SID', '-ID-' and '-TEXT-' (a dash missed in your code). Then you can get the content of Input elements by their key, like values['UserName']."

"语法 - 以下是MySQL的INSERT INTO语句的语法。"

"> INSERT INTO TABLE_NAME (column1, column2,column3,...columnN) VALUES (value1, value2, value3,...valueN);"

"看起来以下语句是错误的代码,值也不正确,如 [‘-ID-’][‘-TEXT-’]"

sql2 = """insert into schema.table (ID, TEXT) values (['-ID-'], ['-TEXT-'])"""
英文:

Better to set the keys for all Input elements, like 'UserName', 'Password', 'Hostname', 'Port', 'SID', '-ID-' and '-TEXT-' (a dash missed in your code). Then you can get the content of Input elements by their key, like values['UserName'].

Syntax - Following is the syntax of the INSERT INTO statement of MySQL.

> INSERT INTO TABLE_NAME (column1, column2,column3,...columnN)
VALUES (value1, value2, value3,...valueN);

It looks like following statement is wrong code, wrong values as the ['-ID-'] and ['-TEXT-']

sql2 = """insert into schema.table (ID, TEXT) values (['-ID-'], ['-TEXT-'])"""

答案3

得分: 0

根据先前的回答和一些尝试,我已经使用以下代码使其正常工作。感谢大家的帮助!

import cx_Oracle
import PySimpleGUI as sg

layout = [
    [sg.Text('输入文本:'), sg.Input(key='text')],
    [sg.Text('输入作者:'), sg.Input(key='author')],
    [sg.Button('提交'), sg.Button('清除'), sg.Button('退出')]
]

window = sg.Window('添加笔记 - 测试', layout)

while True:
    event, values = window.read()
    if event == sg.WIN_CLOSED or event == '退出':
        window.close()
        break
    elif event == '提交':
        # 清除所有输入字段
        for key in values:
            window[key]('')
    elif event == '清除':
        # 清除所有输入字段
        for key in values:
            window[key]('') 
    text = values['text']
    author = values['author']
    conn = cx_Oracle.connect('用户名/密码@主机名:端口/实例名')
    cur = conn.cursor()
    query = 'INSERT INTO 模式名.表名 (ID, TEXT, AUTHOR) VALUES (模式名.序列名.nextval, :text, :author)'
    cur.execute(query, {'text': text, 'author': author})
    conn.commit()
    cur.close()
    conn.close()

window.close()

希望这有所帮助!

英文:

So with the help of the previous responses and some trial and error I've got it working with the below code. Thanks everyone for helping!

import cx_Oracle
import PySimpleGUI as sg

layout = [
    [sg.Text('Enter Text:'), sg.Input(key='text')],
    [sg.Text('Enter Author:'), sg.Input(key='author')],
    [sg.Button('Submit'), sg.Button('Clear'), sg.Button('Quit')]
]

window = sg.Window('Add Note - Test', layout)

while True:
    event, values = window.read()
    if event == sg.WIN_CLOSED or event == 'Quit':
        window.close()
        break
    elif event == 'Submit':
        # Clear all input fields
        for key in values:
            window[key]('')
    elif event == 'Clear':
        # Clear all input fields
        for key in values:
            window[key]('') 
    text = values['text']
    author = values['author']
    conn = cx_Oracle.connect('username/password@hostname:port/sid')
    cur = conn.cursor()
    query = 'INSERT INTO schema.tablename (ID, TEXT, AUTHOR) VALUES (schema.sequence.nextval, :text, :author)'
    cur.execute(query, {'text': text, 'author': author})
    conn.commit()
    cur.close()
    conn.close()

window.close()

huangapple
  • 本文由 发表于 2023年2月8日 22:07:51
  • 转载请务必保留本文链接:https://go.coder-hub.com/75386949.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定