From database to chart plotting

Using mljar studio's recipes suit you can fetch your data from database, convert it into python object, and plot charts from that data. All without leaving the editor, using user friendly piece of code recipes.

This notebook was created with MLJAR Studio

MLJAR Studio is Python code editior with interactive code recipes and local AI assistant.
You have code recipes UI displayed at the top of code cells.

Documentation

All required packages are automatically imported by MLJAR Studio for you so you don't have to worry about them.

# import packages
import psycopg
import os
from dotenv import load_dotenv
from psycopg import sql
import pandas as pd
import matplotlib.pyplot as plt

Make sure you opened a connection in your notebook. To learn how to do it, check out open and test database connection example notebook.

# load credentials from .env file:
load_dotenv(override=True)

# get the credentials
def create_new_connection():
    try:
        conn = psycopg.connect(
            dbname=os.getenv("POSTGRES_DB_NAME"),
            user=os.getenv("POSTGRES_USERNAME"),
            password=os.getenv("POSTGRES_PASSWORD"),
            host=os.getenv("POSTGRES_HOST"),
            port=os.getenv("POSTGRES_PORT"),
        )
        return conn
    # check for errors
    except psycopg.Error as e:
        raise psycopg.Error(f"""
Error occurred while establishing connection: 
    {e}

Maybe you provided wrong credentials, use define new connection recipe to edit them.
Other option is that database server is down or you dont have the right acces to this database.
            """)

# open new connection:
conn = create_new_connection()

First, lets preview our data using select query recipe. Here we select everything from products table. The result of the query is displayed below the code section.

# if connection was used and closed it is reopen here
if conn.closed:
    conn = create_new_connection()

# run query
with conn:
    with conn.cursor() as cur:

        # query db
        try:
            cur.execute(
                sql.SQL("SELECT {columns} FROM {table}").format(
                    columns=sql.SQL("*"),
                    table=sql.Identifier("products"),
                )
            )
        # check for errors
        except psycopg.ProgrammingError as e:
            raise psycopg.ProgrammingError(f"""
Problem running query:
    {e}

Did you spell everything correctly?
You can use show tables and columns recipes.
            """)

        # print the results
        for row in cur.fetchall():
            print(f"{row}")

Great, now that we reviewed our data, we can convert it into pandas data frame to be able to operate on it.

To do so, we we'll use raw query to pandas recipe. If you don't know how to write SQL queries from scratch, search the section above for SELECT FROM pattern and paste it into run SQL query field.

We also have to name the data frame and its columns. The default df is fine, but for clarity lets name the columns the same as in the database.

# if connection was used and closed it is reopen here
if conn.closed:
    conn = create_new_connection()

# run query
with conn:
    with conn.cursor() as cur:

        try:
            cur.execute("""
SELECT * FROM products
            """)
        # check for errors
        except psycopg.ProgrammingError as e:
            raise psycopg.ProgrammingError(f"""
Problem running query:
    {e}
            """)

        # check if query was a select query
        if str(cur.statusmessage).upper().startswith("SELECT"):
            # fetch all the results
            results = cur.fetchall()

            # create dataframe
            try:
                df = pd.DataFrame(data=results, columns=pd.Index(["id","name","price","description"]))
                print(f"{df.shape = }")
                print(df.head())
            except ValueError as e:
                raise ValueError(f"""
Error creating DataFrame:
    {e}

Does number of columns specified matches number of columns in the data.
                """)

This is out data in pandas object form. We can see its shape (5,3), which means that it is 5 rows long and 3 columns wide and its head meaning first 5 rows of the data.

To plot a char we will use scatter from matplotlib. Lets fill out the recipe.

# create scatter
plt.scatter(df["id"], df["price"], color="tab:blue", label="price of the item")
plt.title("my_plot")
plt.xlabel("ID")
plt.ylabel("PRICE")
# add legend box
plt.legend(loc="best")
# display plot
plt.show()

Conclusions

Mljar studio built in code recipes allows you to fetch and analyze your data in one place. Now plot charting gets easier than ever,

Recipes used in the postgresql-python-db-to-plot.ipynb

All code recipes used in this notebook are listed below. You can click them to check their documentation.

Packages used in the postgresql-python-db-to-plot.ipynb

List of packages that need to be installed in your Python environment to run this notebook. Please note that MLJAR Studio automatically installs and imports required modules for you.

psycopg>=3.2.1

python-dotenv>=1.0.1

pandas>=1.0.0

matplotlib>=3.8.4

Similar notebooks

List of similar Python notebooks, so you can find more inspiration 😊