SQLAlchemy: The BEST SQL Database Library in Python

SQL Alchemy it sounds like it's some kind of weird library that combines SQL with questionable practices from the Middle Ages but that's not true it's a really cool package that combines the power of SQL with the flexibility of python it did get me thinking though what would my job be if I was born in the Middle Ages since I'm Dutch I would probably be a traiter you know go to the other side of the world get all sorts of weird exotic spices like notm or doctor which in the Middle Ages basically was somebody killing people for a living and giving the term organ player a whole different meaning what would your job be in the Middle Ages let me know in the comments or write it on a scroll and send it to me by pigeon anyway if you like learning about libraries like SQL Alchemy and other cool packages software design techniques all things SP check out my free newsletter join VI the link below SQL Alchemy is a really powerful and flexible library for working with databases in python as this allows you to map database rows in tables to objects in Python and this kind of tool is also called an object relational mapper or an omm SQL Alchemy has lots of tools for working with database and can work with various types of SQL databases such as sqlite MySQL and posgresql what I'll do today is show you the basics of how it works but then also show you a couple of more advanced things that you can do with SQL alen so start with this very first more functional approach of using SQL alemy so without relying on its class system to map tables to classes so if you're using this approach then you're simply using the builtin objects and functions of SQL Alchemy the first thing that you need to do is create a connection to the database and that involves a couple of lines of code in this case I'm using sqlite I'm using an inmemory database and not an actual database but you can replace this with any database connection string if you have a database running in the cloud for example so we first create the database engine and then we create a connection now of course normally this is not something you're going to do very often this will be hidden somewhere in your code and then you just work with the connection in all the other places but it's still important that you do that of course otherwise there's no way to interact with the database another thing that you need to do is create a metadata object this allows you to define a schema BAS the structure of your database and then the next thing that you do is create the actual schema of your database so in this case we have a single table called user and that table has three columns an ID which is an integer it's also a primary key and we have a username which is a string and an email address which is also a string that's it single table if you want to create more tables you simply add more of these table objects and then specify the columns that you need then once you have this table object you can write functions that interact with it for example here I have an insert user function that gets a username and an email and that uses the insert function insert method on the table and provides values the username and email address and then executes that particular query so you construct a query and then you execute it using the connection that we defined right here similarly you can also have a select user so we have a username that we pass as an argument and we return a result just like the insert operation we create the query which is a select query and we provide a condition which is that the username in the table is the same as the username that we passed as an argument we retrieve the result by execute in the query and then we fetch one of the results and that's going to give us the user and then in the main function we call create all we Supply the engines so this is going to create the tables that are necessary and then we can call the functions that we Define so insert user select user so when I run this then this is what we get we get one user that's been inserted with ID one and this is what's being shown when I print that particular user you can also tell SQL Alchemy to print a bit more details about what it's actually doing by setting Echo to true when you create the engine like so and now when I run this code again you see that we get more details so the first thing that it does creates a table which has an ID integer username and an email and ID is a primary key and then you can also see what for example the insert operation looks like or what the select operation looks like so this is really helpful for debuging so you can then analyze the queries and see whether you're really querying the database in the way that you want to now this is a very simple basic way of using SQL alamy but this will typically not be how you use it because SQL Alchemy has very powerful mechanisms to tie into Python's objectoriented programming to allow for a lot more flexibility and so here you see another version of this exact same example we have a single user table but now I'm using an object oriented approach so the way that this works is that well we still need to create the database engine because that's underlying everything but then we create a session using the session maker function that binds the database to the session and then we create a base and this is the base class that you will use to define actual tables and in the main function you see that base has metadata so that's the object that manages the schema for us and there we pass the database in order to create the actual schema so we use the base object but now that we have this we can Define classes that inherit from base so here for example we have a user class that inherits from base we Supply table name with a d instance variable called so the table is called users and then we use a data class classes like mechanism of defining the column so instead of having to call the column functions we can simply use the types which is a really neat feature of SE Alchemy and in order to specify the types of the columns we use the mapped type so I use mapped integer username and email are mapped strings and then you can call optionally the mapped column function to supply extra information about what kind of column this is and in this case the ID column should be a primary key so that's what we pass here but you can for example also indicate that this is an index and that it should be unique so mapped column basically allows you to specify any type of thing you would normally specify in SQL when you define what a column is supposed to be like but the nice thing is is now that we have classes for each table we can add methods to it for example here I've defined the repper method which gives us a developer friendly representation of the object in this case that's user with an ID username and email address and if you wanted to you can add more methods to the user table here that work with this particular data that's part of each Pro and the way that you work with data now is very simple so we can simply create an instance of the user class that we just defined here and then we can use a session to actually interact with the database so we use the session class that's been generated by session maker and then we create a session instance that we can then use so in this case we add the user to to the database and then we commit that and of course here I'm just adding a single user but you can add as many users as you want and then commit that result and once you have that there's also easy mechanisms to query the database for example here I'm querying the user table and I simply want to get all users and then that's going to print all the users that are currently in the database so when I run this particular example we see that we now get the single user that we just added but let's say we want to add another user let's not call that user Aron but something else like so and now we can also add the second user so onun this again and now you see we get our to users as expected so it's very very simple to use and what I like in particular about this is that this allows me to abstract away from very database specific things like SQL queries which are sometimes a bit hard to read and this really simplifies things a lot so this covers the object part of omm how about relationships here I have another version of the user table but I've added a bit of complication here so we don't just have a user name and an email address we now have next to the ID we have an off object and we have a posts object so user has a user off what that is I'll show you in a minute and it also has posts and as you can see a major difference here is that off is not a mapped column but it's a relationship and this is a relationship between user and user off which is defined below which is also a table in the database and this is what we call a onetoone relationship one user has exactly one user off there's also post which is a m list of user post so that's a one to many relationship a user can have many posts and then user off contains information related to specific authentication method in this case that's a hashed password now this is of course a very basic authentication example and you wouldn't typically develop all this yourself from scratch especially if you're using for example something like Fast API it's going to have already things like authentication or o of flows implemented so you can use those as a starting point but of course you still need to store authentication data in the database now next to authentication another thing you might want to do if you're building more complex product especially for businesses is to have some sort of authorization in other words what is a particular user allowed to do so authentication is identifying who it is authorization is determining what you're allowed to do authorization by the way is complete pain to build yourself if you can avoid doing that and use an outof the-box solution like alsoo the sponsor of today's video oo makes it easy for developers to build fine grain custom roles and per missions into your applications it uses a special language for that called Polar Polar has outof the Box primitive sport Access Control mechanisms like rback reback and aback along with the flexibility to extend those permissions as your applications evolve it's really nice to have authorization taken care of for you because access control is one of the more common things that you can see in the OS top 10 risk related to security incidents also allows you to treat your permissions code exactly like the rest of your application including logging testing auditing tools for compliance you get comprehensive logs and metrics and real-time tracking of Access Control decisions and activities you can access oo Cloud via restful API and there as de case available as well including for python for example here in my authentication example that I showed you before you can simply import alsoo from the ooc cloud package after you've installed that then you create the oo object you pass the URL and you provide your API key which I haven't done in this particular example but when you get an API key you just put it in here you read it from a end file and then you can use the also object to provide information for example here a user with ID Bob has a particular role in this case the owner role and then once you have that set up so this you can link for example with your database and then in your application code it's really easy to authorize users for particular actions for example here I'm checking that Bob is authorized to write Aron codes if Bob is then I can create users and do whatever I want to do otherwise in this case I print a message but of course you can also if this is part of an API for example raise an HTTP error that the user doesn't have the authority to do this get started for free with ooc Cloud today using the link below I've also put in the description of this video Let's Take a closer look at some of these relationships so we have the relationship between user and user off which is a onetoone relationship and you can see that because we indicated that this doesn't use a list so it's really a mapping to a single object also you see that there's a back populates option what back populates does that it syncs the relationships between the different tables in this case user and user off so you see that we have a back populates here but we also have it in user off where it's part of the user relationship so these types of relationships are typically two way and same for the Post you see that we have a back populated user here but on the post side so that's user post we also have a user that back populates to the post so again that syncs the relationship between these two tables there's also a couple of other things that you see here for example a user post has a user ID so that's an ID of a user the user that basically is the owner of this post and in SQL this type of relationship is represented using a foreign key so user ID refers to an ID of a user row and that's what we specify here as well and you can also Define other things here for example we always want user ID to have value so it's not notable it's also an index so you can find all the posts by a user of a given ID and so on so you can really use all of these uh standard SQL options that you are used to in SQL Alchemy and there's even more advanced things that you can do for example you can also specify a Cascade attribute that defines what should happen if for example the parent of an object is deleted so you can set that so that SQL Alchemy will automatically also delete the child object so for example if you delete a user you may also want to delete all the posts created by that user there's different options here of how you want that behavior to work exactly I really recommend you check out the documentation to see all of the options because it's quite extended but you do have those possibilities in SQL Alchemy if you look at the main function in this example you see that I use several session context managers to interact with the dat datase using these context managers is actually really helpful because then we don't forget to commit or roll back our changes here you see we create a user which has a username email and a password and what happens in the initializer of the user is that this then creates the user off object for us and then because user off is a class we're using an OM we can call methods on it so here we set the password and if you look at what this method actually does is that it creates a hash for you so that's one of the nice things about using orams that you can add Behavior to your tables which is normally not available in SQL obviously because that's not object oriented and same thing we can add other methods as well such as for example checking the password so if you look again at the main function you see that we get the user we print the user off object we print the posts belong to this user and then we can do a password check by calling the check password method and of course the first one is the correct password really bad password never use password as your password and if we check the wrong password then this is going to return false and finally you can also print the post by this user and because all of these things are Separate Tables we have a users table an off table and a post table you can also query user post directly and then simply filter it by a particular user so when I run this code then this is what we get so it prints the user prints the user off object it prints the post by the particular user the first password check is true second one is false and then we get to post by doing a simple SQL query there's a lot more that's possible with SQL Alchemy I really just scratch the surface in this video but I do hope that it gave you an idea of what you can do with SQL Alchemy but I'd like to hear from you do you use SQL Alchemy do you like using an omm like this or do you prefer to just write the SQL statements yourself let me know in the comments below if you enjoyed this video you might also like this video where I talk about the difference between using raw SQL statements versus a SQL query Builder versus an oam like SQL Alchemy thanks so much for watching and see you in the next one

Channel: ArjanCodes

Views: 47,305

Keywords: sql python, python sql, SQLAlchemy, python sqlalchemy, python library, python orm, sql alchemy, sqlalchemy tutorial, sqlalchemy python, python database, sqlmodel python, sqlalchemy python tutorial, sqlalchemy 2.0, python map object to database, sqlalchemy vscode, sqlalchemy pydantic, python programming, arjancodes python, arjancodes sqlalchemy, python coding, python coding practices, functional approach, object oriented programming in python, object oriented programming

Id: aAy-B6KPld8

Length: 16min 39sec (999 seconds)

Published: Fri Apr 05 2024