What is SQLAlchemy and how to connect it to database ?

Author: neptune | 15th-Apr-2022 | views: 281

In this tutorial you are going to know "What is SQLAlchemy ?" How you can use it's interface for performing database operations.

Before we start we should take a look into different ORM (Object Relational Mapper) written in Python.

  1. SQLAlchemy

  2. Peewee

  3. The Django ORM

  4. PonyORM

  5. SQLObject

  6. Tortoise ORM

In this tutorial we are going with SQLAlchemy and explore rest in another tutorial.

First of all we will understand what is SQLAlchemy ?

SQLAlchemy is a python toolkit and Object Relational Mapper in simple words it work as a communication channel between Python program and Databases. It translates the python classes/models into tables in relational databases. It support databases like SQLite, Postgresql, MySQL, Oracle, MS-SQL, Firebird, Sybase and Python DBAPI.

If we dig deeper then we will get to know that it has two main components Core and ORM. ORM is used to query from database while Core is used to insert data into database basically ORM is not suited to insert a bulk amount of data while Core can handle it easily. ORM is a kind of optional package which build on a core. Core performed many tasks like saving objects to database.

ORMs are basically not intended for high performance or bulk inserts So SQLAlchemy offers the Core implimentation rather using ORM for such operations.

We are going to connect SQLAlchemy with database before that let's understand the difference in SQLAlchemy and Django ORM which one is better and why ?

In most of the cases people prefer SQLAlchemy over Django ORM because SQLAlchemy are more adaptive to work with high complexity data structures. Along that Django ORM is powerfull in its own way, django ORM comes with features integrated with Django which are much more important than just a performance. Django doesn't allow to swap SQLAlchemy with ORM still you can do some hacks it is unofficial.

In future it is expected that django will provide both SQLAlchemy and ORM. So there is no clear winner it totaally depends on the type of project you are working.

How do we connect to database using SQLAlchemy.

For that we need to create a SQLAlchemy Engine which will interact to Database.

from sqlalchemy import create_engine

engine = create_engine('postgresql: //scott:[email protected]:5432/mydatabase')

After Engine creation we can directly interact with the database, or we can pass the session object to work with ORM. Session object contain the details of configuring database.

engine = create_engine('postgresql: //scott:[email protected]/mydatabase')

Now you can connect to your database and perform the operation on database.

Thanks for Reading!

Related Blogs
How to create a Database Connection Using SQL Developer ?
Author: neptune | 22nd-Feb-2022 | views: 357
#Database #SQL Developer
We are going to see how to setup a Database connection in SQL Developer. What information we required to setup DB connection...
View More