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 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
import sqlalchemy
from sqlalchemy import create_engine, MetaData,
Table, Column, Numeric,insert, Integer,
VARCHAR, update, text, delete
from sqlalchemy.engine import result

# establish connections
engine = create_engine(
    "database+dialect://username:password@hostname: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 table

Query to execute expressions in SQLAlchemy Core

In this article, we can discuss how to use execute function to executeSQLAlchemy core expressions and conventional SQL queries.

Example 1: 

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. The SQLAlchemy query shown in the below code selects all rows where the book price is greater than Rs. 100.

Python3

# 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 books query

Example 2:

The below query returns the book_price which is exactly equal divisible by 10

Python3

# write the SQL query inside the text() block
sql = text("SELECT * from BOOKS WHERE BOOKS.book_price/10 =10")

# Fetch all the records
result = engine.execute(sql).fetchall()

# View the records
for record in result:
    print("\n", record)

Output:

The Output of book_price which is exactly equal divisible by 10

Example 3:

The below SQL expression will insert additional records in the created table using SQLAlchemy core.

from sqlalchemy import insert
insert(table_name).values(column_name="value")

Get the books table from the Metadata object initialized while connecting to the database. Pass the insert 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 inserts additional records in the created table using SQLAlchemy core. 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']


from sqlalchemy import insert
# write the insert statement
stmt1 = insert(BOOKS).values(book_id=6,
                             book_price=400,
                             genre="fiction",
                             book_name="yoga is science")
stmt2 = insert(BOOKS).values(book_id=7,
                             book_price=800,
                             genre="non-fiction",
                             book_name="alchemy tutorials")
# execute
engine.execute(stmt1)
engine.execute(stmt2)

# 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 inserting additional records

Example 4:

Let us see another example related to updating 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 output of the update query