POSTGRESQL DB IN PYTHON

POSTGRESQL DB IN PYTHON

A database is basically an organised collection of data that is structured and electronically stored in a computer system.
Despite the recent advancements within the technology world with regards to databases, relational databases are predominantly used as they store data as collections of predefined tables with rows and columns connected through one or more relationships.
Examples of relational databases are

• Postgresql
• Microsoft SQL server
• Mysql
• Sqlite

Sql is the key cornerstone for the above relational databases.
POSTGRESQL
For an sql-python tandem the psycopg2 package is predominantly used.
POSTGRESQL is a lightweight, free, opens ourselves relational database that is well accepted in the industry owing to the following factors

• A smooth integration with other programming languages such as python, R.
• Data integrity.
• Reliability.
• A proven architecture.
• Safely stores and scales the most complicated data workloads.

PSYCOPG2 KEY CONCEPTS

Psycopg2 is the most popular Postgresql database adapter for python.
Psycopg2 comes into play when we want to connect to a database that is locally available on the machine or on a server via the Internet.
Psycopg2 instructs python how to detect your database by informing python that the database of interest is a postgresql database.

Installation

In this article I am using python in a container environment. Open the command prompt and type;
conda install -c anaconda psycopg2

Side note

• Conda Prompt is the terminal interface for managing Conda environments and launching tools such as JupyterLab.
• Conda prompt acts like a controlled environment where Python packages, environments can be managed safely.

Creating a connection(Python to Postgresql) IN A NOTEBOOK

We first import the library:import psycopg2

The psycopg2 connect() function is used whereby it creates a new database session and returns a new connection instance.
For a connection to happen there are basic connection parameters that are needed

1. Database: The name of the database in question.
2. User: The user name needed to authenticate
3. Password: The password needed to authenticate
4. Host: This could either be the localhost or an IP address.
5. Port: This is the connection port number however whenever it’s not provided it defaults to port number 5432

conn = psycopg2.connect(database = 'school_db',
                        user = 'pytest',
                        host = 'localhost',
                        password = '1234',
                        port = 5432)

• Uses the Psycopg2 library to establish a connection to a Postgresql database.
• After connection is established conn variable holds the connection object which can be used to execute queries and interact with the dB.
The connection encapsulates a dB session and it allows you to execute sql commands and queries such as: SELECT, INSERT, UPDATE, DELETE using the cursor() method and to make changes persistent using the commit() method.
After the cursor instance has been created, one can send commands to the database using the execute() method.
Retrieve data from the table using

1.  Fetchone()
2.  Fetchmany()
3.  Fetchall()

Close the cursor and the connection to the database once operations are done using the close() method.

NB: We only close to free up system resources and prevent memory leaks that could degrade performance or cause application failures.

Creating a Table

# Open a cursor to perform database operations
cur = conn.cursor()
# Execute a command: create university courses table
cur.execute("""CREATE TABLE university courses(
            course_id SERIAL PRIMARY KEY,
            course_name VARCHAR (50) UNIQUE NOT NULL,
            course_instructor VARCHAR (100) NOT NULL,
            topic VARCHAR (20) NOT NULL);
            """)
# Make the changes to the database persistent
conn.commit()
# Close cursor and communication with the database
cur.close()
conn.close()

Inserting Records

# To create records in the university courses table, we need the INSERT command.
# Open a cursor to perform database operations
cur = conn.cursor()

cur.execute("INSERT INTO university_courses(course_name, course_instructor, topic) VALUES('Introduction to SQL','John Kamau,'Sql')");

cur.execute("INSERT INTO university_courses(course_name, course_instructor, topic) VALUES('Analyzing Survey Data in Python','Hezze Ndungu','Python')");

cur.execute("INSERT INTO university_courses(course_name, course_instructor, topic) VALUES('Introduction to ChatGPT','Ruth Stats','Theory')");

cur.execute("INSERT INTO university_courses(course_name, course_instructor, topic) VALUES('Introduction to Statistics in R','Magdaline Wangari','R')");

cur.execute("INSERT INTO university_courses(course_name, course_instructor, topic) VALUES('Hypothesis Testing in Python','Edward Oli','Python')");

conn.commit()
cur.close()
conn.close()

Calling The Classic SELECT Statement

cur = conn.cursor()
cur.execute('SELECT * FROM university courses;')
rows = cur.fetchall()
conn.commit()
conn.close()
for row in rows:
    print(row)