No More Raw SQL: SQLAlchemy, ORMs & asyncio

Speaker 1 [00:06]

My name is Aya Sayed. I am Egyptian and Canadian. I work at Bloomberg for five years now. We hope you enjoyed today's tutorial.

Speaker 2 [00:18]

Thank you, yeah. Hi, can you guys hear me as well at the back? Sweet. So I'm Riddham Patel. I work at Bloomberg too. It's been almost a year and a half. And without further ado, let's just get on with the tutorial. Sweet. So, the topic of our tutorial today is no more raw SQL, SQL Alchemy, ORMs, and asyncio, where we'll explore, we'll have a demo service using just like a simple raw SQL. We'll introduce SQL Alchemy to it, we'll add ORMs to it, and then we'll also convert our asynchronous service, synchronous service to an asynchronous manner using asyncio. So, first of all, what is SQL Alchemy? So it's an open source SQL toolkit for Python. It's a very popular package which allows you to interact with your database using an object-oriented paradigm in a Pythonic manner. And it consists of two parts, the core and the ORM. The ORM stands for Object Relational Mapper, which we'll talk about in some time. So too long to read. It removes raw SQL queries from your code and makes your code in a much more Pythonic manner. A bit of an overview about SQL alchemy. As I mentioned, there are two parts of it, the ORM and the core. The core has multiple components to it where you can define your own schemas and types in Python. It also has a SQL expression language where you can use the queries in a Pythonic manner. We'll talk about this in the tutorial and we'll give you examples. And there is a part called the engine, which is the entry point of SQL alchemy to the Python database. And the engine has two parts, the connection pooling, which helps you to manage multiple connections, multiple database connections with Python, and it also has a part called dialect. The dialect is just like, you know, the type of database. So there would be a dialect for Postgres SQL, there would be a dialect for MySQL, there would be a dialect for Microsoft SQL, et cetera. And then the dialect interacts with the DB API, which is the Python's third party API for talking to the database. So we would have multiple DB APIs even for the same dialect. And this is the driver that talks to the actual database. And these are the different components that the core has. And the ORM builds upon the core to introduce mapped classes. And we'll explore that in a tutorial, of course. So let's talk about the core. It's the foundational architecture as the database toolkit. It has all the components necessary to interact with your database, which allows you to connect your database, multiple of them if you were, and you can interact with your database using queries and you can get results to it and you can process those results however you want. And it also allows you to construct SQL statements in a programmatical manner. The URM builds upon this to introduce an object relational mapping functionality where the Python classes map to the database tables where the columns in your table are the classes of your sorry, the columns in your table are the class variables of your Python class. And the database table is represented by a Python class. And it also uses a SQL expression language from the core. And this allows you to use your SQL queries as user-defined objects. So here is a quick example. This is like a sample code for a select query from which joins like three tables. It shows you a bunch of data for a particular customer. This is how it looks before ORMs. And boom, this is how it looks with ORM. It's very simple and it's really understandable where you're trying to select a table called orders and you're trying to filter it using .where. You're going to learn about all of this in our workshop. And these are some databases that support. So SQLAlchemy supports all the major databases, including PostgreSQL, MySQL, and even Bloomberg's COMDB2. A few advantages are that after using SQLAlchemy, you no longer have raw and bulky SQL queries in your code. As a bonus, it also prevents SQL injection attacks because it uses bound parameters and prepared statements. And with SQL Alchemy, as this is an abstracted layer on top of your Python's DB API, you can easily migrate through different databases and different DB APIs. If you are not a fan of the ORM, you can still use the core without the ORM. But I see no reason why not to. And you can also have various loading strategies to optimize your performance. Therefore, life is easier, and you can also write tests and fixtures, which help you maintain your code much in a better way. And overall, life is easier. Without further ado, let's get our hands dirty and explore the workshop. So would you mind just following this link, tinyurl.com slash SQLAlchemy-workshop. Yeah, that's the link, by the way. So there's another link called sqlalchemy-tutorial that you guys can follow. So we have a document, extensive documentation here to follow along. And the link for that is sqlalchemy-tutorial.

Speaker 1 [06:58]

