To further enhance the synergy between Xata and Streamlit, I've created the st_xatadb_connection package. This package provides a seamless bridge between the two tools, making it incredibly easy to connect your Streamlit application to your Xata database.With just a few lines of code, you can perform CRUD operations (create, retrieve, update, delete) on your Xata data, generate reports and visualizations, and even interact with your data in real time. The st_xatadb_connection package takes care of all the heavy lifting, allowing you to focus on building your application's functionality.
🌐 Learn More: Xata Docs
-
🔄 Seamless Integration:
- Effortlessly connect and interact with your Xata database using intuitive methods.
-
🛠️ Comprehensive Functionality:
- Enjoy a rich set of functions, from basic queries to advanced AI interactions and file handling.
-
🔒 Secure Credentials Handling:
- Safely manage your Xata API key and database URL using Streamlit's secrets manager or environment variables.
-
🚀 Boosted Productivity:
- Streamline your workflow with efficient data manipulation and advanced search capabilities.
-
🌐 Real-time Connectivity:
- Experience real-time interactions with your Xata database, keeping your data up-to-date.
-
🧠 AI Integration Made Easy:
- Pose questions to AI models effortlessly and engage in dynamic conversations with askai and askai_follow_up functions.
-
📄 File Handling Simplified:
- Manage files seamlessly, including uploads, retrievals, and deletions with dedicated functions.
-
📈 Data Aggregation and Summarization:
- Aggregate and summarize data effortlessly, gaining insights at your fingertips.
-
🌟 Advanced Schema Management:
- Dynamically create, delete, and modify tables and columns with ease.
-
⚡️ Efficient Bulk Operations:
- Leverage bulk processing and transactions for lightning-fast database operations.
-
🖼️ Image Transformation Capabilities:
- Transform images on-the-fly with the image_transform function.
-
🌐 Pagination for Large Datasets:
- Navigate through large datasets seamlessly with next_page and prev_page functions.
-
🚀 Developer-Friendly:
- Designed for developers by developers, ensuring a smooth and intuitive experience.
🔍 Data Manipulation Made Easy
methods
- query: Execute queries on specific tables with precision.
- get: Retrieve records using record IDs.
- insert: Seamlessly insert records into tables.
- upsert: Replace records with ease.
- update: Effortlessly update records in your tables.
- delete: Remove records effortlessly.
🔎 Advanced Search Capabilities
methods
- search: Discover specific queries in a branch.
- search_on_table: Uncover data in a table using a search query.
- vector_search: Harness the power of vector searches.
📊 Data Aggregation and Summarization
methods
- aggregate: Aggregate data based on queries.
- summarize: Summarize data in tables with precision.
🔄 Transaction Management
methods
- transaction: Perform transactions effortlessly.
- sql_query: Execute SQL queries seamlessly.
🧠 AI Integration
methods
- askai: Pose questions and receive AI-driven responses.
- askai_follow_up: Engage in dynamic conversations with AI models.
📁 File Handling Made Simple
methods
- upload_file: Upload files to specific tables and records.
- get_file: Retrieve files with ease.
- delete_file: Effortlessly remove files from your database.
🖼️ Image Transformation
methods
- image_transform: Transform images effortlessly.
📄 Pagination and Schema Management
methods
- next_page: Retrieve the next page of results.
- prev_page: Access the previous page of results.
- get_schema: Explore table schemas dynamically.
🛠️ Database Administration
methods
- create_table: Create new tables effortlessly.
- delete_table: Remove tables with precision.
- create_column: Add new columns seamlessly.
- delete_column: Effortlessly remove columns.
- get_columns: Retrieve table columns dynamically.
🚀 Bulk Operations and API Requests
methods
- bulk_processor: Facilitate bulk processing effortlessly.
- bulk_transaction: Manage bulk transactions seamlessly.
- api_request: Create custom API requests with ease.
-
Create a Xata account and database at Xata Web.
-
Generate an API key for your Xata database.
-
Get your Xata database URL endpoint.
-
Install Streamlit by running
pip install streamlit
. -
Install st_xata_connection by running
pip install st-xatadb-connection
.
To securely store your Xata API key and database URL, you can use Streamlit's secrets manager or environment variables.
XATA_API_KEY = "YOUR_XATA_API_KEY"
XATA_DB_URL = "YOUR_XATA_DATABASE_URL"
Import the st_xata_connection
package and use the st.connection() function to connect to your Xata database.
You could also use the the st.session_state
object to store the connection object and reuse it across your Streamlit app.
import streamlit as st
from st_xatadb_connection import XataConnection
xata = st.connection('xata', type=XataConnection)
Use the xata.query()
function to query your Xata.io database.
results = xata.query("Table_Name")
Use Streamlit functions like st.write()
, st.table()
,st.json()
, or st.dataframe()
to display your query results in your Streamlit app.
st.write(results)
You can also use st_xatadb_connection to insert, update, and delete data in your Xata.io database.
record = {
"name": "John Doe",
"age": 30,
"email": "a@b.com"
}
response = xata.insert("Table_Name", record)
The xata.insert()
function returns a response object that contains the information about the inserted record.
It looks like this:
{
"id": "rec_c8hnbch26un1nl0rthkg",
"xata": {
"version": 0,
"createdAt": "2023-05-15T08:21:31.96526+01:00",
"updatedAt": "2023-05-15T21:58:54.072595+01:00"
}
}
where id
is the unique identifier of the inserted record, and xata
contains the metadata about the record.
Now we can use the id
to update the record or delete it. And in the same way, these functions also return a response object
that contains the information about the updated or deleted record.
update_response = xata.update("Table_Name", "rec_c8hnbch26un1nl0rthkg", {"age": 31})
delete_response = xata.delete("Table_Name", "rec_c8hnbch26un1nl0rthkg")
Xata supports file uploads and downloads. You can use the xata.upload_file()
function to upload a file to your Xata database
or the xata.get_file()
function to download a file from your Xata database.
Suppose you have a file called my_avatar.png
in your current working directory. You can upload it to your Xata database like this:
upload_response = xata.upload_file("Table_Name", "rec_c8hnbch26un1nl0rthkg", "column_name", "my_avatar_bas64_encoded")
Now you can download the file from your Xata database like this:
download_response = xata.get_file("Table_Name", "rec_c8hnbch26un1nl0rthkg", "column_name")
If you want to perform multiple operations on your Xata database as a single unit of work, you can use transactions.
For this, you can use the xata.transaction()
function. It takes a list of operations as an argument and returns a response object.
Suppose you want to insert a record and update another record in your Xata database as a single unit of work. You can do it like this:
transaction_response = xata.transaction([
{"insert": {"table": "Table_Name", "record": {"name": "Marie Doe", "age": 21, "email": "marie@mail.com"}}}
{"update": {"table": "Table_Name", "id": "rec_c8hnbch26un1nl0rthkg", "fields": {"age": 31}}}
{"get": {"table": "Table_Name", "id": "rec_c8hnbch26un1nl0rthkg","columns": ["name", "age"]}}
{"delete": {"table": "Table_Name", "id": "rec_c8hnbch26un1nl0rthkg"}}
])
Note that the transaction()
function takes a list of operations as an argument. Each operation is a dictionary that contains
the type of operation and the operation-specific arguments. The supported operations are insert
, update
, get
, and delete
.
If you feel more comfortable with SQL, you can use the xata.sql_query()
function to query your Xata database using SQL.
The xata.sql_query()
function takes a SQL query as an argument and returns a response object with the query results.
For example, suppose you want to query your Xata database using SQL. You can do it like this:
sql_response = xata.sql_query("SELECT * FROM Table_Name")
Xata has an AI assistant that can help you with your queries. You can use the xata.askai()
function to ask a question to the AI assistant,
taking a reference to the table you want to query and the question you want to ask as arguments.
Suppose you want to ask the AI assistant to find the people in your table whose age is greater than 30. You can do it like this:
response = xata.askai("Table_Name", "Find the people whose age is greater than 30")
The response looks like this:
{
"answer": "< answer >",
"sessionId": "cg52bk1eqh5rd5hndhq95jercs",
"records": [
"b70d541d114ff54ad15915636450663f",
"8ae4837002e21f013aa85c30a126ea1c",
"4b137344a3c53d5152c45ed514188cd2"
]
}
The st_xata_connection
package is a powerful tool that makes it easy to connect your Streamlit app to your Xata database.
In this article, we've covered the basics of using st_xata_connection
to query your Xata database, insert, update, and delete data,
work with files, perform transactions, and ask questions to the AI assistant. But there's a lot more you can do with st_xata_connection
.