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()
- Establish 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)
- Define a "create table" statement.
- 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
- Define "insert" statements.
- 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
- Execute SELECT query using execute_sql().
- 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.
- Define an ALTER query statement.
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>
- Create the connection and get the engine.
- 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)
- Define a "create table" statement.
- 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
- Define "insert" statements.
- 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
- Execute SELECT query using execute().
- 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.
- Define an ALTER query statement.