Run SQL queries using teradataml | execute() versus execute_sql() | Teradata Package for Python - Run SQL Queries using teradataml - Teradata Package for Python

teradataml offers a way to run SQL queries on Vantage from Python interface.

For teradataml version 17.20.00.04 and later

From release 17.20.00.04, teradataml introduces new function execute_sql() that supports to run SQL queries on Vantage.

You can use the execute_sql() function to run the queries once the connection is established with Vantage database.

The following example shows how to use the execute_sql() function to create a table, insert rows into the table, select rows of the table, and alter rows of the table.

• Establish connection with Vantage.
  This must be done before using execute_sql().
  • Establish connection.

    >>> eng = create_context("<td_host>", "<td_user>", "<td_pwd>")

  • Get the connection object.

    >>> conn = get_connection()

• Make sure the table to be created ('employee_info') does not exist in the database.
  Check if the table exists or not.

  >>> conn.dialect.has_table(connection=conn, table_name="employee_info")
  False

  If the table is already present, drop the table as follows:

  >>> db_drop_table("employee_info")
  True

  This is not needed when the table is not present.
• Create a table 'employee_info' using execute_sql().
  • Define a "create table" statement.

    >>> create_stmt = "create table employee_info (employee_no BIGINT, first_name VARCHAR(50), last_name VARCHAR(50), date_of_joining DATE FORMAT 'YYYY-MM-DD', DOB DATE FORMAT 'YYYY-MM-DD');"

  • Execute the "create table" statement.

    >>> output = execute_sql(create_stmt)

  • Print the output of execution.

    >>> for row in output:
    ...     print(row)

    Generally, the output of the execute_sql() function returns the cursor. For this example, as it is a create table statement, empty result set is generated.
  • Check if the table exists or not.

    >>> conn.dialect.has_table(connection=conn, table_name="employee_info")
    True

  • Create teradataml DataFrame.

    >>> df = DataFrame("employee_info")

  • Check the columns of the DataFrame.

    >>> df.columns
    ['employee_no', 'first_name', 'last_name', 'date_of_joining', 'DOB']

  • Check the shape of the DataFrame.

    >>> df.shape
    (0, 5)

• Insert rows using execute_sql().
  • Define "insert" statements.

    >>> ins_stmt = "insert into employee_info values (28, 'my_first_name', 'my_last_name', '2018-06-11', '1987-11-25');"

    >>> ins_stmt1 = "insert into employee_info values (4, 'my_first_name_1', 'my_last_name_1', '2018-06-11', '1988-02-23');"

  • Execute the "insert" statements.

    >>> ins = execute_sql(ins_stmt)

    >>> ins1 = execute_sql(ins_stmt1)

    Both "ins" and "ins1" have empty result set.
  • Check the shape of the DataFrame.

    >>> df.shape
    (2, 5)

  • Print the teradataml DataFrame.

    >>> df
                 first_name       last_name date_of_joining         DOB
    employee_no                                                            
    4            my_first_name_1  my_last_name_1      2018-06-11  1988-02-23
    28           my_first_name    my_last_name      2018-06-11  1987-11-25

• Select data using execute_sql().
  Teradata recommends using teradataml APIs, instead of running SELECT queries.
  • Execute SELECT query using execute_sql().

    >>> sel_result = execute_sql("select * from employee_info;")

  • Print the entire result set.

    >>> for row in sel_result:
    ...     print(row)
    [28, 'my_first_name', 'my_last_name', datetime.date(2018, 6, 11), datetime.date(1987, 11, 25)]
    [4, 'my_first_name_1', 'my_last_name_1', datetime.date(2018, 6, 11), datetime.date(1988, 2, 23)]

  • Fetch and print only certain columns.

    >>> sel_result = execute_sql("select * from employee_info;")

    >>> for row in sel_result: 
    ...     print(str(row[0]) + " : " + row[1])
    4 : my_first_name_1
    28 : my_first_name

• Alter data using execute().
  • Define an ALTER query statement.

    >>> alter_stmt = "update employee_info set first_name = 'my_first_name_2' where employee_no = 4;"

  • Execute the ALTER statement.

    >>> alter = execute_sql(alter_stmt)

    This produces empty result set.
  • Print the teradataml DataFrame.

    >>> df
                    first_name       last_name date_of_joining         DOB
    employee_no                                                            
    28           my_first_name    my_last_name      2018-06-11  1987-11-25
    4            my_first_name_2  my_last_name_1    2018-06-11  1988-02-23

    The first_name for the employee with employee_id 4 is changed.

