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.
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:
5. In order to make the changes to the database permanent, we now commit our changes to the cities database:
6. We have committed the necessary changes and can now close out our connection: