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