Write a series of SQL queries to single csv in python
<p>Basically I want to create a single csv file from several SQL queries. What I have so far:</p>

<pre><code>import pyodbc
import csv
import itertools

conn = pyodbc.connect("user")
cur = conn.cursor()

sql_queries = ['query_1', 'query_2', 'query_3']
columns = []
rows = []
for query in sql_queries:
    cur.execute(query)
    columns.append([i[0] for i in cur.description])
    rows.append(cur.fetchall())
flat_columns = list(itertools.chain.from_iterable(columns))

fp = open('temp_file.csv', 'w')
my_file = csv.writer(fp)
my_file.writerow(flat_columns)
for row in rows:
    my_file.writerows(row)
fp.close()
</code></pre>

<p>The flat_columns correctly prints all the column headers of the various queries at the top, but then the rows print sequentially (i.e., not under the correct header, except for the first query). Is there a way to get them to line up horizontally under the appropriate header? The queries produce differing numbers of rows and columns. </p>

<p><strong>EDIT</strong>
For example, let's say query1 produces 2 columns (headers1-2) and query2 and query3 each produce 1 column (header3 and header4 respectively), where query1 yields results of form 'xx', query2 'yy' and query3 'zz'. This is what I'm currently getting:</p>

<pre><code>header1  header2  header3  header4  
xx       xx       
xx       xx
xx       xx
yy
yy
zz
</code></pre>

<p>And this is what I want:</p>

<pre><code>header1  header2  header3  header4
xx       xx        yy      zz
xx       xx        yy
xx       xx
</code></pre>

<p>This answer is assuming that the variable <code>rows</code> contains a list of list of tuples like this <code>[[('xx', 'xx'), ('xx', 'xx'), ('xx', 'xx')], [('yy',), ('yy',)], [('zz',)]]</code> and that you're working with Python2</p>

<pre><code>from itertools import izip_longest

rows = [[('xx', 'xx'), ('xx', 'xx'), ('xx', 'xx')], [('yy',), ('yy',)], [('zz',)]]
final_rows = map(lambda x: reduce(lambda y,z: y+z,x),list(izip_longest(*rows, fillvalue=(None,))))

with open('temp_file.csv', 'w') as outfile:
    writer = csv.writer(outfile, delimiter = "\t")
    writer.writerow(flat_columns)
    writer.writerows(final_rows)
</code></pre>

<p>Output:</p>

<pre><code>header1 header2 header3 header4
xx      xx      yy      zz
xx      xx      yy      
xx      xx              
</code></pre>

<p>Note:
If you wish to use Python3, you may want to wrap the output of the <code>map</code> function around <code>list()</code>, and also <code>import zip_longest</code> instead of <code>izip_longest</code>.</p>

<p>I hope this helps.</p>

python sql csv