Bestand wählen

How Pony ORM translates Python generators to SQL queries

Zitierlink des Filmsegments
Embed Code

Automatisierte Medienanalyse

Erkannte Entitäten
hi everybody good to see you today I'm going to talk about on your but before I get to the like tell you why did and yet another model in in the 1st place we did not use life was the difficulty is that and your family shall come you're with initial that my father the have been interested in object database since we were students been that with became his passion and their facilities and teaching defended that some point he came up with an idea of the creating advantage of the little amount can be inefficient she show this idea you mean and I just love the idea after years you open problems position than any of finance I thought you were such as to if there was 1 of the myself this is composed of what people when you went as you know on user sturdier smart and will have a different view of course this is a it is stored in small into different so what makes 20 different from other men just the following
generators for right with the with words this is an example of a
generator we give the names of the rise of the country
and is of the same degree of specificity for if you can the generator similar musical career was however common problem this last section of the result the from section is similar to the environmental the conditions but similar information that the hearer is completely different the spherical generated there is where they have stored in memory while sequel query selected data from the database imagine how cool it would be for use
generators for this is actually 1 that's he's an example
importance the selected function provided by 1 it is used to generate a product but it doesn't that you is this 1 of the generated by it translates industry the point is that this could be the database and use objects in memory based on the results this function just list of objects the product the class which is the nuclear norm the cold is preserve the product entities and is mapped to at this point in the least
so let's come the point isn't
that view of America's here's the query on this selected products multiple condition the product names that with a the problem is not major water after the previous 2 conditions the product was added before 2014 In 20 reiterate the product entities see how can we use standard function for example the name is doing something you start to function and and this is the actually the of the bank and you can use the attribute with the same engendered the progress in general and the objects that you do it is the manager which is used for putting this object In our example we have balls and and OR conditions we can express and you should just sort a product least considering that list but in order to express or conditions we have used the special few objects and the number of conditions you the last war period in general we have to use double in this course in order to apply specific conditions such as those you who is now let them integral of thing look this
way we efficient objects and made the rest of the problem the point is we would use that the order to apply the conditions but it's a clock maybe you the progress In order to get the year from a product at you we need to use the search function also have to put across the far condition in the bond axis in order to give the proceedings of the Midwest operators if you can wear these
queries live there pretty similar in terms of land some people like John or some people like to go out and sin but the like the generative scene that because it's easier to remember it is the native by the scene that you worked objects which are stored in the database is like the worst stored in memory that make development easier also using
generative model for the query translation joint or single this happened because in 1 year of the result of the translation is cash this like function this is the generic as the parameter and the generative is a standard by an object so we can use it as a key for but for getting the result of the translation of this in general and the and there's no such a key because the filter function just use use a set of parameters and that is why
we need a method to translate these query every time endowments about me when you hear that when you make the translation of the Bible code only once the other you can build
could the best however possible using generated when it also allows you to do is the most it in conditions 1 after another this is done with the help of a few the function it is useful and expression as of parameter this function just a nuclear objects which is quite a condition In this example we have a couple of conditions and then order the result in the slides and the this is the result of the last applied
conditions In this class knowledge of
the company translates of generators in physical quarters some people may think that is complicated but process
and the use do much in I want to show you how to what's so you can see that it's pretty fast and straightforward the translation of
processes consist of 3 steps this shows that we get the generator and is called objects as you know when you bite and doesn't provide digit uh of 1664 generators so you have restored from the right the
2nd step point just live in the city in the office In this intermediate representation of people of this policy but whether or not the sequel couldn't text here because begin with the help of some peculiarities in terms of the dentition of 1st who is in different ballots that can can right so that the more you don't want to or a complicated translation
part we just put it there so so the 1st step is
the completion of the Bible code and historian ethics and that for
the compilation 1 uses an object which is that is the part it has a set of methods and he she has a made response to a Bible comma the books was about cold from top to bottom and we call that formed and a while the kids in bible would accumulated on a stack as with the view of
such an expression and government and translated signal because the byte code from
the generative object and
this best that where when you accumulate the eastern part now I will present the shift from an lovable we call the method that is the object of the same name and this method which the main of goal of this step the next command both is the name of this comment is different from the previous 1 because fellows arrival from a different things but but for the just he said the same thing just arise when it wasn't because them it is to note to this fact the next command is slower than this on applied to the top the that we are exist in the new the new 1 yet the binding at command books to stick element and then pushes the binary and command each that of those 2 existing knowledge it combines said that used to and then we'll all exits reviewed all of the acts were reliable and get 1 attribute facts and finally we want to to that elements and push company which combines them is against this process with it doesn't mean that it's too much money and usually juridical does not that long all also going need to build hold compiler here because I do not just a subset of pop icon Michael duration and
result on the 1st approach we get such as they used it so that the invasion of privacy I expect the 2nd step
wishing to offer the but our
reach sequel should give you can tell
that just looking at the expression it depends on where for example agency can be strings 4 numbers right this gene or a collection if young members
and part of the collection that should generate numeric edition and this of course there's things that we should generate the change in the nation before we had to do with this collection then we should use a software if all of them wanting to the union contract and like a period if will analyze all this arrival times inside the translator will have a lot of you then statements inside translator and if you are a complete is transferred the cell you'll have to
maintain and extend such so you should somehow structures logic of in to keep the of the simple and for this purpose we
use more among the company in which
each translation logic it's not this is on translation was that the through the end the game translation different moments each 1 of accumulated data integration of part before a sequel query as the result
according to different models for different types of 4 different but each 1 defines a set of candidate operations and also how do we combined with other models and each 1 could generate a particular apart from the resulting
mystical query we here I show you a different model types we have a lot of them but just some part of team was no how to make operations with strings for example is change of the model can generate a a joint operation and the harmonic puts a stink bomb the into with using more keeps the
process of translation simple the war syntax tree and process each
node when the exit from the north OK we want that the all
the way down use of the depth of strategy when the end of the additional we don't have all the necessary information for generating sequel because you don't know types in the morning before I know essentially lava on we can't you can way up when didn't know the data model let's say that he is in journal sitting parameter the decisions are more here and anyway so before you nodes included another 1 another example of the use the regular so we created the object determined this morning novels on each object to the in our you work through that and then the translator processes this no it doesn't analyze the multiple just to the case of the get out to you separation the previously processed more it is just that the object you don't want to give that to you from yourself and create a new model which would result of this operation and if these listing argument because most of the time the name of and now I just gift the leftmost and so it yourself to the right by the time you know all the time all of our operations and that this G and the readjusting expects money and that this model can generate the part of the C for all 4 of these green knows so far our too this way of processing logic is encapsulate inside the models help you just the simple and we can easily extend about it in modern new types so you serve and the
end because the problem of model that can generate sequel for the whole gene here the sequel
that together over the result of the translation this this story about a like a least right now and this reminds structure so the 1st element of list is the common goes appearance of this for example in this like common that has parameters you the column 1 from the table to then goes the function from which is followed by 4 argument that should be concatenated by the end of the process of the translation of almost done In the last of the translation
used to translate it into a
specific signal but the summer simple steps and it has a
set of the database providers of those molecules give the knowledge of the data with the polarity of the task the single most in that it hourly structures of that is the part of India incumbent resultant into query string here you can see see the result of the translation to receive elements
what features that this is a way to
improve the translation so that was the very last step in the guy
a sequel query which can be cast in the sense of the database by this time
Margaret due to the use are what we use in this new environment like
those you about the future when the
the objects of that it's much it means that each object is slow that only 1 of them is the same the recession when they try to to get the same object again not when most of the the the police of returned from the from the environment but he also said that and 1 query problem solutions this is where the matter can generate a a lot of unnecessary queries of units of loss of 1 when he can optimized use for example we can substitute some would is left to join in order to improve performance also pointed can
work with the database seems to be an optimistic person control that is to approaches so when you walk around in the in a database and the 2 directives it's modification by another transaction and is of domestic approach that is not a lot before committing point justifies the no other transactions changed to the rule for all the attributes different the point of this whole idea is to issue that you can do that diagram of replication 1 get the code for your entities for sequel asked you for kitchen table let's look at this
example the use of giant and we want to select a couple of students from the database also you want to print out the names and the group how many ways will be sent to the database and how many objects have been created after reading In the skull
let's see after that just heard to is that you have a student project you read it in memory when the acts the property In the 2nd part the center of the the base and the 2nd object to which it's the memory not create another student I would say that both students belong to to the same words we try to access the property you join you cannot control object it is a copy of the 1 this happened because gender doesn't other than the mark each object exists independently and gender doesn't start if a single optical flow the so that's how was
implemented on all the points in this a little bit different frequency we
use square brackets in order to the axis of the body the primary key of the music you that just a line you have the following according to the database an instance of a student was now when you see that there is a group of objects the shift of affiliated you this it means that these control is used in the appropriate roles in table existence the the link up when you blow this object here but you know the basis for sure because it's a system that will and when this is the
object this is probably whole object which has only use primary key the other use remote loaded to this object just like this each of the margin and using this graphical she's allows us to to make some optimization for example you don't need to if you don't need any other attributes budget drop idea when you and that any other questions the because of the primary key or the central solonid if you would like to get any other interview the rule and then it'll be a very because of the center the about in our example we don't need to send a new because some of
the ideas in the student table itself the law the 2nd and what you see is that the subject has the same problem
idea and it links you can use the same if you need to get an attitude of the drop objects for 1 of or both students 2007 just 1 query to the database but there is
the probability that you don't need to send any other
the concept of steep losses the soul was 1 of the from let's say you have an online store and you want to get a customer orders we show that the whole price of the word driven itself In this example we use the order by methods of you is sort of the result in in descending order because you want to see the last customers also created in the 1st page of the result of the page method is just the syntax
sugar or we can use lies about this from 1 user to use the page because usually want to show the results at the mortgage and this is this simple query we should be generated for the just like this to 1 and when they start to region or the In this case most members around and the on and plus 1 the problem this right the model will will generate a civil article quoted in order to launch each customer in order to show its name in and and here it's number of I haven't seen the borders leaves good and 1 it'll
work this way this was clearly to memory and 20 see for or old patterns objects we can see what customers might need some if you look at the rate of opening and when we integrate all of these we get out of it just customer attitudes when you lost those object responsible course instead of this what is select all
customers objects you the idea you long and disease and terms of our constant so that the difference in the world 1 of the few old 1 old from a table or whatever 10 rules the different innovation usually happens when you do a lot of with some of the probability that you don't need all the objects but usually in the
in the replication you don't thousands of objects we just waltz summer part of the model to show that the page and the right the told all the objects by 1 clearly it's a it uses a moral matter performance In jungle of course we can use select relatable officially it's something and other members also have some similar feature but the thing is the programmer needs to think about himself and sometimes you need programmers the boots disarticulated everywhere where this Newton and now and if you put it very where it has performance but if you don't put the workers in a structure from to have to concept of seats losses toward came across from query problem automatically and you don't need to think about and where you need to put it a smart way to do professional the also refused to actually In the jury is out of the way it is using job in order to to enjoy and all the
necessary attributes from other tables and if you do a lot of jobs in the the wizard can be less performance if you just select data from 1 table so when we select those customer ideas on and customer objects in separate query we can draw on both of of those let us in right and you can be more most another thing that we have
components of the major components of the motion of hidden example of when the just and this is generated the sequel we get this query and this would consist of query and called related because of the use values from the outer query but what could you all in 2008 and for each there selected from the main with and you can be sure it can for information and there is a well-known technique for all 3 places the leaders of this is left to draw 1 industrial this afternoon of divisional knowledge
about transactions was the the need to write a function that transfers and this is 1st from 1 account to another if you don't start with something like this here we get the 1st account by its primary key and check whether the mother if there is no not enough for most of them raise exceptions in other words we get to the 2nd account to this system current
this is the object in view of the 2nd the competence of the other any problems with this new this function the problem with this is that these 2 2 concentrations are not defined in the single transactions we have to a separate transactions here and we also that the the best connection after the 1st stage completed we can view the database in the inconsistent state among In the beginning of course all this stuff problems and the character of transactional time didn't so solves the problem and not necessarily because functions can still around they tell you they were changed by Harold transaction when you do save it doesn't take into account this data where changed by another transaction and then we can have a bit of listening
assistance they again In order to avoid this and we can use the special have function f expression or all of the records in the database
and for the purpose of you can use this like for a abatement this will be a lot more records in the database and not the transactions can change them and you are transactions changed the convenience of the programmer needs to specify
the select for a bit method explicitly often and applications consist of several years and the more complex applications you can get dating want you can pass it to another and at that point we can just use this knowledge into you might say over a little bit of a
point where it should be in a locked in the database person and if you so if you use it for a bit everywhere in in order to avoid this problem it again in your performance and point you can
use this like 4 of the method to but besides this 1 uses the concept of optimistic locking here's how we can write our function pointer the session characters mandatory when the x that the the way and it grabs the the of the view that single transaction the get just account objects and check that it
will not this not constantly decrease our and increases the other 1 and you can see that there is no need to call this safe method because when it tracks the object which were changed due to using map and all that changes to sent of the database automatically when you leave school they did this session what will happen if the
world transaction changes in the amount of any kind for the target the active users were right and you can do in the transaction and when we read that huge amount of the of the current for new to members of the old value all this actually
but now when you say that the moment of the W born and additional where conditions where his view this chair the end of the the query returns the number of rows each data during the operation is the volume was changed by April transactions that they're being the sequel operator will return this year and that means that the other transaction change the world and we have pure roll whether the current transaction in order to avoid that the was inconsistent
this approach is called optimistic we don't lock the objects just about keep the old value I for each other to visually and the red or world and
when we come in this object the data with the modified this object holds since they the approaches have scalable then in their own database it allows us to a more permanent transaction to work simultaneously I want you Costa would most of obvious and keep the David and consistency automatically even if the program and you can take matters into when they do that they give you a session undercurrent rules the scope to all check you the the the with in this in the same state another thing which
15 . use entity-relationship well you can yard diagrams for replication so like and this editor of this so that the 1st step is where you can get value of the you know that to preserve so you tangle and also you have at if think that use and relationship attributes in point you specify relationship to both attributes of both sides so here we follow the rules of explicit but
an implicit and it is easier to see a lot of review some of the entity tests when you look at so here you can see that relationship that we use some of the lines in order to achieve the ventilation cheap and effective relationship to maintain its 1st for the also going to use that data get the 2nd I controlled and so you will see in the Python
called of your into and there are several other attempts were
conceived ossicles tips for creating tables in the database
so those are the main features which
I told you to the view from generators and lenders for that with with 170 MeV if this a solution for plus hunkered problems in the case ultimate of innovation of fluid with optimistic transactions so installation that and that
here the map of when you're in the role the moment among agents by the 3 supported because of the most often after feature and then use it in a Microsoft civil service support because at this moment we support only for believing civilized my sequel quadrant and oracle and we were asked about it in support for Microsoft the sequel server and other this is something that you you add migrations and troubles with his suffer with that thank you very
much and do you have any questions be it won't of can you
return to the slide we to be session character but evdb session is when it comes up and yes the sum of how it
is done this earth thread-local local so something goes like this
every but that I
didn't quite understand that questions that held by not asking the same question again and if there's nothing else because the the the 2nd as a so they update is not on because there was some other things would it automatically re trying to do the thing or would it just saying you can't justify it from to the the data but did you session decorrelator or it can be used also as the context manager against justified did try for example 3 or 5 and until to try at India and plus-one curry solution just no for each of items in the 2nd Committee 2 modes of values when
the when we execute the query
so you can the 1st the 1st creditability orders yeah and then you have the 2nd Committee for
the customer yet because
water pairs the relationship to 2 on each customer has some relationship to menu
admission that so this is the
diagram of feature used for of the example see when we go to war yeah I understand that there there's a relationship but How do you know but you for
which just in which customers will be in the loop but you do you just you don't know which where we're going to look for just 3 C. 4 of those objects region has relationship to 2 1 and when start with the regions of this object we understand that it
might be needed at all other objects so you to you know 10 if you look the if there is 1 customer the main you to name for all the history of the many and this request the database is not a big difference in the real world the 1 role from the stable worked on but the big difference in terms of performance so that for all the orders or or customer context of exist yes you the we have all the and then we have a lot
the use of this query you see you all but the last line
CID in the least or ideas and then you look for all the seats that exists it that it seems to me
that that after you have realized that this seats so they have become come objects there's a lot more caching happening and I can imagine that that can also be very dangerous to time using weak references on amino however I how do you know that that after you have such knowledge customers that they can be in energy somehow globally catch them so that other queries OK so far 1 you select because 1 and you realize it and then for 0 order 3 you you you you you have some global reference to its use of that can such it again you yeah think that the questions but you know all this identity map this test is used the and while transaction so when the stock returns action we which you do this session
and when did the sessions calls for me just a few that capture sure and usually you don't work with millions of objects when you application to be you don't use mean you can use both of them if you if you want to have the or working object usually you also 10 20 funded maybe the problem was to show the web page and in this case it's not that the among
entities and of comparing my skill and a secure like for example mass scale has strongly typed columns and joint allows it to insert any data type that you want to know how do you handle this heterogenous data types of do justice disallow them or what how you do in to could considerably stick different data types into the same call is going to us to do that how do you handle these your mapping do you only allo insertion of specific data types or wears you filters and what what happens the With justify the type of object history didn't quite hear what the idea here is that that it my sterile your goal and specifies what data type will be contained within In a skill I'd be called type is just a suggestion you can still have different data types this images of text or basis OK how do you handle them well so you the point is we have different DB if your idea Reuters and in terms of 20 all those types are exactly the same and this is the the priority takes into account the subject let the weights but we use the path annotation of the time and the data presented in the same way in classical life maybe article about this difference in microseconds for example right the view of this type in the minds my supplies and put it takes that into account that using this all they were the in the DAG on on this particular digit this hi how is it possible for me to iterate over results without having all the results of this was not the sort of is it possible for me to iterate over results without having all the results in memory of the data were like like with the database cursor so you don't all that objects to memory but it's really just in the database just well it this I mean you that if if you want to update a lot of recursive what's wrong about the and for that purpose so because 1 method but recently we were asked about it in just a little bit more and you're going to get more functionality to that so you can of the objects without loading them to the of the catch so I guess we have time for the last question by you mentioned the Monte 1st light so that was false this is particularly so what makes false and the hottest compared to others the of benchmark data so ourselves to unitary robust and I test yet but we definitely going to do that but other users of the tried to compare them and this is still a 3 4 times faster widest faster and 1st of all because we can capture the result of the translation of physical because in general and simple automated there is no way to understand that he he is the son who was 20 when our problem is be in here that we can see that this is the same generated just ideas so can immediately understand the above to translated once again you just can't get that result from the caption and also the when we developed our 1 unit of the PG in terms of our work in you of references objects don't do you copy you just the attention to detail so when you just you still often treated in terms of resources for the media needs to run the working object if you catch you try to take into account we the prefer to the 1 with the goal we have a money things look for you talk has
Gewichtete Summe
Service provider
Gruppe <Mathematik>
Abstrakter Syntaxbaum
Translation <Mathematik>
Ordnung <Mathematik>
Gleitendes Mittel
Vervollständigung <Mathematik>
Translation <Mathematik>
Ordnung <Mathematik>
Tabelle <Informatik>
Objekt <Kategorie>
Abstrakter Syntaxbaum
Mathematische Logik
Konsistenz <Informatik>
Demoszene <Programmierung>
Endogene Variable
Spezifisches Volumen
Generator <Informatik>
SQL Server 7.0
Binder <Informatik>
Offene Menge
Wort <Informatik>
Prozess <Physik>
Familie <Mathematik>
Fortsetzung <Mathematik>
Element <Mathematik>
Total <Mathematik>
Lineares Funktional
Web Site
Arithmetisches Mittel
Projektive Ebene
Zellularer Automat
Operations Research
Speicher <Informatik>
Bildgebendes Verfahren
Einfach zusammenhängender Raum
Physikalisches System
Design by Contract
Objekt <Kategorie>
System F
Twitter <Softwareplattform>
Gesetz <Physik>
Monade <Mathematik>
Befehl <Informatik>
Kategorie <Mathematik>
Gebäude <Mathematik>
Kontextbezogenes System
Dienst <Informatik>
Rechter Winkel
Mathematische Logik
Klasse <Mathematik>
Regulärer Ausdruck
Arithmetische Folge
Migration <Informatik>
Attributierte Grammatik
Attributierte Grammatik
Rekursive Funktion
Orakel <Informatik>
Kartesische Koordinaten
Arithmetischer Ausdruck
Einheit <Mathematik>
Prozess <Informatik>
Nichtlinearer Operator
Zentrische Streckung
Filter <Stochastik>
Installation <Informatik>
Klassische Physik
Globale Optimierung
Geschlecht <Mathematik>
Strategisches Spiel
Objektrelationale Abbildung
Topologische Algebra
Gewicht <Mathematik>
Gebäude <Mathematik>
Zusammenhängender Graph
Einfache Genauigkeit
Migration <Informatik>
Inverser Limes
Mapping <Computergraphik>


