![[2026 SQLD 2과목] SQL 활용 미리보기!!](https://i.ytimg.com/vi/jh0UudlBmac/maxresdefault.jpg)
SQLAlchemy 2.0 introduces a redesigned Core architecture that provides a consistent Python interface for relational databases, featuring a new SQL Expression Language that builds upon table metadata to compose SQL statements programmatically using method chaining, with explicit transaction management through begin() and commit() patterns, and unified result handling across Core and ORM layers. The tutorial demonstrates how to create engines, manage connections with context managers, execute text queries with bound parameters, build complex SELECT statements with joins and subqueries, and handle transactions including save points and isolation levels, all while maintaining database agnosticism through SQLAlchemy's facade over the Python DB API.
SQLAlchemy 2.0 Tutorial: Core & ORM Reimagined
Added:great so um this is the talk that's the ever evolving uh talk that i guess i first did in 2013 something like that with some other people and it's been just evolving to a different thing uh but as always the the github source is there if you see the github link on the screen right now um that's where the source code of this whole thing is so um and what it includes that you might want to play with is uh a bunch of python scripts that we're going to run through uh i'm able to run through them in an interactive way because there's no way i can type live type the way dave beasley does so i have a tool do it for me uh but you can actually download the whole thing and run through the scripts and if i go too fast which is likely because we usually want to get the orm stuff at the end and you know so if you know when i go too fast or something just go download the stuff take a look at it and play with it um so the the uh the 1.4 inning uh as i'm trying to stress that the new thing is sql alchemy 2.0 but you'll notice that that's not what's currently released we have this thing called 1.4 and 1.4 is basically this giant transitional thing uh for 2.0 so that it runs cross-compatibly with 1.3 for the most part and we'll get people onto the new 2.0 style uh and it's gonna i think it's a lot better so we'll see a little bit of how that that looks so um what i'm gonna do here is reload this whole page so that the bullets come out okay starting out so this is just the top level boilerplate stuff and you can just sleep through it sqlcommy is all about building python structures that represent sql database concepts in a very direct way um so when you start out it's pretty essential to know some sql um or you'll write angry tweets you know i look at twitter and you see a lot of beginner programmers who don't know sql they should learn sql first uh because my stuff is not going to make sense if you don't know sql it's going to be too much to learn so what sql is good to know for this tutorial just a little bit mostly not no not really much um the main thing is you know what a table in the column is which i'm sure just about everyone here probably does uh create table statement just to general what that does the select statement uh it's pretty hard to not be aware of that uh the basic idea of insert update delete um the general idea of a database transaction you know that there's kind of a begin and then you do some stuff and then you can either commit it or roll it back and i think that's pretty intuitive at that level and then there'll be there'll be some stuff that does with joins and some sub queries and aliases and common table expressions um and that's the part where we don't i'm showing you some examples and it may or may not make sense if you're new to stuff whatever presenting sql alchemy database tools fit for potty time i i've already been said introduced 2005 but the actual first release was feb 2006. so i guess i've been lying it wasn't you i think i first talked about i first wrote like a blog post in 2005 and that was people were talking about it and whatever a single system for all things python relational databases and we're at 1.4.2 which is considered to be transitional for sql alchemy 2.0 philosophies of sql alchemy updated um bring the usage of different databases and adapters to a python interface as consistent and cross-compatible between different kinds of databases as possible but still expose the distinct behaviors and features of all those different databases sql and relational database concepts are not hidden they're very explicit usually when people talk about the word abstraction they say well you should hide the database for me um i don't know if we should use the term abstraction if it confuses people i use the term automation uh so which means that you know you know about sql and you're writing things that represent sql you know the database you know that you're executing statements but you don't have to do every little thing almost like an ide kind of helps you do things that you know what you're doing but i you know but the id helps you do it sql gives you python structures to automate having to write lots and lots of insert statements and lots of selects or building up a select um but you're going to know there's a database there and the more you know the database there the better it'll be this is the old and still current diagram uh we call this a pancake diagram because it's kind of horizontal stuff um so the first half of the talk is going to be about secondary core and then the second half is about the uh the core stuff uh there's also a color coding thing going on here which is not important to know but it's interesting uh the green yellow blue and red if you can see color uh are gonna be a little bit thematic um so anyway uh the orm builds on top of the core and then the core uh has all the nuts and bolts that talk to the python database api and that's the thing that actually talks to the database so the driver or the dvd because the driver will show you that later i shouldn't waste time on that part uh things in the core main top level things take calculator gives you this thing called an engine database connectivity gateway which gives you these two components called the connection and the result uh the result is an is a new object in 1.4 the engine maintains behind the scenes two components called the dialect and the connection pool and then outside of the dialect connection pool and the engine stuff you interact with it using uh sql expression language constructs which will do all that and the sql expression language contracts themselves build upon this system called the schema and type api which we will do that too um that's the core and then quickly before we get to it again later the orm you so now you have the core that's entirely there then the orm is totally on top of the over of the core the orm maps the user defines object model to database tables uh and when you map your define object mode to these tables it persists and updates the state of your objects in python to the database using a pattern called a unit of work the orm provides an extended version of the core expression language that supports uh queries in terms of the object model another thing the orm spends a lot of work doing is getting your database rows from the database to come back as your objects which is a big deal it's actually not a simple problem when you have these large graphs of objects that are all connected to each other um and then it also like most orms provides a way to have objects that are related to each other uh like if one object has a collection of some other objects that would be based on a foreign key in your database so that's like relationship in sql argument we call those relationships like one too many many to one you actually can have a norm that doesn't do that stuff but most of them do um and then as you're working with your objects it's going to synchronize the state of the objects with the state of the database data in an ongoing transaction which really means it's doing that unit of work thing and the converting the rows back and forth kind of in a semi transparent way depending on how you configure things so that's the top level stuff that most people who have dealt with clogged me kind of already know uh the shift 2.0 is a new all-new rethink of many of the core apis of sql alchemy two general areas of emphasis one is fully removing the old patterns that have disturbed been discouraged against for many years um you can deprecate things and you can tell people to not use things and you can take things out of the docks but when you've been around for 15 years there's endless tutorials still doing at the ancient old way and then people are complaining that seek welcome is hard because they read some old tutorials so the only way to solve this is to take those apis out completely and then people will know please stop reading tutorials from 2007.
problem solved um and then the other thing that's to appoint though something i've wanted to do for years uh making the experience of core and orm apis much more similar in cross-compatible uh the way the sql alchemy or m kind of became what it is right now in 1.3 was kind of accidental and kind of evolutionary and kind of not totally figured out ahead of time so now i hopefully have made it much more consistent and makes sense like why is there a query at a select really actually make any sense um and there's a new emphasis and then a new emphasis on explicitness and non-ambiguous happening oh my god what is everybody okay i think some mute action just happens okay so uh new emphasis on explicitness and non-ambiguity and that actually was largely inspired by the fact that people are the python has now is typing with pep484 and my pie uh there was that and then also we had a lot of issues where people were like well this does this this time and then it does this and this other context and i want to be able to write code that expects something consistent i'm like you're right um so we did a lot of that and it makes things a little more verbose here and there but it seems like i think people are ready to have a little more verbosity because it's going to be explicit and clear what's happening as opposed to magic which everyone knows is terrible um other changes python three only which we should have been part of the really by now it's python 2 is extremely not well widely used uh and async i o is in you can use it you can use clock me where they think i owe now and it works pretty well and yep thumbs up and uh there's we're doing basically the next what i'm going to do for the next year is get the my pie pep 44 thing working uh there's a my pi plug-in in 1.4 now uh and it it covers a small amount of things but it seems to work pretty well uh so far so it's kind of usable-ish you know people have reported some bugs but they haven't reported too many bugs so it seems to kind of work for some things and and i want to get the type stubs totally fleshed out um sequel can be 1.4 the transition excuse me so 1.4 implements the 2.0 architecture and feature set as planned so far completely um and it provides cross-compatibility with sql alchemy 1.3 and it still works in python 2.
so in this talk we're going to pretend that 1.3 and everything never existed we're going to represents equality completely from the 2.0 style so everything that we work with here is the new the new way of working and that will be different for people who have used sequel me and it'll look different you'll see it what are the major changes um which you're going to see anyway but here's the the big ones uh python 3 only um the engine has been scaled way back to not have 25 ways to do something uh emulated auto commit is removed which people might not even know it was there it would automatically commit certain kinds of statements when using a certain way that's just gone um connection unless execution is removed uh there's no more statement.execute you have to be like you if you're going to run statements in your database you have to say connect or begin you definitely have to say to the engine i want to connect now here's your unit of connectivity called the connection the result set is way way better we'll see the things the result set does it's pretty awesome uh and the vast majority of sql compilation is now cached uh which was not the case before we had this kind of experimental thing called a big query that kind of did the caching and was hard to use uh it was experimental and now we have an entirely new way to do the caching that was way more work to get it going but now it's awesome and the orm query object is unified with the select state the select object so now you can use the select object excuse me to get orm results you could you can you have a select object for here comes the coffin select object for core and select object for rm and then also when you get results back in the orm or in core you get the same result object it has rows so you can say result.all result returns tuple like rows there's many options for how to change what you get back but you will get the same kind of results so if you have sql statements you could just run them against connection or against the session get the same kind of thing everywhere there's no there hopefully are no surprises what you get back it should be very consistent and this also helps a whole lot with the my pie thing because in python methods that return 25 different kinds of data don't work very well with typing so it's been largely inspired by having the typing system to be you know straightforward and the async i o is there using a recently discovered approach to bridge async and sync apis uh so this is the sql alchemy onion and i'm not sure if there's actually a term called an onion diagram uh it's kind of like the layers you know so the innermost layer is is where we start for this talk and uh you add more layers so i guess you i guess an onion you're supposed to peel the layers off so whatever you get it you see the same green green blue yellow red color so sql alchemy and i'll move the little box here boom sequel alchemy can be understood most fundamentally working from the inside out so the green onions are the engine you know the connection and we'll start with that the engine has to work with this thing called the python db api which is what most of the databases is what we use to talk to most databases with async io it's not quite the same story but generally the db api is that pep in python pep249 it's the de facto system for the python database interfaces um there's lots of them most databases have several they're very very uh inconsistent in what they do the the two pep 249 is a really really excuse the open-ended you know loosey-goosey kind of pep uh what the dba pad looks like if you use psychopg2 which is probably the most tv api tv api uh there's the two objects you deal with are the connection and the cursor uh so the connection is takes some kind of uh the connect function takes some kind of parameters like authentication and host name possibly you get a connection you want to work with sql you have to make a cursor first the cursor is the way that you send sql statements and parameters and then the cursor once you've executed some statements you get fetch methods to fetch rows back and then once you're done with that you say commit or roll back up on the connection again uh the connection has implicitly started a transaction for us and that's kind of the big thing about tv api that so many issues are based on people understanding or not understanding is the the transactional thing important facts about db apis they have a lot of inconsistencies uh you can't write an application that's going to target more than one database trivially you have to write library code it's going to change how things work for different databases when we work with sql databases we really want to deal with bound parameters a lot the gb api decided there should be six formats which is ridiculous in my opinion um let me move the box they're gonna send a lot of time in the box db api has a very specific way of doing transactions see it's very loose about everything else but transactions we do it this way which is that a transaction has begun implicitly there's no begin method when you start sending sql over the db api the transaction has begun whatever that means uh but then nothing gets pushed to the database unless you say connection.commit uh and it gets rolled back if you say connection.rollback so there's no begin method now some dpi api apis do have begin methods for special things like if you want to begin with certain kinds of isolation levels or if you want to begin like a two-phase transaction but generally it's not part of the spec and you don't need to call it by default um and then you can turn off the implicit transactions using auto commit mode which every dp api that we support directly has now when we first did this auto commit was not a thing so sql made its own auto commit but now we took it out because they do it for us they do it much better actually because they're the driver sql alchemy um provides a facade over the gdp api using this thing called the engine uh when you connect there's this consistent url format to connect to databases it's no longer like different kinds of parameters sent to this db api connect method it provides consistency in the following areas transaction control accommodating getting new primary keys when you insert rows or getting defaults back uh triggered update defaults um a wide range of data translation issues like a really really wide range of how data is translated between python like every single driver is completely different and has every driver without exception has a lot of things we have to do to get the data to be consistent back and forth driver specific quirks parameters all kinds of silly things methods you've got to call for some and not others we provide a single bound parameter format for when you are using textual queries which is actually the exception using using textual queries is the exception rather than the rule uh partial cross compatibility for accepting hierarchies uh you can catch integrity error and it'll be the same integrator on all the different drivers uh but what the messages is inside the exception might be different you might have to parse that if you're if you're trying to catch that kind of exception so with that we're ready when you see this gray bar that means we're ready to do the first uh scripted thing so i change the window if anybody wants to jump in with questions or anything so far nick it's really hard to hear you i don't know your mic something on your microphone you see the chat room chat okay you could type in the chat if you want i have the chat open you see that big black box on your screen yeah i also pasted the link to the tutorial code into the slack channel so if anyone missed it on the first slide it's in the slack channel all right do you want me to move on will personally answer the question can type it or okay so now we're in a python interpreter here which will run those python scripts in a very controlled so i can basically step through them um but this is a this is a rebel so i can type code but i have a dell xps laptop with tiny keys and i'm realizing that my fingers are too big for the keys so typing is not going to be good um anyway when you want to make your engine everyone who's you see clogging for 10 seconds knows this the first thing you do is you do from sequel can you import create engine and then you make this object called an engine um and what we're doing here because we're in 1.4 which is the we're in the middle of the 1.4 inning of 2.0 just to make sure it knows we want the full 2.0 experience we send this flag called future equals true which will give us the newer transactional stuff which is cool uh and it means that some other things that will work on the engine in 1.3 will not work on an engine that has future equals true it's actually uh let me see if this even shows you type engine it's called a sequel alchemy future engine so it's actually a subclass of the regular engine from the future uh so we made a an engine with a url with a sqlite uh url and this is the relative path sum.db so it's a sqlite database file called sum.db uh and nothing actually has happened yet at all all we did was make this object called an engine with a url uh it loaded up some modules it hasn't connected to anything at all because the engine is what we call lazily connecting it doesn't do the connect until you tell it to um so when you connect now it actually did something now it actually opened up the sqlite database this is where with people that know sql lite it creates the file upon the first time you talk to a certain database file it creates it this is where that would have happened so now the sum db exists and when you look at the connection you can see because we're in future mode it's also a future it's a connection from the future so now we have this object called a connection and that's not a sql like connection that's a sql alchemy connection that is uh proxying inside of it deeply inside because you have to go connection.connection.connection to get it the real sqlite connection is in there uh but you don't deal with it uh you get that i see a question in the thing from the person who was asking uh my question was about drivers are there better drivers to for 2.0 um good question uh we have not changed our driver recommendation we've added new drivers because of async i o so when you want to use async i o you need to use an async compatible driver um the two drivers for async io that we support at the moment are async pg which is a very good driver and a aio mysql which is an okay driver it's a port of pi mysql for async uh and pretty soon we're going to also have ao sql lite working uh sqlite is not actually able to do blocking non-blocking i o but people want the async interface over sqlite uh so we will do that um so for postgres uh still uh cycle pg2 is the driver for uh mysql client is still the driver uh or pi mysql for oracle cx oracle's the only game in town for sql server still pi idv so it's pretty much the same uh we have a lot more uh data on the site about what database versions we support uh we have a lot of version compatibility stuff so i say check out the website uh drivers haven't changed much except for async i o um back to the connection um when you have this thing called a connection you can call the main thing you want to do with it is execute queries when you want to execute a query you send it some kind of object representing your query and because we haven't done the sql expression language yet i will illustrate the most fundamental kind of query which is the text query meaning i don't want to actually build a python object that represents sql i just want to send a text statement um in 2.0 uh for some historical reasons and some explicitness reasons we would prefer that you send a textual statement in this object called text which is there now it's been there for years so you make a text object representing your sql query you can put bound parameters inside the text object of the form colon with a name and then you send them to the statement uh connection.execute statement with a dictionary of your parameters and then that will run that statement so when you when you go through these slides i have logging turned on so every time i run code is maybe if you're gonna have to look carefully to see and you see you see this sql thing that's uh sql that was sent to the database and then anytime you see other things like python objects coming back that would be the data we got back it's going to be mixed up um sql alchemy from the very beginning was very emphasis had a huge emphasis on um having you see the sequel that was always like number one from our first tutorial was like when you do this this is the sequel it was very always being always about being explicit so what we did when we executed that select statement is we actually got a result object back uh so just to show what we get from a result kind of briefly there'll be more of this we can say result.first which will give us one row from the result and then it'll close off the result which means it's going to close that cursor that's the db api is doing so now we have this row there it is the row looks and acts mostly like a name tuple so just to reiterate it has row uh imp example id so it's a name tuple um if we try to do row imp name we're gonna we'll oh we get that back okay that's legacy stuff um the way the road works you're supposed to go row zero you get the first thing uh you can say tuple row get that it's the same thing um it also has a dictionary interface so if you wanted to look at the row in terms of keys and get item which that get out should not have worked on the row uh you have row mapping keys row mapping values um different ways to get stuff from the row um usually though because it's a tuple uh the most idiomatic python style and i know this because there's a book called patterns in python something like that that shows like the most idiomatic way and they says triple expansion is the most idiomatic way to do things so if you get tuples back from an iterator you go for xyz in iterator so this is that format someone had a question with the text yeah execute yep um so this is the other way to use the result is you you execute a statement and then go for for row and result or for expand row and result uh and then i'm just going to print the stuff here so you see that there's the sequel and then there's the results coming back and also uh i use f strings like crazy now they're awesome uh sql alchemy is still 2.8 python 2 but um i think f strings are great so i'll be using those a lot um there's other methods on result and you'll see these just kind of pop in here and there like dot all uh which is actually a synonym for an old an older mythical fetch all that's all still there but we have an act.all and dot one and dot uh scalars we'll see it all um so when you have this connection that we've connected to um it has a method called close um what this close does is it doesn't typically close the connection to the database it releases it to a connection pool so when we have this engine and we say connect it actually made a connection and made it as part of this connection pool that by default will pull uh five connections and you can make it pull as many connections as you want um it also has a parameter called overflow so if you pool 20 connections with 10 overflow you can have as many as 30 connections out at the same time and if you try to connect beyond that it'll throttle it'll it'll block until the connection's available and that's a good way to limit how many connections your application opens to a database so anyway when you have this sql connection object and you say dot close that's going to actually release the connection back to the pool if the connection was part of overflow it actually will be closed for real but otherwise it just gets sent back to the pool and they will always be this is configurable but usually there will be a rollback called on it because the connection could have had any number of things happening on it any kind of if you did some select statements it could have locked rows it could have has all kinds of different state or settings that need to be rolled back uh and it'll also roll back other things like if you change the transaction isolation level it does that so this is kind of a big rollback stage when you pull the connection if the connect is just closed you can just throw it away but ticoacme will take care of that as best as it knows how at the moment so even though there's a closed method um in sql alchemy 2.0 and even 1.3 we want you to use context managers as appropriate where you can because they're way easier to read and they're way more guaranteed to definitely close out resources at the end that's how we do things nowadays you have a file in python you say with file with open as file handle closes at the end um sql alchemy came out uh we were in python 2.3 i think contact managers didn't come out to like 2.2.5 so we didn't design around them in the beginning but now we are trying to design around them as much as possible so at the end of this block you will you'll pull a connection from the pool do stuff with it and then it'll put it back in the pool release it closing the cursor and everything so that's from the code and you also will notice that sql output in 1.4 has this interesting cached thing so it's going to show you stats on each statement being cast or not even though this is a textual statement there's still some things that have to figure out about that statement that are cached i'm 30.
so a quick thing on trend transactions then uh the way there's a couple of ways to do transactions uh and this will be all of them um there's no auto commit in sql community 2.0 that's at the library level so if you run insert statements or whatever you're going to want to make sure you tell it that hey i need you to commit at the end so one way to do that is when you connect you can commit as you go meaning you say engine.connect as connection then run some statements and then you say connection.commit and then you can run some more statements and you can actually not commit again you can just you can connection.commit as many times as you want and this actually works pretty much like the python db api does you can say commit anytime you want on the connection as you go so we call that commit as you go so that's one transactional approach the other is to say engine.begin and that is an alternative to using engine.connect that will give you a connection that already has a transaction set up uh and then when you run stuff on that connection and then the block ends the connection transaction will be committed for you and then the connection will be released back to the connection pool so it's kind of an all in one way so if you're doing a lot of insert statements and updates deletes doing engine.begin it's a good way to do that it means that you are telling the engine ahead of time i want to commit at the end great and if there's come some kind of exception raised it will do a rollback first before it propagates the exception so if the error is raised that's not caught then you're going to roll back otherwise you get the commit so you can see again we see begin insert some some data and finally third way to do it sorry it's still perl many ways to do it um you can connect and instead of doing commit as you go you can do a begin block as you go which might be helpful if you're passing the connection around and someone wants to do a begin block so with the begin block it's just like the engine begin except you still have the connection at the end this should be pretty straightforward so if anyone's super lost i could slow down um there's that code uh and then this is not there's an extra thing that you can do and it's not important to understand it now is that you can do a thing called a nested transaction uh which is what sequel can call the save point a save point is like a little marker inside of an ongoing transaction where you can kind of have a little sub set of data that is part of your so if you have a transaction that does ten things you can create a save point halfway through and do three more things and then you can roll back to that save point without rolling back the entire transaction and that's why save points are handy if you're doing a big long transaction and you're doing some statements that might not succeed like inserts that might have an integrity violation especially the postgres database uh we'll want you to use a save point because if you have an integrity violation inside of a postgres uh transaction it will say we're done roll back the whole transaction so if you want to be able to recover you need to have a save point so begin nested is used for that uh you can make a save point explicitly and then do save point that rollback if you think you're going to roll it back explicitly or if you know you want to commit it you're trying to check cash exceptions you can do a block so that's a save point and that's all we have in this talk for save points because they they are what they are uh and that's the sequel that comes out so begin nested you can see it is save point and they have names so if you have multiple save points they have different names sql alchemy gives them a name automatically save 0.2 and then release save point means get rid of that save point as though it's like a commit and rollback to save point means undo with that savepointhead as far as autocommit if you actually do want to use autocommit which you actually have to for some things like some postgres ddl operations require autocommit uh databases when you want to do a create database call you need autocommit um if you are writing an application that uses mysql and it's read-only or you have a read-only follower database and you want it to be fast autocommit is actually fast so that's something you should consider for my sql applications is if you have a read only using autocommit can be a big performance booster depending on your backend um so and there's ways to make an engine that's your engine for writing things and then you make a sub engine that's the auto commit one for your fast read-only operations um so to do that you do this thing called execution options isolation level um the isolation level is how you actually set the transaction isolation level that will be used uh serializable recommitted repeatable read and then auto commit is another isolation level because when you have auto commit there is no isolation because every statement is immediately committed and that's how you do that and that's good to know it's this is this kind of a newer sql one 1.3 does do this but in 1.4 2.0 this is the way you do auto commit if you need to uh and also we just changed the login because people were confused sql alchemy still calls the gb api rollback method uh at the end of an operation why are you doing that it's autocommit well because not rollback doesn't do anything so we made the logging and i'm not sure if i want this login to be so verbose but it says rollback using connection rollback but please don't blame us it's autocommit it's ignored as long as your database does support autocommit is not broken um i could have made this just not log anything but that kind of felt wrong because we're doing connection dot roll back because dbe fights are are weird and we don't want to mess with them too much um maybe we could take the roll back out but like if the user changed the auto commit we just want to have that at the end anyway it's a thing uh and then this illustrates that this row i inserted is was auto committed which is fine um and that is the end of the end so that's engines and transactions and then we go on to schema constructs i'm also not i haven't timed this talk so i don't even know how long i guess the first break is 10 30 calvin that's kind of up to you uh that's halfway through right or yeah yeah it wouldn't be all right so if i get through sql by 10 30 then we could do orm for the second half and we'll be in good shape awesome okay okay let's do the next thing back to the slides and i'll put the chat i'll put the chat oh don't do that so the next level is table metadata reflection ddl what is database metadata excuse me popularized by martin fowler in a book that i liked a lot called patterns of enterprise architecture database metadata describes the structure of the database tables columns constraints in terms of uh for us uh data structures in python the format followers probably data structures in java or something like that um when you have this database metadata that describes your tables and your other stuff it serves as the basis for sql generation of sql strings as well as object relational mapping when you have your metadata structure you can use it to generate ddl to a blank database you can build up a whole database to create database create tables and everything from your metadata structure or if you have a existing database and a blank metadata structure you can generate from the database back to your metadata concept construct i'm going to close the chat a little bit later so metadata can it goes both directions you can make some metadata in python and then make a database from it you can take an existing database and get a metadata back from what was there uh and by doing so it forms the basis of this tool called sql alchemy alembic which is the main migrations tool available for sql alchemy and that's the introductory meta so we can go to the slides so most people if people on this talk if you see alchemy this this should be pretty familiar it's pretty basic uh this is how you make metadata for a table um there's typically going to be more than one table uh so we associate those tables with a collection called the metadata object uh metadata is just a python object with some dictionaries and stuff inside of it it's not that interesting so you make this metadata object first that's going to be where i have a bunch of tables you can have a lot of metadata objects if you want it's kind of a matter of how you want to organize your things um and each metadata has zero or more tables inside of it and other xc sequences also but let's go over the talk so if i want to talk about a table uh called user account i can set up a python object that looks like a table uh and when i say looks like a table um the table uh fun class in sql alchemy is intended to look like a sql create table statement like it's not accidental how it's laid out it's a little different than a sql create state create table statement but it starts with table like create table and then a name and then it has the list of columns and also you can put constraints and constraints and stuff in there too so if you know to create table statement it looks like this should be pretty obvious that this is a table named user account which will have three columns id username and full name uh this is an int string in in sql is this type called the varchar usually it could be care but you we do var characters variable length there's also text anyways park here um nobody equals false indicates if the column is capable of storing a null value uh and normal nullable equals true is the default which is what it is in sql most of the time so this is not in a database this is just a python object in my program it's nowhere there's nothing to do with any database anywhere strictly the notion of a table that could be somewhere but it is nowhere yet it has a name you can look inside the name it has a attribute that's very important called dot c which is an associative array of column of the columns so if you want to get at those columns that we talked about user table see id is the id column full name is the full name column and if you put a fake name it should give an error great no there's nothing called following um so the associative array of columns inside the table uh it's a bit like a python dictionary but not totally which i call it associative array um each column also has its name and it has a data type that we put in this is basically looking back at what we just told it but when you get these tables flying around sometimes you want to be able to look inside them and see what's there there's other information that uh sometimes was implicitly created there's a a primary key constraint object because we named one of the columns as primary key so that means that this table has a primary key constraint with one column inside of it and then the table uh when we have these what we're mainly going to do with them later once we've done ddl is they generate sql statements so just for example i can say print i can create an object called the select that we'll see later so a select and you pass it the table you want to select from and if you just print it to see what it looks like it gives you the sql statement so this is an example of where we made this table metadata with three columns and that metadata was then used to generate sql for us um and it's pretty non-controversial in my opinion but if you look at people talking about writing sql versus over ends you think it's a big controversial thing um table of metadata can be used to generate the schema so we have this table object and let's say we have our blank sqlite database so when i make create engine sqlite without any file name that means we're going to use a sqlite in memory database which is uh a great feature for testing and teaching it makes a little database that's in memory and nowhere else and it's gone when you close the connection and they're just great for testing they're great at just building up something quick and experimenting so we're going to make it in memory database and then we're going to say engine.begin because when you emit ddl to a database that actually is something that often needs to be committed not always it depends on if a database does this thing called transactional ddl um the sqlite database actually does do transactional ddl but the python sqlite driver gets in the way and makes it not be transactional ddl because of that implicit begin thing sqlize driver in python says oh i'm not going to that's it that's a create table statement i'm not going to begin yet which a lot of people find to be annoying and they're not wrong but that's how it works so anyway when you do metadata create all you should be in a begin block because it's just something that could or should be transactional um so i said metadata.createall i passed it our connection for connectivity um and then it ran these statements on the database it first uh this thing called check first which is optional it looked in sqlite's two schemas uh the main schema and the temp schema to see if user account already existed and if it was there it would not do it and then they create table and that is in our database so now the sqlite database in memory has this table and now i'm going to slow because i have to speed up so um here's another table with more stuff in it uh more interesting data types like any num [Music] and numeric uh we can create that table and it'll do what it can to create that uh sqlite doesn't have any num so it puts a varchar of one because each of these is one character this is just good stuff we can look inside the metadata object now and see that it has a two tables inside of it in this uh collection called dot tables so it has keys so it has two tables and then if we look at the user account table we can see the table object that's the one that created we get it back um and then also if we want to do this we'll do this again later if you want to make tables with foreign key linkages you use this object called the foreign key at the column level which means that this new table email addresses will have a foreign key constraint referring back to the user account table so we create that so that the the ddl includes foreign key references user account id um and then this is an example that you can look at on the github of a composite primary key with a composite constraint called the foreign key constraint so that's composite uh and when we create those you get a table with composite primary key composite foreign key constraint going back to there and that's schema creating table so then we'll go quick through reflection which is the other way you have a database that exists already we want to look at it okay you can make a metadata object that's empty and then you can talk about specific tables that you want to get back and then you say okay well here's a new metadata um give me the schema for the user account table auto load it with this connection that we have connected so you can do that it'll run a bunch of queries and then you have it back you can have the columns of that table got back and the primary key constraint and as before we can make a select statement from it user account select user account table that's reflection people seem to get that pretty quickly so i'll go through that quickly um if you want more fine grains looking at stuff there's a thing called the inspector which uh is to be used more you can inspect an engine and then the inspector has a method like get table names to give me all the names of the tables that we have in our sqlite database you can get the columns from a table like the columns from an email address and what it gives you here is a kind of a json structure mostly uh and you can just get the raw information about columns if you want to look that and you can get the raw information about the foreign key constraints and also the indexes and the unique constraints and everything else that sql alchemy knows how to reflect and then finally you can also reflect an entire database schema at once doing this method called make another new metadata object you can say metadata dot reflect and that will give you the whole thing back in one shot lots of queries run and then you have the tables are in this dot tables collection you could say table story table and the puppets table and then you have the story table that we just reflected and the publish table and uh ready to use you can use these tables and make sql statements so this is a good way if you have an existing database and you want to make some queries against it you don't want to write out all the python metadata you just use reflection get the stuff back and write the queries out so that's the schema part and that's kind of the basic stuff so any anything with that so far i think most people who've used equity this should be pretty familiar reflection is awesome when you're doing migrations yeah so the alembic migration tool uh relies heavily on on reflection actually uh in the future if i have time or someone does someday we might make it not use reflection because django's migration tool is probably better than alembic in this regard because they actually version your code we don't do that but we probably should someday but not this year sorry for not reflection um metadata table okay so now we're at the third level okay so basically there's two sql expression language things and i need to get done by 10 30 and then we take the break and then we do the orm and then we will have done it all right um also i would note you guys have not seen much new stuff yet the engine stuff was new the way the connect the commit works that's new for 2-0 the schema stuff is pretty much exactly the same in 2.0 it hasn't really changed uh in expression language uh there'll be some new things here and then orm it'll be a little more new stuff there's a question in the chat from vlad yep let me look at the chat i had the window closed yes you get the constraints back you get all the so the question is do you get constraints that are defined when doing reflection you get uh foreign key constraints you get indexes you get unique constraints and you get check constraints you don't get every kind of index yet because some and there's some postgresql indexes that have like functional stuff that we don't reflect yet uh we don't yet get postgresql exclude constraints so you'll get the the easy constraints basically you'll get the foreign keys for sure uh and you'll get normal like you'll get excuse me rudimentary unique constraints indexes and check constraints yes so the next question is can i transform direct reflective tables into orm or m classes yeah there's a there's been various approaches to doing this the current approach that's in sql alchemy is called auto map so it's a tool that you it does reflection and then it will figure out how to make an orm model from that mapping dynamically now there's another tool that's third party called sql auto code that actually generates the files like python code for you and that's probably more of a robust approach because it gives you the the models and you could change the code yourself it's not on the fly sql alchemy's auto map is on the fly um and it's very very useful um i would not use it in like a production web application that's running at high velocity because you don't want to have to i mean like i think if you're running a non-trivial application your models should be first class defined in code um and that would mean that would match what your schema looks like but if you want something more of a one-off you can use the auto map to do that uh the automap is based on a much old a really old tool called sql soup which was also physique alchemy which doesn't work anymore but it does the same thing so yes the answer is yes uh but i wouldn't build a facebook on that all right so now we're on to core sql sql you'll notice that change expression language the sql expression language builds upon the table metadata that we just did in order to compose sql statements in python so we will build python objects that represent individual sql strings statements that we would send to the database these objects are composed of other objects that represent some unit of sql like a comparison between a column and some value uh a select statement uh conjunctions uh you know case statements cast statements all kinds of things uh we work with these objects in python and we build we kind of compositionally build a structure that represents kind of like a parse tree of a sql statement and then they are converted to strings when we execute them that's it sent to the database you can also print them to see what they look like and um when we write these sql expressions we're doing this compositional object building one piece at a time thing that uses a python it uses a programming pattern called method chaining method chaining is actually i think from gang of four and it's a pretty obvious technique that everyone's pretty familiar with these days but it's the one that we use um older versions of sql be very very old did not use method chaining we had a select with tons of keywords in it and we don't do that anymore and one of the things that has changed in in 2.0 is we are fully removing the old keyword based stuff that people don't even know about anymore because i took it out of the docs years ago but it's still there so if you look at some very old sql alchemy from 10 years ago you'll see a lot of keyword stuff no more so then we go on to the two sql expression packages some water for that one so we start out again just like we did before we have this um table object uh this is not exactly the same as before it just doesn't have the nullable constraint but it's same idea um so and then like we did before we're gonna going to create all so we have in our memory database we have this user account table that we can run queries on um so as we saw earlier there's a dot c array on the table that gives us the column objects that we have and um we can do things with them this is one of the little if you have not seen sqlite before this is one of the weird things it does uh and i actually got this from a very old tool called sql object written by a guy named ian bicking and he overloaded python operators like the calves laughing that's where i got it i didn't i did not think of this i was like wow you can do that the eq python has this thing called magic methods even though magic is bad like the eq method it's called dunder eq dunder ne dunder lt dunder gt and what these do is they return they're a function that you can implement so if you have an object with a custom method of comparing it you can do that and then what you're supposed to do and concerningly this is what the python type subs insist that you do which we don't slight problem is return a true or false but we don't do that we actually this object is is evaluable as a boolean but um if you compare a column to a string a python string you get not true or false but you get this weird object called the binary expression and that is because it has a left and has right and it has an operator yeah so that's a boy that's a binary expression with a left right and it's a so it's basically a little container that represents the expression we just typed it didn't actually give us the answer it just gave us another object asking the same question what's that about so you know basically this is going to be used to generate part of a sql statement so user uh you know usertable.username equals spongebob which is the theme of our tutorial spongebob squarepants um you get back this if you print it like a string you get a string of it you get back this string that looks like sql it has actually a bound parameter called username underscore one which was dynamically generated the name spongebob vanished it doesn't seem very useful um it actually is there so what actually happened when we took that object and we stringified it is actually this thing called it did this thing called dot compile dot compile made this object called a sql compiler in this case that is another python it's got stuff inside of it what it has inside of it is the statement and then in a separate little collection it has the params so basically it took our python literal it shuffed it out of there because that's a literal and that could be coming from a web form or untrusted input it could be from anywhere it's a python literal uh we take that away we put it in a bound parameter and when we invoke this a statement that includes this little clause inside of it it'll all work out the spongebob value will be sent as a parameter so this is how sql alchemy um kind of has a role in security it which is that you don't have to remember to use a bound parameter it's going to do that for you anytime you're sending python literals to it any kind of input that could have come from a web form or whatnot if you go into the sql expression it's going to kind of automatically make sure all that stuff gets sent as parameterized which is both for the reason that the parameter will be fully uh escaped from any injection attacks uh usually this is done by the driver not sql alchemy and also it means that the statement itself is written in a way that is agnostic of the literal values that you put inside of it which means that we can cache your sql statements in memory and also the database can cache it too if you the database also will cache query plans based on this as well so you definitely want to use bound parameters uh because it allows for caching and security on both sides of the equation uh okay so building bigger sql constructs we can take we have the the equal expression we can take two of these binary elements and put them together inside of another construct called the or conjunction so that will give us user account as that username equals that or that and then we can take this pattern ad nauseum as they say which i believe means until you're nauseous i'm just trying to make calvin laugh because they could see him um it does actually mean that um we can take the or put it inside of an and with some other stuff so we can build you know a print you know a kind of a composite you know expression here so i think people kind of get what i'm doing here is you're making uh conjunction objects and building up a bigger statement so now we can move on well let's do some more operators first operators you have greater than equals between uh you put them all together between it's kind of cool it's between x and y it's kind of a cool operation you know not equals you can have a comparison to null and not null in relational databases you don't want to say x equals null because null means undefined you only want to do is or is not so sql alchemy is smart enough to say if you're comparing to none make it is not null or is null um operators are type sensitive so like the ones that where it applies like the plus sign can be addition from numbers or string concatenation for uh strings like the double pipe symbol is the general sql symbol for string concatenation uh the in operator is very cool and is very enhanced in 1.4 what this will do if you have a username in three values it's going to uh do the special symbol that looks very ugly uh and what happens is after the string is compiled when it gets into the database we will do a second replace on this to actually break it out into the individual bound parameters because relation the python database drivers when you do an in you have to usually there's a few that don't need this but usually you have to send each in value it's a separate bound parameter so the in operator knows how to break them out into one two three values and if you try to generate an in for an empty set we have this new feature it's kind of it's it's probably a one three but it's totally standard in one four which is it does in from an empty set and to generate an empty set on the database every single database has a different syntax that works here none of them work consistently everyone's got like select from subquery or do this or do that um if you have an in from an empty set an empty set um actually simulating that on the database is the best way to get the exact behavior of how in for an empty set would work uh and for many years we didn't do this for many years sequoia would generate warnings we can't do this we can't do it oh no we don't want to do false because too simplistic doesn't work it works now you can do an in against anything no more errors no more exceptions no more warnings um okay so now we can start working with some statements insert and select are the first because we want to insert some data so we can select it back in insert you say use your table like any table object you want dot insert now you have an interest statement and now you want to generate you want to generatively add upon it some values to insert into the table uh and these go values work with the simple way is uh keys are the column names and then the values so we can begin the transaction because it won't commit unless we begin the transaction and run the statement and you can see the bound parameter format because we actually sent the thing to sql light it now uses question marks for bound parameters but that's sql lights down parameter style and it passed the two parameters as a sequence rather than a dictionary if you ran this against postgres you'd see names here and a dictionary down here so that's where sql alchemy is figuring out the six different name parameter formats for you it's certainly a time saver uh so we just inserted a row we inserted spongebob squarepants into our user account table so far so good yeah mike you understand sql so i don't have to yeah i want you to understand single the first slide was like you need to know sql you need to know this is automation see what we're doing is we're automating like i'm not trying to hide it i want you to see it we're just making it easier to type it's type checked you know my pi typing type checking is going to make this even better um so here's some more ways to insert rows if you don't want to use dot values which you usually don't have to it's better to just say connection execute insert and then send it along the dictionary of what you want or send it a a list of dictionaries insert multiple rows and it'll do that for you um we've also made a huge performance improvement on the psycho pg2 driver in 1.4 uh psychopathy 2 had a very big problem with execute many for inserts where they were very slow and now they are very very fast in all cases so enjoy mike there's one more question i see it yeah i'm not looking i'm now looking for the little red one pops up so now because i don't want to keep the chat and i have to scroll down um you can use joins in sql core or orm depending on what you want to do using the sql core or orm now and when we get to the o-ring you'll see this only depends on what you want to get back because you make the statement in the exact same way now if you're working with core or orm we use the same select construct and you'll see that later um it just matters on how you want to run it if you want to get objects back or not um so the answer is you can join in sql in core oram and it really doesn't even matter it's because it's sending the same kind of sql to the data the database has the same amount of work to do it's just a matter of how you want to do the result processing that's where sql actually has more work to do when it gets orm rows back and that's it so there's no as far as doing a join either way uh but you'll see it's very consistent now it's kind of you know kind of kind of a no-brainer because you're you're using the core selected to join no matter what um so select which now that we have some rows let's select them back um let's make a select statement that is uh select so we go from clogging and first import select that's the the construct that we use so select does not typically it can't there's a table does select method however because we normally want to be able to control what columns and tails we're selecting we use select as a standalone function so we import the select function from sqlocamy and then we can make a select statement by saying select the username column and the full name column okay that's one thing dot where and then you put the binary expression that we saw before in here you put them all together and then you run it get your result back and then you go for row and result get the row and that's you get and that's uh pretty pretty cool so you get user name full name and wear this and you get your result back because that's that's the basic round trip for using a select object whereas in the first chapter we use this text thing this is using select to do kind of the same kind of thing a select statement so you can select all the columns from a table by putting the whole table in the select and that will give you the whole thing and the columns will be in the order that you declared them in the table object uh you can do where dot where and you have order by and you have group by and you have having you have a lot of other methods like prefixes and suffixes and hints uh so we can get a statement with more stuff going on uh we can say where multiple times dot where this dot where that and they get joined by and automatically so we have two different where username is spongebob and full name is spongebob squarepants you get the ands right here um and you get a result back so let's look at some more results so basically what i'm doing this is the part where i'm going to go a little quick for the time and if i see the little number little red one pop up and then i'll open the chat or if someone wants to whatever um so we have a result let's get a result back and do some things with it so we're in core we're not using orm at all the result uh has a familiar method for people that use orm called dot one and one is a method that gives you one row back uh and it expects there to be one row if there is not a row it's going to throw an error and if there's more than one row basically a second row it will only fetch the first two rows it throws an error this is usually when you have like a web form like a web api and you want to be able to do like a get of an exact primary key or exact instance you want one row and if there's no row you want to return like a 404 one is good for this if there are no rows or there's many rows like here's a query that'll return multiple rows result.1 will throw an error called multiple results found so you can actually say you can use one and catch multiple results found too many and have and handle it very specifically so we're very good at the one or none case there's also a one or none which will raise for multiple results but not for zero it'll give you none if there's no results back at all right so far so good um results have new features like you can slice up the kinds of columns you're going to get back so here is a query that will give us three columns in each row but we want just two of the columns and we want them in the reverse order so you say for use full name username and result.columns you name the columns you want to get back and that's what you get back so that's that's going to be hand this is this is more of the orm needs this kind of thing for a lot of cases uh so if your result object has the different you know you can just change it around and you could also just query for that stuff in the first place but you know why not have options um okay the more case for this for the the column slicing is the thing called scalars uh which is when you have a row and there's only one column that you care about usually the first column but it could be any column and i don't want rows back i just want to get the objects back so if i want to get a list of strings i can say result that scalar's full name and get just a list of strings that are not in rows and this is also very critical in the orm where you're going to want to get a list of objects back and not rows with objects in them you'll see um update delete statements um are kind of like inserts and selects kind of mixed together sort of in this as far as syntax uh updates has the values method that insert does and it has the where clause that select does kind of philosophical um so uh this is an update uh the values is actually going to do the set clause of the update statement and then the where clause update user account set full name equals patrick star where user name is patrick right um like an insert it can also generate the set clause based on parameters so i could just say update the rows where username equals patrick but i don't set i don't tell what i want to set later when i go to execute i can put whatever i want in the dictionary it'll set all those columns um if you send multiple dictionaries they have to just be the same sets of keys uh and also you can update to uh and also instantaneous too you can update to sql expression so if i want to update a column set it to be some kind of arbitrary expression like here for no reason i'm going to concatenate the username and the full name together with a space inside of it i could do that i could see you update user account set full name equals and then it'll put the sql expression here so you can do that too and there's more stuff too if you can you can also update a json array element on the left side that's more advanced stuff um and then a delete is kind of uh an update without a without a values clause so it just deletes where something all right so that's the first part of sql and then the next half we're going to do joints and sub queries so far so good okay oh i need that p now we're going to work with two tables um the user account table like we had before and we're going to make the other table called email address in this case so you have user accounts and then a user account can have zero or more email addresses associated with it so to do this this is called a one-to-many relationship right so the email address table has a foreign key uh which refers back to the user so every row in it is also not null so every row in the email address table will refer back to a particular user account row and many different rows in an email address can refer to a single user account row so we're going to look at how to join and slice up tables on this criteria as before we can do the create all get our ddl so now we have two tables that we will select data from and then we're gonna do a big insert data we're gonna only work with selects in this section so we're gonna put a whole bunch of rows well not almost three rows and user table and then we're going to put some rows into the address table that have user id in them that refer back to the user table okay and there's those statements being run so we'll show more capabilities of select so the first thing you do is select from more than one table so here's a select statement that is select from usertable.username and addresstable.email address those are two different tables what happens if you do this it's going to by default say select user account username email address from account comma email address and that's a query that nobody wants to write and the reason is because it does this thing called a cartesian product which means it's going to get every combination of rows from both tables because they have not been equated to each other uh sequoia 1.4 now has this great feature that everyone will appreciate highly that it detects cartesian products in your structure and it will send you it's still going to do the query but it will send you a warning uh select statement has a cartesian product between email address and user account you should apply join conditions to resolve and the reason you're going to want to resolve that is because when you run a query that has from x comma y without equating them you get every combination of every row in both tables together it's very redundant and the cartesian product is the best way to crash your database if you have a really if you have two really big tables this will blow out your memory so you got to really watch these things uh so the way this works is that when you're in development and your query has a confusion product but the database is small you might not notice it because it worked anyway it was just like 10 rows and then you put in production and it blows over because you have a million rows so the cartesian product thing is going to give you this warning based on the structure of your query so the structure of a query is what you're committing to your source control that's the structure that doesn't change so this is a check this is checking the structure of your query ahead of time so that you don't have any cartesian products in your source code and then when you put your code in production i see this question it won't blow up okay let's do the chat yes so the question is can you turn the warning into an error python has this thing called the warnings filter and you can use the warnings filter to turn the warning into an error and you just send you say warnings filter warnings uh and then you you really could have all seek cognitive warnings turn to error so the answer is yes use the python warnings filter to do that uh and if you have a test suite that runs into pi test it probably you know you might need to put this fill yeah use it use filters use pi test plug-ins turn your warnings into errors for sure uh we don't we don't the thing is this you know people could have people do have some cases for fusion products once in a while but yeah the answer is yes um [Music] so when we want to join the two tables together there's a new method that you've never seen before called join from and it also works along with a method called join which was in sql alchemy 1.3 and further previous but it's very different now in that it actually does something useful um so select usertable.username addresstable.emailaddress dot join from the address table you join from the user table to the address table please do that for us okay and then you'll get a join and now you get only you know five what really like you got a reasonable number of rows so it runs select with join and it seems to have figured out how to join the tables too because these two tables have a foreign key between them that is not ambiguous there's just a single foreign key between address and user so second is like oh i can join those on the foreign key now um there's also a join which works like it does in the orm for those who know join you can say user table user table and address table dot join um dot join without uh specifying anything else is going to use the the leftmost table as the the from table and it'll join to the address table on the foreign keys but join from is more explicit if it's being is not sure where to join from join from is probably better to use for starting your chain of joints and then dot join is better to continue your chain joints but you can use either one we get the same thing and then um if your tables have more than one foreign key constraint or they have no foreign key constraints or you want to just make sure it's explicit in your code that's fine you can send another argument to join or join from which is one of those binary expression things and instead of relating uh a table column to like a python string we're relating to call one column to to another column object to get our join condition so we have this all three of these examples generate the same exact um these are just different levels of explicitness uh and the join uh method join from will give you throw an error if it doesn't know how to join if it says this is ambiguous there's too many foreign keys or i don't know how to join this please tell me you would go down to joining in the more explicit ways so sql alchemy i should probably find a way to write about this has one of his philosoph one of its philosophies is that it kind of guides you to the correct code by throwing very informative errors it it looks a lot for you're doing the wrong thing okay maybe you meant this if people have worked with it will know that the way they got their mappings to work is that it raised all these different errors until you finally got it right and that's intentional that um and it really goes to and then here's here's a little tidbit that you probably won't hear me say much this is the reason it's called alchemy because the you know cartoon character version of the alchemist constantly blowing himself up that's the whole thing that's actually really what this is like you you're doing alchemy hmm maybe this will work oh cartesian product hmm maybe this works oh this ambiguous foreign keys okay now you got it the difference between alchemy and sql alchemy is that you actually get gold at the end it's all for my one person audience here okay crazy query time um for people who are okay pretty good at sql you might know that you can refer to a table more than once in a query you can uh use the thing called an alias you could say select a star from table as alias one uh table two as alias two why do we wanna use an alias in a sql table because you might wanna get rows from the same table in different contexts so i want to get uh two i want to get a row from email address and how it relates to the user table in one way and then i want to get another uh row and how it relates to the user table in another way uh and that will actually name in this query it'll name the address table twice using this thing called alias so when we write sql by hand we have to say select star from account table as account table alias or whatever um sql alchemy takes the whole job of making up those names you can name it if you want but cheap me automates the naming of those names because they don't actually matter as long as they match up each time you use the address alias it uses the same name each time you refer to the address alias two it'll use a different name those names themselves are random that way um you can add more aliases you don't have any issues with oh i named this that name is that what's his name uh when you work with the full-blown sql alchemy core expression language approach you don't worry about names anymore because it figures them out for you everything the referring to a certain table is based on the python object identity so we have the address table object we've made this new object called a dot alias called address alias one we made a second address alias object called address alias ii these two variables uh now refer to alias objects and as we refer to them in our select statement they will come out to refer to those certain things so we're going to select from the user table and we're going to select the email address from each of the address alias tables you know derived tables you know we're going to join from the user table to the first alias and then we're going to join from the user table to the second alias so this is kind of a y shape join goes from user to both user address alias 1 or just alias 2.
and then we're going to add a where criteria for both of those aliases separately uh and this is more advanced sequel if you just know stocks are from tables a little more um and then you get the query uh did the right thing where you have it's selected from user account then it joined to email address as email address one and did the on clause and then it did a joint email address as email address well three because the number two got used up here on user account uh use email address 3. user id so it basically worked out the on clause and everything we did and then did the where criteria separately so we got spongebob having both a gmail address and a spongebob.com email address that makes sense i don't see my little number popping up so i guess that'd be up there's a number someone popped up chat how long does the aosd available to use it's a python object so that's that's it that the python object that you have as long as you hold on to that object you can use it everywhere you can use it yep there's no thread safety implications you can just it's just a python object it's it's fixed um sequencing is really trying to make sure these objects are immutable which they're not but they treat them as immutable you know they're pretty immutable we don't so that meaning when you have an immutable structure you can send it around everywhere and not worry too much about somebody changing it um and then finally well not quite finally but now that we know how to do aliases of tables you can do aliases of a whole select statement as well so you have you can derive a table into an alias you can also derive another query into a subquery that also acts just like an alias and in sql alchemy one three uh this was we we would say select we would we refer to a select construct and then say dot alias so now we're referring to the select construct and saying dot sub query which is more explicit so here we have um a select sub we call it sub q for sub query let's do the query where we use the user table username and the address table email address join from user table to address table and then we're going to call it a subquery which means that this is no longer a select statement it's now this uh selectable thing that we can add to a bigger select statement called a subquery when we have a subquery it acts like a table it has a dot c attribute it has columns that it exports so in this case the subquery will have the username and the email address columns on it because that's what's in our subquery uh and then we can select from the subquery and then we can do criteria based on the sub query as the word table it's called a derived table in in the relational whatever so and again we see the the um the anonymous names of things the subquery gets this name called a non-one which is based on it's gonna be a non-underscore number but it's based on a random token so we're going to select the username column from this derived table called the subquery that is our little subquery as a non-one and then the where clause is against the outer a non-one so that's the general idea of how to write a subquery and how to how to use it um and for people that have uc qualcommy this is old hat for people who are this is new to that's like wow that's kind of interesting um so far so good um and then we can make a more complicated sub query that's going to use some aggregate sql functions let's get um the user id column from the address table and let's also count how many rows are in each grouping of user id so if you know about sql group by group by is usually used with sql aggregate aggregate functions aggregate functions are those that were are going to do some kind of calculation across a group of rows uh counting is the simplest one uh the max and min functions will get you the highest at the lowest value uh you can write aggregate functions to do things like standard deviation and stuff like that there's all kinds of aggregates count as the most common so we're going to count the number of rows within each grouping of distinct user id so spongebob i think has two email addresses you get two for him and then sandy has one email address you would get one email address for them um and then we're going to put that inside of a sub query because this sub query is giving us just part of the information that we actually want this gives us the count per user id but we also we also want their names so let's put that into a bigger query called username plus count so we're going to say select another select statement okay select from us select from our database the username column as well as the count column from this subquery the reason we use the word count is because we labeled this thing as count when you use sql functions with the func namespace uh you're going to want to use dot label of some kind because the func doesn't really have a name it's not going to call it count because it's it's just it wants you to give a name to it so you should give a name um select the username and then the count from each sub query and you're going to join from user table to the subquery how we're going to do that and order by the username now this is funny because this is a select statement it's a sub query there's no foreign key from a select a subquery to the user table so how is it going to join well it turns out that c me has this thing that took me 10 years to get right which is it can do this thing called column correspondence which means that it's going to look inside the sub query let me look inside the subquery oh look there's this column inside of it which is user id and there's a foreign key to that so i should join to that oh i can do it it's not good but it's not going to join to the address table column itself it has to join to the user id column in terms of the sub query this is not code you want to write boy was it hard but now you have it so when i run this code you get select user account username a nom1.count from user account table join to our subquery on user account id is a non one user id because that's the column that is derived from this foreign key column and then you get your result back and that is one of the this is an example of automation right sql i've just figured that out for you now you could write it yourself sure but this you know should be fairly obvious what it says it automated it but we understand what we're doing because we're not that we're not hiding it um okay now there's another thing in sql that is super popular now it's because the postgres database is big on them called the common table expression a common table expression is just it's like a sub query except it's not in the from clause it's like above your whole select and it's in this thing called a width and con table expressions i think are popular for a few reasons they offer some new features like recursive queries and they also can be optimized they can produce very optimized forms in some cases as postgres can optimize the cte better than it can as a subquery uh there's a whole set of rows that you can get from and then and so also people find them more intuitive sometimes uh i'm not there yet but i get it um in sql alchemy the cte looks the same in python we can take the same exact select statement that we had before as a sub query this address select which we said address select dot sub query and we could say dot cte okay so now you're not a subquery anymore now you're a cte okay what does that mean well we use it exactly the same way there's literally no difference in this i can't do backwards in this this thing i'm running but this is the same syntax it's right before we had address subquery now we have address.cte uh so you can flip it around now of course it's not that simple ctes have all kinds of other goofy things they can do but it's in the docs not here so for the purpose of this you can see if i just run that we get the same results back uh but you see the sql is with instead of is selecting from subquery as an on one now it's with a non-one as and then there's the thing that was the subquery before and then again we have the same logical select user account a normal account and then it joins in the same way so a cte from a very simplistic view is kind of like a sub query that's on top of your select uh but then it allows for some very crazy kinds of stuff and you can use them with update delete statements in in uh inserts also in postgres they go nuts with cges so we have cds what's next okay finally this is probably the last part um correlated sub queries and this is the last thing so we'll take a break after this a correlated sub query is a subquery that is in the columns clause or the where clause of an enclosing select statement so whereas the previous sub query we did was a from clause subquery that says select star from a sub query a correlated sub query is a sub query that returns that should return exactly one column in one row only and it is used as a column expression in a bigger query and they're usually but not necessarily what we call correlated to other stuff in the table so when we want to do that we tell sql alchemy our intent we make a sub query that we want we want to select the count of address rows as a column correlated to each row of the user table so we make a query like that and we say dot scalar sub query to tell sql and said hey this is going to be a scalar sub query you should now behave as a column expression and do correlation as needed uh when we do this it refers to this sub queries to print it out when you print the car latest upgrade by itself without any context it says from email address user account which again well yeah for it has a rare cluster here so it's not a cartesian product but it has the two tables there so we're not currently into anything here yet because user account is right there so if we take that correlated sub query and we use it as a column in another select statement now it's going to be different it's going to be select username over here and then select this count we did from email address and the other user account from clause disappeared because the correlated sub query figured out oh the user account is out here so when i join from user account to email address user account is actually on the outside that's called the correlation subquery so sql alchemy has logic to figure these things out and you can also tell it explicitly to correlate against something if that doesn't work or you could tell it explicitly to correlate against everything except that and that's the car that's upgrade which people often will map make column orm mappings that will have a certain column doing that so that's that's that so this is basically the intro to sql expressions the main thing that was new here that's not in one three at all is select.join and select that join from those don't work at all in one three there is a join method but it's totally broken so that's totally new stuff and it works a lot like the orm query does uh and then also the scalar select and subquery methods uh those exist in one three with different names those are new names oh there's a little addendum to sql expressions if you already have the perfect sql use text so um i get these questions a lot like i'm having trouble making this and it's usually a postgres query i'm having trouble making this query and sql alchemy i'm like put it in text it's done like you don't have to do it so okay why was this whole sql expression language thing we just did why would we use it why do we need it or why do we not need it because maybe you don't need it and you don't need it you don't need it if you don't want it what it gives you is composability like you have this select you want to add other criteria to it you want to just uh iteratively build up a query and that's very handy and it you know also you know works with your python id and everything uh and then the other one is database agnosticism our query will run on lots of different packets uh depending on what kinds of features we used in the query but you know when we build up a query we have a good you know platform for a lot of different databases because every database has all kinds of different things mostly with data types and there's structural things um what if we have the perfect sql query already use text you know if there's no need to you know if you just want to run this query and it's usually some statistical thing with a lot of weird functions and casts inside of it if you don't need it right now in the expression language just run a text for now have it in your code you're fine just you could change it later you know there's no you know you don't have to do it so that's just a little addendum to sql expression language and you can do text with the orm um it's not in this tutorial but there's some it's in the tutorial online and people don't seem to do it much but you can you can write a textual any query you want as long as it has the columns that the orm needs to build an object you can do it and it's sql is always supported that uh we do it much better now there was some problems with it but it should work no one seems to do it much uh just remember if you write text don't don't stick any user input in the text always use a bound parameter and the text contract has a way of doing this you put brown parameter holders just don't ever concatenate untrusted input into a sql string that's the golden rule don't ever do it um okay so then we can go to the object relational mapping so the whole first hour and a half of this we did not do object relational map you know when people talk about orms there was a very there's a very specific thing about i seek alchemy that a lot of most tools don't seem to have which is that squawking has this whole entire thing that works without any orm happening whatsoever um the reason that was no rem is because we were not using data classes of some kind we were not using any user defined objects we were just getting we were just getting rows back um so you can use uh sql automation tools without using the orm at all uh but now we're going to do the orm so um just to introduce kind of quick so we have till 12th okay sure it's time to learn okay what's the what does orm mean uh to sequel me uh it's the process of associating object-oriented classes with with database tables uh we refer to the set of object oriented classes as a domain model this is a kind of the formal name the domain uses your domain model or your business model uh the most basic thing the orm should do is if you have domain object you can save the domain object to row the table and if you have row in your table you can load it into your domain object that's the most basic orm thinking if you wrote your own application that doesn't use an orm but you wrote this you wrote an orm you are using an orm just your own that's that's it if you have an object come from your row you're in you're orming um so most orms that are you know downloaded you know uh do associations between tables uh funky associations that can give you one too many or many to one and there's also uh the this the many of the many using i guess they call it a pivot table or it's called we call it a secondary table association table uh so rms do this kind of thing for you because it's kind of tedious to do it by hand they provide a way to query the database in terms of your domain model they can represent class inheritance sometimes they can sometimes do sharding of your data if you want to have different kinds of data in different databases like you want to have all the a through a through f and one database and g through m and another database that's called sharding uh concurrency patterns uh like uh if you have an object and suddenly the row is missing from the database that means some other transaction has just taken it out that's a concurrency pattern uh row versioning means that if your row is version three and you go to update it and the update statement sees that the version is at seven that's a concurrency problem that means someone else has put three three versions of your row and you're you're behind so you're not up to date it gets rejected it's concurrency feature um data validation data coercion uh you know set setting dates on your model objects i mean this is clearly a little bit outside of validation is more of a python concern that's handled by other python language but we we have structures to do this um flavors of orm uh everyone talks about active record data mapper uh for me what active record in data mapper means is the object itself has its own persistence method stuck on it so and this is every orm everyone writes you know is you have your user and then you say user.save and it magically goes to some database somewhere somehow but you said save so it's gonna go there and then oops and then you uh can query later and then you can change it then you say dot save again it'll do an update that's activerecord meaning that every object is an island onto itself every object is is a roman table as far as how they integrate with a transaction there's probably some kind of global context manager or maybe you pass something to save like the the notion of a lot of objects working together in a transaction is a secondary notion right it the active record means that each row it's just it's just to run a table what do i care twice put it in you know and i have 25 other rows well they're just they're going to go into who cares that to me clearly i'm being derogatory sorry active record data mapper is more about no no no first of all we have to connect to the database okay second of all everything you're going to do here because you're not just doing one row you've probably got a bunch of rows is going to be in a transaction and you're going to tell us when to commit the transaction so this is datamapper um okay you've got a user you have an address you've got other stuff give me all those put them all in this thing and this thing will persist them this is the persister object this is the you know mediator you know it's explicit there's an explicit mediation between your objects and your database and it makes you be more explicit about where your transaction is what database are we talking to where is this even happening uh we're not using globals we're not using thread locals we're not doing that stuff we have this is my transaction this is where i get my data from oop and then i did that again and then you get yourself back so that's datamapper um two styles of configuration uh declarative style is what i refer to with most orms everywhere do is you have classes that uh name the attributes and the attributes themselves refer to how they will how they will be persisted in the database um that's declarative style this is what c column usually does uh there's the other style i now call imperative style meaning you make a class that doesn't know anything about anything at all about the database and then somewhere else you have this mapping process that will link some kind of table metadata with that class i was going to take comparative style out of clogging 2.0 but a lot a lot of people want to use this style still so good they can use it one of the things that's kind of not wrong but misleading about imperative style is that when you say class user with just a constructor and it knows nothing about the database the mapping process is going to instrument this class to have these attributes on it and the class will take on all kinds of database specific behaviors it will take on all kinds of behaviors that are specific to the qualcomm erm so you really can't say that the class is totally agnostic of the mapping process because you might have areas where it accesses the full name attribute and it assumes that that was persisted from somewhere so this that's why i think in peristyle is a little bit of a lie but it still uh is applicable people want to use it and it also is applicable for uh to some extent to stuff like python data classes which you can map now uh you can map those declaratively also anyway those are the two styles called mrm is essentially a data mapper style orm most users use declarative style uh the urm extends core extends the sql expression language to work with domain classes uh key features include this thing called the unit of work pattern uh which is a system that accumulates pending changes and then transparently sends insert update delete statement to the database to persist those changes in one in a batch the identity map is very important as we work within a transaction uh python objects are kept unique in memory based on their primary key identity so if you select for the same primary key identity twice you'll get the same object back and that's important because it keeps it in sync like if some part of your code is changing the object and then some other part of your code selects the same row again and does something else those are two different you want them to be synchronized in in the transaction and that's because they represent a row in a transaction and that's that's the row in that transaction uh lazy eager loading related attributes and collections can be loaded either on demand which is called lazy loading or upfront which is called eager loading and we'll if we get all the way to the end of everything in this tutorial that'll be at the end lazy and eager loading orm walkthrough okay so we start with the orm basic and like the sql tutorial the basic part will deal with just one table and then the advanced will deal with the two tables and then the joints so keeping in mind that uh for those of us who know sql alchemy the way that you usually call me right now still works completely you still have the declarative base you still have all the same stuff and it's all there but there's a new way to map which is what the declarative base now builds upon called the registry and the registry is uh kind of like your declarative base except it's more general uh you can do uh imperative mappings with the registry as well uh there's a lot of cool stuff that the registry can do like configure the mappers within it and not everywhere else so we made this object called the register and it's kind of like the metadata you know it's kind of a collection has a metadata inside of it and we want to map a class the most immediate way and this is not the only way you can still use declared base is to use it as a decorator uh and this is probably a more slick way to map than the declarative base was this because the declarative base uh for those who know it uh uses a python meta class uh which is fine uh the python media class gets in the way for people who want to do things with medical classes uh the you we you never really needed declarative based it declarative meta just was there as an implementation detail um you can use the decorator and you can map the class and it doesn't have to extend from any hierarchy at all and you can use whatever meta class you want it doesn't matter because sql doesn't really care about meta classes so that was one of the things that declarative meta was kind of misleading people thought that middle class was involved it doesn't have to be so this is a user class that's being mapped to a table and you'll notice with declarative mapping and again there's options for this we don't have to use the table object explicitly we can say user and map this user and when you map it create a table for us called user account based on table name and we will map these three attributes user id username and full name and when you map them to a table please make the table have these columns uh an integer column a string column a string column there is a syntactical helper here which is that we don't have to restate the name in the column if you remember recall from the schema chapter we columns had a name in them you can omit the first argument and just put the the data type uh this reaper is just for this tutorial so you can see what the objects are when we select them you don't need to have a reaper uh and also you notice you don't need to have a constructor uh the constructor if you don't make one you can make one yourself if you don't make one is automatically generated against uh one that will accept these three names as optional keyword arguments so when we did that mapping uh there's now this new dunder table attribute that got stuck on it by the mapping process the the registry mapping decorator did this i'm trying to move this uh user.table so here you have user table name is the table name and then user.table is this table and then we can this won't work they didn't import select from sql alchemy import select you can select it print it like that so a table got generated for us using declarative style and has been associated with this user class and the user class has been mapped to this table for use with the orm and the object that does the mapping is this thing called mapper which you don't generally have to deal with but it's there so the object has a default constructor so we can uh send it some keyword arguments and they're all optional because some of them might be auto generated like for example the id of this uh row will be automatically generated by sqlite cause sqlite has a auto increment feature for a single integer primary key uh nothing's happened with the database by the way we haven't touched the database at all yet this is just an object in python and it's just an object in your python in your python code um if you look at the id attribute that's going to be the primary key it comes out as none okay so it seems like it was already there even though we didn't assign to it it seems like it's already there so using our registry which now has this table object in in its metadata collection we can create do the create all that we've done on all the other tutorials we can create the schema using map registry.metadata.createall there's your user account table like we've seen before and now we want to actually do things with the table okay so here's where the data mapper starts up we work with this object called a session uh and there's a few ways to do the session but the the most common way is to make this factory call the session maker the session maker is a thing that will give us sessions and the reason such a maker is convenient is that you can hand it all the arguments that you are going to use for your sessions up front so we want this session maker to be bound to this engine that we've just used to make our tables and once again there's a future flag that will turn on a couple of little it's it's a couple of transactional behaviors change with session if you turn the future flag on which in sequel me 2.0 this flag will do nothing in future will be the only way that it works so you make your session maker and uh the convention i usually use is to call it uppercase session because it's kind of like a class that will make new objects okay so we've we have an engine we have a session maker that's going to bind to the engine let's make a session so then we go here and we have session so now we have this thing that is bound to this engine it's going to be able to work with objects um and like everything else in sql alchemy nothing has happened yet it has not connected it has not looked at the engine it hasn't made a transaction it's not done anything whatsoever it's just an object with an object inside of it everything in sql alchemy as far as engines is lazy initializing so we have this object called spongebob just to review there's spongebob uh and now we've added that object to the session using a method called f so session.add and then you send out the object that you want nothing happens nothing has happened yet nothing was updated inserted uh we can look at the object in this collection called session.new uh which says okay your spongebob object is in my.new collection that's nice um and we call that a pending object so we say that spongebob is now pending when the spongebob object was not in the session we call it transient so sql alchemy has these notions of object states there's five object states there's uh and i'm gonna forget the fifth one is new is transient this is an interview question transient pending persistent detached and uh pending delete is the last one um so so far we have an object that's transient and now we have an object that's pending and also this notion of object states uh comes from hibernate i studied how hibernate session works and that's how i came up with these terms um okay so the row is pending we haven't connected we've done nothing nothing has happened but yet we're going to do this crazy thing we're actually going to try to query the database which seems ridiculous because we haven't even connected yet there's nothing in the database why don't we do that well the session is doing this thing where it's trying to create this transparent experience where you add things to it and that you can get them out again and it will always try to make sure whatever is supposed to go to the database will go to the database as late as possible but not later than that and you'll get it back so what's gonna happen is we will make a select statement like we did before um using import select like we always did except instead of sending the select a table object or columns we're going to send it the user class we want to select user objects not just rows we want to select user objects and we want to filter them where their username is spongebob now filter by is actually part of select a filter buys a little convenience method where you can just send it simple key value pairs for just where x equals y if you want more elaborate criteria you can go back to using the dot where method which we'll see and then instead of using the engine connection to run the statement like we did before the session also has an execute method that looks just like the execute method on the connection so we have this select statement that looks a little different it's selecting this domain model class which is weird and then we're going to run it with this session thing which is weird and then what's even weirder is that there's nothing in the database the hex we're going to get back well what the session will do is it will auto flush whatever is pending before it runs a query and then when you run another query it will have nothing to flush and it'll be done so it makes sure whatever you've got ready to go oh you want to create let me let me push out what i got first now let's go and query the database and see what we have so just that running the select with auto flush and of course you can turn auto flash off probably about 30 percent of people use the orm turnout to flush off because they don't like it which is fine it's just they mean that they've got to make sure that they do the flush manually um but for the tutorial we're showing you the full cadillac version so what happened is just doing this execute said okay let me begin the transaction let me flush the pending object let me run your select statement let me get it back and then i will turn to you a result object with pending possibly pending results inside of it so that's what just happened and then we can use the result object and we'll use another method called dot scalar which is again this is the same result object that you used in core it's actually a different subclass if you look at the actual class it's different but that doesn't matter they're all they're all subclasses the important part is the one that's cut off result this is the important one so you work with the result object okay that's the mro anyway so we we did result and we called dot scalar which will give us the first column of the first row and what that did was it gave us uh we assigned that first column of the first row to another variable called also spongebob and if we look at also spongebob we see it is a user object that has magically come back to us from a result set so the database doesn't know how to set us objects but the sequel alchemy session translated the row from the database the raw row that had that had these columns in it it looked at the primary key identity oh this is primary key number one let me give you the user object with primary key i did a number one back because we asked it for the user class and that's the round trip so we got that back and we see that because of the flush our original spongebob object that's the one that we added that had none for id all the sudden seems to have a primary key on it so that it modified so the orm unlike the core expression language the the the domain objects of the orm are very much mutable objects um and mutable state has a lot of has has a has a pretty bad rap these days in computers um the orm is based on domain a domain model that's object-oriented and assumes immutability so it things will change so in this case uh dot id was changed to be the number one um and then also the session this is where we see this thing called the identity map because we have queried for the spongebob id number one uh it gave us the same object back and that's called the identity map the identity map means that when you query for primary key number one from a certain table you get the same object back every time as long as an object is in memory um and the whole entire thing builds in that concept and it's pretty pretty critical and the identity map is you can look at it so you can you can go session.identitymap and look at the items um you don't want to mutate this map yourself you want to have the session put stuff into it for you um but if you want to see what's there which you usually don't really have to but if you want there it is you can look at it um so now we're going to look at some more use cases for changing data making changes we can add more objects using another method called at all uh at all is the same as add except you can send a list of objects so if you want to add a lot of objects at once so we'll add two more user objects and they're pending again nothing happens nothing happened um and then we'll also make a change to our original spongebob object let's modify spongebob spongebob's full name to be spongebob jones and again nothing has happened nothing is updated nothing no no sql has run at all we're just we're we are however in a pending transaction because we we did it begin if you were running this against the postgres database right now and you went and looked at your postgres uh information schema that would show you the current uh whatever the views called current current processes it would show uh opening transaction for our process i mean normally the computer would be running this for us we wouldn't be sitting here at the console but we're going very slow um so now we can look at more things in the session the session knows which objects are unquote dirty meaning we change spongebob's name to be spongebob jones it knows that and it has the two objects that are pending that have not been inserted yet these are still these are pending and uh then we can say hey session okay we're done commit transaction and that will commit everything so commit will always flush whatever is not flushed yet and commit the transaction and then the session goes to a completely neutral state after that so when we run this code you'll see it runs the update for spongebob jones and then excuse me and then it will insert the rows for the uh that we had pending and now all three objects are what we call persistent meaning they represent a an active row in the database so when an object is is associated with a session has a primary key identity and there's a row in the database with that primary key you're now what's called persistence um another behavior that you very often will want to turn off but is the default quote unquote cadillac level of automation definitely not appropriate in all cases is that the commit will expire everything on your objects so the objects are in fact empty i'm going to do the quest yet seo one uh the question is is it fair to say that the session manager is like a reference pointer manager where it will mutate data in place rather than creating new things even if it isn't actually memorized um i guess um i haven't done c programming in a long time oh i do it for the accessories but yeah it's i guess yeah i mean the python audits themselves are in place yeah so the session maintains those pythons those python objects that you give it in in memory um it actually does it using a nice weak referencing pattern where if you lose references to your objects and the session has no pending changes it will it will also lose references as well uh but yeah it certainly is a pointer yeah yeah i i would say you're it probably is fair to say that although i don't think it's clear but yeah i think it's fair to say that sure sorry sorry i can't give you a better answer um but we'll we'll go on and we can we can test that theory some more up another number one neat makes sense great so anyway the session is default cadillac level behavior is that it wiped everything out of the memory state so it still has the objects it has three user objects they're there what happens is the inside of them the dict is empty and what that's going to do is that when you access them again the session will be like oh you want to see these again let me open a new transaction let me get the rows back for these objects let me show that to you again um so what's going to happen when we say spongebob.fullname this is going to be the first example of what we call a lazy load meaning upon python attribute access you say dot full name oh boom i'm going to have to run a query now you can see why this might not be desirable behavior because if you commit a whole bunch of objects then your code keeps moving you're going to have craploads of these queries so uh you might depending on what you're doing you might want to turn expire on commit off or you might want to make sure you just work with everything in one big transaction the reason the session expires everything by default is because once the transaction is over now your data is is out there in the database it is now subject to any number of other transactions that want to work on it and there could have been other concurrent transactions even waiting to send updates to what you've been working with so once the transactions is is committed um the sql automation kind of assumes it kind of behaves as though the database acts in what we call serializable isolation which it usually doesn't but it it kind of is built around this this ideal that well if in the absence of the user telling us to refresh things we're going to assume that everything in this transaction is basically not changing but once the transaction's over okay we have we know nothing now we need to get it all back again um and for many years this this feature of expiring command i would i would have thought was crazy um but you know we had people say oh i you know i just commit i have concurrent stuff happening and i got my data is stale and it did the wrong thing and people kind of you know not not everybody but a lot of people kind of expected it to to know about this so that was why the experiment commit model was added just so it's there um if you're writing a web application and you have your web request do a bunch of stuff and then do a commit and then you send all your objects off to like a template to be rendered turn off expire on commit don't just leave it leave it off it's the most easy to set flag expire on commit it's only spartan commit is only if you're gonna have a lot of transactions running with the same set of objects and you're worried about concurrent transactions affecting them uh that's the reason for that and which is really not the usual case it's the most correct case but it does not always perform well to have all these extra queries and also what you'll see is this thing called detached objects is that if the objects have expired and then they are null and then you close your session if gone the objects are in a state known as detached and everyone who works at economy will know that when you try to access unloaded attributes on detach it throws an error because there's no transaction and so anyway experiment commit you may or may not want it just be aware of it okay so rolling back changes we're going to do some changes that we don't want just to show we're going to change spongebob's username to the word spongy is a question let me do the question nah so okay any thought of making the expiring commit to fall off no because i want the debate the the default behavior to be the most correct behavior possible and then performance should be like like the performance of that should be up opt-in because usually people shouldn't really be working usually when you work with the orm you work with your objectives then you commit the transaction and you're done so the use case of i want to keep the objects working after the session whether detached or not you should if you're going to do that programming pattern you should opt into that programming pattern um if i kept expiring the commit default off nobody would ever know about it nobody would ever use it and i'll get all the same bugs again where people who expect it to work in that way um you know uh the thing is when expired commit is on and then people want to use the task objects they get the error immediately oh there's an error message here just so like it's loud uh you want the defaults to be such that when people do the thing that's not supported they get a loud problem so experiment to fall off is more prone to a silent kind of failure whereas having it on is more prone to a loud kind of failure if that makes sense um and that's why we have the session maker just put it uh if you don't want it put it off but like you should know about it because i actually use it for things it depends what you're doing you know if you're doing a low scale thing you want to make sure everything's is correct with concurrency keep it on you know you find that not that many queries are actually running if you're you know if you're just recording everything fresh in your transaction no it's not always that big a deal so in here so we're gonna make um uh some changes we don't like we'll change spongebob username we will make another user with a fake name it's not valid and they will add that to the session pending uh we'll run a query that will autoflush that will change spongebob username to the wrong name that will insert this row we don't like we get them back but these rows while they're quote unquote in the database they're not actually in the database meaning they're not uh durable they're only uh within the scope of the transaction if you go once your postgres database and you shut it off you'd lose this data because depending on your your durability settings uh you know this this transaction will be lost and it's not actually durably committed to the database yet it's just uh within the scope of the transaction and most of our modern databases not sqlite maybe they do now uh use this thing called multiversion concurrency control uh which means that many transactions can all have different versions of the same row because why did this change i did that change and they're all going to see something different and then when they all try to commit it'll all like either lock or whatever but the point is that there's kind of a kind of a shadow copy of a road that's the pending version uh so this the urm session kind of tries to work with that model so we have data we don't like in our database but it's not persistent yet so if we roll it back uh that will roll back the transaction the the database no longer has that data those changes we just made the update and the insert are gone um the session itself will again do this expire thing uh it will expire everything uh and then when we go to spongebob.username again oh it's expired let's begin a new transaction and get the state of the world again reload it and then we see that spongebob's username is back to the name spongebob and then also when we did the rollback any objects that were pending as of that transaction like that fake user we created they will be evicted from the session when a rollback occurs so pending objects objects that were pending too persistent will be evicted upon rollback that's the what it really means is that it just does the thing you expect which is that the fake user is not in the session anymore uh just in case i didn't show this spongebob is in this session so all the objects that are in you you can use contains with session to see an update in session or not all right so so okay and the data is obviously gone from the database so you create a database you can suspect the one row with the correct name okay now we're going to do orm query which we saw a little bit we saw a little bit of this a minute ago we saw that the select object is now used with session execute which is awesome um so back like from the sql expression chapter we'll notice that the attributes that are on the user class so this is where sql orm mapped classes are not really like python data classes we map these attributes that will be persisted on instances of user but it also applies what's called a python descriptor to the class which also provides behavior at the class level so this is not a user object this is the user class and we can look at user dot username and we actually get an object that is kind of like a column it's actually called instrumented attribute and it's a descriptor that has all those same eq any all those overridden python magic methods so while the user does have a table in it and we could say username equally like that and we can print that and we do that we can also do it from the point of view of the user class and actually there actually is a difference between these two things that you can't really see that tends to work itself out is that when you do a user uh that username expression there's actually some secret stuff inside that you don't need to know about but it's there well it's not there okay use your anyway forget i tried that because i just didn't do the right thing there's supposed to be some annotations that refer to the expression it it's it's actually in the username column um the user you when you make a expression with the class there's actually some special annotations that are buried inside of there that have it know that this is an orm expression and not the uh not the the core so the the select object knows to behave a little bit differently in some cases even though my example of my own code failed visually because i forgot how it works so moving on we can take that expression and we're going to make a new query and we're going to say query equals select user the user class not the table the class where the user classes username attribute is spongebob and we're going to order by the user classes id attribute which as we said before have column like things and behaviors so that query is a is a regular course select now this probably know will work because here's another me looking at my own code live to see if i know what i'm doing there you go the query inside in the internals has the table object all right one more try for annotations annotations there it is haha there's the magic orm stuff so you don't need to know this is just me picking up my own code just quizzing myself on how this is the magic of a live event yeah i'm just quizzing myself for no reason you know this is internal you don't need to know this but anyway this is how this is how the magic this is how the sausage is made right it's like you did select user and it went oh let me get the table out there let me have a table with all this magic oh this is actually a map table so it's so inverse but it doesn't matter this is the part you need to worry about sorry about that other part okay so we can execute this select statement and we get back a result object the same one we get in core and we can iterate the result object and we get back rows and by rows we mean the row which is a tuple we don't get back user objects we get back tuples with of rows with the user object is the first column in the thing so you see the same old query we've done about 100 times now and you get back if you look carefully this is a tuple so it's it's a the parentheses in the comma so it's a row um and this is also new in ql is that we just decided this has to be explicit because in query in query all the people that know the orm the dot all method would be like objects or would it be on rows it was this for that it was inconsistent because we were worried that people didn't want to type out the words to get the the scalars back but it's it just had to change because people are it's too inconsistent the way it was starting to lose my voice but we'll make it um as a typical convenient for rows that only have a single element you can say result at scalars and then you get back uh the individual user objects instead of the rows so usually when you want to iterate the orm result that has just one class in it you say for object and result.scalars and then you get back the the class by itself and then you're good someone has some evil microphone thing happening okay it's over that wasn't me right okay um you can also qualify the rows like you do the one thing you want to get just one row back you know so uh one one well here result scalars means give me scalar results and then give me just the dot one so you could do that so the result now has this method chaining thing going on too we can modify the results and then call accessors on it and you can also query for individual columns just like the regular select always does and then you get back a row for room result i'm printing out the two elements of the row so it's it's you know printing here so you get like the tuple-based rows with the individual columns you can combine entities and columns together uh here's the user object with the user name uh and then it's printing out row.user so here's how you get the the name tuple if your name of your class is user the row will have row.user row.user robot username there's some just some access mechanics there you can use the for the where clause you can use filter by which also works in core now you can use filter by with core queries too against table uh filter by is a quick one for just key equals value you can use uh where as before for more uh complicated uh sql criteria just like core it's exactly the same like i don't have to teach you this i already taught it to you in the core chapter that's and that's the end of the basic so the sql queries look exactly like core queries and they run they do the right thing that's the first part of orm and we can go to the next part which is the hard part okay see previously i had a whole a whole explanation for query it's session.query it's different it does this it does it works it's different now it's now it's the same it makes it my job easier yeah it feels way more natural yeah that way this is because when query came out i it kind of happened by accident it's just kind of like some someone contributed something and oh that's cool let's do that it just kind of became this casual thing and that's what we could do and i didn't know queer was going to be so fancy it just became really fancy and i realized slowly how this thing is really supposed to be just like select okay and then for years i'm like how can i join them together because it's so dumb looking now finally okay anyway advanced orm uh where we're now going to work with two tables and joins and sub queries and eager loading um so we have the same user mapping we have the registry like we did before we map a user class and then we're going to add this new thing to it called addresses it's which will be a one-to-many collection to this other class that we can just name by a string called address um we haven't declared the address class yet so we just call it by a string a relationship means on a mapped class this map class will be referring to some other kind of math class so if you look at the map classes as a as a graph the relationships are the edges of the graph the if the map classes are the nodes relationships are the edges and then we're also going to add this thing called back populate user which means that as we manipulate this addresses list of addresses it should also manipulate a dot user attribute on the address class that we haven't even made yet but you'll see it so here's the address class where again like in core we have a column with a foreign key and i probably forgot to note this before when you do have a column with a foreign key we can omit not only the name but also the data type because the data type of a foreign key of a column with foreign key is assumed as it probably has to be to be the same type as the refer as the referred column which is integer in this case so we put the foreign key separately notice and then we also put this relationship so even though the thing is that this relationship refers now to a many-to-one relationship where dot address.user is many to want me many addresses can refer to one user and that's because many rows in the address table can refer to the same user account id column um the the part of sql algorithm that we call the core is this part with the foreign key in the column and the part of c logging that we call the orm is relationship those two things are separate uh because we want you to know about your schema we don't want the relationship to just automatically decide what kinds of columns you want or anything and you'll notice that because we make the foreign key explicit we don't actually have to say many to one or one to many we just say relationship because the the many to one or one to many or many to many is is determined based on what is apparent it's it's it's inferred from the foreign key relationships and there's people that don't seek walk me to test that if your key relationships aren't set up correctly it will help you to do that um we don't want to lock you into a simplistic foreign key primary key setup there are ways to make relationships that join on very elaborate criteria uh so we want that to be open and you can do what you want with it um so to work with these a little bit let's make our database again ddl the two tables that we've seen in the single advanced chapter calvin just changed to a picture of that okay that's a that's a flat picture um we can add some data like we did before add the three user objects and we can uh so also another thing i'm showing here uh we made the session maker with the bind of the engine and the future equals true when you make a session maker in 1.4 you can use the session maker as a transactional context manager which i would recommend um so you have this session maker and you say with session maker dot begin s session so it's going to give you the session it will do things with it and then at the end it will commit it and this is basically analogous to the engine level engine dot begin um so that's another way in which 1.4 is trying to make session orm and core were consistent is that the session maker is analogous to the core engine and the session itself is analogous to the core connection i mean there's some differences but anyway so we add these rows and the new user object that we made gains an empty addresses collection based on the relationship and we're going to populate the collection with some address objects so we have this squidward object and we're going to assign a python list to it of address objects uh and when we do that uh sql alchemy orm because we did this thing called back populates is like okay user.addresses collection each of those address options should have a dot user pointing back to the user so we could say squidward give me your addresses give me the number one element of your address list okay here it is okay of that element what is dot user oh it points back to squidward it's a bi-directional relationship um and these are very common as you call companies because when you have the database foreign key you have one row and then the other row the foreign key you can refer both you know it's not like it's one direction or the other it's both ways based on relational math so um you can set these up in your relationship which is pretty not not required you don't you can have just one direction but usually people are sitting on both sides it's pretty it's pretty much the norm and so when you do that it will it will cross synchronize in memory alone again this this addresses thing has not happened to the database the database doesn't have any address rows in it yet um when we add this new object squidward to the session uh a thing happens again the terms from hibernate called cascade which means that okay squidward's coming into the session okay let me also get everything squidward is related to and put that in the session also so it's going to cascade squidward plus squidward three address objects are all pending in the session so it just saves us some time we could have said session.add all squidward and squidward's addresses but it you know the the the default behavior is to cascade them in i see a question yeah you know there had to be some what is hibernate so you know hibernate is the very famous influences yeah hybrid is the very famous owner for java that because java was kind of still coming online itself you know hibernate is very complicated it's pretty crazy but i had to look and see what they did i took a lot from hibernate um but the fact that python's a dynamic language made doing the things that hibernate does a lot easier more succinct i actually did work with hybrid a lot i work with xml mappings i work with spring i've seen all kinds of things um it's all jokes for kelvin okay anyway everything is uh is cascaded in i i'm a sucker for jokes oh i didn't yeah it's from java yeah hibernate hybrid is from java yeah i thought everyone knew that see that's how old i am i don't recommend getting old it's kind of difficult you know you know things that no one else knows because it's old and then when you say you know people are like wow you're old um commit we know what that'll do that'll push everything in um then what's nice here one of the jobs that sequel alchemy orm does that is a pain in the butt to do manually is it inserted the user account row with just username full name it had to add sqlite hey sqlite what's the primary key for that new row oh it's number one oh actually number four in this case okay let me take that primary key number four back i've got these addresses to put in now let's put those in also and they have user id number four now two things had to happen for this to work correctly the unit of work had to know that the user class goes in first so that we can get the new primary key and we know that the addresses are dependent on the primary on the user so we get the new primary key back and then it has to know to populate that new primary key into the rows into the foreign key of the rows we're going to put in so this is what the unit of work does is if you had a if you had a data active record thing and you had user dot save address that save address that's safe if you did address that save first it would break because user id would be null um so the unit of work takes care of that kind of thing and i don't really know why people would not want to use work that is doing these things for you like reconciling whose foreign key points to what who wants to deal with that it's it's like again just write sql yeah but this is just boring if you do it ten thousand times it's just boring let's automate it i know what a foreign key is i know what a primary i know what auto incoming primary key is i don't not know those things i just want to automate the thing so i can get back to coding um so squidward has now been uh expired and again this is that expire on commit thing we did the commit and it inspired here's another advantage of expire on commit is that after the transaction well now squidward's addresses collection should load and this is called again called a lazy load where squidward is going to both refresh his his primary key and internal stuff and we're also going to get the collection of addresses associated so when we say squidward.addresses you get those three address objects lazy loaded by default and of course there's a whole subject called eager loading that will cover if i don't run out of time um and that collection is persistent in memory until the transaction ends so uh if we load squidward.addresses again it's it's it's there it's not going to make it the query twice there's an option to make it do that but it's not as common so when we want to work with uh collections and references um we don't really want to work with the foreign key we we don't want to say you know address that user id equals five you can you can you you're allowed to set that but the normal sql alchemy way is that you manipulate the references of objects to each other and the foreign keys will be synchronized by the unit of work as updates as update statements happen so in this example we want to get spongebob and then we want to take uh one of the addresses from squidward anyone who's watched spongebob squarepants knows the pattern that spongebob is constantly messing with squidward um take one of squidward's addresses and say dot user is spongebob so squidward's dot addresses collection has three addresses we're gonna say one of them.user is now spongebob so when we do that we just see some select because nothing because again everything is pending nothing has happened um by assigning dot user on one of squidward's email addresses the object moved from one addresses collection to the other this is the back pocket future work so now if you look at squidward.addresses the uh address object that we touched has been removed from his list in memory uh and spongebob now has that address so by manipulating dot user the mini to one side the one to many side was updated for us in memory that usually works in memory without having to uh flush anything commit all that and we see the end result is an update statement where it's going to change the foreign key column on email address to refer from uh to no no no no no to refer the email address to id number two to refer to spongebob's user id which is one so now it's been moving the database so that's basic relationship persistence now we're going to do relationship selecting this is where we see joins again so all the same issues with joining and cartesian products works exactly the same way with orm stuff you have a select and before we set it the user table and the address table or a column from those now we can feed it the user class and the address class and we can just do a a an implicit join in the where clause and we can get rows back and what will the rows be here there will be a tuple with a user and address object in each row so we get that back and we have tuples which are the three rows that we have so we get uh squidward twice because there are two addresses that refer to squidward and we get one for spongebob so that's pretty cool um just like with the uh core you can say select user address or whatever you want dot join address and it will join on the foreign keys from user to address so you get the same results but uh i didn't print them the same way join like that and you can do that you can do join okay so if you want to give it an explicit sql expression for the on clause you can uh set the on clause as the second argument this is again the same exact select we've already illustrated uh and then another way to join we can also use join from which i didn't illustrate here but join from works you could say select user comma address or select user dot join from user comma address that works too everything works the same if it doesn't work the same it's a bug you can report it to me um so here is one more way to join that only is in the orm which is because in the orm we have again this class bound attribute called addresses so the when you send to the select the core select select and you say dot join and then you pass it the user.address's relationship at the class level it will know what to do with that because when we run this statement through the session there's actually a compiler uh extension that gets enabled for this statement um and the statement itself uh the compiler extension is part of the statement so if you print it out here so this is the core people who have worked with c hawking before and know this would know this is very weird this we could never do this before that we have a core select with a joint against a relationship and it did the join using an orm join and that's because the the new architecture has this whole new thing where the fact that we selected from orm entities a whole new compiler extension was brought in behind the scenes to actually interpret things like user addresses in terms of the orb um if this doesn't make any sense to you all you need to know is if you want to join two classes you can say join user.addresses which is fairly intuitive um so and that's that's it that's joining it's that's the whole thing you know we've already gone through it um so here's the same example with alias so um use our user-defined class is not a table so before we had like usertable.alias sql alchemy doesn't want to attach any extra methods on your class because it's your class we don't want to step on your names at all you know maybe we don't step on your names at all um so if you want to make an alias of of what we call an orm entity like the user or address classes there's a function in orm called alias and you run the alias function on the outside of it and that will create an alias object very much like the one we have in core except it is a little more orem enabled it has the relationships on it it has all the other stuff so this is the same query that we did in the core chapter in terms of orm records so it's the same sql uh select from the user join from user to address alias one join from user to address alias 2 put some where criteria for each each alias separately uh and this query here is mostly if not completely identical to the one that we had in the core chapter so when you read the 1.4 2.0 tutorial it will do it will teach you about select statements just once because 99.9 percent of what you learn about select for core are exactly the same in orm except for the really the only thing is joining on the relationship thing is the only thing that's different um and another little modifier if you want to join using a relationship but the target of the join is an alias or not the normal thing that user.js points to there's a modifier this is also in one three of type so usually when you say select user join to user addresses that means join from user to address what if we want to join from user to the address alias but we want to use this relationship to tell us how to join it that's fine uh we just say dot of type and it'll adapt that join to be against the the alias version the reason we might want to be using join user addresses because we might have relationships in our classes that have all kinds of special conditions coded at the class level you can have uh relationships that will query for objects that are all as of a certain date or all have a certain criteria or they all have like a deleted flag or something set 15 minute warning yep i'm doing really well look at the 21 i'm on slide 21.
it's like like it's like a spacex landing on the platform right um again this is that same not quite the same query this is a query with uh sub queries and joins um just like before nothing changes uh select uh count of addresses and user id group by user id and do a sub query this is the exact same syntax we literally could use the tables here if we want it doesn't matter and then um we can select from uh username and then the funk coalesce and then we will do an outer join here uh to the sub queue and we're actually doing this the the on clause explicitly which we don't actually don't have to it'll do the automatic thing but it's okay and we can get the subquery back and ctes work the same way too uh so basically everything we did in core is already good for the orm and i think the next section should be eager loading yep eager loading so the uh biggest the most famous issue of orms is called the n plus one problem and when i tried to explain it last time i did this talk i got confused so let me see if i can do it the m plus one problem is if you select 10 objects and each of those objects is related to some other object and then you go to those objects and you want to access the additional object and it uses lazy loading you will get n plus one query so if n is 10 you have 10 rows you'll get a query for the 10 rows in the first place and then for each eight of those 10 you get a second query so that's n plus one queries so what that looks like is this a lot of queries so we basically went through a bunch of user objects and then we printed the user.addresses collection and we got n plus one queries um so instead we can do eager loading uh which means we can apply options to so eager loading means that we have to tell the query about our intent for what related things we wanted to load also up front so eager loading by nature requires some pre-planning we know that we want to load all these user objects and we know that we want to get all the addresses from them which is a collection the best eager loader these days if it's compatible to use is called select and load uh select load will emit a second query that uses an in against the primary keys of all the primary rows that it loaded um so we take the same query we write it the same way we add this extra call called options and options are where we put loader options there are always special modifiers that are handled by the orm core does not have the notion of options the core core has the options method but it doesn't have any effect with core with the orm it knows you want to select and load these these are these address objects select and load itself is a function that we import from the orm namespace so this is otherwise the same query that we just saw it made a lot of n plus one queries so now when we run it we see just two queries the first query is the user account the second query is going to take all the user ids here and put them in an in clause here and that way it loads all the clauses at once and the reason this is very efficient is because it does not use outer joins which are not good for query planners it does not emit any queries for rows that we didn't find like if we had user accounted you know like left out if if uh it doesn't equate extra queries i'm getting confused um also it produces a select statement that normally does not involve any other tables it doesn't have any joins in it and especially mysql is has a lot of trouble with join so the select and load tries to make a query in as many cases possible that has no joins or sub queries in it at all it does a straight in lookup which will use the indexes uh that are on the user id uh foreign key so it's very fast from a single point of view um okay so the oldest loading strategy is called join load and this does a left outer join or an inner join based on what you say uh to the related row um and it works for collections uh but it's usually better for many to ones because with the many to one you're getting the rows back and each of your primary row will have only one entry for the related object because it's mini to one uh that way you're not getting lots of cartesian rows for each collection element so uh join load uh has a optional inner join so we're going to load address objects and tell it to inner join load the user at the same time so you see a nice inner join and when we print address object.user that username there's no extra query emitted um the zen of instant the zen of eager loading refers to the fact that eager loading does not want to ever change the results that you get back eager loading is only about population of collections and attributes eagerly um if you want to write a query with a join and you want to join because you want to actually join to this other table and filter in the table you don't use join load for that you use dot join for that join load is always going to keep its work away from you you can't actually get to the sequel that joined low to myth because it's it's going to aliase it with anonymous names if so if you want to join from address to user you should join from address the user um a problem arises when you want to select from address join to the user table you want to filter on the user table but you also want to eager load it that's a third problem that happens with collection eager loading we have many to one eager loading and then we have manual and evolution where i also want to join okay what do we do here um well if we say join load we'll get something that we don't want it'll work fine but it's not as efficient as we want it'll get it'll be um joining let me move the little window up here it will select from email address table join to user account and then it also is going to additionally join to it again with the left outer join which is not as efficient because we didn't put inner join equals true here so it's joining to user account twice uh so that i can get this thing and that is wasteful um this is not automatically handled uh if you want to handle this case you do another loader option that you should know about called contain seeker contains eager means i want to equally load something but i already wrote the query for it you just get it for me you figure it out you figure out where it is and i'll and i'll get it so what we did here is we said okay select from address join with just user filter on the username of squidward and then also assign that for me please as you get those user rows add those columns for user to the to the to the from to the select statement and put them in for me so we'll notice if we were to select from address and join to the user table you would see in the select in the columns clause select you know id name id email address from email address you wouldn't see the columns for the user table but because we said contains eager oh let's get those two and also stick them onto the user onto the address object so when you run this you'll see i have to move my little box again you'll see the user account columns are here and the email address columns are here and the join is there and it loads everything all at once using contains either so contains eager um is used when you have a joined load that you want to do but you're also joining and it's kind of a more specific thing it's it's kind of interesting that i even have it in this talk it's kind of more of a detail thing uh but anyway that's the the eager learning story is for collections uh select and load is pretty much the best one if it works because sometimes the reason select and load is not always compatible is because if you have composite primary keys when it does the n lookup for the uh the parent object it has to use uh a tuple uh within and not every database supports tuples like i think oracle or maybe sql server doesn't support it yeah most databases support it for a while sqlite did not support now they do um so select and load might have problems with composite primary keys but not too much um so selecting load is very good for collections and joined load is good for many new ones um and there's a whole lot of other things that go on go along with how many to ones and collectors are loaded um but it's in the docs because we have so much time and that's probably the last slide for that okay
Up Next
Deep Packet Inspection: Techniques, DPI Firewalls, and Applications
@WaqasTechVideos
6.3K views•2024-09-29
BitTorrent Protocol Explained: Piece Selection & Peer Choking
@StevenGordonAU
481 views•2013-02-22
HTTP Requests Explained: GET, POST, PUT, DELETE
@codecademy
103.1K views•2021-10-07
Enigma Machine Mechanics: WWII Encryption Explained
@JaredOwen
13.2M views•2021-12-11
Related Study Plans & Knowledge Roadmaps
Structured learning paths in Computer Science