Sqlalchemy Core With Text SQL For Date Range

SQLAlchemy Core is a low-level SQL abstraction layer of SQLAlchemy, a popular Python Object Oriented Mapping(ORM) library. It provides a way to interact with relational databases wing python code, allowing developers to write SQL Queries in a more object-oriented manner.

SQLAlchemy is a python library that provides a set of tools for working with databases. It is designed to work with a wide variety of relational databases, including:

MySQL
PostgreSQL
SQLite
Oracle
Microsoft SQL Server

It uses a unified API to interact with different database systems, allowing you to switch between databases without having to change your code. It provides a powerful and flexible way to work with SQL databases in python.

To use SQLAlchemy Core with text SQL for the date range you can use the “text” function to write a SQL Query that includes a date range filter.

Stepwise implementation:

Step 1:

Define an SQLAlchemy “engine” object to connect to your database.

Python

from sqlalchemy import create_engine

engine = create_engine('postgresql://username:password@localhost/mydatabase')

Step 2:

Create an SQLAlchemy “connection” object using the engine.

Python

connection = engine.connect()

Step 3:

Write your text SQL query with placeholders for the data range.

Here we will retrieve all the records between a start date and an end date.

SELECT *
FROM mytable
WHERE date_column >= :start_date AND date_column <= :end_date

Step 4:

Use the “text()” function from SQLAlchemy Core to create a “text” object representing your SQL query.

Python

from sqlalchemy import text 

sql_query = text( 

    "SELECT * FROM mytable WHERE date_column >= :start_date AND date_column <= :end_date") 

Step 5:

Execute the query using the “execute()” method on the connection object, passing in a dictionary with the start and end dates as values for the placeholders in the SQL query.

Python

result = connection.execute( 

    sql_query, {'start_date': '2022-01-01', 'end_date': '2022-12-31'}) 

Step 6:

Process the result set as needed, hereby iterating through the rows and converting the result set to a pandas data frame.

Python

for row in result: 

    print(row) 

Output:

Terminal output

In this example, we were using the “text” function to write a SQL query that selects all rows from the “table_name” table where the “date_column” column is between “start_date” and “end_date”. We were then passing in the “start_date” and “end_date” parameters using the “execute” method of the SQLAlchemy “engine” object.

NOTE: When we are using SQL queries with SQLAlchemy Core, you need to be careful to properly sanitize any user input to prevent SQL injection attacks.

Article Tags :

Python

Python-SQLAlchemy