to_sql() Method - Teradata Python Package

Teradata® Python Package User Guide

Product
Teradata Python Package
Release Number
16.20
Published
February 2020
Language
English (United States)
Last Update
2020-02-29
dita:mapPath
rkb1531260709148.ditamap
dita:ditavalPath
Generic_no_ie_no_tempfilter.ditaval
dita:id
B700-4006
lifecycle
previous
Product Category
Teradata Vantage

Use the to_sql() function to create a table in Vantage based on a teradataml DataFrame.

The function takes a table name as an argument, and generates DDL and DML queries that creates a table in Vantage. You can also specify the name of the schema in the database to write the table to. If no schema name is specified, the default database schema is used.

Example: Create a table

Assume a teradataml DataFrame "df" is created from the a Vantage table "sales", using command:
df = DataFrame("sales")

Enter df to display the teradataml DataFrame:

>>> df
              Feb    Jan    Mar    Apr    datetime
accounts
Blue Inc     90.0   50.0   95.0  101.0  04/01/2017
Alpha Co    210.0  200.0  215.0  250.0  04/01/2017
Jones LLC   200.0  150.0  140.0  180.0  04/01/2017
Yellow Inc   90.0    NaN    NaN    NaN  04/01/2017
Orange Inc  210.0    NaN    NaN  250.0  04/01/2017
Red Inc     200.0  150.0  140.0    NaN  04/01/2017

And a new teradataml DataFrame is created from this DataFrame with only the columns "accounts", "Jan", "Feb".

>>> df = df.select(["accounts", "Jan", "Feb"])

Enter df to display the new DataFrame:

>>> df
     accounts    Jan    Feb
0   Jones LLC  150.0  200.0
1    Blue Inc   50.0   90.0
2  Yellow Inc    NaN   90.0
3  Orange Inc    NaN  210.0
4    Alpha Co  200.0  210.0
5     Red Inc  150.0  200.0

Example: Create a NOPI table

Use the to_sql() function to create a new table "sales_df1" in Vantage based on the new DataFrame. The table "sales_df1" is a NOPI table because the primary index was not specified.

>>> df.to_sql("sales_df1")

Verify that the new table "sales_df1" exists in Vantage using the DataFrame() function which creates a DataFrame based on an existing table.

>>> df1 = DataFrame("sales_df1")

Enter df1 to display the DataFrame just created.

>>> df1
     accounts   Jan    Feb
0    Blue Inc    50   90.0
1  Orange Inc  None  210.0
2     Red Inc   150  200.0
3  Yellow Inc  None   90.0
4   Jones LLC   150  200.0
5    Alpha Co   200  210.0

Example: Create a table using the optional parameter primary_index

Use the to_sql() function with the optional parameter "primary_index" to create a new table "sales_df2" in Vantage with the primary index "accounts". Use the optional parameter "if_exists" to replace the existing table "sales_df2" in Vantage

>>> df = DataFrame("sales")
>>> df = df.select(["accounts", "datetime"])
>>> df.to_sql("sales_df2", if_exists="replace", primary_index="accounts")
>>> df2 = DataFrame("sales_df2")
>>> df2
            datetime
accounts           
Blue Inc    17/01/04
Orange Inc  17/01/04
Red Inc     17/01/04
Yellow Inc  17/01/04
Jones LLC   17/01/04
Alpha Co    17/01/04

Example: Use optional parameter "set_table" to create a SET table "sales_subset_set".

>>> df = DataFrame("sales")
>>> # First create a new dataframe with the columns "Apr", "datetime", to showcase the effect of the paramter
>>> df_for_set = df.select(['Apr','datetime'])
>>> df_for_set
    Apr    datetime
0   101  04/01/2017
1   250  04/01/2017
2   180  04/01/2017
3  None  04/01/2017
4   250  04/01/2017
5  None  04/01/2017
>>> df_for_set.to_sql('sales_subset_set', primary_index='Apr', set_table=True)
>>> set_df = DataFrame('sales_subset_set')
>>> set_df
     datetime
Apr
180  17/01/04
101  17/01/04
250  17/01/04
NaN  17/01/04

Example: Create a new Primary Time Index table "sales_pti".

>>> df.to_sql('sales_pti', timecode_column='datetime', columns_list='accounts')
>>> sales_pti= DataFrame('sales_pti')
>>> sales_pti
           TD_TIMECODE    Feb   Jan   Mar   Apr
accounts
Blue Inc      17/01/04   90.0    50    95   101
Orange Inc    17/01/04  210.0  None  None   250
Red Inc       17/01/04  200.0   150   140  None
Yellow Inc    17/01/04   90.0  None  None  None
Jones LLC     17/01/04  200.0   150   140   180
Alpha Co      17/01/04  210.0   200   215   250