Python: Create hyper file from multiple data frames/CSV

I have to create a hyper file with single table by joining multiple data frames. I have attached the code and the error message that I'm receiving. My approach is:

1- Read input data and convert to pandas data frame

2- Define temporary tables using Table definition:

3-Create Schema and add the data through iteration

4- Using SQL to join the data

Code:

• Step 1:

1. table_one_csv_path = "table_one.csv"
2. table_two_csv_path = "table_two.csv"
3. table_one = pd.read_csv(table_one_csv_path)
4. table_two = pd.read_csv(table_two_csv_path)

• Step 2:

with HyperProcess(telemetry=Telemetry.DO_NOT_SEND_USAGE_DATA_TO_TABLEAU) as hyper:

1. # Creates new Hyper file ".
2. with Connection(endpoint=hyper.endpoint, database=hyper_name,
3. create_mode=CreateMode.CREATE_AND_REPLACE) as connection:
4. #Create Schema
5. connection.catalog.create_schema("Extract")
6. # Creates multiple tables.
7. sales = TableDefinition(
8. table_name=TableName("Extract", "sales"),
9. columns=[
10. TableDefinition.Column("Product Key", SqlType.int()),
11. TableDefinition.Column("Sales", SqlType.int())
12. ],
13. persistence=Persistence.TEMPORARY
14. )
15.  
16. products = TableDefinition(
17. table_name=TableName("Extract", "products"),
18. columns=[
19. TableDefinition.Column("Product Key", SqlType.int()),
20. TableDefinition.Column("Product Name", SqlType.text())
21. ],
22. persistence=Persistence.TEMPORARY
23. )

• Step 3:

# I think I should not create table as in Table definition I set the persistence to Temporary, But I still receiving error --> "sales" table not found

1. connection.catalog.create_table(sales)
2. connection.catalog.create_table(products)
3. with Inserter(connection, sales) as inserter_1:
4.  
5. for index, row in table_one.iterrows():
6. inserter_1.add_row(row)
7. inserter_1.execute()
8.  
9. with Inserter(connection, products) as inserter_2:
10. for index, row in table_two.iterrows():
11. inserter_2.add_row(row)
12. inserter_2.execute()

Step 4:

1. table_names = connection.catalog.get_table_names("Extract")
2. path_to_database = Path(hyper_name)
3. print(f"Tables available in {path_to_database} are: {table_names}")
4.  
5. joined_connections = connection.execute_command(f'CREATE TABLE "Extract"."Extract" AS SELECT * FROM {sales.table_name} as A LEFT OUTER JOIN {products.table_name} as B ON A.{escape_name("Product Key")} = B.{escape_name("Product Key")}')

Error Message, while Im creating table using connection.catalog.create_table()

"tableauhyperapi.hyperexception.HyperException: cannot create temporary object in non-temporary schema "

Error Message, while Im not creating the table, but keeping the persistence to Temporary:

"tableauhyperapi.hyperexception.HyperException: table "joined_hyper"."Extract"."sales" does not exist: "