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”);
SQLAlchemy Core
In the above example and with reference to the category table created earlier, we have filtered out the records that have one of the ‘Action’, ‘Horror’, or ‘Sci-Fi’ in the name field. We are selecting the category_id and name column from the category table. As we can see the example corresponds to SQLAlchemy Core, we have used the metadata object to get the metadata information about the table. Now, let us look at how we can do the same using SQLAlchemy ORM.
Python
# IMPORT THE REQUIRED LIBRARYimport sqlalchemy as db# DEFINE THE ENGINE (CONNECTION OBJECT)engine = db.create_engine("mysql+pymysql://\root:password@localhost/sakila")# CREATE THE METADATA OBJECT TO ACCESS THE TABLEmeta_data = db.MetaData(bind=engine)db.MetaData.reflect(meta_data)# GET THE `category` TABLE FROM THE METADATA OBJECTcategory_table = meta_data.tables['category']# SELECT category_id, name FROM category# WHERE name IN ("Action", "Horror", "Sci-Fi");query = db.select([ category_table.c.category_id, category_table.c.name]).where( category_table.c.name.in_([ "Action", "Horror", "Sci-Fi" ]))# FETCH ALL THE RECORDS IN THE RESPONSEresult = engine.execute(query).fetchall()# VIEW THE ENTRIES IN THE RESULTfor record in result: print("\n", record) |
Output:
SQAlchemy Core Example
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 LIBRARIESfrom sqlalchemy.orm import sessionmakerimport sqlalchemy as dbfrom sqlalchemy.ext.declarative import declarative_baseBase = 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 QUERYINGclass 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 DATABASESession = 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 RESULTfor record in result: print("\n", record.category_id, "-", record.name) |
Output:
SQLAlchemy ORM Example
