How to Execute Raw SQL in SQLAlchemy

Name

Rajiv Chandra

Published on 5/17/2023

(opens in a new tab) (opens in a new tab) (opens in a new tab) (opens in a new tab) (opens in a new tab) (opens in a new tab)

SQLAlchemy is a powerful Python library for working with relational databases. While it provides an excellent ORM (Object-Relational Mapping) toolkit for database application development, sometimes you need to execute raw SQL queries to perform advanced data processing and analysis tasks. In this tutorial, we will cover the basics of how to execute raw SQL queries in SQLAlchemy. We will explore how to create a database engine, use the text module, and update columns with sample code examples.

Need to visualize your data? You can connect your database to RATH and instantly get AI-powered data insights.

Learn the advanced Data Analysis features of RATH and what RATH can do for your data!

Connecting to a Database Engine

The first step in executing raw SQL queries is to connect to a database engine. A database engine is a software component that enables communication between a database server and client applications. With SQLAlchemy, this process is simple.

To connect to a SQLite database, use the following code:

from sqlalchemy import create_engine
 
engine = create_engine('sqlite:///example.db', echo=True)

In this example, we are creating a database engine for a SQLite database named example.db. The echo=True parameter is optional, and it enables logging of the SQL statements that are executed.

To connect to a PostgreSQL database, use the following code:

engine = create_engine('postgresql://username:password@hostname/database_name')

In this example, we are creating a database engine for a PostgreSQL database named database_name hosted on a server at hostname. The username and password parameters should be replaced with valid credentials for the database server.

Using the Text Module

Once you have connected to a database engine, you can use the text module in SQLAlchemy to execute raw SQL queries. The text module enables you to define the SQL query as a string and execute it using the execute() method on the database engine.

Here is an example of using the text module to insert a record into a table:

from sqlalchemy import text
 
insert_query = text("INSERT INTO users (name, email) VALUES (:name, :email)")
engine.execute(insert_query, username="John Doe", email="johndoe@example.com")

In this example, we are inserting a record into a table named users. The text() function takes a SQL query string as an argument, with placeholders for the values we want to insert. The execute() method on the database engine takes the text query and a set of parameters.

Here is an example of using the text module to query a database:

select_query = text("SELECT * FROM users WHERE name=:name")
result = engine.execute(select_query, name="John Doe")
 
for row in result:
    print(row)

In this example, we are querying the users table for all rows where the name column equals John Doe. The execute() method on the database engine takes the text query and a set of parameters. The result of the query is returned as an iterable object, which we can iterate over and print the rows.

Updating Columns

The text module can also be used to update columns in a table. Here is an example:

update_query = text("UPDATE users SET email=:new_email WHERE name=:name")
engine.execute(update_query, name="John Doe", new_email="newemail@example.com")

In this example, we are updating the email column in the users table for a row where the name column equals John Doe. The execute() method on the database engine takes the text query and a set of parameters.

Crosstab Table

A crosstab table is a special type of table that displays summary information for two or more variables. In SQLAlchemy, the text module can be used to create a crosstab table.

Here is an example:

create_query = text("""
CREATE TABLE sales AS
SELECT city, product, SUM(amount) as total_amount
FROM sales_data
GROUP BY city, product
""")
engine.execute(create_query)

In this example, we are creating a crosstab table named sales from a table named sales_data. The text() function takes a SQL query string as an argument, with the CREATE TABLE statement and a SELECT statement that aggregates the data by city and product.

FAQs

What is SQLAlchemy?

SQLAlchemy is a Python library for working with relational databases. It provides a powerful ORM toolkit for database application development, as well as tools for executing raw SQL queries.

How do you install SQLAlchemy?

SQLAlchemy can be installed using pip, the Python package manager. Open your terminal or command prompt and type the following command:

pip install sqlalchemy

What is a database engine?

A database engine is a software component that enables communication between a database server and client applications. With SQLAlchemy, you use a database engine to connect to a database and execute SQL queries.

What is a metadata object?

A metadata object in SQLAlchemy is like a blueprint for a database. It contains information about the tables, columns, and relationships in the database.

What is the SQL Expression Language?

The SQL Expression Language is a way to build SQL statements using Python code. It provides a way to express complex queries in a more natural, readable format.

How do you create a table in SQLAlchemy?

To create a table in SQLAlchemy, you use the create_table() function on a metadata object.

from sqlalchemy import Table, Column, Integer, String, ForeignKey
 
metadata = MetaData()
 
users = Table('users', metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String),
    Column('email', String),
)
 
metadata.create_all(engine)

In this example, we are creating a table named users with three columns: id, name, and email. The create_all() method on the metadata object takes the database engine as an argument, and creates the table in the database.

What is the syntax for creating an engine for PostgreSQL?

To create an engine for a PostgreSQL database, use the following syntax:

engine = create_engine('postgresql://username:password@hostname/database_name')

Replace username and password with valid credentials for the database server, hostname with the server name, and database_name with the name of the database.

How do you use the Text module in SQLAlchemy?

To use the text module in SQLAlchemy, use the text() function to define a SQL query as a string, and then execute it using the execute() method on a database engine.

from sqlalchemy import text
 
query = text("SELECT * FROM users WHERE id=:user_id")
result = engine.execute(query, user_id=1)

In this example, we are using the text module to query the users table for the row with an id value of 1.

What is the execute method in SQLAlchemy?

The execute() method in SQLAlchemy is used to execute a SQL query on a database engine. It takes a text query and a set of parameters as arguments.

result = engine.execute("SELECT * FROM users")

In this example, we are executing a text query to select all rows from the users table.

What is a crosstab table?

A crosstab table is a special type of table that displays summary information for two or more variables. In SQLAlchemy, the text module can be used to create a crosstab table.

How do you update a column based on a filter of another column in SQLAlchemy?

To update a column based on a filter of another column in SQLAlchemy, use the text() function to define a SQL update query as a string, and then execute it using the execute() method on a database engine.

query = text("UPDATE users SET email=:new_email WHERE name=:name")
engine.execute(query, name="John Doe", new_email="newemail@example.com")

In this example, we are updating the email column in the users table for the row where the name column equals John Doe.

What is the difference between a Keyword, NPL, and LSI type keyword?

A keyword is a word or phrase that represents a particular concept or topic. In natural language processing (NLP), keywords are used to identify important words in textual data. In latent semantic indexing (LSI), keywords are used to identify the underlying topics in a document.

How do you select a page slug (path)?

A page slug, or path, is the portion of a URL that identifies a specific page on a website. In SQLAlchemy, you can select a page slug using a text query that filters the url column of a pages table.

query = text("SELECT slug FROM pages WHERE url=:url")
result = engine.execute(query, url="/blog/post-1.html")
 
slug = result.fetchone()['slug']

In this example, we are selecting the slug column of the row in the pages table where the url column equals /blog/post-1.html.