Need to execute SQL query dynamically

Question

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

Z. Clément · Accepted Answer · 2018-06-09 08:36:46Z

3

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.

edited Jun 9, 2018 at 8:36

answered Jun 9, 2018 at 8:03

Z. Clément

4744 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Simran Tuteja Over a year ago

its showing a error-

cur.execute("UPDATE customer SET customerName=%s WHERE  customerName=%s",(original_name,new_name)) cx_Oracle.DatabaseError: ORA-01036: illegal variable name/number

Z. Clément Over a year ago

I added the explanation in the answer .

Collectives™ on Stack Overflow

Need to execute SQL query dynamically

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related