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.
About Jeffrey Kemp
Application Designer & Developer at Oracle specialising in Oracle APEX (Application Express), Oracle SQL and PL/SQL. Oracle ACE Alumni ♠️. Piano player, father, husband, Christian. The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.
Venu
17 October 2018 - 1:34 am
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.
Jeffrey Kemp
17 October 2018 - 8:21 am
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
Carl
5 September 2019 - 9:24 pm
Thanx! It works 🙂
Pradeep
31 December 2020 - 1:44 am
How much data it can handle? I have 250M records, will this script works?
I’m sorry u haven’t tried but please suggest.
Jeffrey Kemp
31 December 2020 - 9:20 am
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.