title: How to save results of postgresql to csv/excel file using psycopg2?
body: <p>I use <strong>driving_distance</strong> in postgresql to find distances between all nodes, and here's my python script in pyscripter,</p>
  
  <pre><code>import sys
  
  #set up psycopg2 environment
  import psycopg2
  
  #driving_distance module
  query = """
      select *
      from driving_distance ($$
          select
              gid as id,
              start_id::int4 as source,
              end_id::int4 as target,
              shape_leng::double precision as cost
          from network
          $$, %s, %s, %s, %s
      )
  ;"""
  
  #make connection between python and postgresql
  conn = psycopg2.connect("dbname = 'routing_template' user = 'postgres' host = 'localhost' password = '****'")
  cur = conn.cursor()
  
  #count rows in the table
  cur.execute("select count(*) from network")
  result = cur.fetchone()
  k = result[0] + 1
  
  #run loops
  rs = []
  i = 1
  while i &lt;= k:
      cur.execute(query, (i, 1000000, False, False))
      rs.append(cur.fetchall())
      i = i + 1
  
  #print result
  for record in rs:
      print record
  conn.close()
  </code></pre>
  
  <p>The result is fine, and part of the it in python interpreter looks like this,</p>
  
  <pre><code>[(1, 2, 35789.4069722436), (2, 2, 31060.0761437413), (3, 19, 30915.1312550546), (4, 3, 33438.0715007666), (5, 4, 29149.0894812718), (6, 7, 25504.020006665), (7, 7, 29594.741802956), (8, 5, 20736.2427352646), (9, 10, 19545.809601197), (10, 8, 22609.5146670393), (11, 9, 14134.5400189648), (12, 11, 12266.7845493204), (13, 18, 17426.7449057031), (14, 21, 11754.7277029158), (15, 18, 13128.3548040769), (16, 20, 21924.2253916803), (17, 11, 15209.9969992088), (18, 20, 26316.7797545076), (19, 13, 604.414419026164), (20, 16, 740.652673783403), (21, 15, 0.0), (22, 15, 2378.768084459)]
  [(1, 2, 38168.1750567026), (2, 2, 33438.8442282003), (3, 19, 33293.8993395136), (4, 3, 35816.8395852256), (5, 4, 31527.8575657308), (6, 7, 27882.788091124), (7, 7, 31973.509887415), (8, 5, 23115.0108197236), (9, 10, 21924.577685656), (10, 8, 24988.2827514983), (11, 9, 16513.3081034238), (12, 11, 14645.5526337793), (13, 18, 19805.5129901621), (14, 21, 14133.4957873748), (15, 18, 15507.1228885359), (16, 20, 24302.9934761393), (17, 11, 17588.7650836678), (18, 20, 28695.5478389666), (19, 13, 2983.18250348516), (20, 16, 3119.4207582424), (21, 15, 2378.768084459), (22, 15, 0.0)]
  </code></pre>
  
  <p>I want to export these results to a new csv or excel files, and I have looked these related post and website,</p>
  
  <ul>
  <li><a href="https://stackoverflow.com/questions/8119297/postgresql-export-resulting-data-from-sql-query-to-excel-csv">PostgreSQL: export resulting data from SQL query to Excel/CSV</a></li>
  <li><a href="https://stackoverflow.com/questions/1517635/save-postgres-sql-output-to-csv-file">save (postgres) sql output to csv file</a></li>
  <li><a href="http://initd.org/psycopg/docs/index.html" rel="noreferrer">Psycopg 2.5.3.dev0 documentation</a></li>
  </ul>
  
  <p>But still can't export these working under pyscripter, how can I do？</p>
  
  <p>I am working with postgresql 8.4, python 2.7.6 under Windows 8.1 x64.</p>
  
  <hr>
  
  <p><strong>Update#1:</strong>
  I tried the following code provided by Talvalin(thanks!),</p>
  
  <pre><code>import sys
  
  #set up psycopg2 environment
  import psycopg2
  
  #driving_distance module
  query = """
      select *
      from driving_distance ($$
          select
              gid as id,
              start_id::int4 as source,
              end_id::int4 as target,
              shape_leng::double precision as cost
          from network
          $$, %s, %s, %s, %s
      )
  """
  
  #make connection between python and postgresql
  conn = psycopg2.connect("dbname = 'TC_routing' user = 'postgres' host = 'localhost' password = '****'")
  cur = conn.cursor()
  
  outputquery = 'copy ({0}) to stdout with csv header'.format(query)
  
  with open('resultsfile', 'w') as f:
      cur.copy_expert(outputquery, f)
  
  conn.close()
  </code></pre>
  
  <p>But got error below,</p>
  
  <pre><code>&gt;&gt;&gt; 
  
  Traceback (most recent call last):
    File "C:/Users/Heinz/Desktop/python_test/driving_distance_loop_test.py", line 27, in &lt;module&gt;
      cur.copy_expert(outputquery, f)
  ProgrammingError: 錯誤:  在"語法錯誤"附近發生 %
  LINE 10:         $$, %s, %s, %s, %s
                       ^
  </code></pre>
  
  <p>Maybe I need to add something more in the code above.</p>