CODEWITHSUNDEEP
pythonsqlcsvsnowflake-cloud-data-platform
JMV12
JMV12

Reputation: 1045

Python Output Snowflake Connector To CSV

I currently am trying to print the output of a snowflake connector query and write it to csv, but I'm having issues doing this. Currently, my code example looks like:

query = '''SELECT TOP 10 * FROM TABLE;'''

conn = snowflake.connector.connect(user='XXXX',
                                   password='XXXX',
                                   account='XXXX')

query_output = conn.cursor().execute(query)

Is there a way for me to print the output of the query, like maybe print(query_output) and then also write it to csv like query_output.to_csv(path)?

Upvotes: 1

Views: 7963

Answers (3)

Ankur Srivastava
Ankur Srivastava

Reputation: 923

You can do like this :

import csv
.....
.....
.....
rows = cur.fetchall()
column_names = [i[0] for i in cur.description]
with open('samplefile.csv', 'w') as fp:
  myFile = csv.writer(fp, lineterminator = '\n')
  myFile.writerow(column_names)
  myFile.writerows(rows)

Upvotes: 3

user13472370
user13472370

Reputation:

I highly recommend using Pandas' DataFrames API. Amongst several other data transformation and analysis capabilities, it has an excellent CSV reader and writer implementation that is very flexible.

Snowflake's Python Connector also natively supports transforming results as Pandas DataFrame objects.

A very simple, adapted example is provided below (1 added line):

query = '''SELECT TOP 10 * FROM TABLE;'''

conn = snowflake.connector.connect(user='XXXX',
                                   password='XXXX',
                                   account='XXXX')

query_output = conn.cursor().execute(query)

## You may have to run: pip3 install --user pandas ##

query_output.fetch_pandas_all().to_csv("/path/to/write/table.csv")

The default CSV formatting uses , as the separator, OS-specific newlines (\n except on Windows, which uses \r\n), and minimal quoting (auto-quotes VARCHAR columns that carry delimiter characters within them).

Upvotes: 4

VTi
VTi

Reputation: 1319

You can use this to print the results. To save it in csv, just save the results in a dataframe using for loop and then use df.to_csv

    cursor.execute(query)
    sql_result = cursor.fetchall()
    print(sql_result)     ## just print the results or use the for loop below to print the results row by row 
    for row in sql_result:
        print(row)

Upvotes: 0

Related Questions