Quickly Build Real-time Data Applications with Streamlit and Prisma
Written on
Chapter 1: Introduction to Streamlit and Prisma
Streamlit has emerged as a valuable tool for the Data Science community, offering a straightforward method for developing data applications swiftly, even without frontend expertise. The array of components available in Streamlit is growing continuously. Users typically rely on Pandas and CSV files to create dashboards, but there are limited methods to link Streamlit directly to a real database (whether SQL or NoSQL) for dashboard creation. This blog will demonstrate how to connect a PostgreSQL database to Streamlit using Prisma.
Before we proceed to the integration details, let’s first understand Prisma.
Section 1.1: What is Prisma?
Prisma functions as an Object-Relational Mapping (ORM) tool, enabling developers to perform read, write, update, and delete operations on data through an object-oriented approach. Crafted with TypeScript, Prisma boasts an intuitive data model, ensures complete type safety, and automates database migrations. While Prisma is predominantly utilized in NodeJS environments, Robert Craige has made it accessible to Python developers through the creation of the Prisma Python client. Notably, the Prisma Python client operates using Rust to interact with the Prisma interface.
To install the Prisma Python client, execute the following command:
pip install prisma
Prisma requires a data model to understand the relationships among tables within a schema or database, as well as the data types associated with each column in every table for ensuring type safety. This configuration is specified in a schema.prisma file. For this tutorial, we will utilize the following data model:
generator client {
provider = "prisma-client-py"
interface = "sync"
recursive_type_depth = 5
}
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}
model Movies {
id BigInt @id @default(autoincrement())
movieId BigInt @unique
movieId_mapped BigInt @unique
imdbId String
tmdbId String
meta Meta[]
cast Cast[]
crew Crew[]
video Videos[]
backdrop BackDrops[]
poster Posters[]
genres Genre[]
}
...
To enhance your development experience, it is recommended to download the Prisma extension for Visual Studio Code, which provides formatting and auto-completion support for Prisma schemas.
The generator section specifies the type of client and interface. The Prisma Python client offers both synchronous and asynchronous interfaces; however, this tutorial will focus on the synchronous version.
The datasource section details the type of database being used, along with the connection URL. You can easily set up a PostgreSQL database using Supabase for free. Since I have data stored in an AWS RDS instance, I will be using that instead. If you opt for Supabase, your URL should resemble:
postgresql://postgres:[YOUR-PASSWORD]@[YOUR-HOST-NAME]:5432/postgres
The model outlines the column names, data types, and relationships between tables in the database. For instance, the Movies model contains fields such as id, movieId_mapped, movieId, imdbId, and tmdbId. Each movie also has associated metadata stored in the separate Meta table. To link the Movies and Meta tables, we include a field called meta of type Meta.
Chapter 2: Database Setup and Integration
To utilize the same data model and dataset, you can request access to download the data dump from Google Drive. If you are using Supabase, follow these steps after obtaining the data dump:
Step 1: Retrieve the Database URL from Supabase, found under settings/database, and replace [YOUR-PASSWORD-HERE] with your actual database password.
Step 2: Navigate to the folder containing the data dump in your terminal and execute the following command to import the data into your Supabase database. Ensure that the psql command is available on your system; if not, refer to the relevant installation tutorial.
psql -d database_link < data-backup.psql
After running this command, the data transfer will take a few minutes. Once completed, you should see the expected tables in your database.
For those interested in learning the fundamentals of Prisma and performing CRUD operations using the Prisma Python client, detailed documentation is available. This blog will not replicate that content.
Once you have Prisma installed and your database configured, we can move on to the integration process.
Section 2.1: Integration Steps
- If you haven't already done so, install Streamlit:
pip install streamlit
- Create a .env file and input your database URL as follows:
DATABASE_URL=postgresql://postgres:[YOUR-PASSWORD]@[YOUR-HOST-NAME]:5432/postgres
- Copy the schema.prisma file as outlined earlier.
- Develop a utilities.py file to include the following code, which generates the Prisma client before importing it:
import os
import re
import subprocess
import json
from typing import List, Dict
def generate_prisma_client():
"""Generates the Prisma Client and loads it."""
print(f'GENERATING PRISMA CLIENT')
subprocess.call(["prisma", "generate"])
print(f'GENERATED PRISMA CLIENT')
generate_prisma_client()
try:
from prisma import Prisma
except RuntimeError:
from prisma_cleanup import cleanup
cleanup()
print(f'GOT RUNTIME ERROR')
generate_prisma_client()
from prisma import Prisma
- Create a Movies class to initialize the Prisma database and query data:
class Movies:
"""Movies class with methods to search and list movies."""
def __init__(self):
self.db = Prisma()
self.db.connect()
def to_dict(self, obj: object):
"""Converts an Object to Dictionary."""
return json.loads(json.dumps(obj, default=lambda o: o.__dict__))
def list_movies_by_title(self, title):
"""Returns list of movies based on the title."""
matches = self.db.movies.find_many(where={
"meta": {
"every": {
"title": {
"contains": title,
"mode": "insensitive"
}
}
}
},
include={
"meta": True,
"cast": True,
"poster": {
"take": 3}
})
matches = list(map(lambda m: self.to_dict(m), matches))
return matches
- Create a components.py file to define functions for generating HTML components:
def card(title, image, overview):
return f"""
<div>
<h3>{title}</h3>
<img src="{image}" alt="{title} poster"/>
<p>{overview}</p>
</div>
"""
def rows(strings):
return f"""
<div>
{strings}</div>
"""
def grid(movies):
card_strings = [
card(title=movie.get("meta")[0].get("title"),
image=movie.get("meta")[0].get("poster"),
overview=movie.get("meta")[0].get("overview")) for movie in movies if movie.get("meta")[0].get("poster")
]
row_strings = "\n".join([
rows("\n".join(card_strings[ix:ix + 4]))
for ix in range(0, len(card_strings), 4)
])
return f"""
<div>
{row_strings}</div>
"""
- Set up the streamlitapp.py file to build the frontend:
import streamlit as st
from utilities import Movies
from components import card, grid
movObj = Movies()
# Add bootstrap CSS
st.markdown(
"""
""",
unsafe_allow_html=True
)
st.header("Prisma Demo")
movieText = st.text_input("Search Movies")
if movieText:
with st.spinner(text="Searching.."):
# Search for movies based on the input text
movies = movObj.list_movies_by_title(movieText.strip())
st.markdown("""<div></div>""", unsafe_allow_html=True)
# Render the HTML
st.markdown(grid(movies), unsafe_allow_html=True)
- Create a requirements.txt file and include the necessary modules:
prisma
streamlit
requests
python-dotenv
Run the application with the command:
streamlit run streamlitapp.py
You should see the application interface.
The first video, "Building a Data Application in Streamlit," provides insights on developing data applications using Streamlit.
Chapter 3: Hosting the Application
In this instance, you need to declare the DATABASE_URL as the only environment variable:
DATABASE_URL = "postgresql://postgres:[YOUR-PASSWORD]@[YOUR-HOST-NAME]:5432/postgres"
After adding the environment variable and selecting the Python version, click on Deploy. Your application will be up and running shortly.
The second video, "Supercharge your Streamlit apps with real-time data," explores enhancing Streamlit applications with live data integration.
Conclusion
In this blog, we explored how to utilize Streamlit to create a real-time data application by connecting it to a live database using the capabilities of Prisma ORM. We demonstrated how to query existing data, and we also discussed the potential to create forms in Streamlit for adding data to the database using Prisma.