Entity Framework Core Example Using SQLAlchemy

Earlier I wrote an article, called Getting Started with Entity Framework Core and SQLite, where I walked through an example of using Entity Framework Core to perform CRUD operations against a SQLite Database on macOS using Visual Studio Code. The ultimate goal of that article is to update my ASP.NET Core Web API example to use a database, but I had this desire to first try the same thing with Python using SQLAlchemy.

Entity Framework Core and C#

Let me first show the C# EF Core example that peforms very simple CRUD operations for a SQLite database. I pulled this from my EF Core and SQLite article mentioned earlier.

using System.ComponentModel.DataAnnotations;

namespace DatabaseApplication {
    public class Reminder {
        
        public int Id { get; set;}
        
        public string Title { get; set;}
    }
}

using Microsoft.EntityFrameworkCore;

namespace DatabaseApplication {
    public class SqliteDbContext :  DbContext {
        public DbSet<Reminder> Reminders { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) {
            optionsBuilder.UseSqlite("Filename=./Reminders.sqlite");
        }
    }
}

using (var context = new SqliteDbContext()) {

    // Start with a clean database
    context.Database.EnsureDeleted();
    context.Database.EnsureCreated();

    // Add reminders.
    context.Reminders.Add(new Reminder {
        Title = "Meditate"
    });
    context.Reminders.Add(new Reminder {
        Title = "Eat a nutritious breakfast"
    });
    context.SaveChanges();

    // Fetch Reminders
    var reminders = context.Reminders.ToArray();
    foreach(var reminder in reminders) {
        Console.WriteLine($"{reminder.Title}");
    }

    // Remove a reminder
    context.Database.ExecuteSqlCommand(
        "DELETE FROM Reminders WHERE Title = {0}", "Meditate");

    // Fetch Reminders
    var reminderz = context.Reminders.ToArray();
    foreach(var reminder in reminderz) {
        Console.WriteLine($"{reminder.Title}");
    }
}

First, I use Entity Framework to create a new database and Reminders Table. It creates the database and table automagically based on metadata and conventions used in the application. I do not have to create the database and table manually. The following two commands delete and create the database to start off fresh. Note: I could have used EF data migrations and the EF Tools as well, which I will write about later.

// Start with a clean database
context.Database.EnsureDeleted();
context.Database.EnsureCreated();

Once I create the database and table, I use a combination of O/R Mapping operations and SQL Commands to perform CRUD operations against the table. I used O/R Mapping to add and fetch reminders,

// Add reminders.
context.Reminders.Add(new Reminder {
    Title = "Meditate"
});
context.Reminders.Add(new Reminder {
    Title = "Eat a nutritious breakfast"
});
context.SaveChanges();

// Fetch Reminders
var reminders = context.Reminders.ToArray();
foreach(var reminder in reminders) {
    Console.WriteLine($"{reminder.Title}");
}

and chose to use a SQL Command directly to delete a reminder.

// Remove a reminder
context.Database.ExecuteSqlCommand(
    "DELETE FROM Reminders WHERE Title = {0}", "Meditate");

You can do a lot more with EF Core and this is just a trivial example, but the experience was wonderful. It is easy to code and easy to use.

SQLAlchemy and Python

My goal is to do the same thing with SQLAlchemy and Python. I want SQLAlchemy to create the database and table based on metadata and conventions used in my Python script and perform O/R Mapping and SQL Commands just like with EF Core and C#. I didn't know SQLAlchemy. I only knew that it is a popular tool for O/R Mapping in Python. Armed with the documentation, here is similar functionality using SQLAlchemy.

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker
from sqlalchemy.sql import text

# Using SQLite
eng = create_engine('sqlite:///Reminders.sqlite', echo=True)
Base = declarative_base()
Base.metadata.bind = eng


class Reminder(Base):

    __tablename__ = "Reminders"

    Id = Column(Integer, primary_key=True)
    Title = Column(String, nullable=False)

    def __init__(self, title):
        self.Title = title


# Start with a clean database
Base.metadata.drop_all()
Base.metadata.create_all()

# Similar to SqliteDbContext in EF Core
Session = sessionmaker(bind=eng)
sess = Session()

# Add reminders
sess.add_all([
    Reminder('Meditate'),
    Reminder('Eat a nutritious breakfast')
])
sess.commit()

# Fetch reminders
reminders = sess.query(Reminder).all()
for reminder in reminders:
    print reminder.Title

# Remove a reminder
with eng.connect() as con:
    con.execute(text('DELETE FROM Reminders WHERE Title = :Title'), Title='Meditate')

# Fetch reminders
reminders = sess.query(Reminder).all()
for reminder in reminders:
    print reminder.Title

Just like with EF Core, I had no database. SQLAlchemy created the SQLite database and Reminders Table for me based on metadata and conventions from the Python script.

# Start with a clean database
Base.metadata.drop_all()
Base.metadata.create_all()

I then used O/R Mapping routines to add and fetch the reminders just like with EF Core - simple and elegant.

# Add reminders
sess.add_all([
    Reminder('Meditate'),
    Reminder('Eat a nutritious breakfast')
])
sess.commit()

# Fetch reminders
reminders = sess.query(Reminder).all()
for reminder in reminders:
    print reminder.Title

Sticking with my use of a SQL Command to delete a reminder, I did the same thing with SQLAlchemy.

# Remove a reminder
with eng.connect() as con:
    con.execute(text('DELETE FROM Reminders WHERE Title = :Title'), Title='Meditate')

I only have 1 hour of SQLAlchemy experience under my belt so far, but I found it very easy to use and very similar to EF Core. They both were enjoyable to use.

PyCharm Database Tools

I want to give a shoutout to the PyCharm Database Tools. I have a database tool on my MacBook Pro that works with SQLite, but it's nice to have this integrated with PyCharm, since that is what I used to write the Python code. I only needed the database tools to verify the database design as well as query the reminders, but as I work more with Python I will clearly be using more of PyCharm's database tools.

I mention this mainly because Visual Studio Code, which I used to write the EF Core example, does not have a database management tool. I am not knocking Visual Studio Code, since it is clearly an editor and not an IDE. I only mention it if you plan on using Visual Studio Code with EF Core on macOS.

Visual Studio Code also does not have a HTTP REST Client like PyCharm, and so I used PyCharm's REST Client to test my ASP.NET Core Web API, too! PyCharm is pretty useful even if you don't plan on using it for Python.

I am really looking forward to JetBrains' Rider for developing ASP.NET Core applications on macOS.

Conclusion

I really enjoyed using SQLAlchemy for the first time and comparing it to Entity Framework Core. It is great to see similiar functionality even if it was just for trivial examples. I have no doubt SQLAlchemy will more than meet my needs going forward with Python development.

Thanks for reading. I hope to see you on twitter!