Create Excel file from SQL query using Python

This will create a file called “emp.xls” with a nicely formatted spreadsheet based on the query specified. The header row is based on the column names, and is frozen. The column widths are set according to the max width of the data.

import cx_Oracle
from table2Excel import write_cursor_to_excel

orcl = cx_Oracle.connect('scott/tiger')
curs = orcl.cursor()
curs.execute("""
  SELECT e.ename "Employee",
         e.job "Job",
         e.hiredate "Hire Date",
         e.sal "Salary",
         e.comm "Commission",
         d.dname "Department",
         (SELECT ename FROM emp WHERE emp.empno = e.mgr) "Manager"
  FROM   emp e, dept d
  WHERE  e.deptno = d.deptno
""")
write_cursor_to_excel(curs, 'emp.xls', 'Employees')

You can get the source for table2Excel from here [table2Excel.py.txt]. Feel free to adapt it to your needs.

Great number formatting trick
Two questions about stored outlines

Comments

  1. Hi Jeff,

    Thanks.

    Trying to run the script but it fails. Can you please let me know if anything to be changed apart from db connections and sql.
    I am begginer to python so had to ask this question.

    • Hi Venu,

      Please check to see if any error message is displayed when you run the script. Read the error message, it might help you to discover what’s gone wrong.

      Please note that this article was written over nine years ago, so some things may have changed since then.

      Cheers,
      Jeff

  2. Thanx! It works 🙂

  3. How much data it can handle? I have 250M records, will this script works?
    I’m sorry u haven’t tried but please suggest.

    • Hi Pradeep, given that Microsoft XLS files support a maximum of 1,048,576 rows per sheet, I suspect it is not the best solution for you. In your situation I would probably look for an alternative that can export as XLSX.

Leave a Reply

Your email address will not be published / Required fields are marked *