Execute SQL queries using teradataml - Execute SQL Queries using teradataml - Teradata Package for Python

Teradata® Package for Python User Guide

Product
Teradata Package for Python
Release Number
17.00
Published
November 2021
Language
English (United States)
Last Update
2022-01-14
dita:mapPath
bol1585763678431.ditamap
dita:ditavalPath
ayr1485454803741.ditaval
dita:id
B700-4006
lifecycle
previous
Product Category
Teradata Vantage

teradataml offers a way to execute SQL queries on Vantage from Python interface. User can use the execute() method of the connection object returned by get_connection() function to execute the queries.

Example

This 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.

  1. 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>
  2. Make sure the table to be created 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.
  3. 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)
  4. 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
  5. Execute SELECT query using execute().
    • 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
  6. 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.