Best ORM SQL library for Python

SQL libraries are used with relational databases. In a relational database, the data is stored in different tables, each containing multiple records. These tables are connected using one or more relations.

Let check the best SQL libraries to work with in Python

Peewee #

Peewee is a simple and small ORM. It has few (but expressive) concepts, making it easy to learn and intuitive to use.

  • a small, expressive ORM
  • python 2.7+ and 3.4+ (developed with 3.6)
  • supports sqlite, mysql, postgresql and cockroachdb
  • tons of extensions

To install peewee

pip install peewee

Basic usage:

from peewee import *

db = SqliteDatabase('people.db')

class Person(Model):
    name = CharField()
    birthday = DateField()

    class Meta:
        database = db # This model uses the "people.db" database.

Django ORM #

The Django ORM is the interface used by Django to provide database access.

It’s based on the idea of models, an abstraction that makes it easier to manipulate data in Python.

The basics:

  • Each model is a Python class that subclasses django.db.models.Model.
  • Each attribute of the model represents a database field.
  • Django gives you an automatically-generated database-access API

To install:

pip install django

Records #

Records is a very simple, but powerful, library for making raw SQL queries to most relational databases.

Just write SQL. No bells, no whistles. This common task can be surprisingly difficult with the standard tools available. This library strives to make this workflow as simple as possible, while providing an elegant interface to work with your query results.

Database support includes RedShift, Postgres, MySQL, SQLite, Oracle, and MS-SQL (drivers not included).

To install Records:

pipenv install records[pandas]

Feature

  • Iterated rows are cached for future reference.
  • $DATABASE_URL environment variable support.
  • Convenience Database.get_table_names method.
  • Command-line records tool for exporting queries.
  • Safe parameterization: Database.query('life=:everything', everything=42).
  • Queries can be passed as strings or filenames, parameters supported.
  • Transactions: t = Database.transaction(); t.commit().
  • Bulk actions: Database.bulk_query() & Database.bulk_query_file().-

SQLAlchemy #

SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that gives application developers the full power and flexibility of SQL. It provides a full suite of well known enterprise-level persistence patterns, designed for efficient and high-performing database access, adapted into a simple and Pythonic domain language.

Unlike many database libraries, it not only provides an ORM layer, but also provides a common API, and you can write database-independent code without using SQL.

To install SQLAlchemy, run the command:

pip install records

Pugsql #

PugSQL is a simple Python interface for using parameterized SQL, in files.

To install:

pip install pugsql

basic usage:

import pugsql

# Create a module of database functions from a set of sql files on disk.
queries = pugsql.module('resources/sql')

# Point the module at your database.
queries.connect('sqlite:///foo.db')

# Invoke parameterized queries, receive dicts!
user = queries.find_user(user_id=42)

# -> { 'user_id': 42, 'username': 'mcfunley' }

In the example above, the query would be specified like this:

-- :name find_user :one
select * from users where user_id = :user_id

PonyORM #

Pony is an advanced object-relational mapper. The most interesting feature of Pony is its ability to write queries to the database using Python generator expressions and lambdas. Pony analyzes the abstract syntax tree of the expression and translates it into a SQL query.

Installation:

pip install pony

Tags:

Python

Recent Posts