In the previous tutorials, we looked at how to create a database, connect to the database and how to create your database relations/tables using python scripts.

In this tutorial we are going to look at:

  1. Basics of inserting data into a postgres database table
  2. Advance models methods of inserting data
  3. Fetching data from a database table

Inserting data into postgres database basics

To insert a row into a PostgresQL table in Python, you use the following steps:

First, connect to the PostgreSQL database server by calling the connect() function of the psycopgmodule.

1conn = psycopg2.connect(url/dns)

The connect() function returns a new instance of the connection class.

Next, create a new cursor object by calling the cursor() method of the connection object.

2cur = conn.cursor()

Then, execute the INSERT statement with the input values by calling the execute() method of the cursor object.

3cur.execute(sql, (value1,value2))

You pass the INSERT statement to the first parameter and a list of values to the second parameter of the execute() method.

In case the primary key of the table is an auto-generated column, you can get the generated ID back after inserting the row. To do this, in the INSERT statement, you use the RETURNING id clause. After calling the execute() method, you call the  fetchone() method of the cursor object to get the id value as follows:

4id = cur.fetchone()[0]

After that, call the commit() method of the connection object to save the changes to the database permanently. If you forget to call the commit() method, psycopg will not change anything to the database.

5conn.commit()

Finally, close the communication with the PostgreSQL database server by calling the close() method of the cursor and connection objects.

6cur.close()
conn.close()

Example

The following insert_posts() function inserts a new row into the posts table and returns the newly generated post_id value.

import psycopg2
from config import config
 
 
def insert_posts(description):
    """ insert a new vendor into the vendors table """
    sql = """INSERT INTO posts(description)
             VALUES(%s) RETURNING post_id;"""
    conn = None
    post_id = None
    try:
        # read database configuration
        params = config()
        # connect to the PostgreSQL database
        conn = psycopg2.connect(**params)
        # create a new cursor
        cur = conn.cursor()
        # execute the INSERT statement
        cur.execute(sql, (vendor_name,))
        # get the generated id back
        post_id = cur.fetchone()[0]
        # commit the changes to the database
        conn.commit()
        # close communication with the database
        cur.close()
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)
    finally:
        if conn is not None:
            conn.close()
 
    return post_id

Advance models methods of inserting data

This code section is part of myblog application that we have been working on now for quite some time. In this PostModel class, I will have all my related methods that will related to handling and managing posts.

The first method here __init__ is our constructor and by now I assume you know what they do.

from ...database_config import init_db

class PostModel(BaseModel):
    """docstring for UserModel"""

    def __init__(self, title="title", description="description", created_by="created_by"):
        self.title = title
        self.description = description
        self.created_by = created_by

    # method to save  user data
    def save(self):
        post = {
            "title": self.title,
            "description": self.description,
            "created_by": self.created_by
        }

        con = init_db()
        cur = con.cursor()

        query = """ INSERT INTO posts (title, description, created_by) VALUES \
					( %(title)s, %(description)s, %(created_by)s) RETURNING post_id """
        cur.execute(query, post)
        post_id = cur.fetchone()[0]
        con.commit()
        cur.close()
        return post_id

I then declare a save method that I use to insert my data into the posts table. Everything here is pretty much easy and straight forward from what I have in the explanation above.

On my POST blog endpoint, I will have the following code:

class Blogs(Resource):
    """docstring for Blogs"""

    def post(self):
        """creating a blog"""
        req = request.get_json()
        new = {
            "title": req['title'],
            "description": req['description'],
            "created_by": req['created_by']
        }

        reequest = PostModel(**new)
        res = reequest.save()

        if isinstance(res, int):
            return make_response(jsonify({
                "msg": "Created",
                "post_id": res
            }), 201)
        else:
            return make_response(jsonify({
                "msg": "post already exists"
            }), 409)

Fetching data from postgres database table

To fetch data, I will add a get_posts() method in our PostModel class

    def get_posts(self):
        con = init_db()
        cur = con.cursor()
        query = "SELECT title, description, created_by, post_id, created_on FROM posts;"
        cur.execute(query)
        data = cur.fetchall()
        res = []

        for i, items in enumerate(data):
            title, description, created_by, post_id, created_on = items
            posts = dict(
                post_id=int(post_id),
                title=title,
                description=description,
                created_by=int(created_by),
                created_on=str(created_on)
            )
            res.append(posts)

        return res

The line data = cur.fetchall() holds the data fetched from the database in a list format. We then loop through that list and created a dictionary of each that we append to out empty res list.

Then here is the GET posts endpoint:

    def get(self):
        """retrieving all blogs"""
        res = PostModel().get_posts()
        if not res:
            return make_response(jsonify({
                "msg": "Database is empty"
            }), 200)
        else:
            return make_response(jsonify({
                "msg": "Ok",
                "posts": res
            }), 200)

Thats it for this tutorial. For more please watch and subscribe to Kenya-tech youtube channel. Here is a recap of the same.


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 😃


1 COMMENT

LEAVE A REPLY

Please enter your comment!
Please enter your name here