SQLAlchemy: Deciphering the Alchemy
Published: 2023-07-25 16:15:23

<em>tldr</em>: We will briefly summarize the set-up syntax of basic sqlalchemy 

Let’s break this down line by line:

`from sqlalchemy import Column, Integer, String`

sqlalchemy has packed all data-types into classes. We import them along with the `Column` class in the line above. These data-type classes inherit methods from the base class `TypeEngine` which comes with various methods and attributes. While we’re starting out with sqlalchemy, it’s not critical to understand what goes on beneath this layer, aside from knowing that even though it may appear cumbersome, it is only so because sqlalchemy helps bridge the gap between python and sql. 

More information about `TypeEngine` can be found here: https://pydoc.dev/sqlalchemy/latest/sqlalchemy.types.TypeEngine.html 

`from sqlalchemy.ext.declarative import declarative_base`

Here, we are importing the function titled `declarative_base` that, as we are starting out, will not have any parameters to accept and simply returns as class. Just understand that, similar to imports from the first line, the class that we get from declarative base will serve as the parent class to any object class we’d like to define in relation to a sql table. 

`Base = declarative_base()`

Next, we assign the return value of declarative base, which, again, is just a class, to the class named `Base`. It is important that we declare Base in upper-camel-case so that we know that it’s a class, otherwise it would just be an ordinary variable. 

`class Student(Base)`

In case any readers are unacquainted with **inheritance** in python: inheritance is when classes are defined in a hierarchical structure so that child classes inherit methods and attributes from their parent classes so that our code can be more dry (less repetitive). When we pass an argument into a class declaration, like above, the class name provided is taken as the parent class to the class being declared. In this example, the `Student` class is being declared as a child class of the `Base` class, which as we know (or, in fact, don’t know) is the magical class we received from the function we imported from sqlalchemy. 

Again, it suffices to understand that by declaring `Student` as a child class of `Base`, we’ve **equipped** `Student` with a lot of **bells and whistles** that we will need to integrate it into sql. That’s all the `Base` class exists to do. 	

More information about declarative base can be found here: 
https://docs.sqlalchemy.org/en/20/orm/mapping_api.html#sqlalchemy.orm.declarative_base

```
    __tablename__ = 'students'

    id = Column(Integer(), primary_key=True)
    name = Column(String())
```

These three lines should hopefully be easier to decipher. We declare the name of the table first, then declare its column names with variables we assign with `Column` classes (from our import) which we declare also with data type classes (also from our import). We pass an additional argument into the second line for `id` indicating that it will be the primary key of our table. 

```
if __name__ == '__main__':
    engine = create_engine('sqlite:///students.db')
    Base.metadata.create_all(engine)
```

Breaking down this conditional: we’ll start with the conditional line itself first which, in a very basic sense, ensures that its code block doesn’t run at the wrong time i.e. possibly when a script is imported. More information on this can be found here: https://stackoverflow.com/questions/419163/what-does-if-name-main-do

For our purposes, the engine class will largely remain a mystery as well. It will have to suffice to understand that it provides a source of database connectivity and behavior, perhaps akin to some kind of pipeline through which we can interact with our sql database with python. The engine object is instantiated with create_engine and is passed the desired address of our database file. More information about the engine class can be found here: https://docs.sqlalchemy.org/en/20/core/connections.html#sqlalchemy.engine.Engine

The third line is where the sql table is created. Remember: Base is the declarative base class we imported from sqlalchemy at the beginning, and to which we defined the child class ‘Student’. Metadata is an object attribute of the Base class where newly defined Table objects are stored. (https://stackoverflow.com/questions/70402667/how-to-use-create-all-for-sqlalchemy-orm-objects-across-files) When we invoke the create_all method on it with the engine object passed as a parameter, we are creating the sql table at the address stored in the engine object. 
	
Putting all this together: 

```
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Student(Base):
    __tablename__ = 'students'

    id = Column(Integer(), primary_key=True)
    name = Column(String())

if __name__ == '__main__':
    engine = create_engine('sqlite:///students.db')
    Base.metadata.create_all(engine)

```

The above code sets up sqlalchemy in a python file and connects a python class named Student with a sql table named students such that we can now create entries in the sql database through python. 

In a nutshell, there was a lot going on beneath the hood; so much so, that even what is visible to us in python was difficult to understand. 

Additional terms follow:

**Session:** a class that establishes conversation with the database and acts as a ‘holding zone’ for interactions with the database. It is initiated with an instance of the engine class. It will suffice to understand that we perform CRUD operations on the database through the session object. More information here: https://docs.sqlalchemy.org/en/20/orm/session_basics.html

**Alembic:** an extension of sqlalchemy that manages both executing changes to as well as version control of the database. A migration, in plain english, is the process of changing the schema of a database, that is, its structure, i.e. adding, deleting columns etc. When we write our sqlalchemy in our python files, we don’t normally write them to be executable on their own (not scripts), so we run Alembic commands in the terminal to execute our python files. In some way, Alembic acts as the middle man between our python code and the sql database. 

**__repr__:** the dunder method that returns the string representation of a class, i.e. what is printed to the console when an object is printed. By default the repr shows only the memory location of the object, but if we want to customize the repr format to show other attributes perhaps, we can overwrite the repr method. More information here: https://www.pythontutorial.net/python-oop/python-__repr__/