tldr: 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__/
Top comments (0)