Python and PostgreSQL – Creating database, schema, tables and inserting data using python library psycopg2

In this post, we will learn to create a database called 'foodApp' in postgreSQL,a schema called 'foodschm' within the database, a table called 'menu' within the schema, and finally, insert data into the table 'menu'. 

Install PostgreSQL from https://www.postgresql.org/download/. 

Install psycopg2 using pip install. 

Create a folder under your working directory. Let's call it 'foodApp'. Inside 'foodApp', create a directory called backend.Next, inside the folder backend, create the program called main.py with the code as follows:

1.      Import psycopg2 
import psycopg2

2.     Define database connection properties and connect to the host. Connect to the default database ‘postgres’ with whatever password you gave it during installation.  Make sure to keep the autocommit to true. Without this, postgresql will throw up a transaction error. Use the connection object to create a cursor object.

db_host = "127.0.0.1"

db_name = "postgres"

db_password = {your password}

db_user = "postgres"

db_port = "5432"

myConn = psycopg2.connect(database=db_name, user=db_user,

                        password=db_password, host=db_host, port=db_port)

print (myConn)

myConn.autocommit=True

 

myCursor = myConn.cursor()

5.      Part 1 – create database foodDb.

                                First check to see if the database exists.  If there is no database, go ahead and create it. Close  the connection.

try:

    myCursor.execute("select 1 from pg_database where datname='fooddb'")

    foundRows = myCursor.fetchone()

    print(foundRows)

    if not foundRows:

        myCursor.execute("create database foodDB")

        print('db created successfully')

except Exception as err:

    print(err)

myConn.close()

6.  Part 2 – Create schema ‘foodschm’  in the database.

                Change the connection to database ‘foodDb’. Create the cursor from this connection. 

                Check if the schema with the name ‘foodschm’ exists and if not, create it. Ensure commit.

db_name = "fooddb"

myConn = psycopg2.connect(database=db_name, user=db_user,

                          password=db_password, host=db_host, port=db_port)

print(myConn)

myCursor = myConn.cursor()

 

try:

   myCursor.execute("SELECT 1 FROM information_schema.schemata WHERE schema_name='foodschm'")

   foundRows = myCursor.fetchone()

   if not foundRows:

       myCursor.execute("CREATE SCHEMA foodschm AUTHORIZATION postgres")

       myConn.commit()

       print('Schema successfully created')

except Exception as err:

    print(err)

 

 

7.   Create table ‘menu’

                        Retain the connection to database ‘fooddb’. Use the options feature of the connect method to change default schema to the ‘foodschm’ schema.Create the new connection and cursor. Check if the table ‘menu’ exists in the schema and create it if it does not exist. Ensure commit.

db_name = "fooddb"

db_options = f'-c search_path=foodschm'

myConn = psycopg2.connect(database=db_name, user=db_user,

                          password=db_password, host=db_host, port=db_port, options=db_options)

print(myConn)

myCursor = myConn.cursor()

try:

    myCursor.execute ("SELECT 1 FROM pg_tables \

                    WHERE schemaname='foodschm' \

                        AND tablename='meals'"

                    )

    foundRows = myCursor.fetchone()

    if not foundRows:

        myCursor.execute(

            "CREATE TABLE foodschm.meals ( " +

            "id varchar(5) PRIMARY KEY," +

            "name varchar(15) , " +

            "description varchar(30)," +

            "price numeric(5, 2))"

        )

        myConn.commit()

except Exception as err:

    print(err)

8.      Finally, insert records into the table. Do not forget to commit the transaction.

try:

    myCursor.execute(

            "INSERT INTO foodschm.meals(id, name, description, price) VALUES" +

            "('m1', 'Sushi', 'Finest fish and veggies', 22.99), " +

            "('m2', 'Schnitzel', 'A german specialty!', 16.5), " +

            "('m3', 'Barbecue Burger', 'American, raw, meaty', 12.99), " +

            "('m4', 'Green Bowl', 'Healthy...and green...', 18.99) "

        )

    myConn.commit()

except Exception as err:

    print(err)

 

myCursor.close()

9.      Close the cursor.

In the next post, we will learn how to read this data from the database, bind it to respective fields and present it on the front-end using React.JS.

Comments

Post a Comment

Popular posts from this blog

Full Stack Web Developer BootCamp. (A course by Jose Portilla on Udemy) - Updated for recent versions of Python and Django

Image
      Welcome to a step by step write up to creating a web application using Python (V3.10) and dJango (v4.1.5). While going through the course by Jose Portilla on Udemy for a full stack web development bootcamp by Jose Portilla, I realised that the course followed much older versions of python and Django. Hence, I noted updated findings and hope that it may help someone chart their path through a bit easily. I am skipping the parts in learning HTML/CSS/JavaScript etc and going straight to Section 16 (Django Level One Basics) of the course.   1.      Install Django using Python pip feature. Command prompt: pip install django 2.      Create your project under your working directory. Let us call it "first_project". Command prompt:   django-admin startproject first_project This command will create the following files under the first_project directory -   asgi.py, settings.py, urls,py and wsgi.py:  ...
Read more

Python and Django Full Stack - Have React speak to PostgreSQL

Backend Overview The most popular way of getting ReactJS to speak to PostgreSQL is using a backend framework that can handle the communication between the two, such as Express.js or Django.  While the PERN stack (PostgreSQL, Express, React, Node.js) is a popular full-stack solution for building web applications with React and PostgreSQL, the  React/Django/PostgreSQL stack is popular for its versatility and ease of use, and is suitable for large-scale web applications that require a solid backend. In this post, we will work with the Django framework and achieve our objective of having react communicate with PostgreSQL. ReactJS/Django/PostgreSQL stack Here's a general overview of how to connect ReactJS, Django, and PostgreSQL to build a full-stack web application: Setting up the backend: Install Django using pip:  pip install django Create a new Django project using the command line:  django-admin startproject projectname Create a new Django app within the project:...
Read more