Manipulate records in a SQLite3 database with PyScript 🐍
console.log('Executing python script...')
import numpy as np
import pandas as pd
import sqlite3
import asyncio
import panel as pn
from panel.io.pyodide import show
con = sqlite3.connect(':memory:')
c = con.cursor()
console.log("Connected sqlite3 database(':memory:')")
sql = '''
CREATE TABLE items (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
item TEXT NOT NULL,
qty INT NOT NULL,
price INT NOT NULL
)
'''
c.execute(sql)
console.log("Creating table {sql}...")
table = pn.widgets.Tabulator(pagination='remote', page_size=10)
# hide pagination 'First, Prev [1][2][3] Next, Last'
document.getElementById('table').style.display = 'none'
await show(table, 'table')
def insert_item(*args, **kwargs):
item = Element('item').element.value
qty = Element('quantity').element.value
price = Element('price').element.value
sql = f"INSERT INTO items (item, qty, price) VALUES ('{item}', {qty}, {price})"
c.execute(sql)
con.commit()
console.log(f"Inserting data {sql}...")
def update_item(*args, **kwargs):
id = Element('id').element.value
item = Element('item').element.value
qty = Element('quantity').element.value
price = Element('price').element.value
sql = f"UPDATE items SET item='{item}', qty={qty}, price={price} WHERE id={id}"
c.execute(sql)
con.commit()
console.log("Updating data {sql}...")
def delete_item(*args, **kwargs):
id = Element('id').element.value
sql = f"DELETE FROM items WHERE id={id}"
c.execute(sql)
con.commit()
console.log("Deleting data {sql}...")
def clear_input(*args, **kwargs):
Element('id').clear()
Element('item').clear()
Element('quantity').clear()
Element('price').clear()
console.log("Clearing input data...")
def fetch_record(*args, **kwargs):
sql = "SELECT * FROM items"
df = pd.read_sql(sql, con)
table.value = df
document.getElementById('table').style.display = 'block'
console.log(f"Feching data {sql}...")