SQLAlchemy Core is a SQL toolkit and Object-Relational Mapper (ORM) that provides a set of high-level APIs for communicating with relational databases. It is a part of the SQLAlchemy library.
Here’s a step-by-step guide:
Step 1: Install SQLAlchemy
Type the following command to install it if you have not already!
python3 -m pip install sqlalchemy
Step 2: Import SQLAlchemy
import sqlalchemy as db
As you can see, we imported sqlalchemy as db.
I have already created a Database called shows.db in my project folder, but if you don’t know how to create it, check out creating an SQLite database.
I have created a table called shows with some records in that Database. We will fetch those records using SQLAlchemy.
Step 3: Connect with the Database.
To connect with the Database, use the create_engine() function.
The engine allows us to create multiple database connections and manage them.
engine = db.create_engine('sqlite:///shows.db')
We can create a connection from the engine and run the database queries.
The next step is to use the connect() function to connect with the Database.
connection = engine.connect()
Step 4: Retrieve the metadata
We can retrieve the metadata about our Database with DB.metadata. This metadata will hold all the information about our table.
metadata = db.MetaData()
Step 5: Load the shows table
To load the SQL table, use the db.Table() function. It takes Shows table, Metadata, autoload=True, and autoload_with=engine.
shows = db.Table('Shows', metadata, autoload=True, autoload_with=engine)
Step 6: Prepare the Query
With our table loaded, let’s prepare a query to select all the records from the shows table. In regular SQL, selecting all the records is “SELECT * FROM shows”.
The SQLAlchemy Core query looks like this.
query = db.select([shows])
We prepared a query, but this did not do anything.
We need to execute this query. So first, we need to save the select query into the query variable.
Step 7: Execute the Query
To execute the query, use the execute() function. It returns the object that proxies the cursor object from the Python Database API.
result_proxy = connection.execute(query)
Now, we use the result_proxy object to retrieve the data.
Step 8: Fetch the data.
To fetch the data from the object, use the fetchall() method.
result_set = result_proxy.fetchall()
And now, print the result_set.
print(result_set)
Here is the complete code:
import sqlalchemy as db
engine = db.create_engine('sqlite:///shows.db')
connection = engine.connect()
metadata = db.MetaData()
shows = db.Table('Shows', metadata, autoload=True, autoload_with=engine)
query = db.select([shows])
result_proxy = connection.execute(query)
result_set = result_proxy.fetchall()
print(result_set)
Output
[('Stranger Things', 'Shawn Levy', 2016),
('Money Heist', 'Alex Rodrigo', 2017),
('Dark', 'Baran bo Odar', 2017),
('1992 Scam', 'Hansal Mehta', 2020)]
As you can see, we get all the records from the shows table. It returns the list of tuples containing our records.
We can also select the specific rows because it returns the list, and we can use the index to get the particular list data.
print(result_set[1])
print(result_set[:2])
Output
('Money Heist', 'Alex Rodrigo', 2017)
[('Stranger Things', 'Shawn Levy', 2016), ('Money Heist', 'Alex Rodrigo', 2017)]
That is it!