In the previous series for this tutorial, we managed to build Version one of our API endpoints using data structures and flask restful. We also managed to integrate travis CI tool to automate our builds and testing.

In this series of our /api/v2 we are going to:

  1. Add Blueprints.
  2. Do introduction to postgres database connection and setup
  3. Work with our database to add users and store posts
  4. Authenticate users using token based authentication
  5. Write more tests.

Database connection

The first thing we need to do is to create a create a database. This we will do in our postgres terminal.

CREATE DATABASE myblog;

To connect to the myblog database, you use the connect() function of the psycopg2 module. The connect() function creates a new database session and returns a new instance of the connection class.

With connection object, you can create a new cursor to execute an SQL statement and terminate a transaction using either commit() or rollback() method.

You can specify the connection parameters as a string and pass it to the connect() function as follows:

conn = psycopg2.connect(host="localhost", port=5432, database="myblog", user="postgres", password="postgres")

The following is the list of the connection parameters:

  • database: the name of the database that you want to connect.
  • user: the username used to authenticate.
  • password: password used to authenticate.
  • host: database server address e.g., localhost or an IP address
  • port: the port number that defaults to 5432 if it is not provided.

To make it more convenient,

#MAKE_IT_BETTER: We will use a configuration file to store all connection parameters. The following is the content of the database.ini file:

[postgresql]
host=localhost
database=myblog
user=postgres
password=postgres

The following config() function read the database.ini file and returns the connection parameters. We put the config() function in the config.py file which is normally in the instance folder:

#!/usr/bin/python
from configparser import ConfigParser
 
 
def config(filename='database.ini', section='postgresql'):
    # create a parser
    parser = ConfigParser()
    # read config file
    parser.read(filename)
 
    # get section, default to postgresql
    db = {}
    if parser.has_section(section):
        params = parser.items(section)
        for param in params:
            db[param[0]] = param[1]
    else:
        raise Exception('Section {0} not found in the {1} file'.format(section, filename))
 
    return db

The following connect() function connects to the myblog database and prints out the PostgreSQL database version.

#!/usr/bin/python
import psycopg2
from config import config
 
def connect():
    """ Connect to the PostgreSQL database server """
    conn = None
    try:
        # read connection parameters
        params = config()
 
        # connect to the PostgreSQL server
        print('Connecting to the PostgreSQL database...')
        conn = psycopg2.connect(**params)
 
        # create a cursor
        cur = conn.cursor()
        
 # execute a statement
        print('PostgreSQL database version:')
        cur.execute('SELECT version()')
 
        # display the PostgreSQL database server version
        db_version = cur.fetchone()
        print(db_version)
       
     # close the communication with the PostgreSQL
        cur.close()
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)
    finally:
        if conn is not None:
            conn.close()
            print('Database connection closed.')
 
 
if __name__ == '__main__':
    connect()

How it works.

  • First, read database connection parameters from the database.ini file.
  • Next, create a new database connection by calling the connect() function.
  • Then, create a new cursor and execute an SQL statement to get the PostgreSQL database version.
  • After that, read the result set by calling the  fetchone() method of the cursor object.
  • Finally, close the communication with the database server by calling the close() method of the cursor and connection objects.

The connect() function raises the DatabaseError exception if an error occurred. To see how it works, we can change the connection parameters in the database.ini file.

Putting it to work

First take some time and go through that top tutorial and understand how it works. Its very important to understand the basics of this. As we transition to putting this knowledge into use, please note there are several ways this can be done and we are just going to use one of them; simplest of all.

To get started with this, we are going to create a file database_config.py in our app directory. Please check this video after implementing our blueprints to get a better understanding of the directory structure.

import pyscopg2

# host = 'localhost'
# user = 'Mcogol'
# port = 5432
# password = 'root'
# dbname = 'myblog'

#con_url = "dbname='myblog' host='127.0.0.1' port='5432' user='Mcogol' password='root'"
#url for databse connection
uri = os.getenv(['DATABASE_URL'])

#url for test databse connection
test_uri = os.getenv(['DATABASE_TEST_URL'])


#return connection
def connection(url):
	con = pyscopg2.connect(url)
	return con
	

#return connection and creates tables 
def init_db():
	con = connection(uri)
	cur = con.cursor()
	queries = tables()

	for query in queries:
		cur.execute(query)
	con.commit()
	return con


#return connection and creates tables (TDD)
def init_test_db(test_url):
	con = connection(test_uri)
	cur = con.cursor()
	queries = tables()

	for query in queries:
		cur.execute(query)
	con.commit()
	return con

#Deletes all tables after tests have been run
def destroydb():
   pass

#contain all table creation queries
def tables():
   pass

How it works!

  • In the code section above, we have created functions that would rather simplify most of the work for us.
  • However, instead of storing the configurations in the database.ini file as I explained earlier, I decided to store then in the virtual environment as a variable. Watch video for more
  • We then declare functions that would help us do the database connection and create tables effectively

Thank you for reading. let me know on the comment section how helpful it was.

If you like the Article you can give it a clap 👏.

If you feel like you owe me 🍺 after learning something valuable you can always leave a comment and share with your network 😃


2 COMMENTS

  1. Hey, bro, you have done it, I am here on my knees thanking you and at the same time thanking GOD for giving us such a person like you, may GOD bless you exceedingly

LEAVE A REPLY

Please enter your comment!
Please enter your name here