What is the SQLAlchemy
- Python SQL Toolkit (most popularly used)
- End-to-end tools for working with relational databases without writing SQL
- Offers an ORM (Object Relational Mapping library), which provides an interface for using object oriented programming to interact with a database
- Maps tables and columns to class objects and attributes.
Writing SQLAlchemy vs raw SQL
In SQL
CREATE TABLE todos ( id INTEGER PRIMARY KEY, description VARCHAR NOT NULL, completed BOOLEAN NOT NULL DEFAULT false );
SELECt * FROM todos;
In SQLAlchemy ORM
class Todo(db.Model): id = db.Column(db.Integer, primary_key = True) description = db.Column(db.String(), nullable=False) completed = db.Column(db.Boolean, nullable=False, default=False)
Todo.query.all()
Why use SQLAlchemy over writing raw SQL?
- Work entirely in Python. Don’t write raw SQL anymore.
- Features function-based query constructions: allows SQL clauses to be built via Python functions and expressions.
- Avoid making errors in SQL syntax. It generates SQL and Python code for you to access tables, which leads to less database-related overhead in terms of the volume of code you need to write overall to interact with your models.
- more rapid web development
- We can forget about the database system we’re using
- Switch between SQLite for development, and Postgres for production
- Moreover, you can avoid sending SQL to the database on every call. The SQLAlchemy ORM library features automatic caching, caching collections and references between objects once initially loaded.
Other ORM libraries that exist across other languages include popular choices like javascript libraries Sequelize and Bookshelf.js for NodeJS applications, the ruby library ActiveRecord, which is used inside Ruby on Rails, and CakePHP for applications written on PHP, amongst many other such ORMs.
