SQLAlchemy library for python
SQLAlchemy library for python
Introduction:
What is SQLAlchemy?
SQLAlchemy is a popular SQL toolkit and Object Relational Mapper.
It is written in Python and gives full power and flexibility of SQL to an application developer.
It is open-source and cross-platform software released under MIT license.
SQLAlchemy is famous for its object-relational mapper (ORM), using which classes can be mapped to the database, thereby allowing the object model and database schema to develop in a cleanly decoupled way from the beginning.
It is one type of ORM library, AKA an Object-Relational Mapping library, which provides an interface for using object-oriented programming to interact with a database.
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.
Note on ORMs: are they a "best practice"?
Using an ORM to interact with your database is simply one of many valid approaches for how you can add additional layers of abstraction to your web application to interact with a database more easily.
There are other kinds of query builder libraries you can use that are between talking to a database directly (with a database driver library like pyscopg2), and using an ORM.
An ORM is considered to be the highest level of abstraction you can add to a web application for database management.
Query Builder libraries are somewhere in the middle.
There are many mixed opinions about whether ORMs are a best practice approach in all cases, such as this opinion on "Why you should avoid ORMs".
Thankfully, SQLAlchemy happens to offer multiple levels of abstraction you can prefer, between the database driver and the ORM, so you can customize the development of your web application to your own liking.
Why use SQLAlchemy over writing raw SQL?
- Work entirely in python don't write raw SQL anymore.
- Avoid making an error in SQL syntax
-- more rapid web development.
- We can forget about the database system we're using
-- Switch between SQLite for development and Postgres for production.
- Features function-based query construction: allows SQL clauses to be built via Python functions and expressions.
- Avoid writing raw SQL. 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.
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.
Prerequisites :
Before you start proceeding to learn this library, we assume you have a good understanding of the Python programming language.
A basic understanding of relational databases, DB-API.
SQLAlchemy's Layers of Abstraction
SQLAlchemy is broken down into various layers of abstraction.
Let's go through each layer in depth.
Definition:
- Without SQLAlchemy, we'd only use a DB-API to establish connections and execute SQL statements.
- Simple, but not scalable as complexity grows.
- SQLAlchemy offers several layers of abstraction and convenient tools for interacting with a database.
SQLAlchemy vs psycopg2:
1- SQLAlchemy generates SQL statements.
2- Psycopg2 directly sends SQL statements to the database.
3- SQLAlchemy depends on psycopg2 or other database drivers to communicate with the database, under the hood.
SQLALchemy lets you traverse through all 3 layers of abstraction to interact with your database:
- Can stay on the ORM level.
- Can dive into database operations to run customized SQL code specific to the database, on the Expressions level.
- Can write raw SQL to execute, when needed, on the Engine level.
Can more simply use psycopg2 in this case
Good Design Practice (Opinion):
- Keep your code Pythonic. Work in classes and objects as much as possible.
- Makes switching to a different backend easy in the future.
- Avoid writing raw SQL until absolutely necessary.
SQLAlchemy abstraction layers
1- DBAPI
2- The Dialect
3- The Connection Pool
4- The Engine
5- SQL Expressions
6- SQLAlchemy ORM (optional)
SQLAIchemy layers
The Dialect layer:
In SQLAIchemy the dialect layer!
- Allows us to forget about the database system we're using
- Switch between SQLite for development and Postgres for production.
The Connection Pool layer:
Because SQLALchemy has a connection pool, No longer do you have to open
and close your own connections manually using the DB-API alone.
With a connection pool, the opening and closing of connections and which connection you are using you're executing statements within a session are completely abstracted away from you.
As a result of having a connection pool:
1- Uses a connection pool to easily reuse existing connections.
2- Avoid opening and closing connections for every data change.
3- Handles dropped connections
4- Avoid doing very many small calls to the DB(very slow).
The Engine layer:
The engine is one of three main layers the SQLALchemy has,
In addition to the dialect and connection pool for how you may choose to interact with the database.
1- 1 of 3 main layers for how you may choose to interact with the database.
2- This is the lowest level layer of interacting with the database, and is much like using the DBAPI directly. Very similar to using psycopg2, managing a connection directly.
Moreover:
- The Engine in SQLAlchemy refers to both itself, the Dialect, and the Connection Pool, which all work together to interface with our database.
- A connection pool gets automatically created when we create an SQLAlchemy engine.
SQL Expressions:
- Instead of sending raw SQL (using the Engine), we can compose python objects to compose SQL expressions, instead.
- SQL Expressions still involves using and knowing SQL to interact with the database.
Why would we want to use SQL Expressions to compose python objects and expressions for writing SQL?
- We can avoid doing SQL query string composition.
SQLAlchemy ORM:
- Lets you compose SQL expressions by mapping python classes of objects to tables in the database
- Is the highest layer of abstraction in SQLALchemy.
- Wraps the SQL Expressions and Engine to work together to interact with the database.
Moreover, SQLAlchemy is split into two libraries:
- SQLAlchemy Core.
- SQLAlchemy ORM (Object Relational Mapping library). SQLALchemy ORM is offered as an optional library.
So you don't have to use the ORM in order to use the rest of SQLAlchemy.
- The ORM uses the Core library inside.
- The ORM lets you map from the database schema to the application's Python objects.
- The ORM persists objects into corresponding database tables.
SQLAlchemy summary
Comments
Post a Comment