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 CharFields. 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.