How to create sql db2 data table using python?

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.

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)

Advertisement

Answer