Python Import CSV into Postgres

Introduction

In this article we learn how to use Python to import a CSV into Postgres by using psycopg2’s “open” function for comma-separated value text files and the “copy_from” function from that same library. Here’s how we’ll do it:

  • What? We’ll study two functions to use for importing a text file and copying that data into a PostgreSQL table. We’ll also learn how to use the “render_template” function so we can show an error page to the user.
  • How? Finally, we’ll build a Python application to use both the “open()” and “copy_from()” Python functions.

Prerequisites

  • You will want to have some experience with the basics of using Postgres, whether you are using the free “PGadmin” tool or another database admin tool. And/or writing applications with languages like Python, Javascript, PHP, C#, Java, ASP.Net, VB.Net, Note.js, Ruby, etc. to write to a PostgreSQL database. Here, we are using Visual Studio Code to write Python and Dbeaver to manage our Postgres database.
  • Optional but helpful: Tutorial on naming conventions explaining why we prefix our variables, column names, table names, etc. as you see us doing in this article. For example, naming “tvariable_name” with the “t” you see at the beginning in order to delineate it as a “text” (string) object and “tbl_” before table names in order to clearly mark those objects as tables.

Before writing our Python application, 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.

Python’s Open() function

This function requires use of the Python “psycop2g” library. Prerequisite and syntax:

import psycopg2
f_contents = open('[path and file name]', '[r OR w]')

Analysis

  • “import psycopg2”: Python needs this library for the “open” function to be used.
  • “open(‘path-to/text-file.csv’, ‘[r OR w]’)”: We supply the “open” function with a file name and either “r” or “w”, meaning read or write, respectively. The function fills a Python array type with the text delineated in the file designated as the “path and file name” parameter.

Connecting to the Postgres database

from flask import Flask
from flask import render_template # to render the error page
import psycopg2
t_host = "PostgreSQL database host address" # either "localhost", a domain name, or an IP address.
t_port = "5432" # 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()

Analysis

  • “from flask import render_template”: Later in our code we will error check. In order to send the user to a dynamic HTML page we built, we need the “render_template()” function.
  • “t_host…t_pw”: This is where we set up our Postgres database credentials (parameters) like database host address, port, database name, user name, and password.
  • “db_conn”: Here we are simultaneously creating a database object and handing it the parameters it needs.
  • “db_cursor”: This is where we create a “database cursor”, which we’ll use for database operations.

Python’s Copy_From() function

This function also requires the Python “psycop2g” library. Another library we are adding is flask’s “render_template” so we can show an HTML template to the user. We also need to connect to the database and set up a “cursor” object.

Copy_from() reads text data from a file, adding that data to a database table. Here is some code so you can see this function used in context and to start building our application:

f_contents = open('data/users.csv', 'r')
db_cursor.copy_from(f_contents, "tbl_users", columns=('t_name_user', 't_email'), sep=",")

Analysis

  • “f_contents”: We use Python’s “open” function to create a two dimensional array, “f_contents”, and fill it with data.
  • “copy_from”: Here we copy “f_contents” into the “t_name_user” and “t_email” columns in the “tbl_users” table.
  • “sep=’,'”: This tells Python the delineater in the text file is a comma.

Understanding the render_template() function

PostgreSQL’s “Render_Template” function allows us to display an HTML page for a user, and it can be filled with dynamic content we control with parameters. Do not confuse this function with the redirect function, which will send the user away without waiting for a return.

Syntax of the Render_Template() function

RETURN render_template(t_name_page, t_message = "a value")

We feed the RENDER_TEMPLATE function with these parameters:

  • “t_name_page”: The name of our template. Be aware that you will need to store your HTML templates in a folder called “/templates”.
  • “t_message”: The contents of “t_message” will be sent, along with the user, to the page designated in “t_name_page”, an html template you create, that is set up to receive and use the value stored in “t_message”.

Here’s an HTML error page template we created so you don’t have to:

<html>
  <head>
    <link rel="shortcut icon" href="favicon.ico" />
    <title>Test Python Template</title>
  </head>
  <body>
    <!-- The message you see below is why we call this a template -->
    <!-- message is filled in by the Python file as it calls up this HTML -->
    <!-- Later you may wish to separate page title from message -->
    <h1>Import CSV Error Page</h1>
    <!-- div to contain form -->
    <div>
      <!-- ================ -->
      {{t_message}}
      <!-- ================ -->

      <!-- Close the container div -->
    </div>
    <!-- Close the body of the HTML document -->
  </body>
</html>

The thing to note about the HTML script you see above is “{{t_message}}” The error message you will see in the Python code below will get sent to this page, “error_page.html”, and the spot where you see {{t_message}} will be filled with the value in the “t_message” parameter in the render_template() function.

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

from flask import Flask
from flask import render_template # to render the error page
import psycopg2

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

t_host = "PostgreSQL database host address" # either "localhost", a domain name, or an IP address.
t_port = "5432" # 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("/import")
def csv_import():
    # Trap errors for opening the file
    try:
        t_path_n_file = "data/users.csv"
        f_contents = open(t_path_n_file, 'r')
    except psycopg2.Error as e:
        t_message = "Database error: " + e + "/n open() text file: " + t_path_n_file
        return render_template("error_page.html", t_message = t_message)

    # Trap errors for copying the array to our database
    try:
        db_cursor.copy_from(f_contents, "tbl_users", columns=('t_name_user', 't_email'), sep=",")
    except psycopg2.Error as e:
        t_message = "Database error: " + e + "/n copy_from"
        return render_template("error_page.html", t_message = t_message)

    # It got this far: Success!

    # Clean up by closing the database cursor and connection
    db_cursor.close()
    db_conn.close()

    # Now send the user onward...

INCREASE SECURITY: Use Stored Procedures

Protect the integrity of your users’ data by learning about

Conclusion

In this tutorial we learned how to use Python to import a CSV file into two columns in a Postgres table. We used a Python array, the file “open()” and “copy_from” functions from the psycopg2 library, and the “render_template” function from the Flask framework, to show error reports to the user. We took it step by step to build an “import from CSV into PostgreSQL” application using Python and PostgreSQL. Finally, we shared all the source script for this Python application here for you to copy into any of your projects.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.