So this is the website for the tutorial, for all the instructions. So we're going to be covering most of the content on here, but we also have more detail written down for you if you want to read it later and understand in a bit more detail. So let's get started. So as we said, yeah, this is going to be a hands-on workshop. We're going to be using an example repo, which is hopefully you followed the link and you're able to open up the repo. it's a dummy like market place service it's a flask service yeah and we're gonna be modifying it throughout the tutorial to make it better we'll add SQL alchemy in the synchronous version of it and then in the asynchronous version of it using async IO yeah just quickly here we found this definition from Udacity, if that's how you say it, of the words Pythonic. So they said, Pythonic describes a coding style that leverages Python's unique features to write code that is readable and beautiful. So that's what SQL Alchemy is going to do for us today. The internet seems to be a bit slow, but we'll just give it a second. Are you connected? Yep, I am. Oh, okay. All right, so for this tutorial, we're gonna be using GitHub Codespaces um to you know standardize our work environments hopefully you've had a chance to install the docker desktop on your laptop we're going to be needing that today but for everything else don't worry about it we're going to be installing it as we go along so here you can also find if you opened up the link to the instructions so that's sql alchemy dash tutorial on tiny url You can also follow the example project link here. So the first thing we're going to do is just you're going to create your own fork of the repo. But to do that, you know, we have instructions here on how to do it. We want to use this template. I think it's a bit small. I'm just going to show you here. So once you visit the example repo, there is a button here, a green button that says use this template. so open up this menu and choose the first option create a new repository and then this part is important I'll just let it load it's very slow Okay, so we're just going to change the Wi-Fi seems to be very slow, so we're just going I'm gonna go on my data here. You're welcome to use this as well if the Wi-Fi is really slow. Just connect on AS, and then the password is sqlacry. Okay, we'll try this one more time. Okay, it doesn't seem to be working. Okay, maybe try with the Wi-Fi now. Let's go back and try with the Wi-Fi perhaps. It's too slow. Yeah. Okay. I'm assuming everyone else's internet is also as slow. But I'll just, in case, you know, some of you are able to get it to load, I'll just show the instructions on here. So from the example repo you're going to click on use templates create a new repository so this will create a copy of the example repo for you in your organization but the an important step here is to check the the check box include all branches we have multiple branches in the example repo and this is going to be helpful to basically at the end of each step we have a completed version of the code so if you don't get time to like fully write the codes you can just check out the branch of that step and then follow along from there so yeah check include all branches and then name your repo you know something like my marketplace or my market SVC and then once the new repo is created you can then go from the code button and I mean we recommend using code spaces here so um you know click create code space on main but of course the the wi-fi seems to be um not very responsive so maybe you just want to clone it locally um and just work locally here Okay. Okay. You want to try now? Hello. Good help. I just thought it's impossible. Maybe you can use my hotspot. Okay. Same password. The dumb's iPhone. Um, which one is it? The dumb's iPhone. Okay. Okay. Okay. Okay. Okay. Okay. Okay. Okay. Okay. Okay. Okay. Okay. Okay. Okay. Okay. Okay. Okay. Looks like we're just going to use the local version.

Speaker 2 [14:25]

No, it's working.

Speaker 1 [14:25]

