0

Greetings for the day!

How can i execute an insert query using sqlalchemy, the call query is generating fine means when i manually run that query in oracle it inserts the price but not happening via python.

import wind32com.client
from sqlalchemy.engine import create_engine
import datetime
DIALECT = 'oracle'
SQL_DRIVER = 'cx_oracle'
USERNAME = 'xxx'  # enter your username
PASSWORD = 'xxx'  # enter your password
HOST = 'pv-prod-orc-01.xxx.com'  # enter the oracle db host url
PORT = 1521  # enter the oracle port number
SERVICE = 'PVL01PD_APP.ec2.internal'  # enter the oracle db service name
ENGINE_PATH_WIN_AUTH = DIALECT + '+' + SQL_DRIVER + '://' + USERNAME + ':' + PASSWORD + '@' + HOST + ':' + str(
    PORT) + '/?service_name=' + SERVICE

engine = create_engine(ENGINE_PATH_WIN_AUTH)

conn = engine.connect()

date = datetime.datetime.strptime(Date,"%Y-%m-%d").strftime("'%d%b%Y'")

sql_query = 'call CORE_VALUATIONS.VALUATIONS.INSERTEQCLOSINGPRICE(%d, %s, %f, NULL, NULL)' % (int(Pkey), date, float(price))
print(sql_query)
conn.execute(sql_query)
conn.close()

enter image description here

Rahul Vaidya
  • 189
  • 2
  • 12
  • Use `engine = create_engine(ENGINE_PATH_WIN_AUTH, echo=True)` and check the log output. After your `'call …'` statement is executed do you see a COMMIT happening? – Gord Thompson Jun 08 '22 at 17:02
  • seems like call query is not getting executed as the price is not getting inserted in database – Rahul Vaidya Jun 08 '22 at 19:09
  • The recommended approach is to use a context manager (`with` block). See the examples [here](https://docs.sqlalchemy.org/en/14/tutorial/dbapi_transactions.html#committing-changes). – Gord Thompson Jun 08 '22 at 19:12
  • i think i need some more research – Rahul Vaidya Jun 08 '22 at 19:57

0 Answers0