# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python [conda env:test_conda_s2v]
#     language: python
#     name: conda-env-test_conda_s2v-py
# ---

# # How to use dataloader

import pandas as pd
import sklearn2vantage as s2v
from sklearn.datasets import load_digits
from sqlalchemy import create_engine


def make_df(loader):
    data = loader()
    columns = data.get("feature_names")
    df = pd.DataFrame(data.data, columns=columns)
    df["target"] = pd.Series(data.target)
    return df


engine = create_engine("teradata://dbc:dbc@172.16.58.3:1025/tdwork")

df_digits= make_df(load_digits)

# + jupyter={"outputs_hidden": true}
# replace
s2v.tdload_df(df_digits, engine, tablename="tdload_digits", ifExists="replace",
                     ssh_ip="172.16.58.3", ssh_username="root", ssh_password="<PASSWORD>")
# -

pd.read_sql_query("select count(*) from tdload_digits", engine)

# + jupyter={"outputs_hidden": true}
# insert
s2v.tdload_df(df_digits, engine, tablename="tdload_digits", ifExists="insert",
                     ssh_ip="172.16.58.3", ssh_username="root", ssh_password="<PASSWORD>")
# -

pd.read_sql_query("select count(*) from tdload_digits", engine)

# + jupyter={"outputs_hidden": true}
# errror if table already exists
s2v.tdload_df(df_digits, engine, tablename="tdload_digits", ifExists="error",
                     ssh_ip="172.16.58.3", ssh_username="root", ssh_password="<PASSWORD>")

# + jupyter={"outputs_hidden": true}
# saveIndex, make index ("index"), which is unique, and use xz compressing for scp
s2v.tdload_df(df_digits, engine, tablename="tdload_digits", ifExists="replace",
                     ssh_ip="172.16.58.3", ssh_username="root", ssh_password="<PASSWORD>",
                     saveIndex=True, indexList=["index"], isIndexUnique=True, compress="xz")
# -

df_titanic = pd.read_csv("titanic/train.csv").set_index("PassengerId")
df_titanic.head()

# + jupyter={"outputs_hidden": true}
# test with titanic data, with less verbosity
s2v.tdload_df(df_titanic, engine, tablename="titanic_train", ifExists="replace",
                         ssh_ip="172.16.58.3", ssh_username="root", ssh_password="<PASSWORD>",
                         saveIndex=True, indexList=["PassengerId"], verbose=False)
# -

df_house = pd.read_csv("house-prices-advanced-regression-techniques/train.csv")
df_house.head()

# + jupyter={"outputs_hidden": true}
# test with house data, with gz compressing
s2v.tdload_df(df_house, engine, tablename="house_train", ifExists="replace",
                          ssh_ip="172.16.58.3", ssh_username="root", ssh_password="<PASSWORD>", compress="gz")
# -

# create sample data
df_sample = pd.DataFrame({"c_int":[1,2,3,4], "c_float":[1.2, -0.3, 0.9, 1.5],
                          "c_bigint":[int(1e12), int(2e12), int(3e12), int(4e12)],
                         "c_bool":[True,False,True,False], 
                          "c_string":["a", "b", "c", "d"],
                         "c_list":[[1,2,3], [4,5,6], [7,8,9], [1,2,3]],
                         "c_timedelta":pd.to_timedelta(["00:01:02", "00:03:01",
                                                     "05:02:01", "1:01:08"]),
                         "c_date":pd.to_datetime(["2020/1/1","1998/12/2",
                                                   "1980/4/1","2008/1/1"]),
                         "c_timestamp":pd.to_datetime(["2020/1/1 10:10:10.09",
                                                   "2020/1/1 10:10:10",
                                                   "2020/1/1 10:10",
                                                   "2020/1/1 10:10:05"])})
df_sample.head()

# + jupyter={"outputs_hidden": true}
# test with sample data
s2v.tdload_df(df_sample, engine, tablename="t_sample", ifExists="replace",
                          ssh_ip="172.16.58.3", ssh_username="root", ssh_password="<PASSWORD>",
                          compress="bz2")

# + jupyter={"outputs_hidden": true}
# test with sample data and data type overwriting
s2v.tdload_df(df_sample, engine, tablename="t_sample", ifExists="replace",
                          ssh_ip="172.16.58.3", ssh_username="root", ssh_password="<PASSWORD>",
                          compress="bz2", dtype={"c_int": "varchar(5)", "c_bigint":"float"})

StarcoderdataJupyterScriptsDedupFiltered