SQLAlchemy is a popular Python programming SQL toolkit and Object Relational Mapper that gives application developers the full power and flexibility of SQL in a Pythonic way.
SQLAlchemy ORM or object-relational mapper is a component that provides an abstraction layer over the SQL database which makes it easy to interact with as compared to the traditional method.
Whereas SQLAlchemy Core is a component that is responsible for query building and SQL execution. It is fairly a low-level component that provides a set of tools for working with SQL directly.
Expected Prerequisites
To understand the article on SQLAlchemy Core conjunctions, the following prerequisites would be helpful:
- Familiarity with Python programming language
- Understanding of SQL syntax and relational databases
- Basic understanding of SQLAlchemy ORM and SQLAlchemy Core
Overall, a good grasp of SQL and Python programming is recommended to fully understand the concepts covered in the article.
What are SQLAlchemy Core Conjunctions?
In language, conjunctions refer to the terms used to connect or combine two or more clauses, the same way in SQL conjunctions are used to combine multiple conditions in a WHERE clause using logical operators such as AND, OR, and NOT. By using conjunction developers can create complex queries that can perform required filtration to retrieve data of interest from the database.
Requirements:
To follow up, you will need to install the SQLAlchemy Python packages:
$ pip install sqlalchemy
Once you have these dependencies installed, you can run the code in an IDLE of your choice.
SQLAlchemy Conjunctions
Step1: SetUp a Database
To keep this demo simple, we are using the SQLite database to avoid the hassle of connecting the database. Now to configure an SQLite database in Python we will use the SQLAlchemy create_engine function.
from sqlalchemy import create_engine
# Create a SQLite engine
engine = create_engine('sqlite:///mydatabase.db', echo=True)
Step2: Insert Some Data into the Database
Now to perform conjunction operation on the database we need some data and before we insert some data we need to create a Schema/Table. We will use the utilities from the SQLAlchemy library to create a Table name ‘students’ with some columns as mentioned in the script below.
from sqlalchemy import Table, Column, Integer, String, MetaData
#Define a metadata object
metadata = MetaData()
# Define a table
students = Table('students', metadata,
Column('id', Integer, primary_key=True),
Column('name', String),
Column('age', Integer),
Column('gender', String)
)
# Create the table if it does not exist
metadata.create_all(engine)
The table is created but still, we haven’t inserted any data. So let’s insert some data into the ‘users’ table by using the SQLite engine.
# Insert some data into the students table
conn = engine.connect()
conn.execute(students.insert(), [
{'name': 'Alice', 'age': 25, 'gender': 'Female'},
{'name': 'John', 'age': 30, 'gender': 'Male'},
{'name': 'Charlie', 'age': 35, 'gender': 'Male'},
{'name': 'Jane', 'age': 40, 'gender': 'Male'}
])
The overall database creation and data insertion code is as follows:
Python3
from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData, or_, and_, not_
metadata = MetaData()
students = Table('students', metadata,
Column('id', Integer, primary_key=True),
Column('name', String),
Column('age', Integer),
Column('gender', String)
)
metadata.create_all(engine)
conn = engine.connect()
conn.execute(students.insert(), [
{'name': 'Alice', 'age': 25, 'gender': 'Female'},
{'name': 'John', 'age': 30, 'gender': 'Male'},
{'name': 'Charlie', 'age': 35, 'gender': 'Male'},
{'name': 'Jane', 'age': 40, 'gender': 'Male'}
])
|
Output:
Once you run the above code it will create a mydatabase.db file where the source code is located is shown in the output,
mydatabase.bd database created
Students table created
Step3: Using the Conjunctions
In SQLAlchemy Core the and_(), or_() and not_() functions provided by the theSQLAlchemy library can be used in Python to add conjunction while query building. These functions take one or more conditions as arguments and combine them using the AND and OR operators, respectively.
Let’s consider we need to filter all records from a table where the “name” column is either “John” or “Jane”. This can be achieved using the or_ function in Python as follows:
Python3
query = students.select().where(
or_(students.c.name == 'John', students.c.name == 'Jane'))
result = conn.execute(query)
for row in result:
print(row)
|
In this example, we used the or_() function to combine two conditions. The equivalent SQL query will look something like this:
SELECT students.id, students.name FROM students WHERE students.name = 'John' OR students.name = 'Jane';
Output:
Output and SQL query built by SQLAlchemy is been highlighted.
2023-02-27 18:45:20,379 INFO sqlalchemy.engine.Engine [generated in 0.00092s] ('John', 'Jane')
(2, 'John', 30, 'Male')
(4, 'Jane', 40, 'Male')
SQL query of SQLAlchemy
Step4: Using the Advance Conjunctions on the Data
Advance conjunctions refer to conditions when we build complex queries which cannot be done just by using a single conjunction function so we can use a combination of these conjunctions and_(), or_() and not_() which is demonstrated by an example to perform the required filtering on the data.
Let’s say we want to write a query to get all students who are either over 30 years old or whose name is Alice but are not male. We can write this query using the and_, or_ and not_function in Python as follows:
Python3
query = students.select().where(
and_(
or_(
students.c.age >= 30,
students.c.name == 'Alice'
),
not_(students.c.gender == 'Male')
)
)
result = conn.execute(query)
for row in result:
print(row)
|
In this example, we used advanced conjunctions with the and_(), or_(), and not_() functions. The resulting SQL query will look something like this:
SELECT *
FROM students
WHERE (age >= 30 OR city = 'New York') AND gender != 'Male';
Output:
The output shows the record that matches the condition in the query executed.
(1, 'Alice', 25, 'Female')
SQL query of SQLAlchemy
Code:
For reference here is the entire source code:
Python3
from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData, or_, and_, not_
metadata = MetaData()
students = Table('students', metadata,
Column('id', Integer, primary_key=True),
Column('name', String),
Column('age', Integer),
Column('gender', String)
)
metadata.create_all(engine)
conn = engine.connect()
conn.execute(students.insert(), [
{'name': 'Alice', 'age': 25, 'gender': 'Female'},
{'name': 'John', 'age': 30, 'gender': 'Male'},
{'name': 'Charlie', 'age': 35, 'gender': 'Male'},
{'name': 'Jane', 'age': 40, 'gender': 'Male'}
])
query = students.select().where(or_(students.c.name == 'John', students.c.name == 'Jane'))
result = conn.execute(query)
for row in result:
print(row)
query = students.select().where(
and_(
or_(
students.c.age >= 30,
students.c.name == 'Alice'
),
not_(students.c.gender == 'Male')
)
)
result = conn.execute(query)
for row in result:
print(row)
|
Conclusion
In conclusion, SQLAlchemy Core makes the process of writing complex queries for SQL databases easy by allowing us to build complex SQL queries within Python as we saw in the demonstration in the article. With the feature to combine multiple conditions using conjunctions whether you’re working on a personal project or a large enterprise system, SQLAlchemy Core’s conjunctions make it easier to write and maintain complex SQL queries for the SQL databases.
Share your thoughts in the comments
Please Login to comment...