PostgreSQL Example

Here is an example of how to use PostgreSQL with Python. The example uses the psycopg2 library to connect to a PostgreSQL database and perform some basic operations. Please refer to tutorial 04 for more information.

# Install the required packages
# conda install psycopg2

# Import the required library
import psycopg2

# Connect to the database
conn = psycopg2.connect(
    host="localhost", 
    port=5432,
    database="tutorial",
    user="postgres",
    password="") # Change the password to your password

# Create a cursor object. This is used to interact with the database.
cur = conn.cursor()

# Now we will execute the queries
cur.execute("SELECT * FROM students")

# Fetch the results
rows = cur.fetchall()

# Print the results
for row in rows:
    print(row)

(1, 'Alice', 25)
(2, 'Bob', 30)
(3, 'Charlie', 35)

# Let us run another query
cur.execute("SELECT * FROM students WHERE name = 'Bob' ")

# Fetch the results
rows = cur.fetchall()

# Print the results
for row in rows:
    print(row)

(2, 'Bob', 30)

# Close the cursor and the connection. This is important as it will free up the resources.
cur.close()

# Commit the transaction. This will save the changes to the database. 
# You do not need to do this if you are only reading from the database.
conn.commit()

# Close the connection
conn.close()

# Converting tables to pandas dataframe
# Install the required packages
# conda install pandas sqlalchemy

# Import the required libraries
import pandas as pd
from sqlalchemy import create_engine

# Create a connection to the database
# The syntax is 'postgresql://username:password@host:port/database'
# In our case, the username is postgres, password is blank, host is localhost, 
# port is 5432 and database is tutorial
engine = create_engine('postgresql://postgres:@localhost:5432/tutorial')

# Read the table into a pandas dataframe
df = pd.read_sql_table('students', engine)

# Print the dataframe
print(df)

   id     name  age
0   1    Alice   25
1   2      Bob   30
2   3  Charlie   35