Row INSERT into table does not persist

[burusam]

I have this script: 

from databricks import sql
import os
import pandas as pd

databricksToken = os.environ.get('DATABRICKS_TOKEN')

connection = sql.connect(server_hostname = "",
                 http_path       = "",
                 access_token    = databricksToken)

def databricks_write(name, racf, action):
  global connection  # Use the global connection variable
  cursor = connection.cursor()
  df = pd.DataFrame({'operatorName': name, 'racf': racf, 'action': action, 'datetime': [pd.Timestamp.now()]})
  columns = ', '.join(df.columns)
  print(columns)
  values = ', '.join([f"'{value}'" for value in df.values[0]])
  print(values)
  query = f"INSERT INTO okta_log.log ({columns}) VALUES ({values})"
  cursor.execute(query)
  connection.commit()

 This does successfully add a row to the table and I can see subsequent rows also getting added after running this function each time. But when I reload the table and run the select * again I don't see my rows of data. It seems that the row of data persists only for a short duration and is not permanent

How do I fix this?

[Emil_Kaminski]

HI, 

are you closing connection at the end?

cursor.close()
connection.close()

Also, can you elaborate what you mean by saying "when I reload the table"

Cheers

[Kaniz]

Hi @burusam , Your script successfully adds rows to the table, but the data doesn’t seem to persist permanently. 

Let’s address this issue:

Temporary vs. Permanent Tables:

• The behavior you’re observing might be due to the type of table you’re using.
• By default, temporary tables in Databricks are session-specific and exist only for the duration of the session. They don’t persist across different sessions or after the session ends.

Solution: Use a Permanent Table:

• To make your data persist permanently, consider using a permanent table (also known as a Delta Lake table).

Benefits of Delta Lake:

• Delta Lake provides features like ACID transactions, schema evolution, and time travel.
• It ensures data consistency and durability, making it suitable for long-term data storage.