Best Python ORMs(SQLAlchemy,SQLAlchemy,Peewee,PonyORM)
An Object-Relational Mapper (ORM) is a tool that allows you to map your database tables to objects in your programming language, providing a simple interface for interacting with the database. There are several ORMs available for Python, and the best one for you will depend on your specific needs and preferences. Some popular options include:
SQLAlchemy
A powerful and flexible ORM that supports a wide range of databases and provides a lot of control over the queries that are generated
Here is an example of how you might use SQLAlchemy with FastAPI:
from fastapi import FastAPI
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
# Create a database engine to connect to a database
# You might need to adjust the database URL based on your database provider
engine = create_engine("postgresql://localhost/mydatabase")
# Declare a base for the SQLAlchemy ORM
Base = declarative_base()
# Define a model for a table in the database
class User(Base):
__tablename__ = "users"
id = Column(Integer, primary_key=True)
name = Column(String)
# Create all the tables in the database
Base.metadata.create_all(engine)
# Create a session to make queries to the database
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
# Create a FastAPI app
app = FastAPI()
# Define a route to create a new user in the database
@app.post("/users")
def create_user(name: str, db: SessionLocal):
# Create a new user object
user = User(name=name)
# Add the user to the session
db.add(user)
# Save the changes to the database
db.commit()
# Return the ID of the created user
return {"id": user.id}
This example demonstrates how to create a new user in a users
table in a database using a POST request. It uses SQLAlchemy's ORM to define the User
model and create a connection to the database using an engine
. It also uses FastAPI's dependency injection to pass a database Session
object to the route function, which allows you to make queries to the database within the route.
Note that this is just a simple example to demonstrate the basic idea. In a real application, you might want to handle database errors, validate user input, and add more functionality.
Django ORM
This ORM is built into the Django web framework and is optimized for use with Django. It is a good choice if you are using Django for your web application.
Here is an example of how you can use the Django ORM to perform database operations:
from django.db import models
# Create a model to represent a person
class Person(models.Model):
first_name = models.CharField(max_length=30)
last_name = models.CharField(max_length=30)
# Create a model to represent a car
class Car(models.Model):
make = models.CharField(max_length=30)
model = models.CharField(max_length=30)
owner = models.ForeignKey(Person, on_delete=models.CASCADE)
# Create a person
person = Person.objects.create(first_name='John', last_name='Doe')
# Create a car and assign the person as the owner
car = Car.objects.create(make='Toyota', model='Prius', owner=person)
# Query the database for all cars
cars = Car.objects.all()
# Query the database for all cars owned by a specific person
cars = Car.objects.filter(owner=person)
# Update the make of a car
car.make = 'Honda'
car.save()
# Delete a car
car.delete()
In this example, we have two models: Person
and Car
. The Person
model has two fields: first_name
and last_name
, both of which are CharField
s. The Car
model has three fields: make
, model
, and owner
. The owner
field is a ForeignKey
field that represents the relationship between a car and its owner, which is a Person
object.
To create a new object, you can use the create()
method on the model's manager. For example, to create a new person object, you can use Person.objects.create(first_name='John', last_name='Doe')
.
You can also use the all()
method on the model's manager to retrieve all objects of a particular model from the database. For example, Car.objects.all()
will retrieve all car objects from the database.
The filter()
method allows you to retrieve a subset of objects from the database based on certain criteria. In the example above, Car.objects.filter(owner=person)
will retrieve all cars in the database that are owned by the person
object.
To update an object, you can modify its fields and then call the save()
method. For example, to update the make of a car, you can do car.make = 'Honda'
and then car.save()
.
To delete an object, you can call the delete()
method. For example, car.delete()
will delete the car
object from the database.
Peewee ORM
Peewee is an object-relational mapper (ORM) for Python that allows you to interface with databases in a pythonic way. Here is an example of how to use Peewee to connect to a SQLite database, create a table, insert data into the table, and select data from the table:
import peewee
# Connect to the database
db = peewee.SqliteDatabase('my_database.db')
# Define a model for a table in the database
class Person(peewee.Model):
name = peewee.CharField()
age = peewee.IntegerField()
class Meta:
database = db # This model uses the "my_database.db" database
# Connect to the database and create the table if it doesn't exist
db.connect()
db.create_tables([Person])
# Create a Person object and save it to the database
person = Person(name='John', age=35)
person.save()
# Query the database for all persons in the Person table
persons = Person.select()
# Print out the persons
for person in persons:
print(f'Name: {person.name}, Age: {person.age}')
This example creates a Person
table in a SQLite database, inserts a single person into the table, and then retrieves all persons from the table.
For more information on Peewee, you can refer to the documentation: https://docs.peewee-orm.com/
PonyORM
A modern ORM that supports a wide range of databases and provides a simple and intuitive interface for working with your data
Here is a simple example of how to use PonyORM, a Python Object-Relational Mapping (ORM) library, to store and retrieve data from a database:
This defines a database with two tables, Person
and Book
, and creates the tables in a SQLite database file called database.sqlite
. The Person
table has three fields: name
, age
, and friends
. The friends
field is a Set
of Person
objects, which represents a many-to-many relationship between Person
objects. The Book
table has three fields: title
, author
, and borrower
. The borrower
field is an Optional
Person
object, which represents a one-to-many relationship between Person
objects and Book
objects.
To store data in the database, you can create instances of these classes and call the commit()
method:
from pony.orm import *
db = Database()
class Person(db.Entity):
name = Required(str)
age = Required(int)
friends = Set("Person")
class Book(db.Entity):
title = Required(str)
author = Required(str)
borrower = Optional(Person)
db.bind(provider='sqlite', filename='database.sqlite', create_db=True)
db.generate_mapping(create_tables=True)
with db_session:
alice = Person(name='Alice', age=30)
bob = Person(name='Bob', age=35)
charlie = Person(name='Charlie', age=40)
alice.friends.add(bob)
alice.friends.add(charlie)
book = Book(title='The Great Gatsby', author='F. Scott Fitzgerald', borrower=alice)
commit()
This will create three Person
objects and one Book
object and store them in the database. The friends
field of the alice
object will be set to a set containing bob
and charlie
, and the borrower
field of the book
object will be set to alice
.
To retrieve data from the database, you can use the select()
function and access the fields of the returned objects:
with db_session:
books = select(b for b in Book if b.borrower.name == 'Alice')
for book in books:
print(f'{book.title} borrowed by {book.borrower.name}')
This will retrieve all Book
objects whose borrower
is alice
and print the title and borrower's name for each book.
You can find more information about using PonyORM in the documentation.