When I first started working with Trino (formerly PrestoSQL), I found it powerful but somewhat tricky to connect from Python while handling authentication, SSL, and dynamic query parameters. After spending some time figuring it out, I decided to share what I’ve learned to help anyone facing the same challenges. If you’re trying to connect to Trino from Python, pass credentials securely, and send parameters dynamically, this guide will walk you through the process from start to finish.
Thank me by sharing on Twitter 🙏
This post assumes you already have a Trino server up and running and want to connect to it securely using Python. We’ll also explore how to query the database efficiently by passing user-defined values to SQL queries.
Setting up Your Python Environment
Before writing any code, the first step is to ensure you have the necessary Python packages installed. For Trino, the official client library is called trino. You can install it via pip:
pip install trinoThis package will allow your Python script to communicate with Trino. Additionally, make sure you are familiar with some basic Python concepts, such as how to handle exceptions and securely manage credentials.
The Nvidia Way: Jensen Huang and the Making of a Tech Giant
$17.50 (as of January 22, 2025 11:32 GMT +00:00 - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)
Co-Intelligence: Living and Working with AI
$17.79 (as of January 22, 2025 11:32 GMT +00:00 - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)
Unexpected Healer: A Fantasy LitRPG Isekai Adventure (Earthen Contenders Book 1)
$4.99 (as of January 22, 2025 11:32 GMT +00:00 - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)Establishing a Connection to Trino with SSL
Connecting to Trino with SSL involves more than just specifying a hostname and a port. You need to make sure that your Python script is set to use HTTPS correctly, verify the SSL certificate, and manage authentication.
Here’s a Python snippet to get started with a secure connection:
import trino
from trino.auth import BasicAuthentication
from trino.exceptions import TrinoQueryError
def connect_to_trino():
conn = trino.dbapi.connect(
host='your-trino-host', # Replace with your server address
port=443, # Common SSL port (443 or 8443)
http_scheme='https', # Use HTTPS for SSL
auth=BasicAuthentication('your-username', 'your-password'),
verify=True # Set to True to validate the server certificate
)
return connIn this code, we create a connection object using trino.dbapi.connect(). We set the http_scheme to ‘https’ to ensure the connection is encrypted. We also use BasicAuthentication to send the username and password securely. If your Trino server requires a specific certificate, you can provide its path by setting verify=’/path/to/cert.pem’.
If you’re working with self-signed certificates during development, you might be tempted to set verify=False to skip validation. However, be aware that skipping SSL verification can expose your connection to security risks. It’s best to use this option only for testing purposes.
Running a Query with Dynamic Parameters
Once the connection is established, the next step is to send SQL queries. But what if you need to pass user-defined parameters, such as a specific user_id, to your query? Writing queries directly into your code can be error-prone and opens the door to SQL injection if not handled properly. That’s why I always use parameterized queries, which keep my code cleaner and safer.
Here’s an example of how to pass a user_id dynamically to a SQL query:
def run_query_with_user_id(conn, user_id):
sql_query = """
SELECT * FROM your_table
WHERE user_id = ?
LIMIT 10;
"""
try:
cursor = conn.cursor()
cursor.execute(sql_query, (user_id,))
result = cursor.fetchall()
print("Query Results:")
for row in result:
print(row)
except TrinoQueryError as e:
print(f"Query failed: {e}")
finally:
cursor.close()In this example, the ? placeholder in the SQL query allows us to pass the user_id as a parameter. We provide the value as a tuple (user_id,) to cursor.execute(). This approach not only prevents SQL injection but also makes it easier to reuse the query with different inputs.
If you’re working with integer inputs, it’s good practice to validate them before running the query. Here’s a small adjustment to ensure only valid numbers are accepted:
user_input = input("Enter the user_id: ")
if not user_input.isdigit():
print("Invalid user_id. Please enter a numeric value.")
else:
run_query_with_user_id(conn, int(user_input))This extra step makes sure that only numeric inputs are accepted, avoiding errors when querying the database.
Managing Errors Gracefully
When working with databases, things don’t always go as planned. A connection might fail, or a query might be incorrect. To handle these scenarios gracefully, I recommend wrapping your query logic inside a try-except-finally block. This ensures that even if something goes wrong, the database cursor is properl/y closed.
Take a look at this enhanced error-handling code:
try:
cursor = conn.cursor()
cursor.execute(sql_query, (user_id,))
result = cursor.fetchall()
if result:
print("Query Results:")
for row in result:
print(row)
else:
print("No data found for the given user_id.")
except TrinoQueryError as e:
print(f"An error occurred: {e}")
finally:
cursor.close()In the except block, we catch any TrinoQueryError that might occur. This ensures the script prints a helpful message instead of crashing. I also added a check to print a message if the query returns no results, so the user isn’t left wondering what happened.
Conclusion
Connecting to Trino from Python with password authentication and SSL might seem intimidating at first, but once you break it down step by step, it becomes quite manageable. By using the trino library, setting up secure connections with SSL, and passing parameters dynamically, you can write more robust and flexible code for your data queries.
I’ve found that parameterized queries not only keep my code cleaner but also help prevent SQL injection attacks, which is critical when dealing with user input. And with error handling in place, you can ensure your scripts run smoothly even when things don’t go according to plan.
The next time you need to query Trino from Python, you’ll have everything you need—from secure connections to dynamic queries—ready to go.