Formale Metadaten

Titel How Pony ORM translates Python generators to SQL queries
Serientitel EuroPython 2014
Teil 115
Anzahl der Teile 120
Autor Malashkevich, Alexey
Lizenz CC-Namensnennung 3.0 Unported:
Sie dürfen das Werk bzw. den Inhalt zu jedem legalen Zweck nutzen, verändern und in unveränderter oder veränderter Form vervielfältigen, verbreiten und öffentlich zugänglich machen, sofern Sie den Namen des Autors/Rechteinhabers in der von ihm festgelegten Weise nennen.
DOI 10.5446/19938
Herausgeber EuroPython
Erscheinungsjahr 2014
Sprache Englisch
Produktionsort Berlin

Inhaltliche Metadaten

Fachgebiet Informatik
Abstract Alexey Malashkevich - How Pony ORM translates Python generators to SQL queries Pony ORM is an Object-Relational Mapper implemented in Python. It uses an unusual approach for writing database queries using Python generators. Pony analyzes the abstract syntax tree of a generator and translates it to its SQL equivalent. The translation process consists of several non-trivial stages. In this talk one of Pony ORM authors will reveal the internal details of this process. ----- [Pony ORM] is an object-relational mapper implemented in Python. It allows writing advanced queries to a database using plain Python in the form of a generator expression. This way queries look very concise. The main feature of Pony is to provide a method to write declarative queries to databases in pure Python using generators. For this purpose Pony analyzes the abstract syntax tree of a generator and translates it to its SQL equivalent. Following is a sample of a query in Pony: select(p for p in Product if "iPad" in and p.price >= 500) This query translates to SQL using a specific database dialect. Currently Pony works with SQLite, MySQL, PostgreSQL and Oracle databases. In this talk one of Pony ORM authors will go through the process of the query translation and dig into the implementation details. Attendees are going to walk away with the understanding of: 1. Principles of building a programming language translator 2. Python to SQL translator implementation details 3. Approaches for creating a pluggable translator architecture The presentation outline: - Why Python generators are good for representing SQL queries - Main stages of Python to SQL translation overview - Decompiling Python bytecode into Python AST - Translating Python AST to database-independent SQL representation - Generating SQL for specific database - Pluggable translator architecture - Performance concerns: is such way of building SQL slower or faster then Django's and SQLAlchemy's?
Schlagwörter EuroPython Conference
EP 2014
EuroPython 2014

Zugehöriges Material

Ähnliche Filme