Python has many libraries to connect to SQL databases. SQLAlchemy is a Python SQL toolkit that allows developers to access and manage SQL databases using Pythonic domain language. You can write a query in the form of a string or chain Python objects for similar queries. SQLAlchemy allows users to connect databases using Python language, run SQL queries using object-based programming, and streamline the workflow. It is intended to make Python programming easier.

We have two flavors of SQLAlchemy: SQLAlchemy Core and SQLAlchemy ORM. Whether you are using SQL Alchemy Core or SQL alchemy ORM, both will work for MySQL, PostgreSQL and SQLite, as well as any other supported relational database management systems. SQLAlchemy is an object relational mapper, or ORM. ORM (object relational mapping) is a programming technique for converting the data between incompatible type systems in the object oriented programming language, so it allows you to work with Python or your language of choice instead of SQL to query and manipulate the data in your database. There is other ORMs you can use in Python, but SQLAlchemy is one of the most popular ones.

The biggest difference between these two is that SQL Alchemy Core has a schema centric view which has focused around tables, keys, and SQL concepts. While SQL alchemy ORM encapsulates the schema with the business objects, and this makes your database interactions feel more like normal Python code. So “Core is like SQL and ORM is like Python”.

SQLAlchemy Core

With SQL core, you can use the SQL Expression Language, which is a mild abstraction of typical SQL language. This abstraction allows it to be consistent with the language across many relational databases. Statements of expression language will be translated into the corresponding raw SQL queries by SQL alchemy engine, so we can execute the various SQL queries with this help.

SQLAlchemy ORM

The ORM part of SQL alchemy, abstracts away the direct use of SQL in favor of object oriented approach in ORM, classes in Python are mapped to database tables, and instance of those classes are tied to the rows. Operations on the objects are transparently converted into SQL queries. Changes in the states of objects and rows are synchronously matched with each other. SQL alchemy enables expressing the database queries in terms of user defined classes and their defined relationships.

Which one is better? The one you are most comfortable with is the best one. I would say that if you are comfortable with SQL, go with Core.

I get version 1.4.39 when I run the code below in Jupyter Notebook under Anaconda Navigator in November 2024.

import sqlalchemy
sqlalchemy.__version__

Learn with Udemy

To learn how to work with databases in Python, you could consider taking a Udemy course. One that I am taking is called Python Database Course: SQLite, PostgreSQL, MySQL,SQLAlchemy.

Learn with Online Tutorials

For a tutorial on SQLAlchemy we can look at datacamp.com and read an article called SQLAlchemy Tutorial With Examples.