I would like to run an sql db2 query on python that will create a data table in a public schema but I’m stuck because of this error
ResourceClosedError: This result object does not return rows. It has been closed automatically.
Below is the Python code I’m using, I have deleted the private information.
import numpy as np
import pandas as pd
from sqlalchemy import create_engine
#connection string
user = "xxx"
pwd = "xxx"
host = "xxx"
port = "xxx"
db = "xxx"
conn_strng = "redshift+psycopg2://%s:%s@%s:%s/%s" %(user,pwd,host,port,db)
#establish connection
engine = create_engine(conn_strng)
with engine.connect() as conn, conn.begin():
pd.read_sql("""
drop table if exists public.fc_SER_ACC_By_DLCX_Date;
create table public.fc_SER_ACC_By_DLCX_Date as
SELECT DLCX_Date, tool_id as DLCX_Tool,Model, avg(SQZSER) as SQZSER_Mean, avg(SQZSER_BASE) as SQZSER_BASE_Mean,
avg(PRED_ACC_SMR) as PRED_ACC_SMR_Mean, avg(PRED_ACC_CMR) as PRED_ACC_CMR_Mean, count(slider_id) as Tested_Sliders
FROM (SELECT DISTINCT a.slider_id, LEFT(a.product_id,2) as Model,
a.xti_wrn1_p23 AS SQZSER,a.SER0 AS SQZSER_BASE, a.FOM2 AS PRED_ACC_SMR, a.FOM1 AS PRED_ACC_CMR,
TRUNC(c.transaction_date_time) as DLCX_Date, c.tool_id
FROM ah.param_jade_wide a
LEFT JOIN ah.param_lap_summary b ON (a.wafer_id, a.row_number) = (b.wafer_id, b.row_number)
LEFT JOIN ah.his_job c ON c.job_number = b.job_number
WHERE c.transaction_date_time > '2020-03-01'
AND LEFT(a.product_id,2) IN ('C3')
AND b.source_system_code IN ('MFG2.SLDR.LAPRUN')
AND a.xti_wrn1_p23 between -10 and 0
AND a.SER0 between -10 and 0
AND c.operation_id IN ('510150')
AND a.retest_number = 0
AND a.class_description IN ('PROD')
AND NOT c.tool_id = 0 AND NOT c.tool_id in (''))
group by DLCX_Date, DLCX_Tool, Model
Union
SELECT DLCX_Date, tool_id as DLCX_Tool,Model, avg(SQZSER) as SQZSER_Mean, avg(SQZSER_BASE) as SQZSER_BASE_Mean,
avg(PRED_ACC_SMR) as PRED_ACC_SMR_Mean,'0'PRED_ACC_CMR_Mean, count(slider_id) as Tested_Sliders
FROM (SELECT DISTINCT a.slider_id, LEFT(a.product_id,2) as Model,
a.XTI_WRN1_P19 AS SQZSER,a.XTI_WRN1_P18 AS SQZSER_BASE, a.XTI_RSVD0 AS PRED_ACC_SMR,
TRUNC(c.transaction_date_time) as DLCX_Date, c.tool_id
FROM ah.param_jade_wide a
LEFT JOIN ah.param_lap_summary b ON (a.wafer_id, a.row_number) = (b.wafer_id, b.row_number)
LEFT JOIN ah.his_job c ON c.job_number = b.job_number
WHERE c.transaction_date_time > '2020-03-01'
AND LEFT(a.product_id,2) IN ('L2','L3')
AND b.source_system_code IN ('MFG2.SLDR.LAPRUN')
AND c.operation_id IN ('510150')
AND a.XTI_WRN1_P19 between -10 and 0
AND a.XTI_WRN1_P18 between -10 and 0
AND a.retest_number = 0
AND a.class_description IN ('PROD')
AND NOT c.tool_id = 0 AND NOT c.tool_id in (''))
group by DLCX_Date, DLCX_Tool, Model
order by DLCX_Date;
commit;""", conn)
conn.close()
engine.dispose()
print("Table has been updated!")
Please help in fixing my code and thanks in advance.
Advertisement
Answer
Error indicates Pandas read_sql
cannot import data into a data frame since you only have DDL actions: DROP TABLE
and CREATE TABLE
and nothing that return rows like SELECT
.
If not using Pandas for data analytics, simply run your queries with SQL Alchemy transactions. And no need to close
using context manager like with
:
with engine.connect() as conn:
with conn.begin()
conn.execute("""DROP TABLE IF EXISTS public.fc_SER_ACC_By_DLCX_Date;""")
conn.execute("""CREATE TABLE public.fc_SER_ACC_By_DLCX_Date AS
""")
Or combined:
with engine.begin() as conn:
conn.execute("""DROP TABLE IF EXISTS public.fc_SER_ACC_By_DLCX_Date;""")
conn.execute("""CREATE TABLE public.fc_SER_ACC_By_DLCX_Date AS
""")
And if you really do need a data frame, use engine
object in read_sql
after transactions:
df = pd.read_sql("""SELECT * FROM public.fc_SER_ACC_By_DLCX_Date;""", engine)