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

Teradata® Package for Python User Guide

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Teradata Package for Python
Release Number
20.00
Published
March 2024
Language
English (United States)
Last Update
2024-04-09
dita:mapPath
nvi1706202040305.ditamap
dita:ditavalPath
plt1683835213376.ditaval
dita:id
rkb1531260709148
Product Category
Teradata Vantage

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.

This approach is not suitable if SQLAlchemy version is 2.0.x and later.

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.