Demo program for exporting pandas data frames to SQL databases

pandas_to_sql.py

"""
Demo program for exporting pandas data frames to SQL databases.
"""
import pandas as pd


df = pd.DataFrame(
        {
            "station_id": pd.Categorical(["00001"]),
            #"date": [pd.Timestamp("1970-01-01").tz_localize("UTC")],
            "date": [pd.Timestamp("1970-01-01")],
            "qn": pd.Series([1], dtype=pd.Int64Dtype()),
            "rs_indicator": pd.Series([1], dtype=pd.Int64Dtype()),
            "label": pd.Series(["P"], dtype=pd.StringDtype()),
        }
    )


def pandas_to_sql(dburi):
    df.to_sql(
        con=dburi,
        name="testdrive",
        if_exists="replace",
        index=False,
        #method="multi",
        chunksize=5000,
    )


if __name__ == "__main__":

    # Use SQLite.
    # pandas_to_sql(dburi="sqlite:///dwd.sqlite")

    # Use PostgreSQL.
    # docker run -it --rm --publish=5432:5432 --env=POSTGRES_HOST_AUTH_METHOD=trust postgres:13.2
    # psql postgres://postgres@localhost --command "CREATE DATABASE dwd;"
    # pandas_to_sql(dburi="postgres://postgres@localhost/dwd")

    # Use CrateDB.
    # docker run -it --rm --publish=4200:4200 --publish=5432:5432 --env=CRATE_HEAP_SIZE=2048M crate/crate:nightly
    # pandas_to_sql(dburi="crate://crate@localhost/dwd")
    # pandas_to_sql(dburi="postgres://crate@localhost/dwd")

    # Use QuestDB.
    # docker run -it --rm --publish=5432:8812 questdb/questdb
    # pandas_to_sql(dburi="postgres://admin:quest@localhost/dwd")

    # Use CockroachDB.
    # docker run -it --rm --publish=5432:26257 cockroachdb/cockroach start-single-node --insecure --logtostderr
    # psql postgres://root@localhost --command "CREATE DATABASE dwd;"
    # pandas_to_sql(dburi="postgres://root@localhost/dwd")

    print("Ready.")