It's working. There you go. We have a connection here, so we'll finally show you so yeah I clicked on create new repository include all branches, and then I'll name it something like my marketplace Again clicked on this and then click create new repository So that created a copy of the repo in my org and then now I'll create code space so you can see like all the branches here we're we're copied as well and then I'll create a code space okay we're gonna let that set up okay wasn't too bad Was anyone able to open the example repo in Codespaces? Yeah. Yeah? Okay. Great. I hope you're not. All right. So, yeah, before we start with adding SQLAlchemy, we're just going to walk through the files here. So, yeah, maybe I'll zoom in so you can see. And you can make this bigger. Okay. Hopefully. Green button. Sorry. Yeah, so as you would expect, we have a database, a Postgres database. You can see the composition of the database here through this initdb.sql file. So because this is a marketplace service, we have a number of tables. We have customers, their addresses. Customers can create orders. order has items and you know and a quantity associated with it and then there is a repository of items that clients or customers could order so this is the database setup and then we've also inserted some sample data here so that we can play around with the code without having to insert some data so that's the market DB folder and then in the market SVC folder this is our lightweight Python service that we're going to be modifying throughout this tutorial. So the entry point to this service is server.py. As you can see it's just a lightweight Flask app that responds to a number of endpoints like get customers, get a customer's order, the total price of an order, orders between days, and you can also add a new order for a customer. Just yeah a Flask app on port listening to traffic, HTTP traffic on port 9090. Now the main file we're going to be modifying today is dbaccessor.py. So as you can see here in the beginning, we're just using a PsychoPG2, which is a DB API that just allows you to interface with Postgres. So here we just have the database configuration, some like the username, the host, password, port, etc. And then here you can see in the codes we're just you know we establish a connection with the database we get a cursor we execute a query we fetch the results or just if it's an insert then we commit the results to the database and you can see here it's just you know raw sequel queries that we'd had to write out so this is our starting point and we're going to be modifying this codes throughout the workshop to add sequel alchemy and make this much more maintainable. One final file to note is the requirements.txt. This file contains the Python dependencies that we're going to be using. So far it's just Flask, PsychoPG2, and RAV4 just formatting, but we're going to be adding dependencies as we go along. And finally we're going to be running our services through Docker. Hopefully you've used Docker before, but if you haven't, you know, it's not a big deal. It's basically, you know, a tool that allows you to run your services in lightweight containers. They're loosely kind of isolated containers. Yeah, so here we have two containers, a Postgres container that we'll be running our database on and the market SVC container where our Python app is going to be running. If you if you want to know more detail about like the you know how this file is configured you can read that in the instructions but for now we don't need to worry about it. Finally we have this file just to make things easier to run for you. It's just a you know a shell script that allows you to run the docker service and then also interact with the flask app by just running curl commands all right so now we're hopefully familiar with the files we know what to expect so you can go ahead and first build your docker services so run docker space compose space build you can just copy it from here um so i will do that So you can see here, Docker is building the two containers that I've defined here in my compose.yaml file. We'll just let that build while we talk about the next step. All right, so now that we know what our service looks like, we can look at adding SQLAlchemy to this. So the first thing we'll do is add SQLAlchemy as a PIP dependency in our requirements.txt file that we saw. over here in requirements.txt, you're going to be adding SQL Acme here. OK, actually, my Docker build is done, so now I can run my services. Just so you can see how to interact with it. So you can copy this run command, or you can use the convenience shell script that we've provided for you. But for now, I'll copy this one. So I'm back in my code spaces and I'll run my services. Because this is the first time we're running it, it's pulling some images. All right. So because, you know, once you've added a requirements, sorry, a PIP dependency in your requirements.txt, you're going to need to build your Docker containers again just to install that extra dependency. And, of course, every time you add a new requirements, you're going to need to rebuild that container, the MarketSVC container, because that's where we've specified to install the PIP dependencies in the Docker container definition of this MarketSVC container. you can see my service is ready and running now so there are two containers running as we expect so we have the database one and then we also have our market as we see one which is now available on port 1990 as we have defined which means that I can now interact with my service so I'll use the convenience shell script here so I can for example say run customers and that should give me just the list of customers Yeah, if you got this far, go ahead and add SQL Alchemy, I'll do that quickly. And then rebuild my services, I'll stop this. and docker-compose down, and I'll rebuild. All right. OK, so how do we add SQL Acme? We're going to start by adding a new file. So in your market-svc folder, we're going to add a folder called db. And we'll add a file base.py. The first thing we'll do is connect to the database. To do that, we need to create the engine object. So the engine object allows you to interface with your database. So here, we create first a URL object where we define the, well, a number of things. First, the database dialect that we're using, which is Postgres SQL. And then the DB API that we're using, is a psycho PG2 and then you know the username password host database and ports that we're using to connect to the database so once you've created a URL you can pass that to the create engine function and that creates an engine instance the engine as we said manages the connection pool to the database we're going to use the flag echo true this will give us debug logs so SQL Alchemy will be printing out the exact SQL commands that it's running, and that will help us understand what it's doing under the hood. So once we've created our engine, let's talk about the connection object. So to establish a connection to the database, we want to create a connection object. Because we want to manage the lifetime of a connection, The way you choose it is establish a connection, run your queries, and then clean up your connection. Because you always want to kind of follow this pattern, SQLAlchemy exposes a context manager API for creating a connection. So that will allow you to not have to call connection.close explicitly at the end of your interaction with the database. So yeah, our code will look something like this. So to execute a query, we're going use the context manager API on engine. So engine.connect returns a connection instance that we named connection. And then we're going to execute our query. And then at the end of this context manager, connection.close is called, which cleans up the connection. Here we're using the text API to say that our query variable is just a string, we're going to start with that, and then later we're going to see how to use the SQLAlchemy expression language that we saw in the presentation. So you can go ahead and update your execute query function, import the relevant functions they need to use and you can now use it in your get customers function yeah so what does what does connection to execute to here the one that we see here so it returns a result object which is basically a cursor. So if you've used another database API, you're familiar with a cursor. So it's like an iterable of the resulting rows depending on your query. So the row is a SQLAlchemy object as well. It behaves a lot like a named tuple. So I can access properties on the thing that I query. using like the dot parameter because it's a name tuple and then finally because this is a flask app we're gonna need to turn this name tuple or like the set of name tuples that we've queried from the database into JSONifiable objects so in our server.py we're gonna after we call get customers which returns, you know, an iterable set of rows, we're going to need to turn them into dictionaries. So we can JSONify them to return them through our Flask app. So this will be our last step. We used list comprehension here to turn our name tuples into a dictionary. And then we're just from there we can just return the response. So I'll give you some time here. you've done that you should be able to try run.shell customers and you should see that you're able to establish a connection to through sql alchemy to the database and you're able to query customers the logs that you should be able to see because we've enabled logging will look something like this well it will look exactly like this so it will say begin select star from customers which is exactly the query that we told it to run here and then it will end with we didn't use any parameters so yeah and then it will end with a rollback which is basically like at the end of our query we clean up the connection so it's ready for the next use all right I'll give you some time here I'll also give myself some time here to do this stuff I will just – yeah, can you start walking around and see if you have questions? Thank you. Thank you. Thank you. Okay. I've just updated my executes query function to use SQLAlchemy. Has anyone gone this far? Yeah. Some people? Okay. can see here I can see in my logs my my query here let's run just what I expect

Speaker 2 [32:13]

No, not yet.

Speaker 1 [32:24]

So, you can...

Speaker 2 [32:24]

I'm just hoping it works on your machine.

Speaker 1 [32:34]

I think mine didn't work.

Speaker 2 [34:24]

two years more

Speaker 1 [34:37]

