CODEWITHSUNDEEP
python-3.xoracle-databaseoracle11gpycharm
Simran Tuteja
Simran Tuteja

Reputation: 33

Need to execute SQL query dynamically

I need to update name of customer using the input provided by the user. original_name need to be updated by new_name

cur.execute(''' 
CREATE TABLE customer (customerId int,customerName varchar(20),telephoneNo int,
                    addressId varchar(20), customerType varchar(20),
                    discount int, memCardType varchar(20))''')

I'm trying to perform this code, but it gives an error.

    original_name=input("Enter name to be modified: ")
    new_name=input("Enter new name of the customer: ")
    cur.execute("UPDATE customer SET customerName=new_name WHERE  customerName=original_name   ")
    print("Customer name changes successfully.")

Error:

 cur.execute("UPDATE customer SET customerName=new_name WHERE  
customerName=original_name   ")
cx_Oracle.DatabaseError: ORA-00904: "ORIGINAL_NAME": invalid identifier

Upvotes: 2

Views: 72

Answers (1)

Z. Clément
Z. Clément

Reputation: 474

I suppose you use a SQL cursor so I would do it like that :

original_name=input("Enter name to be modified: ")
new_name=input("Enter new name of the customer: ")
cur.execute("UPDATE customer SET customerName=:new_name WHERE  customerName=:cust_name",{'cust_name': original_name, 'new_name' : new_name})
print("Customer name changes successfully.")

The concept is simple. :new_name and :cust_name are binding parameters, in fact you told to your function you will add this parameter as variable then in 

{'cust_name': original_name, 'new_name' : new_name}

You just fill those binding parameters with your variables.

Upvotes: 3

Related Questions