My journey in Full stack has been a fun path so far. I have learned so much since I started. Recently I have been focused on Python and SQL. Learning this has been challenging, especially in such a short time. SQL stand for Structure Query Language. We use SQL to store and find data into databases. We can connect a table in SQL to a class in Python.
Classes and Tables
A Table can be created and given values threw a class in Python.
The above image is a table
The top image is a class in Python that represents a record of an instance of the class(object). The additional value and method are part of the Python object to help establish a relationship and a representation of the object.
SQLAlchemy
SQLAlchemy is a popular Python ORM(Object-Related Mapping). SQLAlchemy is one of the many libraries that we can use that works with Python. To start using sqlalchemy the pip environment must be installed, this environment supports sqlalchemy and allows you to use the library using Python. The next key part of using sqlalchemy are the requirements when writing code. It requires specific traits for the library to be able to create tables. It requires a __ tablename __ attribute, Column attribute (one or more), a Column for the primary key (a specific non-nullable value), and an inheritance from a declaritive_base object.
The declarative_base combines the metadata from a table with a group of methods that map the SQL database with Python class.
You are able true print instances of the class(the values of the columns from specific class) with a __ repr() __ method. This method returns the string associated with whatever information you provide it with.
Creating records:
To create a record in any database you have to create a new instance, an object, of the class. for example:
In the above image I created 3 different objects of the Doctor class. These instances are what give the columns a value.
Session:
A session is created to add and commit objects to the class and to the database. To create a session you need to import packages from
SQLAlchemy.
These packages create a session maker linked to the database, in the image above it would be engine = create_engine('sqlite:///db/client_database.db'). You use the .add() to create an instance and then use .commit() to commit that object to the database and fill in the columns to a specific row. You are able to modify these values if they are not the primary key.
The above image is an example on how to delete an entire row using the filter() method.You filter to locate the specific object you want to delete and finally commit the changes for the effect to take effect in the database and on the class itself.
Modify:
You can also modify a specific value. In the image above I modify the value for signed off to be the value of the user's id. I did this by filtering threw the database and finding the specific object and changing the value of a specific attribute.
If at any there is a problem or you simply want to delete a table you can do so using the .drop() method. An example would be
Client._table_drop()
If I were to drop my client table the code would look similar to the one above.
Relationships:
There are a few relationships that you can use to link tables and objects together. One-to-one, one-to-many, and many-to-many are a few examples of relationships when it comes to sql.
In the clients class, you can see an attribute called doctor_id This is a column in the client database linking the client class and the doctor class. The attribute clients in the Doctor class is what links the Doctor class to the client class. The relationship these 2 databases share is one-to-many. This means that there are many clients per one doctor. This helps she the information the user wants to see in the database but also gets a reference about another database where extra linked information will be. For my project, it was not necessary for the user to see the doctor for the client unless there would be an emergency, and in that case, they have the id of the doctor. This keeps 2 databases separated and each with important information but there is a relationship to make looking for the linked row easy to find.
The other relationships are exactly what they sound like. One-to-one links one object in one class to another object in another class. Many-to-many links many objects in a class to many objects in another class.
Migrations:
Migrations are used to update tables either by adding columns, deleting columns, or creating relationships in the database. Every time you make a new migration it creates a new version script. Migrations need a specific environment to be able to work. You can install all these in the pip environment.
Once you modify the code to fit what you want your database to include or exclude you create a new version by running alembic revision --autogenerate -m 'message'. The metadata is inspected in the Base and if it all works it will update the database and write a new version script. You can also modify the information manually and run alembic revision -m'message'.
There is a lot to alembic and migrations that I will focus on. I wanted to give a small description since migrations are a part or sqlalchemy and Python classes.
The top image is a script of my first migration for a project. As you can see there is a message on top and there is no upgrade or downgrade version.
The top image is an example of a migration I did to add the doctor class, also adding the doctor table to the database. you can notice there is a downgrade version since this script shows the upgrade that was made from the previous version. You can always downgrade versions and go in another direction if the database is not what you expected. You can also run another migration and upgrade if they are missing attributes on the current database version.
Conclusion:
There is much more to sqlalchemy than I have explained and given examples. I used Python and sqlalchemy to help me link databases and classes together. With Python I am also able to open files and modify them using Python and using sqlachemy to display and convert them into a database. Using sqlalchemy and Python to make databases was difficult because there are so many methods and rules to modify the database. I was able to understand how to use this to my advantage and was able to do everything in one place compared to using an outside database to view and modify my tables.
Top comments (0)