Hi All, this is my first post, I am new to dash I need to create a data table where user can input value in cell and then I need to compare that value with old cell and if it’s new than I need to save that back to sql db ,I need some reference script for SQL DB so that I can use it for my work.
Thanks in advance
Hi @akash1997
welcome to Dash.
Have you created your first Dash app yet? Do you feel comfortable using the callback and the datatable?
Yes I have created the data table and used basic callback for dropdowns but need help in creating callback for saving data into sql db as of now I am fetching data from sql db only using sql query
hi @akash1997
this post has code that identifies when a DataTable cell has been updated by user, and it will pull the data of the datable. Then, you would need to create another callback that would save that data inside your database through sql query. Here’s an example of how I write data into a MongoDB from a Dash app. It’s on line 130. This is not SQL query, but I hope this guides you in the right direction.
To achieve the functionality you’re describing—creating a data table where users can input values, comparing new values with old ones, and saving changes to an SQL database—you’ll need to use a combination of b2b data enrichment tools Dash for the web application and a library like SQLAlchemy for interacting with the SQL database.
Below is a simple example to get you started. This example assumes you have a SQLite database and a table named your_table with columns id, column_name, and value. You should modify this example to fit your specific database schema.
import dash
from dash import dcc, html, dash_table
from dash.dependencies import Input, Output, State
from sqlalchemy import create_engine, Table, Column, Integer, MetaData
app = dash.Dash(name)
DATABASE_URL = ‘sqlite:///your_database.db’ # Update with your database URL
engine = create_engine(DATABASE_URL)
metadata = MetaData()
your_table = Table(‘your_table’, metadata,
Column(‘id’, Integer, primary_key=True),
Column(‘column_name’, Integer),
Column(‘value’, Integer)
)
app.layout = html.Div([
dash_table.DataTable(
id=‘editable-table’,
columns=[
{‘name’: ‘ID’, ‘id’: ‘id’, ‘editable’: False},
{‘name’: ‘Column Name’, ‘id’: ‘column_name’, ‘editable’: False},
{‘name’: ‘Value’, ‘id’: ‘value’, ‘editable’: True},
],
editable=True,
row_deletable=True,
data=[
{‘id’: 1, ‘column_name’: ‘Column1’, ‘value’: 100},
{‘id’: 2, ‘column_name’: ‘Column2’, ‘value’: 200},
# Add more initial data as needed
]
),
html.Button(‘Save Changes’, id=‘save-button’, n_clicks=0),
html.Div(id=‘output-message’)
])
@app.callback(
Output(‘output-message’, ‘children’),
[Input(‘save-button’, ‘n_clicks’)],
[State(‘editable-table’, ‘data’)]
)
def save_changes(n_clicks, data):
if n_clicks > 0:
with engine.connect() as conn:
for row in data:
# Check if the value has changed
result = conn.execute(your_table.select().where(your_table.c.id == row[‘id’]))
db_value = result.fetchone()[‘value’]
if row[‘value’] != db_value:
# Update the value in the database
conn.execute(your_table.update().where(your_table.c.id == row[‘id’]).values(value=row[‘value’]))
return html.Div('Changes saved to the database')
else:
return html.Div()
if name == ‘main’:
app.run_server(debug=True)
Remember to install the necessary libraries using:
pip install dash dash-table dash-bootstrap-components sqlalchemy