SQLAlchemy

SQLAlchemy is split into two main parts.

• SQLAlchemy Core
• SQLAlchemy ORM

The Core contains APIs for writing raw SQL statements while the ORM is for interacting with the database with python code. The ORM itself is written on top of the Core API

The Engine

The Engine is the component that contains all connections to one database. It gives a factory to make a new connection to it and also has a holding space called as a connection pool. Ideally each database should have only one engine that helps the developer connect to it

            
                from sqlalchemy import create_engine

                engine = create_engine("mysql+pymysql://root:root@localhost/database", echo = True)
            
            

However this in itself does not connect to the database yet. It will connect only when we actually try to query something. This is called as Lazy initialization

Working with Transactions and DBAPI

The Engine provides us with a Connection object which resembles one connection to the database

We can connect to the database by creating a connection and querying it.

                
                    from sqlalchemy import text

                    connection = engine.connect()
                    result = connection.execute(text('select "hello world"'))
                    print(result.all())
                
            

Doing it this way just hits the query. We can also do the same in a context manager

                
                    with engine.connect() as connection:
                        result = connection.execute(text('select "hello world"'))
                        print(result.all())
                
            

Doing it this way creates a transaction and runs the query and since we don't have a connection.commit() at the end, the queries get rolled back. Including it will commit it