All right. Let's continue. So this was a very simple query because it didn't require us to, like, pass any parameters or anything. So let's look at a more involved query. So typically in a query, you want to be able to pass some parameters. Like, you want to be able to say select from table, you know, rows that match a certain criteria. So, for example, here, if we wanted to query a customer by ID, we're going to need to do something like this. So this here uses what we call parameter binding. So instead of passing the customer ID that we want to query directly as, you know, as part of the string, we use parameter binding. So the syntax that SQL Alchemy expects here for parameter binding is to use a colon followed by the variable name. And then you pass a dictionary of all of the keys and values that you're using for your parameters. The reason why this is very important, and you've probably come across this, you know, working with other databases as well, is that, you know, this protects your code against SQL injection attacks. If you were to just pass the variable value directly into your SQL query, that leaves your code vulnerable to SQL injection attacks, which, of course, we don't want to do. In this case here, we expect a single row, so we just pass a single dictionary with our keys and values. But if we wanted to do multiple rows, like, for example, if we wanted to insert multiple records in a database, you can use a list of dictionaries here instead. Okay. And next let's look at committing data. So far we've been looking at fetching data, which doesn't require us to necessarily commit something to a database, but if we wanted to insert into the database, then we need to commit the data. There are two styles of committing data. There is the commit-as-you-go style. So here we have established our connection to the database. executed a query which is you know an insert query and then we get a result like say the ID of the new record that we have installed and then we commit the data that we just inserted before we finally return the result and exit out of the context manager which closes the connection to the database so you can use it like this for example here we have the function add new order for customer. So here I'm inserting into orders, customer ID and order time, a fused parameter binding to pass the customer ID, and then the SQL function now to just record the time of the order creation. So you can see the parameter value is bound here. And yeah, the result that I get returned from here is the cursor, the row that resulted from this connection.execute, I want to fetch just one because I expect just one row to have been inserted. Now this gives me the name tuple, like the row object, which I can then access its properties using like the dot notation. So here I want to know the ID of the new order, so I just call dot ID and now I have my new order ID but we don't want to just insert an empty order we also want to add some order items so to do that okay first like the results from the first insertion if you look at the logs it will look something like this right like you can see it's inserted into orders the customer ID and order time you know and then the parameter bound to it is shown here, so these are the keys and values. But you'll notice here that we can see an explicit commit rather than the rollback that we saw earlier here. So that's because we called connection.commit after the insertion. Now if an exception were to happen, then a rollback instead will happen, so this data wouldn't be committed to the database. This style is called commit as you go. But there is another style, which we will talk about shortly after we see how to insert multiple rows. So here, you know, the first insertion was a single row in two orders, and then now we want to also insert a set of items. So again, I use my execute insert query function, but here instead of passing a single dictionary I use list comprehension to create a list of multiple dictionaries because I have multiple items to insert and I can do that in just a single call to execute query yeah so we passed the order ID item ID and quantity that the user would have passed us through the flask endpoints and yeah that's it Not really. So once you've updated this function, you can give a new order a try. Just before I give you time to do that, we'll talk about the other committing style called begin once. So here we've used a slightly different API to get the connection from the database called engine.begin. So this is ‑‑ so if you remember at the top here, we've used just engine.connect, which returns a connection object, but here in this other style, engine.begin is another context manager that at the end of the context manager, it calls connection.commit before it calls connection.close so that I don't have to call connection.commit explicitly. So this allows me to perhaps execute multiple queries and at the end just commit once. And yeah, this is why this method is called begin once. So give that a try as well. Yeah, we'll give you some time here to update your insert query as well. If you are stuck or if there is something that's unclear, you can check out the branch step2-sqlalchemy, and it will have the completed code for this step. So you can see what your code should look like by the end of this step.

Speaker 3 [41:59]

There's a question. Oh, sure. Where can I see my SQL Alchemy logs?

Speaker 2 [42:04]

Right, so there are two terminals running here in the code space. The first one is where we actually run the Docker image by using the shell script or by using docker-compose-run-p-1990-market-service. So the logs are generated. And in the other terminal, you just pass in the actual curl query using this convenience script that we've created. So, in the first terminal, you'll see the logs generated from SQL Alchemy. Is this visible or is it too small? So it's in the terminal where you run the actual service. Yeah, I'm deleting all of this and checking out. How do I do it? This one? They stashed the others. Why is it so slow? Why is it lagging so much?

Speaker 1 [44:42]

I'm going to switch on my thing.

Speaker 3 [44:59]

And there's another question. Somebody gets a 500 when he or she calls post API. I can show you post API add new order. And it gets a 500.

Speaker 2 [45:10]

I mean, we're just

Speaker 3 [45:11]

Return code, so something wrong with the internal server.

Speaker 2 [45:14]

Should we just say to switch to the branch? Do you want to say that? I mean, it's hard to say without seeing the whole log.

Speaker 1 [45:26]

it's probably like the is the container running like it's her market as we see container running did it say any

Speaker 2 [45:36]

Do you want to say to just switch to the branch?

Speaker 1 [45:44]

