Write Tabulator data to database

Panel
1

I’m new to panel and am liking what I see so far. Unfortunately I’ve hit a snag.

I’m using a Tabulator as a way of capturing constrained user input. I have this working.
Once the user has entered all of the data I then need to write this data to a database. It is this that I am having trouble with.

Questions:

  1. Is it possible to write tabular data to a database
  2. Are there any resources on how to go about this.

I’ve tried searching for an answer but haven’t got any relevant results. If someone knows could they please point me in the right direction.
Thanks in advance
Aptperson

2

Welcome to the community :slight_smile: I build up a connection to a postgressql database using the sqalchemy package. In my usecase I create a new relation for every pandas Dataframe. Using the method psql_insert_copy makes it al lot faster. Its copied from pandas.DataFrame.to_sql — pandas 1.2.4 documentation

Sqalchemy can be used with MySQL and others as well.

A usecase by @hyamanieu you can find here: IoT measurements data?

from sqlalchemy import create_engine
import pandas as pd
from io import StringIO
import csv

df = yourtabulardata
engine = create_engine(
    'postgresql'+psycopg2://postgres:password@localhost/dbname')

def psql_insert_copy(table, conn, keys, data_iter):
    """
    Execute SQL statement inserting data

    Parameters
    ----------
    table : pandas.io.sql.SQLTable
    conn : sqlalchemy.engine.Engine or sqlalchemy.engine.Connection
    keys : list of str
        Column names
    data_iter : Iterable that iterates the values to be inserted
    """
    # gets a DBAPI connection that can provide a cursor
    dbapi_conn = conn.connection
    with dbapi_conn.cursor() as cur:
        s_buf = StringIO()
        writer = csv.writer(s_buf)
        writer.writerows(data_iter)
        s_buf.seek(0)

        columns = ', '.join('"{}"'.format(k) for k in keys)
        if table.schema:
            table_name = '{}.{}'.format(table.schema, table.name)
        else:
            table_name = table.name

        sql = 'COPY {} ({}) FROM STDIN WITH CSV'.format(
            table_name, columns)
        cur.copy_expert(sql=sql, file=s_buf)
    dbapi_conn.cursor().close()

Insert your tabular data into database. Each table as a new relation.

df.to_sql(name=str(name_relation),
                    con=engine,
                    if_exists="replace",
                    index=False,
                    method=psql_insert_copy)
3

Thanks for your fast response @sunny. I’ve looked at your solution and am not sure how to get it working for my use case. Here is a minimal example of what I’m trying to do:

import pandas as pd
import panel as pn

pn.extension()

NUSERS = pn.widgets.IntInput(name='Number of users', value=1)

@pn.depends(NUSERS.param.value)
def get_nrow_df(NUSERS):
    df = pd.DataFrame({
                    'ID': [1],
                    'NAME': ['Joe Smith']
    })
    df = pd.DataFrame(df.values.repeat(NUSERS, axis=0),
                     columns = df.columns)
    return pn.widgets.Tabulator(df)
    
    
def write_to_db(df):

    # display success text
    text.value = 'Submitted {0} times to db'.format(submit_button.clicks)
    # db insert here
    
text = pn.widgets.TextInput(value='Ready')
submit_button = pn.widgets.Button(name='Sumbit users')
submit_button.on_click(write_to_db)

pn.Column(NUSERS, get_nrow_df, submit_button, text).servable()

Essentially the user can specify the number of users they want to create, enter there name, and once happy hit submit, and I want this data written to the database. It is the point of database insertion that I am struggling with. How do I get the data to the function write_to_db as a dataframe? If I pass get_nrow_df this is a function.

4

I’ve been digging around a little more and have found something similar to what I want to do in the example https://panel.holoviz.org/gallery/simple/save_filtered_df.html?highlight=download%20filtered
Except where this example saves a CSV I want to write to a database.