Sample code for establishing a connection with an e6data cluster.
from e6data_python_connector import Connectionusername ='<username>'# Your e6data Email ID.password ='<password>'# Access Token generated in the e6data console.host ='<host>'# IP address or hostname of the cluster to be used.database ='<database>'# # Database to perform the query on.port =443# Port of the e6data engine.catalog_name ='<catalog_name>'cluster_uuid ='<cluster_uuid>'# Specify cluster UUID on when secure=Truesecure =True# Flag to use a secure channel for data transfer, default to Falseconn =Connection( host=host, port=port, username=username, database=database, password=password, cluster_uuid=cluster_uuid, secure=secure)
Switch Database in an Existing Connection
database ='<new_database_name>'# Replace with the new database namecursor = conn.cursor(database)
Perform Query & Get Results
This code executes a query and fetches all the results.
The cursor supports fetchmany and fetchone methods.
query ='SELECT * FROM <TABLE_NAME>'# Replace with the query.cursor = conn.cursor(catalog_name=catalog_name)query_id = cursor.execute(query) # The execute function returns a unique query ID, which can be use to abort the query.
all_records = cursor.fetchall()for row in all_records:print(row)
To get all the results:
records = cursor.fetchall()
To get only one result (first row):
record = cursor.fetchone()
To limit the number of results:
limit =500records = cursor.fetchmany(limit)
Code Hygiene
It is recommended to clear the cursor, close the cursor and close the connection after running a function as a best practice. This enhances performance by clearing old data from memory.
cursor.clear()# Not needed when aborting a querycursor.close()connection.close()
Get Row Count
This code executes a query and returns the number of rows in the result set.
Fetching rowcount from queryplanner is preferred over using fetchall method.
The following code is an example using multiple functions defined above to return:
Number of rows
The time taken to execute the query
Query results
from e6data_python_connector import Connectionimport jsonusername ='<username>'# Your e6data Email ID.password ='<password>'# Access Token generated in the e6data console.host ='<host>'# IP address or hostname of the cluster to be used.database ='<database>'# # Database to perform the query on.port =80# Port of the e6data engine.sql_query ='SELECT * FROM <TABLE_NAME>'# Replace with the actual query.catalog_name ='<catalog_name>'# Replace with the actual catalog name.conn =Connection( host=host, port=port, username=username, database=database, password=password)cursor = conn.cursor(db_name=database, catalog_name=catalog_name)query_id = cursor.execute(sql_query)all_records = cursor.fetchall()explain_response = cursor.explain_analyse()planner_result = json.loads(explain_response.get('planner'))execution_time = planner_result.get("total_query_time")/1000# Converting into seconds.row_count = cursor.rowcountcolumns = [col[0]for col in cursor.description] # Get the column names and merge them with the results.results = []for row in all_records: row =dict(zip(columns, row)) results.append(row)print(row)print('Total row count {}, Execution Time (seconds): {}'.format(row_count, execution_time))cursor.clear()cursor.close()conn.close()