trino how to insert use sqlalchemy

2 min read 05-09-2024

If you're looking to insert data into a Trino database using SQLAlchemy, you're in the right place! In this guide, we will walk you through the steps required to connect to Trino and perform insert operations effectively.

What is Trino?

Trino is a distributed SQL query engine designed for running interactive analytic queries against various data sources. It's fast and scalable, making it a popular choice for analytics.

What is SQLAlchemy?

SQLAlchemy is a powerful and flexible SQL toolkit and Object-Relational Mapping (ORM) library for Python. It allows developers to interact with databases in a more Pythonic way, abstracting away many of the repetitive tasks that come with direct SQL manipulation.

Prerequisites

Before we dive in, ensure that you have the following:

Python installed on your machine.
Access to a Trino server.
The necessary libraries installed. You can install SQLAlchemy and the Trino connector via pip:

pip install sqlalchemy
pip install trino

Step-by-Step Guide to Insert Data into Trino

Step 1: Connect to Trino

To begin, you need to establish a connection to your Trino database using SQLAlchemy. Here’s how to do that:

from sqlalchemy import create_engine

# Replace with your Trino connection details
host = 'localhost'  # Trino host
port = '8080'       # Trino port
username = 'your_username'
catalog = 'your_catalog'  # e.g., 'hive'
schema = 'your_schema'    # e.g., 'default'

# Create connection URL
connection_url = f'trino://{username}@{host}:{port}/{catalog}/{schema}'
engine = create_engine(connection_url)

Step 2: Insert Data into a Table

Now that we have a connection to Trino, let’s move on to inserting some data. You can use SQLAlchemy's execute method to run an insert statement.

Example Table Creation

Let's assume we have a table named users with the following structure:

CREATE TABLE users (
    id INTEGER,
    name VARCHAR,
    age INTEGER
);

Inserting Data

Here’s how to insert data into the users table:

# Connect to the engine
with engine.connect() as connection:
    # Example insert statement
    insert_query = """
    INSERT INTO users (id, name, age) VALUES
    (1, 'Alice', 30),
    (2, 'Bob', 25);
    """
    
    # Execute the insert statement
    connection.execute(insert_query)
    print("Data inserted successfully!")

Step 3: Confirming the Inserted Data

To ensure that your data has been inserted correctly, you can query the table:

with engine.connect() as connection:
    result = connection.execute("SELECT * FROM users")
    
    for row in result:
        print(row)

Conclusion

Inserting data into a Trino database using SQLAlchemy is straightforward once you have your connection established. The combination of SQLAlchemy’s flexibility with Trino’s powerful querying capabilities allows you to manage and manipulate your data effectively.