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