Python Output Snowflake Connector To CSV

Question

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)?

Just curious, after writing are you going to import it in pandas? — sam
– sam, Commented Jun 8, 2020 at 16:24

score 4 · Accepted Answer · 2020-06-05 01:17:34Z

4

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).

edited Jun 5, 2020 at 1:17

answered May 29, 2020 at 10:03

user13472370

Sign up to request clarification or add additional context in comments.

3 Comments

anyfactor Over a year ago

You need to install Pandas with this command pip install "snowflake-connector-python[pandas]". Docs: docs.snowflake.com/developer-guide/python-connector/…

Skippy le Grand Gourou Over a year ago

@anyfactor Or just pip install pyarrow if you already have Pandas, as implied with the error message

snowflake.connector.errors.ProgrammingError: 255002: Optional dependency: 'pyarrow' is not installed, please see the following link for install instructions: https://docs.snowflake.com/en/user-guide/python-connector-pandas.html#installation

.

Skippy le Grand Gourou Over a year ago

This solution and the csv module one seem about as efficient.

Skippy le Grand Gourou · Accepted Answer · 2023-04-21 15:43:49Z

3

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)

edited Apr 21, 2023 at 15:43

Skippy le Grand Gourou

7,8526 gold badges65 silver badges82 bronze badges

answered May 28, 2020 at 19:27

Ankur Srivastava

9239 silver badges10 bronze badges

1 Comment

Skippy le Grand Gourou Over a year ago

This solution and the Pandas one seem about as efficient.

VTi · Accepted Answer · 2020-05-28 17:23:02Z

0

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)

answered May 28, 2020 at 17:23

VTi

1,3298 silver badges14 bronze badges

Collectives™ on Stack Overflow

Python Output Snowflake Connector To CSV

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related