Streamlit + DuckDB Tutorial

#emptystring

Streamlit + DuckDB Tutorial

The beautiful thing about learning is that nobody can take it away from you.
B.B King

Motivation

In this era of Everything Web, we are basically compelled to build web interfaces for our various application to ease the access, hence, bring value.

As a Python developer , we have a lot modules & framework that helps us build web interfaces.

This is whereStreamlit comes in, with it’s pythonic way of creating and web applications, it's the go to solution to build fast and with great ease web application.

We’ll useduckDB for it’s built-in OLAP capabilities and easy usage (the databases is a file, no complex infrastructure needed).

In this tutorial, you’ll learn how to build web apps usingStreamlit and usingduckDB as your database.

Table of content

What is Streamlit ?

Streamlit turns data scripts into shareable web apps in minutes.
All in pure Python. No front‑end experience required.

With Streamlit, we can easily create using python ONLY dynamic web application. It’s ideal for data apps as it natively has built-in function to present data in different ways (tables, charts, etc.).

You can read morehere

What is duckDB ?

DuckDB is an in-process SQL OLAP database management system.

It is serverless comes with great features like (Direct Parquet & CSV querying, Vectorized Engine, Parallel query Processing). It is an alternative to SQLite purposely built for Analytics workload.

You can read morehere.

Code

The full code can be access below.

https://github.com/mikekenneth/streamlit_duckdb

Data Generator

The below generates a1000 rows data and loads toorders table induck.db when thebutton is clicked:

fromgenerate_dataimportgenerate_dataset_orders,load_filebutton=st.button(label="Generate or Refresh Data")ifbutton:db="duck.db"destination_table_name="orders"filename="orders.csv"generate_dataset_orders(filename=filename,num_rows=1000)load_file(db=db,infile_path=filename,table_name=destination_table_name)

Screenshot

I wrote a little helper to easily generate the orders data and load intoduckDB. The code can be accessedhere.

Extract data from DuckDB

I created a functionexecute_query to easily get the data using only the desired SQL query. It also allows specifying the type of output needed. You can read more in the officialdocumentation.

defexecute_query(query:str,return_type:str="df"):withduckdb.connect("duck.db",read_only=True)ascon:ifreturn_type=="df":returncon.execute(query).df()elifreturn_type=="arrow":returncon.execute(query).arrow()elifreturn_type=="list":returncon.execute(query).fetchall()destination_table_name="orders"data=execute_query(f"select * from{destination_table_name}",return_type="df")

Display a sample of the data

WithStreamlit we can easily display aDataframe like a table. You can read morehere.

st.write("## Sample")st.dataframe(data.head(10),height=300)

Screenshot

Visualization

Streamlit allow us to easily display charts. We will use it to display some bar charts. You can read morehere.

st.write("## Visualization")option=st.selectbox("Select a dimension",["product_name","customer_name","status"],key="option")ifoption:st.write(f"### Bar Chart:{option} x Quantity")st.bar_chart(data,x=option,y="quantity")st.write(f"### Bar Chart:{option} x Amount")st.bar_chart(data,x=option,y="amount")st.write(f"### Bar Chart:{option} x Count")st.bar_chart(data[option].value_counts())

Screenshot

Filtering Data

We can alsofilter out data directly from the web using the below. You can read morehere.

st.write("## Filters (by Products Name)")products_list=[row[0]forrowinexecute_query(f"select distinct(product_name) from{destination_table_name}",db=db,return_type="list")]product_filter=st.selectbox(label="Select a Product",options=products_list,key="product_filter")ifproduct_filter!="--":result=execute_query(f"select * from{destination_table_name} where product_name ='{product_filter}'",db=db,return_type="df",)st.dataframe(result,height=400)

Screenshot

Download / Exporting Data

We can export data to csv using the Streamlitst.download_button function.

@st.experimental_memo# An optimization wrapper to memoize the result of the functiondefexport_df(df):returndf.to_csv(index=False).encode("utf-8")st.write("### Download Data")st.download_button(label="Press to Download",data=export_df(result),file_name=f"orders - product='{product_filter}'.csv",mime="text/csv",key="download-csv",)