copy_to_sql

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 copy_to_sql() function to create a table in Teradata Vantage based on a teradataml DataFrame or a pandas DataFrame.

The function takes a teradataml DataFrame or a pandas DataFrame and a table name as arguments, and generates DDL and DML commands 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'.
  • index: Specifies whether to save pandas DataFrame index as a column or not. The possible values are True or False.

    The default value is False.

    This argument is only used with pandas DataFrames.
  • index_label: Specifies the column label for pandas DataFrame index columns. If no value is given and index is True, then a default label 'index_label' is used.
    This argument is only used with pandas DataFrames.
  • primary_index: Creates Teradata tables with Primary index column. The value can be a single column name or a list of columns names.
    • For Pandas DataFrames, when primary_index is not specified, default primary index is selected as per Teradata table creation rules.
    • For teradataml DataFrames, 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 a 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 or pandas 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 or pandas DataFrame is saved to the default database of the user.

Examples Prerequisite

You must import pandas and copy_to_sql first.
>>>import pandas as pd
>>>from teradataml.dataframe.copy_to import copy_to_sql
Assume you create the "sales" table as follows:
>>>sales = [{'accounts': 'Jones LLC', 'Jan': 150, 'Feb': 200.0, 'Mar': 140, 'datetime':'2017-04-01'},
            {'accounts': 'Alpha Co',  'Jan': 200, 'Feb': 210.0, 'Mar': 215, 'datetime': '2017-04-01'},
            {'accounts': 'Blue Inc',  'Jan': 50,  'Feb': 90.0,  'Mar': 95, 'datetime': '2017-04-01' }]
And a pandas DataFrame "pdf" is created from the table "sales", using command:
pdf = pd.DataFrame(sales)
Enter pdf to display the pandas DataFrame:
>>>pdf
     Feb  Jan  Mar   accounts    datetime
0  200.0  150  140  Jones LLC  2017-04-01
1  210.0  200  215   Alpha Co  2017-04-01
2   90.0   50   95   Blue Inc  2017-04-01

Example: Create a Teradata table "pandas_sales" based on the pandas DataFrame

Use the copy_to_sql() function to create a new Teradata table "pandas_sales" based on the pandas DataFrame "pdf" that is created in the prerequisite.
>>>copy_to_sql(df = pdf, table_name = "pandas_sales", primary_index="accounts", if_exists="replace")
Verify that the new table "pandas_sales" exists in Teradata using the DataFrame() function which creates a DataFrame based on an existing table.
>>>df = DataFrame("pandas_sales")
Enter df to display the DataFrame just created.
>>>df
             Feb    Jan    Mar    datetime
accounts
Blue Inc    90.0   50.0   95.0  2017-04-01
Jones LLC  200.0  150.0  140.0  2017-04-01
Alpha Co   210.0  200.0  215.0  2017-04-01

Example: Create a new Teradata table "pandas_sales" using the optional index and index_label parameters

Use the function copy_to_sql() with the optional index and index_label parameters to include the index and an index label in the table "pandas_sales". And verify by creating a DataFrame based on this newly created table.
>>>copy_to_sql(df = pdf, table_name = "pandas_sales", index=True, index_label="idx", if_exists="replace")

>>>df = DataFrame("pandas_sales")
Enter df to display the DataFrame just created.
>>>df
       Feb    Jan    Mar   accounts    datetime
idx
0.0  200.0  150.0  140.0  Jones LLC  2017-04-01
2.0   90.0   50.0   95.0   Blue Inc  2017-04-01
1.0  210.0  200.0  215.0   Alpha Co  2017-04-01

Example: Create a new Teradata table based on a teradataml DataFrame

First create a teradataml DataFrame based on the Teradata table "sales". And Enter df to display this DataFrame.

>>>df = DataFrame("sales")

>>>df
              Feb    Jan    Mar    Apr    datetime
accounts
Jones LLC   200.0  150.0  140.0  180.0  2017-04-01
Blue Inc     90.0   50.0   95.0  101.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
Alpha Co    210.0  200.0  215.0  250.0  2017-04-01
Red Inc     200.0  150.0  140.0    NaN  2017-04-01
Use the copy_to_sql() function to create a new Teradata table "sales_df1" based on the teradataml DataFrame "df" just created. And verify by creating a DataFrame "df1" based on this newly created table.
>>>copy_to_sql(df = df, table_name = "sales_df1", primary_index="accounts", if_exists="replace")

>>>df1 = DataFrame("sales_df1")
Enter df1 to display the DataFrame just created.
>>>df1
              Feb    Jan    Mar    Apr    datetime
accounts
Jones LLC   200.0  150.0  140.0  180.0  2017-04-01
Blue Inc     90.0   50.0   95.0  101.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
Alpha Co    210.0  200.0  215.0  250.0  2017-04-01
Red Inc     200.0  150.0  140.0    NaN  2017-04-01