All right. If you haven't had a chance to complete all the codes, you can just check out this branch, so step-2-sqlacme, and continue from there. You can see I have done that, so I am now on this branch. And, yeah, I was able to run new order, which added a new order for me in the shell script just to make it easy again we've kind of hard-coded a customer id and some items so that's what it added in the new order and i can confirm that this has executed successfully because one i can see the the logs from my sql alchemy service or like my market svc service so you can see here this was my my two insert queries that i expected one is an insert into orders and the other is an insert into order items and i can see all my items here in this list of dictionaries of bound parameters i can see that the data was committed into a database and now when i ask the service to give me the order total for order id number four um i get the total cost the total cost is 67 which is what i expect you can check the numbers yeah all right um this is also the query because i because i've asked for the order total this is the query for the order total so you can now move on to the next step

Speaker 3 [47:31]

You have time for question. There's a question here. Do I need to rerun the docket in order to update the services with the modifications?

Speaker 2 [47:39]

Are you okay with that?

Speaker 1 [47:41]

There is a hot reload feature, but if your service threw an exception, then you do need to run it again. So yeah, you can just use the shell script run, space run, and yeah, it should rerun the service.

Speaker 2 [47:57]

All right. So, we've explored. We've added SQL Alchemy to our service now. But the queries are still raw text. Like, how can we improve that? How can we bring the optimization from, like, a big chunk of block to a single line of code? The solution to that is using ORMs, which is basically stands for Object Relational Mapping, where you can map the tables to Python classes, where the columns of the table become the class variables of the class. And this structure, where, you know, the columns turn into the variables or attributes, is called decrelative mapping. And the way to do that in Python is just to create a class called base, which inherits from declarative base. From here, you can just copy and add it to the base.py. Remember to keep the previous stuff, the engine, running in the code, so let's just do that quickly. In dbBase.py, you just need to add a class called base, which inherits from declarative base. And declarative base is from SQLAlchemy.ORM. With this, your declarative base class is set up. And to create mapped ORMs, or mapped classes, all you need to do is create a class which inherits this base class that we just created. Now let's talk about, you know, these fields in this customer class. So this is the customer ORM of our customer table. And the first attribute is the table name attribute, which says that this is the customer table of our database. And you just call underscore table name, underscore dunder method, dunder attribute, sorry. And just put customer over there. And to show that the columns, to signify the columns of the table to the class, you just create like class variables with the same name. For example, we have the ID column. And we've created this class variable called ID. And we're using the map annotation, which is derived from the PEP484 convention. And when we pass int to map, it understands that this column is the type of integer. If we pass on string, for example, string is passed on to the name attribute, this understands that it's the varchar type. So this is how SQLAlchemy understands the data type of the column. Now you can also use map column, which returns the column type, and you can pass on these special parameters to this. For example, ID is the primary key of a customer table. How do we represent this in an ORM class? We just put primary key equal to true in the map column construct, which signifies that this class attribute is the column which is a primary key in the customer table. For this one, we've also added auto increment as true because that's the way how our database our table is set up, where you don't need to manually insert ID. It's just automatically increments from one. So you signify that using auto increment as equal to true. The ORM map class also has a built-in init method which helps you to instantiate all these classes which we'll explore when we talk about adding data to our table. But for now, we've also defined a wrapper function which will help us in debugging where it will show you these different attributes in a string format. Now we talked about that the name of the class variable has to be the same as the column name. But it doesn't have to be. For example, over here we have written customer underscore name. We can pass on the name, which is a name of the column as the first parameter of map column. And here we are not using mapped of string as well as compared to here. The other alternative is by just using string and nullable equal to false. So this is an alternative way to do this. But this one is recommended. And if there are optional fields in your column, you can signify that using string or none or you can say optional of string, whatever you prefer. With that, let's, I'll give you some time to create the address ORM Mac class. So we've seen the customer ORM. Let's try to create address. And there's a hint where address has three columns, ID, flat number, and postcode. I'll give you a couple of minutes to create address, and I'll try to create as well. So we are creating a new file in DB called customer.py. I'm just going to copy this for now. I'm just going to remove relationship. four minutes yeah we are supposed to finish by now You can have a look at the market db in a db.sql. So flat number here is an integer. You're right. I just assumed. Sorry. so you don't know it just makes it like a legend Yes, I'm in the browser, but how can the browser Okay, but they have to install a Docker container. Oh, yes. So, you have, so, separate companies, because the docker is paid for this, but when you have, like, local laptops, but the docker containers and the images are in your code space. So, from a browser, can you use the docker? But it is only in the browser. So, does this file, do they exist? No, no, this is, no, this is not exist. Uh, do you remember using, you can use this template and creating a repository? Yeah, I can talk about it. It's not a strong paragraph. Okay, yeah, we'll talk about it. It's really a good idea. So this is how your address should look like. Yeah, we just put integers, like, for the postcode, too. Oh, wow. OK. See, even in the UK, like, I live in the UK, and the postcode is a string. You have, like, letters in your postcards as well? Yeah. That's what I said. It's probably . Yeah, no. I mean, this is just for tutorial. Like, I'm sure, like, in real-world databases, people will be using, like... So they have, like, legacy, and the new one is correct. Okay. Have all of you written the address ORM yet or are still in the process? All right. So once you've done that, creating the address ORM, let's talk about relationships. And you might notice there is the address ID column in customer, which might hint that this is a foreign column, foreign key in the customer table to the address table. see how do we represent that in SQL alchemy. It's quite simple, really, where you just need to define the special type of relationship which helps you to define linkages between two classes or linkages between two tables. You just write the name of, you know, your class attribute. Let's say address.py, I'm writing in the address class, I'm having a relationship on customer. So that's why I'm saying customer. And in the mapped annotation, I'm not using integer or string or Boolean, I'm using customer. You can either say the customer class itself or you can put that in a string to remove circular dependency. And then you can just call the relationship construct from SQLAlchemy.ORM and then just define these two attributes called back populates as address and lazy equal to join. We can talk about lazy join and these attributes in the next section in the optimization techniques, But for now, I can just say that this is using the joint relationship between these two tables. And in customer.py, you do pretty much the same thing, but in reverse, where you define an address class attribute and you say mapped of address because that's the relationship on the customer table to the address table. And you just put back populates to customer and you put the same joint relationship. But in customer class, you also define this new class attribute called address underscore ID, which is a type of integer. And then you call foreign key of address.id. This is a special scenario where this will help SQLAlchemy know that this address ID is the foreign key to the address table. And the column in address table is called the ID, whereas the column in customer table is called address underscore ID. So this is the crucial element that links SQL Alchemy, that tells SQL Alchemy to know that these are the two columns that we need to link. And finally, in customer ORM, I'm also defining this simple method called as dict, which will help me convert the ORM into a dictionary because our service expects dictionary to to convert it into a JSON. So I'm just simply creating a nested dictionary of name and address and just passing on name, flat number, and postcode. So once you're done with all this, this is how the customer ORM should look like. You have all these class variables and class attributes. And then you have this relationship over here. You have a wrapper method, and you have an asdic method. You can go ahead and copy this for customer class and go ahead and copy this for the address class. We'll do the same. And a small change to server.py as well. Because since we've added a function called asdict, let's also edit the customer's function in server.py to use the asdict function which will convert all the role objects to dictionaries. So our service can understand the response. So we've modified customer.py. That's the ORM. modified address.py, which is the ORM as well. And let's also edit server.py. What is this? What is this? What is this? What is this? What? I'm just checking out the step 3 ORMs branch so it's easier for me to demonstrate in the a short span of time. Let's run the service again. So you can check out the step 3 ORMs branch as well. We've added the map classes for all the different tables. So we have addressPy, customer.py, item.py, orderItems.py, and orders.py. And you can have a look around to see what are the different class attributes, how many relationships there are, what are the different fields on them. Let's give one more minute and I'll talk about querying them, yeah? We have a couple of minutes, we don't have to check out and, yeah, two or three. Thank you. All right, so now we've created ORMs for customer and address. If you have checked out the branch, you automatically have ORMs for all the other tables as well. Let's see how to query data now and like how let's see how magic occurs from SQL alchemy. So first of all, we go to dbaccessor.py, and previously we were having raw SQL text in the queries, but now let's fully utilize the SQL expression language that SQL Alchemy offers and see how sessions work. So sessions and connections is pretty much the same, like sessions uses connections under the hood. But when working with ORMs, you use a session instead of a connection, where first of all, You just use sessions API using the context manager. It's pretty much the same thing as using a connection where you call a session using with session of engine as session and you pass on the engine that we define in the DB slash base.py. And the first line you have is the actual SQL statement. So instead of select star from customer, the SQL expression language is simply select customer. That's it. And customer is the map class where that's the class we want to select. And once you do that, you pass on the statement to the execute function of session. So connection.execute and session.execute is the same thing. You get the result from that. And you call in two special dot methods. You call dot scalars and dot all. So dot scalars will help you to get the actual map class rather than the row object from the result. And dot all will say that we want all of them, not just one. So using dot scalars and dot all, you will find the list of customers using this query. So let's do that in our code space. I'm going to copy this. So and oh, since I've checked out the branch, I don't need to copy. And I'm going to run it now. So you're going to use the convenience shell script. And I'll hit the customer's API. And now I'm getting a list of customers. So there are three customers. And thus, I'm getting a list of three dictionaries. And as you can see, these are nested dictionaries. And this is how we've defined our as dict function. And when you go back to the logs of the service, you can see that, OK, let me just go to the slides, which have a better representation. Right. So we've just mentioned select of customer. But what's going on behind the scene? What's happening under the hood? To understand that, we need to see the logs. So first, we have a begin as expected. Then we have this select query, which is automatically generated by SQLAlchemy, where we are selecting customer.id. name all the attributes of the ORM. And then we also have a left outer join. So do you remember when I talked about lazy equal to join? So this is how it uses the join to join these two tables. Where we define lazy equal to join, it says that use left outer join, the strategy to join and link these two tables. And this is the SQL query generated automatically by SQLAlchemy. And then finally, you have a rollback because we did not commit anything. It's just a normal select query. we have a rollback at the end. So we've checked out step 3 RMs already. Let's talk about inserting data. So the way to get data was using select construct. To insert data, it's as easy as just creating new objects of the map class and just putting them in session.add. That's basically saying add this object to my current session. This is because we have a custom init. We have an automatically generated init constructor of the map classes that SQL Alchemy provides. And the way to initialize them is, for example, in the address ORM, I just pass in the two fields as keyword arguments, where flat number is 101 and postcode is 1001. And you just instantiate your object this way. And you can pass on this to session.add. This will insert your class to your database where you can insert this row to the DB. With that, let's update this function to add a new order for customer. We first have a session context manager again where we use with session engine as session. Before inserting, let's get the customer whose order we are generating. So here we pass in the customer ID field first. And with the customer ID, we are just trying to select what we're just trying to get the customer object. And we use .scalar here again where we want the actual mapped ORM rather than like a result or a new object. Now let's create a new object for orders. Orders is the ORM. Like you already have this if you have checked out the step three ORMs branch. And when you just pass in the customer ID, the order time, and the customer object that we just got previously as keyword arguments, you have this new object instantiated. Now order and order items are related with the one-to-many relationship. And one order can have multiple order items. And the way to add multiple order items is by just setting a list of, you know, order items and putting that in the relationship to new order. So if you look at order class quickly, let me just go back and show you the relationship. So order items is a one-to-many relationship over here where here we are signifying map of list of order items rather than just a singular type. So SQLAlchemy infers that this is a one-to-many relationship. And to add order items to order, all we do is get a list of order items and you put that in order items. It's pretty easy. And we've used list comprehension here to create a list of order items. And once we have this new order object ready, we just put that in session.add. This means that you're adding the current object to session. And once you call session.commit, it will insert all the required rows automatically. Let's see how that works behind the scenes now using by seeing the logs. Again if it's successful, you will see a commit message. If it's, if an exception occurs, you will see a rollback. So behind the scenes, first you will begin, as always. The first select is for getting the actual customer, getting the customer whose ID we specified using the bound parameter, as you can see over here. Now we have two insert queries. The first one is to insert data into the orders table. And the second one is to insert data into order items. So these are the SQL queries automatically generated by SQLAlchemy. In the Python code, all we've done is add this new object to session and we've just called session.commit. So we are interacting with the database in a Pythonic manner using all the constructs provided by SQLAlchemy. But behind the scenes, the SQLAlchemy package is automatically generating these insert queries. And we are also using bound and it's also using bound parameters. We talk about bound parameters because they are safe and prevent SQL injection attacks and ORMs use this by default. So let's just see it in action now. So let's say the current order total of the order ID with five is zero because there's no such order. And let's add a new order. You will get a successful response, 200 okay. And as I mentioned, these are the actual insert queries that happen in the background. And let's run the order total for order ID 5 again. Now we have this inserted and you can see The total cost is $67 for this order. So now you understand how this works, where previously we had the raw SQL text, and it was long, and it was bulky. And sometimes, this is just a simple example. In production, you might have multiple tables. You might have multiple relationships and joins. And your queries can go even longer than 100 lines. But using SQL Alchemy, you can just convert it to objects and interact with your database in a Pythonic manner. And it's just like English. You're just saying select orders, where orders, customer IDs, the customer ID be provided. And that's pretty much it. So I'll quickly talk about relationship loading techniques. All right, 10 minutes left. OK, how do I do it really quick? So one way to do this is the loading techniques. I'm going to talk about the popular n plus 1 problem, where let me just quickly go to customer. Okay. So let's say instead of join that we had previously, we were to modify to select. That means just use lazy loading. Lazy loading is a strategy where if you're a parent and a child class and both of them have a relationship, but if you're only using the parent class, it does not load the child class at the same time. It will wait until you do parent print parent dot child. So it's lazy. It will not do the work up front. Rather than that, it will wait for it to do it at the end. And after you try to access the child attribute of parent, it will then emit additional SQL query, additional select query to access the child attributes. So similarly, we've done this in our query where we first got a customer. And for each customer in customers, I'm trying to access the address attribute of the particular customer. So what happens in the background? So you have a select query for the customer itself, the customers itself. And then for each customer, you have additional select query based on the given customer ID. So for the customer ID as one, you have a select query to get the address of the first customer. Then here you have the select query to access the address of the second customer. Similarly, you have the select query for the third customer as well. And And then you have a rollback. So as you can see, this is not really optimal, where if there were 100 customers, you would have 101 queries. If you had like a million customers, you would have a million queries, which is not really nice. So in this scenario, eager loading is better, where it will load all the related objects up front, where it can use the join clause. We have explored that previously, where it used left outer join. So the query it will build is that it will use left outer join from the beginning itself, and it will load all the addresses of the particular customers already. So it won't emit additional select statements, and we won't have this n plus 1 problem. Yeah, I think let's just jump to asyncio now, given we talked about SQL alchemy a lot. And there is a lot of optimizations. There's a lot of SQL alchemy stuff, which we couldn't, like we cannot cover in this short span of time. So feel free to read on this documentation later. I'll hand it over to Aya.

