Skip to content

Python PostgreSQL Examples

Jump to bottom
Franknaw edited this page Feb 8, 2021 · 7 revisions

Simple examples working with Python and Postgres

Setup Environment (Centos 8)

  • Setup Python Postgres driver
    • cd /usr/bin/
    • sudo ln -s pip3 pip
    • sudo chown your-user:your-user /usr/local/lib64
    • pip install psycopg2-binary

Setup Database Connection

  • Create file "connect_db.py" and add the following
import psycopg2


def initial_connect_db():
    # establishing the connection
    return psycopg2.connect(database="postgres", user='postgres',
                            password='mypasswd', host='127.0.0.1', port='5432')


def connect_db(db_name):
    # establishing the connection
    return psycopg2.connect(database=db_name, user='postgres',
                            password='mypasswd', host='127.0.0.1', port='5432')

Create Database

  • Create file "create_db.py" and add the following
import connect_db as db


def create_db(db_name):
    # get connection instance
    conn = db.initial_connect_db()

    conn.autocommit = True

    # Creating a cursor object using the cursor() method
    cursor = conn.cursor()

    # Preparing query to create a database
    cursor.execute("SELECT 1 FROM pg_catalog.pg_database WHERE datname = %(value)s", {'value': db_name})
    exists = cursor.fetchone()
    if not exists:
        cursor.execute("CREATE DATABASE " + db_name)
        print("Database created successfully........")
    else:
        print("Database already exists........")

    # Closing the connection
    conn.close()

Create Table

  • Create file "create_table.py" and add the following
import connect_db as db


# get connection instance
def create_table(db_name):
    conn = db.connect_db(db_name)
    # Creating a cursor object using the cursor() method
    cursor = conn.cursor()

    conn.autocommit = True

    # Droping EMPLOYEE table if already exists.
    cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

    # Creating table as per requirement
    sql = '''CREATE TABLE EMPLOYEE (
      FIRST_NAME CHAR(20) NOT NULL,
      LAST_NAME CHAR(20),
      AGE INT,
      SEX CHAR(1),
      INCOME FLOAT
   )'''
    cursor.execute(sql)
    print("Table created successfully........")

    # Closing the connection
    conn.close()

Insert Records

  • Create file "insert_data.py" and add the following
import connect_db as db


def insert_data(db_name):
    # get connection instance
    conn = db.connect_db(db_name)
    # Setting auto commit false
    conn.autocommit = True

    # Creating a cursor object using the cursor() method
    cursor = conn.cursor()

    # Preparing SQL queries to INSERT a record into the database.
    cursor.execute('''INSERT INTO EMPLOYEE (FIRST_NAME,LAST_NAME,AGE,SEX,INCOME) 
   VALUES ('Shiba', 'Inu', 25, 'F', 200)''')

    # Commit your changes in the database
    conn.commit()
    print("Records inserted........")

    # Closing the connection
    conn.close()

Query Table

  • Create file "query_table.py" and add the following
import connect_db as db


def query_table(db_name):
    # get connection instance
    conn = db.connect_db(db_name)
    # Open a cursor to perform database operations
    cur = conn.cursor()

    # Execute a query
    cur.execute("SELECT * FROM EMPLOYEE")

    # Retrieve query results
    records = cur.fetchall()
    print(records)

Drop Database

  • Create file "drop_db.py" and add the following
import connect_db as db


def drop_db(db_name):
    # get connection instance
    conn = db.connect_db(db_name)

    conn.autocommit = True

    # Open a cursor to perform database operations
    cur = conn.cursor()

    # Execute a query
    cur.execute("DROP DATABASE " + db_name)

    # Closing the connection
    conn.close()

Main Program

  • Create file "main.py" and add the following
# import functions from other files
from create_db import create_db
from create_table import create_table
from insert_data import insert_data
from query_table import query_table
from drop_db import drop_db

# Press the green arrow to run the script.
if __name__ == '__main__':
    # Create variable with name of database
    db_name = "my_test_db"

    print('Call Create DB Function')
    create_db(db_name)

    print('Call Create Table Function')
    create_table(db_name)

    print('Call Insert Data Function')
    insert_data(db_name)

    print('Call Query Table Function')
    query_table(db_name)

    # Uncomment if you want to drop the DB
    # print('Delete DB')
    # drop_db(db_name)

References

Clone this wiki locally