For teradataml version 17.20.00.03 and earlier

You can use the SQLAlchemy execute() method of the connection object returned by get_connection() function to execute the queries.

The following example shows how to use execute() method to create a table, insert rows into table, select rows of the table and alter rows of the table.

• Create a connection and get the connection parameters.
  • Create the connection and get the engine.

    >>> eng = create_context("td_host", "td_user", "td_pwd")

  • Print the engine.

    >>> eng
    Engine(teradatasql://td_user:***@td_host)

  • Get the connection object.

    >>> conn = get_connection()

  • Print the connection object.

    >>> conn
    <sqlalchemy.engine.base.Connection at 0x7fb598edb610>

• Make sure the table to be created ('employee_info') does not exist in the database.
  Check if the table exists or not.

  >>> eng.dialect.has_table(connection=conn, table_name="employee_info")
  False

  If the table is already present, drop the table as follows:

  >>> db_drop_table("employee_info")
  True

  This is not needed when the table is not present.
• Create table 'employee_info' using execute().
  • Define a "create table" statement.

    >>> create_stmt = "create table employee_info (employee_no BIGINT, first_name VARCHAR(50), last_name VARCHAR(50), date_of_joining DATE FORMAT 'YYYY-MM-DD', DOB DATE FORMAT 'YYYY-MM-DD');"

  • Execute the "create table" statement.

    >>> output = conn.execute(create_stmt)

  • Print the output of execution.

    >>> for row in output:
    ...     print(row)

    Generally, the output of the execute() function returns the result set. For this example, as it is a create table statement, empty result set is generated.
  • Check if the table exists or not.

    >>> eng.dialect.has_table(connection=conn, table_name="employee_info")
    True

  • Create teradataml DataFrame.

    >>> df = DataFrame("employee_info")

  • Check the columns of the DataFrame.

    >>> df.columns
    ['employee_no', 'first_name', 'last_name', 'date_of_joining', 'DOB']

  • Check the shape of the DataFrame.

    >>> df.shape
    (0, 5)

• Insert rows using execute().
  • Define "insert" statements.

    >>> ins_stmt = "insert into employee_info values (28, 'my_first_name', 'my_last_name', '2018-06-11', '1987-11-25');"

    >>> ins_stmt1 = "insert into employee_info values (4, 'my_first_name_1', 'my_last_name_1', '2018-06-11', '1988-02-23');"

  • Execute the "insert" statements.

    >>> ins = conn.execute(ins_stmt)

    >>> ins1 = conn.execute(ins_stmt1)

    Both "ins" and "ins1" have empty result set.
  • Check the shape of the DataFrame.

    >>> df.shape
    (2, 5)

  • Print the teradataml DataFrame.

    >>> df
                 first_name       last_name date_of_joining         DOB
    employee_no                                                            
    4            my_first_name_1  my_last_name_1      2018-06-11  1988-02-23
    28           my_first_name    my_last_name      2018-06-11  1987-11-25

• Select data using execute().
  Teradata recommends using teradataml APIs, instead of running SELECT queries.
  • Execute SELECT query using execute().

    >>> sel_result = conn.execute("select * from employee_info;")

  • Print the entire result set.

    >>> for row in sel_result:
    ...     print(row)
    (4, 'my_first_name_1', 'my_last_name_1', datetime.date(2018, 6, 11), datetime.date(1988, 2, 23))
    (28, 'my_first_name', 'my_last_name', datetime.date(2018, 6, 11), datetime.date(1987, 11, 25))

  • Fetch and print only certain columns.

    >>> sel_result = conn.execute("select * from employee_info;")

    >>> for row in sel_result:
    ...     print(str(row['employee_no']) + " : " + row['first_name'])
    4 : my_first_name_1
    28 : my_first_name

• Alter data using execute().
  • Define an ALTER query statement.

    >>> alter_stmt = "update employee_info set first_name = 'my_first_name_2' where employee_no = 4;"

  • Execute the ALTER statement.

    >>> alter = conn.execute(alter_stmt)

    This produces empty result set.
  • Print the teradataml DataFrame.

    >>> df
                    first_name       last_name date_of_joining         DOB
    employee_no                                                            
    28           my_first_name    my_last_name      2018-06-11  1987-11-25
    4            my_first_name_2  my_last_name_1    2018-06-11  1988-02-23

    The first_name for the employee with employee_id 4 is changed.