# 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:** 1\
**Showing post:** 2

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

---

_[View the full topic](https://discourse.holoviz.org/t/write-tabulator-data-to-database/2268)._