Speaker 1 [80:40]

Yeah, so hopefully you can see through this short example how different relationship loading techniques can be optimal in different scenarios depending on the usage pattern. Okay, now we want to look at how SQL Alchemy can be used in an asynchronous service using AsyncIO. If you're not too solid on how AsyncIO works, we do have a section here on AsyncIO. So it's just an AsyncIO introduction that walks you through a series of small examples that kind of builds your knowledge incrementally on AsyncIO. It explains what cooperative multitasking is, how coroutines are scheduled in AsyncIO. We have some GIFs here that will help you understand the event loop and how this works. So feel free to go over this if you want to understand how AsyncIO works. But in the interest of time, we're just going to look at how to use SQL Alchemy now in an asynchronous service using AsyncIO. So SQL Alchemy ships with an async flavor. Okay. Here, first, like, we're explaining that if you check out this branch, we have, again, a vanilla service that is using this asynchronous package. So this is a DB API that allows you to interact with a Postgres database in an async service. So here, if I check out this branch, so that is branch six base. You will see that this is just a service that is using, again, plain Python. There is no SQL Alchemy here. And it's just interacting with the database asynchronously through this DB API. So similar to what we've done before, we're going to incrementally change this to add SQL Alchemy. Except because we are short on time, I'm just going to talk through it. but you can go home and read the details and do it yourself. We're also using, of course, the async flavor of Flask. So you can see here, our server.py is now using coroutines rather than plain functions for requests that we are servicing asynchronously. So we're going to be making a few changes here. here first there is a description of um some of the concepts that we're using here like asynchronous context managers um yeah and asynchronous for loops you can read about that but let's see how um did i skip over the engine okay no let's see how we can start adding um sql acme here so the first change we're going to do is use the asynchronous version of the engine so the connection pool manager, and we're going to be using the asynchronous pgdb api to connect our database instead of the psycho pg2 that we've used previously in our synchronous service. The second change we're going to do is in our execute query functions we're going to be using asynchronous context managers, and we're going to be using await on the statements that we want to be executing asynchronously. So, of course, you know, the biggest value that you're getting out of using an asynchronous service with asyncio is that you don't want to be blocking your code on IO-bound tasks. So this is what we're doing here, really, when we use an asynchronous context manager, instead of blocking this coroutine, it's a coroutine because we're using the asynchronous stuff. So instead of blocking your code and hogging the CPU when your coroutine is just awaiting an IO bound operation, which is connecting to the database here, establishing a connection, instead when we use the asynchronous with keyword that allows us to yield control to the event loop while the IO bound operation, which is the connection to the database, is being executed. You can read here on more detail about exactly how this is implemented and how asynchronous context managers work. Similarly, when we execute a query in the database, we don't want to be hogging the CPU. That's another IO-bound operation, so we use await here to call connection.execute any query. This is showing the insert version of this, so we also await the connection.commit operation. So when we're committing data to the database, again, an IO-bound operation that we don't want to be hugging the CPU while we're just waiting for this to happen. There's another useful function here that the SQLAlchemy async version exposes called asyncConnection.stream. So this allows you to, instead of kind of loading all of the results from your query at once, you can use an asynchronous generator to read the result one by one. We do have a description of asynchronous generators. If you read the asyncio introduction, you'll understand how this kind of helps you again not hog the CPU while you're waiting for results to be produced. So this is an example of where that can be useful. I will unfortunately have to keep going. Finally, the last section here talks about how to use SQL Alchemy ORMs with asynchro. Again, you can always check out the branch. We have for these two steps, we have a base branch and a solved branch. So you can see how you can go from not having ORMs to having ORMs in this case. How much time do we have? Two minutes. One and a half minutes. Can we take a look at the questions?

Speaker 3 [87:27]

We have a little bit more than one minute, so officially it's finished now, but since we have lunch break, so officially if you want to go eat, you can go eat, otherwise we can stay longer because we have the luxury to have a break so we can answer the questions.

Speaker 1 [87:45]

Okay, are there any questions on the chat?

Speaker 3 [87:47]

Yeah, there's one with three votes. What about the question with the most upvotes. What about queries that go beyond the join, e.g. partitions? What does SQL Alchemy support, not support on a high level? that go beyond join, and as example, partitions. That does SQL game support partitions.

Speaker 2 [88:20]

I think they do, yeah. I think they do support partitions. We haven't explored that in the workshop. And I haven't played around with partitions with SQL Alchemy. So I don't know how to do it exactly, but I believe they do support and they have documentation, I think.

Speaker 1 [88:38]

Just before you go off to lunch, we do have a feedback form here. Sorry, like the async version was like a little bit, well, very rushed. Oh, we're not sharing the screen. Okay. Well, the feedback form is like tinyurl.com slash SQLacme dash feedback. Thank you.

Speaker 3 [88:57]

Thank you very much.