 Okay good morning everybody thanks for showing up. Eric is a late addition to the program he volunteered to speak for somebody who withdrew his talk. So I'd like to thank you for that and with no further ado, a good morning. My name is Eric Janssens. I spent the last number of years working with SQL alchemy on various projects and this talk is a SQL alchemy drill. So the idea is that you fire up a Python console. You do pip install SQL alchemy and you follow the commands I will type on the screen. You follow them on your own console so that at the end of this session you've actually started using SQL alchemy and we've gone together through all the basics of SQL alchemy. Now the whole session can be downloaded at my website with all the instructions if you are not fast enough to follow. So what is SQL alchemy? SQL alchemy is two things. It's a Python toolkit that allows you to manipulate SQL and it's an object relational mapper. What is the important work on this slide? Indeed relational because if you look at SQL alchemy and you compare it with other object relational mappers, then you will notice that SQL alchemy has spent a great deal of effort in getting the relational part correct. So if you haven't started your Python interpreter yet, do so now and let's move on. If I'm going to fast, please give me a sign. If I'm going to slow as well. So the first thing you should know about SQL alchemy is that it's not a tool to hide SQL from you. Instead it's a tool that allows you to work with SQL and it will help you to manipulate SQL. So don't try to use it as a tool that completely hides the SQL for you. You still need to look at it and it's even good to look at it. So when you work with SQL alchemy you're encouraged to look at the SQL it generates. To do so, you do so through the Python logging module. So we import the logging module. We're now going to configure the logging of our Python interpreter. We set it to the debugging level like this and now we'll configure SQL alchemy to log all the SQL alchemy statements through the logging module. So first we get the logger which is SQL alchemy.engine.base. If you turn on this logger, SQL alchemy will log all the SQL it sends to the database and you will also see the results it receives from the database. So we set the level to debug as well. Okay. Now if you work with databases in Python, you work with the Python DB API and the Python DB API is actually just a specification to which the various database drivers in Python conform so that all the database drivers work more or less the same way. But when you work with the Python DB API, you need to take care of a lot of things yourself. You need to open and close connections to the database. As you know, doing so uses some resources. So you want to pull your connections. You need a connection pool. If you work with SQL statements, you need to manage your cursors manually. If you work with transactions, you need to manage those as well. And already at this level, SQL alchemy is going to help you. So I'll just import everything from SQL alchemy. SQL alchemy comes with a connection pool. In SQL alchemy speak, this is called an engine. So let's create a connection pool. So this is a connection pool to an in-memory SQLite database. In memory, it's handy for development because it's easy to throw away. Now we can get a database connection out of this pool. So let me say connection is engine.connect. And now we have a database connection. And we can send SQL through this connection. So simple SQL command. And you see SQL alchemy locks what it sends to the database. It didn't receive anything back yet. Now we got a result object back. And we call the scalar function, which just returns the first element of the first row. And now it retrieves data from the database and it prints it out. So the engine, the connection pool is the first important concept in SQL alchemy. Second important concept is metadata. Metadata in SQL alchemy speak, it's a set of Python objects that describe your database schema. So they describe your tables, your constraints, your indexes, things like that. So let's create a metadata object. So this will describe our database schema. Once we have this, we can create a table. So a table, it's a Python object as well. It's called table. We give it a name person. We specify that it belongs to this set of metadata. And then we add some columns to the table. First, an integer primary key. And we'll give the person a name as well. That's string, 25 characters long. And it's a required field. So we set nullable to false. Okay. This is a table object. From a table object has an attribute columns, which is its list of columns. If I print it out, it will be formatted better. Okay, so you see the table has an ID and a name column. We can now also have a look at our metadata object, which has a list of tables in the metadata. Like this. Now, we just created our schema in Python objects. So we haven't yet created the tables in the database itself. To do so, we are going to instruct the metadata to create itself in the database through a connection pool. So we say metadata create all using the engine. It will check out the connection if it needs one. And check it back in into the pool. Now you see the SQL to create the table is sent to the database. And now the table exists in the database. Now, I said that SQL Alchemy is a tool that allows you or assists you to manipulate SQL clauses. So how do we create clauses? Let's create an insert clause for a table. You do so by calling the insert method on the table. So now we have a clause. I can print the clause. This clause is a Python object. It's a Python object that represents an SQL clause. If I print the clause, I get some pseudo SQL code. This didn't execute anything. So how do we execute this clause? Again, we'll send it through the connection pool. So I say engine execute the clause and I give some arguments. For example, name is Guido. Now you see SQL Alchemy inserted Guido in our person tables. We can create select clauses in the same way. So we say table.select. This is select clause. And now this is the nice thing about SQL Alchemy. You can start manipulating this clause. So we have now a very simple clause. We just select all persons from the database. I manipulate it. And I say I create a new clause object which limits the previous clause to one row. Print the clause. You see the SQL has changed. The limit is there as well. I can further manipulate this clause. And I can say order this clause by the name of the person like this. Print the clause. And the clause has been manipulated as well. This is very handy if you need to build up complex queries. You can do much the same thing for delete clauses and update clauses. Now let's move over to the object relational mapper part. To use the object relational mapper of SQL Alchemy, the easiest thing is to use the declarative extension. The declarative extension allows you to define your clauses and tables at the same time using the active record pattern. You can use other patterns with SQL Alchemy as well. But for the sake of a short drill, let's take this route. I will start with a fresh metadata object. I import the declarative extension. Now what the first thing we will do is create a base clause. And all our mapped clauses will derive from this base clause. So you create a base clause using the declarative base function in SQL Alchemy. This will, at the same time, associate all the clauses that derive from the base clause with a certain metadata object. So that if we define a subclass of base, we will also define a table within this metadata. So let's define our subclass. So we have the person that derives from base. We give it an under-under table name under under attribute, which is the name of the table. Again, the person needs an ID integer. It's a primary key. And we give the person a name. Like this. What did I do wrong? I don't see it. Here. Okay. Thank you. No. What's the stupid? Our table person is already defined in this metadata object. Create a new base clause. Create a new metadata object. Create a base clause again. And create. Okay. Now I have this person clause. You'll see that at the same time, we created a table. If we print person under under under table, we get the table that is created. The table as columns. And SQL can be also created a mapper object. A mapper object defines how the table is mapped to the class. So in this case, it's just easy, every column maps to an attribute, but that can be different as well. So again, we will only created the table object. Now we still need to create our table in the database. So we'll say metadata. Create all through the engine. And it didn't actually do anything because our table existed already in the database. And SQL Alchemy just looks to see if the table already exists. And it doesn't create a new one. So now we have created our class. Next thing we're going to do is we're going to create objects. Again, the relational part of SQL Alchemy comes into scope now. Because in SQL Alchemy, you never manipulate or create an object alone. You always work with sets of related objects. And those sets of related objects, they are grouped in a session. So session is a group of objects you're working with. So we have to create a session first. In SQL Alchemy, you first create a session factory. And a session factory, it's normally indicated with a session with a capital S, is created with the session maker function. And it's bound to a connection pool to an engine. SQL Alchemy first imports all the object relational map related things. Now I have a session factory. With the session factory, I can create a session. This is usually in the documentation session with a small s. So this is our session. Now I can start create person objects. Create a person with name Guido again. I have a person, but this person doesn't belong to a session yet. So the first thing I have to do after creating a person is add it to this set of objects I'm going to manipulate. I can check where the person is now in the session. That's true. And if you look in session.new, you'll get a list or a set of all the objects that have not yet been written to the database. To write the objects to the database, I do session.flush. And you see now the SQL is generated to insert the person. If you want to retrieve objects from the database, you go through the session as well. So you create a query object, which you do through the session query method. You say query the person. Okay. Now I'm going to retrieve all persons from the database. And I got at the end a list of person objects back. Now I said that the most important part of SQL alchemy is mapping the relations. So let's start with the relations. We're going to create an address class, which is related to a person. So we create an address class, which is a subclass of base as well as a different table name. Give it a primary key and give it a street. It's a very simple address. Okay. So again, we created our objects. Now we still need to create the table in the database. So let's recreate all our tables from the metadata. And you see a new table is created in the database. So now we only defined our relationship at the database. Oh, I forgot to do this. I'm going to add to the address person ID column, which is an integer as well, but a foreign key to the person. Forgot to do this in the definition, but I can add it later on. I'll have to recreate the database schema now. I'll drop everything and create it again. Okay. So in the in this line, I only defined the relationship at the database level. I now also want to define it at the object level. So I'll give the address class a person attribute, which relates to a person. And at the same time, I'm going to give the person class an attribute addresses, which is a list of all the addresses related to the person. That's done like this. So you can look at the relation between person and address tables sees that there is a foreign key, and then we'll figure out how to do the relations at the object level. Okay, I have to and I need a new person. Now, this is my. Okay, so now I have my person. I'll create an address related to the person. Let's see, Guido lives in the Karl Marx. Only just around the corner here. So now I can look in. I have to add the person to the session as well. And let's see what's in the session. So the person is in the session and seek walking me at the address in the session as well, since the address is related to the person. So we should manipulate them together. Let's write everything to the database. We flush the session. So seek walking me inserts person and address in the right order. And what we can do now as well, we can look at the person dot addresses. And we see here a list of address objects. So this is in short the basic functionality that seek walking me offers you. There is also more advanced functionality, which you can look in the documentation. Another important part is working with transactions. I'm not going to demonstrate that today. What else can you do? You can map arbitrary selects to your classes instead of just mapping tables to classes. That's very interesting. You can also map arbitrary relations. So not only just foreign key defined relations, you can use alternative collections for your relations. So in our example, the list of addresses on a person, the addresses on a person where a list, but you can use sets and dictionaries as well. And seek walking me supports vertical and horizontal partitioning of your database. There's also a large number of related libraries that work with SQL alchemy. One of them is a limbic, which helps you in defining schema migrations. You have flask SQL alchemy. So flask SQL alchemy integrates SQL alchemy with flask. And then you have camelot, the library I've developed, which is a graphical interface on top of SQL alchemy. So this concludes the SQL alchemy drill. Any questions? Please go ahead. Thanks for the presentation, anybody with questions. Please step forward to the microphone. No SQL alchemy questions. Who uses SQL alchemy? And you have no questions. So I think my longstanding SQL alchemy question is, is there any plan for schema update? When you edit the column, it could have extended the table, right? Yeah. That's in a different library. It's in the limbic. It's in a limbic. With a limbic you can do things like that. Like automatic schema migration, automatic. When it's possible, I don't really believe in automatic schema migrations. But a limbic can do it. You can also define your schema migrations in a limbic. It allows you to version your database schema to do the migrations automatically in production. I have one question. Our database engineers are still using PsychoPG. Excuse me, excuse me. Sorry? I didn't understand that. Okay. Our database engineers are still heavily relying on PsychoPG too directly. And so we have like a gap between the application developers using SQL alchemy and database engineers like doing this low level task with PsychoPG. Would you say that relying just on SQL alchemy, because it, as you said, it's really, you can do full SQL. So it's not like this evil ORM stuff only. So would you say, okay, just switch totally to SQL alchemy and using directly PsychoPG? I would certainly consider that. Yes. I use PsychoPG a lot as well. But only through SQL alchemy. And you will see that SQL alchemy out of the box supports a lot of the PsychoPG stuff. And the things it doesn't support can be very easily added to it specifically for your application. So if you use some PsychoPG functions, the functionality that is not supported through SQL alchemy, you can really define it so SQL alchemy uses that functionality. You don't actually lose anything when you go through SQL alchemy. And that's very important to know that you don't lose anything. You don't lose the flexibility. I have another question. Do you know any good framework for accessing stored procedures from Python? Like probably SQL alchemy also has some abstraction on top of it? No, no, no. If you find one, let me know. Go to the microphone, please. I come over and you might mind. I was looking at Alembic for a short while. Is it possible to kind of connect the models you define in SQL alchemy to the Alembic so you don't have to define them twice basically? Just use the use the Alembic for the database migrations but use the models defined in SQL alchemy. Yes, you can do that. What I usually do is I keep because the model continually it evolves all of the time of course. So you define the model and you keep updating that and from time to time you take a snapshot of the model with Alembic and that are your database versions. So you don't have to redefine it? No, basically I thought just in SQL alchemy I define a model in a class and then just pass it on to Alembic to do this stuff. That works. Okay, thank you. Okay, if there are no further questions, thanks again to the speaker and enjoy the break.