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 |
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.
