SQLAlchemy Core - SQL Expressions
Last Updated :
28 Feb, 2022
In this article, we are going to see how to write SQL Expressions using SQLAlchmey CORE using text() in SQLAlchemy against a PostgreSQL database in Python.
Creating table for demonstration
Import necessary functions from the SQLAlchemy package. Establish connection with the PostgreSQL database using create_engine() function as shown below. Create a table called books with columns book_id and book_price. Insert record into the tables using insert() and values() function as shown.
Python3
# import necessary packages
from sqlalchemy.engine import result
from sqlalchemy import create_engine, MetaData,\
Table, Column, Numeric, Integer, VARCHAR
from sqlalchemy import text
# establish connections
engine = create_engine(
"dialect+driver://username:password@host:port/database_name")
# initialize the Metadata Object
meta = MetaData(bind=engine)
MetaData.reflect(meta)
# create a table schema
books = Table(
'books', meta,
Column('book_id', Integer, primary_key=True),
Column('book_price', Numeric),
Column('genre', VARCHAR),
Column('book_name', VARCHAR)
)
meta.create_all(engine)
# insert records into the table
statement1 = books.insert().values(book_id=1,
book_price=12.2,
genre='fiction',
book_name='Old age')
statement2 = books.insert().values(book_id=2,
book_price=13.2,
genre='non-fiction',
book_name='Saturn rings')
statement3 = books.insert().values(book_id=3,
book_price=121.6,
genre='fiction',
book_name='Supernova')
statement4 = books.insert().values(book_id=4,
book_price=100,
genre='non-fiction',
book_name='History of the world')
statement5 = books.insert().values(book_id=5,
book_price=1112.2,
genre='fiction',
book_name='Sun city')
# execute the insert records statement
engine.execute(statement1)
engine.execute(statement2)
engine.execute(statement3)
engine.execute(statement4)
engine.execute(statement5)
Output:
Sample tableImplementing a query to execute SQL expressions in SQLAlchemy
SQLAlchemy provides a function called text(). We can write any conventional SQL query inside the text function enclosed by "". Now, passing this SQL query to execute function will convert this query to SQLAlchemy compatible format and returns the result.
from sqlalchemy import text
text("YOUR SQL QUERY")
Pass the SQL query to the execute() function and get all the results using fetchall() function. Use a for loop to iterate through the results.
Example 1: Executing basic query
The SQLAlchemy query shown in the below code selects all rows where the book price is greater than Rs. 100.
Python3
from sqlalchemy import text
# write the SQL query inside the text() block
sql = text('SELECT * from BOOKS WHERE BOOKS.book_price > 100')
results = engine.execute(sql)
# Fetch all the records
result = engine.execute(sql).fetchall()
# View the records
for record in result:
print("\n", record)
Output:
The output of conventional SQL expressionExample 2: Executing insert query
The below SQL expression will insert additional records in the created table in a conventional SQL way.
Python3
# define a tuple of dictionary of values to be inserted
data = ( { "book_id": 6, "book_price": 400,
"genre": "fiction",
"book_name": "yoga is science" },
{ "book_id": 7, "book_price": 800,
"genre": "non-fiction",
"book_name": "alchemy tutorials" },
)
# write the insert statement
statement = text("""INSERT INTO BOOKS\
(book_id, book_price, genre, book_name) \
VALUES(:book_id, :book_price, :genre, :book_name)""")
# insert the data one after other using
# execute statement by unpacking dictionary elements
for line in data:
engine.execute(statement, **line)
# write the SQL query to check
# whether the records are inserted
sql = text("SELECT * FROM BOOKS ")
results = engine.execute(sql)
# View the records
for record in results:
print("\n", record)
Output:
The output of the insert queryExample 3: Executing update query
Let us see another example related to update query.
Tablename.update().where(Tablename.c.column_name == 'value').values(column_name = 'value')
Get the books table from the Metadata object initialized while connecting to the database. Pass the delete query to the execute() function and get all the results using fetchall() function. Use a for loop to iterate through the results.
The SQLAlchemy query shown in the below code updates the genre "non-fiction" as "sci-fi" this will effectively update multiple rows at one go. Then, we can write a conventional SQL query and use fetchall() to print the results to check whether the table is updated properly.
Python3
# Get the `books` table from the Metadata object
BOOKS = meta.tables['books']
# update
stmt = BOOKS.update().where(BOOKS.c.genre == 'non-fiction'
).values(genre='sci-fi')
engine.execute(stmt)
# write the SQL query inside the
# text() block to fetch all records
sql = text("SELECT * from BOOKS")
# Fetch all the records
result = engine.execute(sql).fetchall()
# View the records
for record in result:
print("\n", record)
Output:
The result of an update queryExample 4: Executing delete query
Deleting table elements have a slightly different procedure than that of a conventional SQL query which is shown below
from sqlalchemy import delete
Tablename.delete().where(Tablename.c.column_name == value)
Get the books table from the Metadata object initialized while connecting to the database. Pass the delete query to the execute() function and get all the results using fetchall() function. Use a for loop to iterate through the results.
The SQLAlchemy query shown in the below code deletes the "fiction" genre this will effectively delete multiple rows at one go. Then, we can write a conventional SQL query and use fetchall() to print the results to check whether the table is updated properly.
Python3
# Get the `books` table from the Metadata object
BOOKS = meta.tables['books']
# delete
dele = BOOKS.delete().where(BOOKS.c.genre == "fiction")
engine.execute(dele)
# write the SQL query inside the
# text() block to fetch all records
sql = text("SELECT * from BOOKS")
# Fetch all the records
result = engine.execute(sql).fetchall()
# View the records
for record in result:
print("\n", record)
Output:
The output of delete query
Similar Reads
SQLAlchemy Core - Set Operations
SQLAlchemy Core is a powerful tool for working with databases in Python. One of the key features of SQLAlchemy Core is its support for set operations, which allow you to perform complex queries on your data. In this article, we will explore the basics of set operations in SQLAlchemy Core and provide
7 min read
SQLAlchemy Core - Executing Expression
In this article, we are going to see how to execute SQLAlchemy core expression using Python. Creating table for demonstration: Import necessary functions from the SQLAlchemy package. Establish connection with the PostgreSQL database using create_engine() function as shown below, create a table calle
4 min read
PL/SQL Common Table Expressions
PL/SQL Common Table Expressions (CTEs) make complex queries easier to write and understand by creating temporary result sets. We can use it multiple times in the same query. They help organize and simplify your code, especially for handling hierarchical data or recursive relationships. CTEs make you
10 min read
SQLAlchemy Core - Joins
SQLAlchemy Core is a Python toolkit that enables developers to create complex database applications. It provides several features, one of which is the ability to join tables. Joining tables allows developers to retrieve data from multiple tables simultaneously, which is useful when the data is rela
3 min read
SQLAlchemy Core - Selecting Rows
In this article, we are going to see how to write a query to get all rows based on certain conditions in SQLAlchemy against a PostgreSQL database in python.Creating table for demonstration:Import necessary functions from the SQLAlchemy package. Establish connection with the PostgreSQL database using
2 min read
MySQL Common Table Expressions
One of the most powerful features added in MySQL version 8.0 is common table expressions, which allow for the construction of temporary result sets within a single SQL query. In our daily life queries, we often use common table expressions and it makes our work easier. In this article, we will under
6 min read
SQLAlchemy Core - Functions
SQLAlchemy provides a rich set of functions that can be used in SQL expressions to perform various operations and calculations on the data. SQLAlchemy provides the Function API to work with the SQL functions in a more flexible manner. The Function API is used to construct SQL expressions representin
7 min read
SQLAlchemy ORM - Creating Session
In this article, we will see how to create a session for SQLAlchemy ORM queries. Before we begin, let us install the required dependencies using pip: pip install sqlalchemySince we are going to use MySQL in this post, we will also install a SQL connector for MySQL in Python. However, none of the cod
3 min read
SQL Server Common Table Expressions
SQL Server is a relational database management system (RDBMS) that is used to handle complex data and maintain it in of tabular manner. With the help of SQL Server, one can easily protect their data as it provides various security features. In this article, we are going to explore SQL server's CTE a
8 min read
SQLAlchemy db.session.query()
In SQLAlchemy, session.query() can be used as a filter in a query to specify criteria for which rows should be returned. This is done using the expression module and the filter method of the query object. The expression module allows you to create an expression that can be used in a query. This can
5 min read