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
from sqlalchemy.engine import result
from sqlalchemy import create_engine, MetaData,\
Table, Column, Numeric, Integer, VARCHAR
from sqlalchemy import text
engine = create_engine(
meta = MetaData(bind = engine)
MetaData.reflect(meta)
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)
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' )
engine.execute(statement1)
engine.execute(statement2)
engine.execute(statement3)
engine.execute(statement4)
engine.execute(statement5)
|
Output:
Sample table
Implementing 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
sql = text( 'SELECT * from BOOKS WHERE BOOKS.book_price > 100' )
results = engine.execute(sql)
result = engine.execute(sql).fetchall()
for record in result:
print ( "\n" , record)
|
Output:
The output of conventional SQL expression
Example 2: Executing insert query
The below SQL expression will insert additional records in the created table in a conventional SQL way.
Python3
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" },
)
statement = text(
)
for line in data:
engine.execute(statement, * * line)
sql = text( "SELECT * FROM BOOKS " )
results = engine.execute(sql)
for record in results:
print ( "\n" , record)
|
Output:
The output of the insert query
Example 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
BOOKS = meta.tables[ 'books' ]
stmt = BOOKS.update().where(BOOKS.c.genre = = 'non-fiction'
).values(genre = 'sci-fi' )
engine.execute(stmt)
sql = text( "SELECT * from BOOKS" )
result = engine.execute(sql).fetchall()
for record in result:
print ( "\n" , record)
|
Output:
The result of an update query
Example 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
BOOKS = meta.tables[ 'books' ]
dele = BOOKS.delete().where(BOOKS.c.genre = = "fiction" )
engine.execute(dele)
sql = text( "SELECT * from BOOKS" )
result = engine.execute(sql).fetchall()
for record in result:
print ( "\n" , record)
|
Output:
The output of delete query
Like Article
Suggest improvement
Share your thoughts in the comments
Please Login to comment...