SQLAchemy ORM
The ORM example results are the same as the results obtained for the Core example. The difference between the two is the syntax. For the ORM, we need to define the table model. In the above code, we have created the Category class which also defines the different fields or columns present in the table. We use this class to query the database. Due to the use of class models, ORM feels more pythonic in SQLAlchemy. However, it is preferred when your program is supposed to define the database schema and architecture otherwise Core can prove to be much handy.
Python
# IMPORT REQUIRED LIBRARIES from sqlalchemy.orm import sessionmaker import sqlalchemy as db from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() # DEFINE THE ENGINE (CONNECTION OBJECT) engine = db.create_engine("mysql + pymysql: / / \ root:password@localhost / sakila") # CREATE THE TABLE MODEL TO USE IT FOR QUERYING class Category(Base): __tablename__ = 'category' category_id = db.Column( db.SmallInteger, primary_key = True , autoincrement = True ) name = db.Column(db.String( 25 )) last_update = db.Column(db.DateTime) # CREATE A SESSION OBJECT TO INITIATE QUERY IN DATABASE Session = sessionmaker(bind = engine) session = Session() # SELECT category_id, name FROM # category WHERE name IN # ("Action", "Horror", "Sci-Fi"); result = session.query(Category.category_id, Category.name) \ . filter ( Category.name.in_(( "Action" , "Horror" , "Sci-Fi" )) ) # VIEW THE ENTRIES IN THE RESULT for record in result: print ( "\n" , record.category_id, "-" , record.name) |
Output:
How to use the IN operator in SQLAlchemy in Python?
In this article, we will see how to use the IN operator using SQLAlchemy in Python.
We will cover 2 examples, one each for SQLAchemy Core and ORM layers. In both examples, we will count the number of records present in the category table within the sakila database. The sample data from the table looks like.
If you do not have sakila database and want to follow along with this article without installing it then use the below SQL script to create the required schema and category table along with the records.
CREATE DATABASE IF NOT EXISTS `sakila`; USE `sakila`; DROP TABLE IF EXISTS `category`; CREATE TABLE `category` ( `category_id` tinyint unsigned NOT NULL AUTO_INCREMENT, `name` varchar(25) NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`category_id`) ); INSERT INTO `category` VALUES (1, 'Action', '2006-02-14 23:16:27'), (2, 'Animation', '2006-02-14 23:16:27'), (3, 'Children', '2006-02-14 23:16:27'), (4, 'Classics', '2006-02-14 23:16:27'), (5, 'Comedy', '2006-02-14 23:16:27'), (6, 'Documentary', '2006-02-14 23:16:27'), (7, 'Drama', '2006-02-14 23:16:27'), (8, 'Family', '2006-02-14 23:16:27'), (9, 'Foreign', '2006-02-14 23:16:27'), (10, 'Games', '2006-02-14 23:16:27'), (11, 'Horror', '2006-02-14 23:16:27'), (12, 'Music', '2006-02-14 23:16:27'), (13, 'New', '2006-02-14 23:16:27'), (14, 'Sci-Fi', '2006-02-14 23:16:27'), (15, 'Sports', '2006-02-14 23:16:27'), (16, 'Travel', '2006-02-14 23:16:27');
The SQL query which we are looking at in the below two examples is:
SELECT category_id, name FROM category WHERE name IN (“Action”, “Horror”, “Sci-Fi”);