pythoncursorcommitexecutetrino

cursor.commit() not working in Python for data upload into Trino


I can pull data from Trino into Python and manipulate it but when I go to try to upload the manipulated data into a new table, nothing gets committed. I can DROP/CREATE the same table in DBeaver fine, but if I try to do something as simple as dropping the table via python - it does not work. I don't get an error, but nothing is committed.

Example of code that does work:

# Importing required packages
from trino.dbapi import connect
from trino.auth import BasicAuthentication

# Establishing a connection to Trino
trino_conn=connect(
host='hostname.example.com',
port=8443,
user='user',
catalog='system',
schema='runtime',
http_scheme='https',
auth=BasicAuthentication("username", "password"),
)
trino_conn._http_session.verify = False
cursor = trino_conn.cursor()

query = """
SELECT  * FROM db.schema.sample_table
"""

cursor.execute(query)
rows = cursor.fetchall()
columns = [rows[0] for rows in cursor.description]
dataframe = pd.DataFrame(rows, columns=columns)

After successfully creating the table sample_table_II in DBeaver, the following code does not drop it in Python:

# Importing required packages
from trino.dbapi import connect
from trino.auth import BasicAuthentication

# Establishing a connection to Trino
trino_conn=connect(
host='hostname.example.com',
port=8443,
user='user',
catalog='system',
schema='runtime',
http_scheme='https',
auth=BasicAuthentication("username", "password"),
)
trino_conn._http_session.verify = False
cursor = trino_conn.cursor()

cursor.execute("DROP TABLE db.schema.sample_table_II")
trino_conn.commit()

Keep in mind that cursor.commit() works fine for me when I am working with Teradata:

import teradatasql
teradata_conn = teradatasql.connect(host='hostname.example.com', user='XXXXXXXXXX', password='XXXXXXXXXX')
cursor = teradata_conn.cursor()

Solution

  • I guess the environment I was working in had too many dependencies with the trino package because when I created a new environment via:

    conda create -n trino_env python=3.7 anaconda
    conda activate trino_env
    

    and then used:

    pip install trino
    

    Python was able to commit the changes I was trying to make to the database.