# Write Tabulator data to database

**URL:** <https://discourse.holoviz.org/t/write-tabulator-data-to-database/2268>\
**Category:** Panel\
**Created:** [May 10, 2021, 5:46am UTC](https://discourse.holoviz.org/t/write-tabulator-data-to-database/2268 "2021-05-10T05:46:47Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![aptperson](https://discourse.holoviz.org/letter_avatar_proxy/v4/letter/a/9de053/32.png) [@aptperson](https://discourse.holoviz.org/u/aptperson)\
**Post date:** [May 10, 2021, 5:46am UTC](https://discourse.holoviz.org/t/write-tabulator-data-to-database/2268/1 "2021-05-10T05:46:47Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![sunny](https://discourse.holoviz.org/letter_avatar_proxy/v4/letter/s/7c8e57/32.png) [@sunny](https://discourse.holoviz.org/u/sunny)\
**Post date:** [May 10, 2021, 7:25am UTC](https://discourse.holoviz.org/t/write-tabulator-data-to-database/2268/2 "2021-05-10T07:25:08Z")

</div>

Welcome to the community 🙂 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](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_sql.html)

Sqalchemy can be used with MySQL and others as well.

A usecase by @hyamanieu you can find here: [IoT measurements data](https://discourse.holoviz.org/t/iot-measurements-data/1692)?

```
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)
```

---

<div class="post-metadata">

**Author:** ![aptperson](https://discourse.holoviz.org/letter_avatar_proxy/v4/letter/a/9de053/32.png) [@aptperson](https://discourse.holoviz.org/u/aptperson)\
**Post date:** [May 10, 2021, 10:44pm UTC](https://discourse.holoviz.org/t/write-tabulator-data-to-database/2268/3 "2021-05-10T22:44:05Z")

</div>

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:

```auto
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.

---

<div class="post-metadata">

**Author:** ![aptperson](https://discourse.holoviz.org/letter_avatar_proxy/v4/letter/a/9de053/32.png) [@aptperson](https://discourse.holoviz.org/u/aptperson)\
**Post date:** [May 10, 2021, 11:35pm UTC](https://discourse.holoviz.org/t/write-tabulator-data-to-database/2268/4 "2021-05-10T23:35:03Z")

</div>

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](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.