to_sql() Method

Teradata® Python Package User Guide

brand
Teradata Vantage
prodname
Teradata Python Package
vrm_release
16.20
category
User Guide
featnum
B700-4006-098K

Use the to_sql() function to create a table in Teradata 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 Teradata 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.

The function also takes the following optional arguments:

  • if_exists: Specifies the action to take when table already exists in the database. The possible values are:
    • 'fail': If table exists, do nothing;
    • 'replace': If table exists, drop it, recreate it, and insert data;
    • 'append': If table exists, insert data. Create if does not exist.

    The default value is 'append'.

  • primary_index: Creates Teradata tables with Primary index column. The value can be a single column name or a list of columns names. when primary_index is not specified, "No Primary Index" (NOPI) Teradata tables are created and saved.
  • temporary: Specifies whether to create a Teradata permanent table or Teradata volatile table. Volatile tables only exist for the lifetime of the session. Volatile tables automatically go away when the session ends. The possible values are:
    • True: Creates a volatile table;
    • False: Creates a permanent table.

    The default value is False.

  • schema_name: Specifies the name of the database to save the required teradataml DataFrame to. A non-default database name to which the current user has write permissions to save tables, can be provided to this parameter. When no schema_name is provided by the user, the teradataml DataFrame is saved to the default database of the user.

Examples Prerequisite

Assume a teradataml DataFrame "df" is created from the a Teradata 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  2017-04-01
Alpha Co    210.0  200.0  215.0  250.0  2017-04-01
Jones LLC   200.0  150.0  140.0  180.0  2017-04-01
Yellow Inc   90.0    NaN    NaN    NaN  2017-04-01
Orange Inc  210.0    NaN    NaN  250.0  2017-04-01
Red Inc     200.0  150.0  140.0    NaN  2017-04-01

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 Teradata 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 Teradata 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
     Feb    Jan    accounts
0   90.0    NaN  Yellow Inc
1  210.0  200.0    Alpha Co
2  200.0  150.0   Jones LLC
3  210.0    NaN  Orange Inc
4   90.0   50.0    Blue Inc
5  200.0  150.0     Red Inc

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 Teradata with the primary index "accounts". And verify by creating a DataFrame based on this newly created table.

>>> df.to_sql("sales_df2", primary_index="accounts")

>>> df2 = DataFrame("sales_df2")

Enter df2 to display the DataFrame just created.

>>> df2
              Feb    Jan
accounts
Alpha Co    210.0  200.0
Blue Inc     90.0   50.0
Yellow Inc   90.0    NaN
Jones LLC   200.0  150.0
Red Inc     200.0  150.0
Orange Inc  210.0    NaN

Example: Use the optional parameter "if_exists" to replace the existing table "sales_df2"

>>> df = DataFrame("sales")
>>> df = df.select(["accounts", "datetime"])

>>> df.to_sql("sales_df2", if_exists="replace")

>>> df2 = DataFrame("sales_df2")

Enter df2 to display the DataFrame just created.

>>> df2
     accounts    datetime
0   Jones LLC  2017-04-01
1    Blue Inc  2017-04-01
2  Yellow Inc  2017-04-01
3     Red Inc  2017-04-01
4    Alpha Co  2017-04-01
5  Orange Inc  2017-04-01

Example: Use a primary index to replace the existing table "sales_df2"

>>> df.to_sql("sales_df2", if_exists="replace", primary_index="accounts")

>>> df2 = DataFrame("sales_df2")

Enter df2 to display the DataFrame just created.

>>> df2
              datetime
accounts
Blue Inc    2017-04-01
Alpha Co    2017-04-01
Jones LLC   2017-04-01
Yellow Inc  2017-04-01
Orange Inc  2017-04-01
Red Inc     2017-04-01