Write output of a python script calling a sql script to a file
<p>I'm learning python and sql at the same time. ;)  The following python script calls a sql script.  I am trying to figure out how to get the output from the query to go to a csv.  I have gotten this far:  </p>

<hr>

<pre><code>#!/usr/bin/env python
import cx_Oracle
import sys
import csv
import os.path
import subprocess
con = cx_Oracle.connect('creden/tials@(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(Host =server.work.net)(Port = 3453)))(CONNECT_DATA = (SID = SQLTEST)))')
cur=con.cursor()

f=open('/usr/local/src/sql/script_01.sql')
    full_sql=f.read()
    sql_commands=full_sql.split(";")
for sql_command in sql_commands:
    cur.execute(sql_command)

# Here is where the magic should happen
# The output of above should go to the 'test_query' file
query_results = &lt;OUTPUT OF FOR LOOP&gt;

c=csv.writer(open("/home/user/sqlplus/test_query/test_query.csv","w"),delimiter=";",lineterminator="\r\n")
c.writerows(query_results)

cur.close()
</code></pre>

<hr>

<p>Can someone please tell me what I'm missing?</p>

<p>May be this will help you. <a href="https://cx-oracle.readthedocs.io/en/latest/cursor.html" rel="nofollow">Documentation</a>  </p>

<p>There is function fetchall() which fetches all rows of query result.</p>

<p>You can use it as</p>

<pre><code>cur.execute("sql_command")
query_result = cur.fetchall()
</code></pre>

<p>query_result will be of type tuple.</p>

<p>This will contain result of only one command at a time.</p>

<p>Now you can use list. And append all the results.</p>

<p>e.g.</p>

<pre><code>results = []
for sql_command in sql_commands:
    cur.execute(sql_command) #sql_command is of type string
    query_result = cur.fetchall()
    print(query_result)
    #query_result will be tuple so select which part of the tuple you need. e.g query_result[0], query_result[1] etc.
    results.append(query_result[0]) #change index according to your need
</code></pre>

<p>Now at the end of for loop you will have list (i.e results) having all the outputs of query.</p>

<p>By using another for loop over results(which is a list) you can get the result of each query one by one 
and can write in csv file.</p>

<p>I don't now how to handle csv file. Maybe you need to save csv file at each iteration.
I hope you can handle this.           </p>

python sql oracle