In this article, we will see how to write a Conventional SQL query in SQLAlchemy using text() 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.
# import necessary packages import sqlalchemy
from sqlalchemy import create_engine, MetaData,
Table, Column, Numeric, Integer, VARCHAR from sqlalchemy.engine import result
# establish connections engine = create_engine(
# 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:
Example 1: Implementing a query to extract complete rows 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 the function the engine object which we created while connecting the database will interpret this and convert this query to SQLAlchemy compatible format and results in 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. 50.
from sqlalchemy import text
# write the SQL query inside the text() block sql = text( 'SELECT * from BOOKS WHERE BOOKS.book_price > 50' )
results = engine.execute(sql)
# View the records for record in results:
print ( "\n" , record)
|
Output:
Example 2: Inserting a record using raw SQL Query in SQLAlchemy
The SQLAlchemy query shown in the below code selects all rows where the book price is greater than Rs. 50.
from sqlalchemy import text
# 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: