psycopg2: Connect Python to PostgreSQL Database – Part II

psycopg2

In a previous tutorial, we looked at how to create a simple PostgreSQL database of temperature across different world cities.

The PostgreSQL database was created through a Linux terminal, and the same was then connected to R to import data/commit queries.

If you haven’t already, I would recommend reading part I of my tutorial to get a bit more familiar with the background.

Here, we’ll take a look at how this can be done using the psycopg2 library in Python.

1. We import our psycopg2 library, and enter our database credentials:

import psycopg2
conn = psycopg2.connect("dbname=weather user=postgres password=rainbow host=localhost")

2. Now, let’s execute two commands. Firstly, we wish to insert an extra row of data into our database from Python directly:

cur = conn.cursor()
cur.execute("INSERT INTO cities (date, city_name,averagetemp_celsius) VALUES (%s, %s, %s)",
...      ("05/08/2017", "Sydney", "16"))

3. Once we have done that, we can then display our data within the Python console:

cur.execute("SELECT date, city_name, averagetemp_celsius from cities")
rows = cur.fetchall()

4. And, we see that our data is displayed in the Python console:

psycopg2

5. In order to make the changes to the database permanent, we now commit our changes to the cities database:

conn.commit()

6. We have committed the necessary changes and can now close out our connection:

cur.close()
conn.close()