/ ID: 910 Title: From Postgres to CSV with Python 910 Meta Description: In this article we'll export data from Postgres to a CSV with Python. Code samples included. Meta Keywords: Postgres CSV Python Author: orkb Template: Unstructured Tutorial Categories: PostgreSQL Tags: PostgreSQL, CSV, Python, Export Status: Published /

Introduction

In this article we study how to export data from Postgres into a CSV with Python scripting, using Python's psycopg2 "OPEN" and "COPY_EXPERT" functions for creating a comma-separated values text file while moving data into that file, along with PostgreSQL's "COPY TO" function to get the data we want out of a Postgres query. Here is how the lesson will go:

Prerequisites

Before writing our Python app, we'll learn how to use the functions mentioned above. After learning how each function works, we'll then build a chunk of code using that function to create a portion of our import application.

Postgres COPY TO function

sql COPY tbl_my_table (optional column_1, column_2, etc) | optional t_query TO optional f_my_path_and_file | optional STDOUT WITH parameter(s)

Python's OPEN function

This Python function can create a file (w), open for write (w), or open for read (r). It depends on the "psycop2g" library. Here's the syntax:

python import psycopg2 open('[path/filename.extension]', '[r OR w]') AS f_output

Analysis

Now to put our newfound understanding of the Postgres "COPY" function and Python's "Open" and "Copy_expert" functions into operation! We are going to write the following Python code so that ease of understanding and readability are prioritized vs what all is needed for your application. Not to worry because at the end of this tutorial we will provide you with the full, working source code.

``` python

create a query to specify which values we want from the database.

s = "'" s += "SELECT *" s += " FROM " s += "tbl_users" s += "'"

set up our database connection.

conn = psycopg2.connect... db_cursor = conn.cursor()

Use the COPY function on the SQL we created above.

SQL_for_file_output = "COPY ({0}) TO STDOUT WITH CSV HEADER".format(s)

Set up a variable to store our file path and name.

t_path_n_file = "c:\data\users.csv" WITH Open(t_path_n_file, 'w') as f_output: db_cursor.copy_expert(SQL_for_file_output, f_output) ```

Analysis

Now that we have built and scrutinized the critical and potentially new functions needed for our application, let's look at a full listing of the source code you can copy directly into your projects:

Full Source Code in Python

``` python from flask import Flask import psycopg2

app = Flask(name) @app.route("/")

t_host = "Your Postgres database host address" # either a domain name, an IP address, or "localhost" t_port = "5432" # This is the default postgres port t_dbname = "database name" t_user = "database user name" t_pw = "password" db_conn = psycopg2.connect(host=t_host, port=t_port, dbname=t_dbname, user=t_user, password=t_pw) db_cursor = db_conn.cursor()

@app.route("/export") def csv_export(): s = "'" s += "SELECT *" s += " FROM " s += "tbl_users" s += "'"

# set up our database connection.
conn = psycopg2.connect...
db_cursor = conn.cursor()

# Use the COPY function on the SQL we created above.
SQL_for_file_output = "COPY ({0}) TO STDOUT WITH CSV HEADER".format(s)

# Set up a variable to store our file path and name.
t_path_n_file = "c:\data\users.csv"

# Trap errors for opening the file
try:
WITH Open(t_path_n_file, 'w') as f_output:
    db_cursor.copy_expert(SQL_for_file_output, f_output)
except psycopg2.Error as e:
    t_message = "Error: " + e + "/n query we ran: " + s + "/n t_path_n_file: " + t_path_n_file
    return render_template("error.html", t_message = t_message)

# Success!

# Clean up: Close the database cursor and connection
db_cursor.close()
db_conn.close()

# Send the user on to some kind of informative screen.

```

SECURITY TIP: Use Stored Procedures

Protect the integrity of your users' data by learning about

Conclusion

In this tutorial we learned how to export data using Postgres, CSV, and Python. We combined the use of the "COPY" Postgres function with the format, open, and copy_expert Python functions. Some other functions we used here were render_template() so that we could do some error reporting. This required use of some "try and except" error checking. We explained the parts first, and then dove in and created a fully operational Python export data from Postgres application. Finally, we shared all the source script here for you to use in your project.