如何使用Python从数据框中解析proc中的变量值

发布于 2025-02-03 18:24:58 字数 1347 浏览 2 评论 0原文

我已经编写了一个代码来从电子邮件主体中选择特定值并将其存储在数据框架中，现在下一步是将这些值存储在Oracle数据库中，因为我正在使用Sqlalchemy，但我不确定如何将这些值传递给存储过程像下面的

call CORE_VALUATIONS.VALUATIONS.INSERTEQCLOSINGPRICE("**SGEPSBSH**",to_date('"&TEXT(**2022-06-01**,"DDMMMYYYY")&"','ddmonyyyy'),"**111.9852**",NULL,NULL);

from sqlalchemy.engine import create_engine
import datetime


 today = datetime.date.today()

DIALECT = 'oracle'
SQL_DRIVER = 'cx_oracle'
USERNAME = 'robinhood'  # enter your username
PASSWORD = 'XXXXXX'  # enter your password
HOST = 'pv-prod-orc-01.XXXXX.com'  # enter the oracle db host url
PORT = 1521  # enter the oracle port number
SERVICE = 'XXXX_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)

# test query


query = """
call CORE_VALUATIONS.VALUATIONS.INSERTEQCLOSINGPRICE("**SGEPSBSH**",to_date('"&TEXT(**2022-06-01**,"DDMMMYYYY")&"','ddmonyyyy'),"**111.9852**",NULL,NULL);

"""
con = engine.connect()
outpt = con.execute(query)
con.close()

原文

I have written a code to pick specific values from email body and store it in dataframe now the next step is to store those values in oracle database for that I am using sqlalchemy but I am not sure how I can pass those values to a stored procedure like below

call CORE_VALUATIONS.VALUATIONS.INSERTEQCLOSINGPRICE("**SGEPSBSH**",to_date('"&TEXT(**2022-06-01**,"DDMMMYYYY")&"','ddmonyyyy'),"**111.9852**",NULL,NULL);

from sqlalchemy.engine import create_engine
import datetime


 today = datetime.date.today()

DIALECT = 'oracle'
SQL_DRIVER = 'cx_oracle'
USERNAME = 'robinhood'  # enter your username
PASSWORD = 'XXXXXX'  # enter your password
HOST = 'pv-prod-orc-01.XXXXX.com'  # enter the oracle db host url
PORT = 1521  # enter the oracle port number
SERVICE = 'XXXX_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)

# test query


query = """
call CORE_VALUATIONS.VALUATIONS.INSERTEQCLOSINGPRICE("**SGEPSBSH**",to_date('"&TEXT(**2022-06-01**,"DDMMMYYYY")&"','ddmonyyyy'),"**111.9852**",NULL,NULL);

"""
con = engine.connect()
outpt = con.execute(query)
con.close()

分享到QQ

分享到微博

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

发布评论

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

聽兲甴掵 2025-02-10 18:24:58

如何将这些值传递给存储过程

.execute（sql_text，dict_of_param_values），例如，

import sqlalchemy as sa

# …

sql_text = sa.text(
    "call CORE_VALUATIONS.VALUATIONS.INSERTEQCLOSINGPRICE(:param1, :param2, :param3, NULL, NULL);"
)
dict_of_param_values = dict(param1="SGEPSBSH", param2="2022-06-01", param3=111.9852)
with engine.begin() as conn:
    conn.execute(sql_text, dict_of_param_values)
# (transaction will automatically commit when `with` block exits)

how can I pass those values to stored procedure

Call .execute(sql_text, dict_of_param_values), e.g., something like

import sqlalchemy as sa

# …

sql_text = sa.text(
    "call CORE_VALUATIONS.VALUATIONS.INSERTEQCLOSINGPRICE(:param1, :param2, :param3, NULL, NULL);"
)
dict_of_param_values = dict(param1="SGEPSBSH", param2="2022-06-01", param3=111.9852)
with engine.begin() as conn:
    conn.execute(sql_text, dict_of_param_values)
# (transaction will automatically commit when `with` block exits)

回复收藏 0 原文

~没有更多了~