Exporting data from DB2 using Python

0
votes

We have nearly 300 reports which we download from the DB2 database on daily basis in .xlsx format and circulate to many recipients. This is done using the Data Transfer utility in the IBM Package. It’s a very time consuming task to download each and every file using the Data Transfer utility. Is there a way I could download the data in .xlsx format by running a script in Python? All reports are already created so only requirement is to connect to the DB2 database library where the reports are saved and download the data in .xlsx format.

Utility used to download the data from DB2

1
pythondb2db2-400
DB2 Library path and file name is given in the image – Shabeen Zarook
If you want to automate the file-transfer as a batch action, without programming, the Data Transfer tool lets you do this. Follow the instructions here ibm.com/support/knowledgecenter/ssw_ibm_i_73/ifs/… – mao

1 Answers

1
votes

You could use the Python module pandas. There is a Stack Overflow answer showing how to load a Db2 result set into a pandas DataFrame. pandas has the built-in function to_excel() which allows to generale an xlsx file.

If you want to run the Python script on the Db2 server but download it to a local machine, you can take a look into my blog how to use the function to generate in-memory Excel or CSV files.

So this is the very condensed code with the query itself missing:

# read the Db2 result set into a DataFrame
df = pd.read_sql(sql, conn)
# write out to Excel file
df.to_excel('your_data.xslx', sheet_name='Sheet1')