Logo TIB AV-Portal Logo TIB AV-Portal

On snakes and elephants

Video in TIB AV-Portal: On snakes and elephants

Formal Metadata

On snakes and elephants
Using Python with and in PostgreSQL
Title of Series
Number of Parts
CC Attribution - NonCommercial - ShareAlike 3.0 Unported:
You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal and non-commercial purpose as long as the work is attributed to the author in the manner specified by the author or licensor and the work or content is shared also in adapted form only under the conditions of this license.
Release Date

Content Metadata

Subject Area
Using Python with and in PostgreSQL Python is one of the most popular application programming languages and there's a plethora of PostgreSQL libraries and utilities for Python. This talk will try to give an overview of the contemporary Python-PostgreSQL landscape in a way that's useful both for Python programmers starting on a PostgreSQL project and DBAs dealing with what those programmers wrote. We'll try cover a slightly opinionated selection of libraries, frameworks and technologies and give some recommendations. The richeness of the environment is sometimes confusing. Python people starting with PostgreSQL often don't know which driver or ORM library should they be using. Sometimes they're not aware of all the things PostgreSQL can offer to a Python programmer and the tools available. On the other hand, DBAs sometimes need to debug Python programs (mis)using their database and PostgreSQL-savvy people join or consult on projects written in Python and need to have at least a basic understanding of how Python works, particularily on the database connection front. We'll try to make both of these groups a bit more comfortable when dealing with the other. The talk will cover available drivers, focusing especially on psycopg2 and some of its lesser-known features and ORM libraries, focusing mainly on SQLAlchemy. We'll also discuss PL/PythonU, the possibilities it opens, along with some best practices and caveats.
man mining Computer animation maximal life Torque Video
man Slides means email Computer animation code system call van PDF extent
point decision Coloured consultant van information drivers Google software Payload web man enterprises Development projects Java Databases applications Types processes sample Computer animation versions website cycle form systems
point man Mass MACH versions drivers Computer animation robotics communication Payload Right level Gamma poles
Slides maximal knot drivers powerful versions fraction period drivers extent share plugin position Chi-Quadrat-Verteilung man standards sin wrappers Databases expansions words Computer animation PEP drivers versions extent
Actions presentation parsing multithreaded Java code time singularities workstation iked part total Compression programs predictive computational information subset fraction connections mathematics PIT configuration interpretations framework model extent man services email wrappers traction The list sampling expansions bits scans backend connections message-based means interfaces Right sort write poles Results record point implementation functionality modules Sequel varieties translation drivers cursor help van production powerful drivers specific Formula Bipartite ensemble level testing implementation Gamma conditions modules default server interfaces projects Java Databases limitations system call powerful particle Indexable Computer animation environment case Query drivers interpretations American Physical Society speech life iterations objects protocols extent
Slides functionality interpolation existential quantification gaps print maximal argument distances van elements MACH notation string ensemble Gamma man meter Java argument instance sequence Types category sample Computer animation Query string communication placeholder WPAN cycle
implementation code Integrationen ones maximal cloud platforms Continuation cursor dictionaries van mechanisms Types notation Synchronous information extent addresses classes data types man NET The list storage provide scans connections Types Arrays processes Computer animation Query chain interfaces statements des classes box extent
functionality response Julia expressive storage cloud platforms cursor dictionaries Sphere van Types uniformity Computer animation Void's Query statements ensemble WPAN classes information extent
views time sets schemes clients argument rollback part predictive data management connections hypermedia socket extent man Transactional Continuation scans several open subsets connections category data management processes ring interfaces cycle modes filters functionality control interpolation cursor van versions statements information NET interfaces states Databases incident cursor mathematics Computer animation Query versions statements objects extent
man Development gaps Development projects maximal knot argument argument scans applications sample Computer animation case sets versions configuration life WPAN Payload tasks form
complex Actions decision singularities sources schemes open subsets part web report structured data Types PIT Rolling framework model position man curve mapping Relational Development beasts storage The list share Transactional subgraphs part scans sequence several connections Types normal model Right cycle point Vacuum modes implementation Regular Expressions Sequel maximal experts van powerful number domain objects goodness Board operations ensemble Payload lies conditions default response key operations smart Databases limitations applications words Computer animation Forum drivers versions statements life key objects box table Jump form
man beta modes existential quantification key states operations Databases scans applications part Types Computer animation Rolling ensemble classes model objects form form
man table key mapping breadth sources code applications unicode Arm van predictive uniformity Computer animation logic ensemble classes Sum key objects table form
classical Slides table code time ones water unicode van uniformity model form classes man generate Arm Development interfaces moment code Ext bits frame words processes Computer animation classes key objects table form
web pages software engineers Regular Expressions table breadth time maximal compiler iked part van area predictive powerful man Arm key sin Gender expressive scans limitations plane Indexable Computer animation Query statements key table form write
standards code singularities argument iked programs predictive information web cryptography different sets middleware man track information systems real Development inner rollback Transactional part scans variables backend substitutive connections category data management configuration Right modularity procedure figure track functionality server Regular Expressions Sequel factor events van versions operations ensemble response law Java code Databases applications Location Computer animation logic objects
Context runtime Java code time argument dictionaries part data replication energy Compression variables information Types different memory interpretations single extent systems man Development sampling The list scans variables sequence backend connections Types processes testing Right procedure cycle Slides functionality modules overhead files Sequel van production versions causal memory operations string integers equations form modules overhead information code Databases directories DoS applications words Computer animation environment case Query string functions interpretations objects Tools tuple
email Actions functionality link Sequel Transformers images maximal division total van domain image caches Display extent errors modules man services constantly constraints matchings interfaces The list Stream Ext Databases instance applications connections numerous sample Computer animation Query modularity cycle libraries extent
man domain name flow graded Ext powerful domain sample Computer animation functions string DAS Right modularity model extent errors record extent
functionality table lines time The list argument dem domain completion internet DAS Relation ensemble domain modules icons man flow Ext Databases staff gute DoS timestamps connections means Location sample events Computer animation functions calculation website modularity
man beta schedule system call Regular Expressions lines time unit rates gute domain Pell's equation orders sample Computer animation functions des
and this is going to talk about this it of what we have and you I think I think that took something so there's a there's a saying that says that every situation in life as accurately and completely described by Annex the common for that's mine for this well and so the torque is
gonna be about Python and the general ecosystem of from surrounding cultures so my means young that's my my company and that's my e-mail and so if you look the
slides you can get them from them schools makes an elephant PDF if you wanna follow with the call you can try doing that I recommend is doing it afterward the slide if you if you want to know so what make virtual appears to the others it at so but it is as you know we can get the slides full hour or back-up himself so the the
basic idea of the talk was to give some days in the people that you know Harry the consulting on or projects that use Python their companies are funded by the and they can be used to take make decisions on that offer deviates that work in Python shots and you know that last little person given them trouble and they need to can understand what's what's going on with so the next door quick like I quick gracile language and that the point in question would aversion tissues the vertical diverse or how the actually talk to the database that is that some of that such a long history with Python we'll take a look at Lawrence is you if you do application development you might end up having contact with the and new by have to deal with that and then we'll see pure Python which is up and that type things like all boulders and and when then I try to have some fun with that so what I found
is that it all warring enterprise technologies on like for all those had languages that called kids use like job PHP always these can you project cycles dress up by the most recent find 91 very so it is all the legal drinking age in 2012 and like the other projects up it's sets subscribes to a philosophy that arise when so just going put the OK so it's it's just a philosophy that says as there should be 1 way to do it as opposed to so it it really a really but some of you know it it tries to be maintainable readable we know it's a Doolittle and which as as people of color and then it's used quite big shops like will last sites it's really widespread so that it's it's something you you might consider and it is it's it's quite so what
problems as poses people do we have by so up there so many diverse you can just imagine rule by from the poles dress you get lots of stuff and you will get really old stuff really obsolete stop you get misconceptions so logically enough that there are quite a few islands that are there is there's kind of confusion about how to use them and they could give some visibility freelancer consider that are good to but then there's this to within 3 mass the people on a into Python they kind of past should I use the latest version sure I mean that that's a quick and the kinds of really so the idea of the talk is to be opinionated and so I will make some assertions that if you I mean if you don't subscribe to we can we can think of mobile robot the idea is to you know you go out and your research but these are can model communication so so we the right to
point 7 we don't that's as well
actually so Python 3 is the future and it will come it will become the standard it's not the standard yet so you so just stick with with 2 what because everything works with 2 and not everything was 3 and then there's no real reason why do go to the leading edge that's gaining adoption of still while you can just stick with with the thing that's not widespread you if you write your stuff in the correct way and use a recent version you will not have that much pain on so think about it but stay with by the to refer only to the so if you if you do the things right you will have a power to Python 3 great in the future but for now do this and that this this whole talk is going up and will be saying but this worse with 2 discourse the 3 up to the 7 this is the thing you should be OK let's see how can you talk to the lady of waste from Python so there's something called the the API to 0 this is a standard of blessed by the community of how do you talk to a database but it's you might come across as too many people refer to it as yet that 2 4 9 but that's alright arrestees for 4 by so that the size of the baseline of what a driver should it's really bad because it's supposed to work with any database to scan the lowest common denominator of all they have is access for quite and so many drivers just take this and then they implement their own stuff position it doesn't contemplate the features you can get from like all because this is the period immediately I should be to use the identity with with slight equal you know maybe to bolster so I have to provide extensions and you will have to use expansions if you wanna really use the database fully or or even of usefully and so have people specially I've come across all of people in the that kind of had its own DAPI but it is really useful it has proven to be so it is good to have a baseline and have to assume away so there's this is at least the plus some stuff but you have things like you know wrappers or proxy you or or kind of blood plug-ins that by the virtue of of being able to assume this is evidently the API to they they can provide useful things so you might not be like it but it's it is a useful center was so it wouldn't do a quick overview of the existing diverse and this quite a few days right but I've ordered them from the least interesting the most interesting moping and so as not to a few words of each 1 of them and as you can see from the previous slide was gonna have a going talk about technology and the day after so that's that's that's
basically what you should what should be said driver but let's take a look at each 1 of these 2 2 2 Can you
know what they provide so drivers come in 2 varieties for poles there are things that rock lead and they providing our Python API about the CPI that that that doesn't PQ and they tend to be fast because of goes that the actual probable parsing database that love is done in they tend to do things right because if the PU does something wrong you might argue it's it's you know it's is kind of the reference implementation so they they tend to get the property rights and stuff like you know PG ports or already have your delta G passage using the PQ or proper will work which is which is great and then so we have basically that that's that's the advantage of doing it would as a issue laughter and then there is a variety that do just implement a protocol in your life they just be you know should fights over a network and by implement the spec and so what about the advantage would be to be able to run this kind of like the environment when you can't use a c extension so in presence with Python interpreter the not suffice so anorexia formula with by pi adjacent diphone is an implementation of the Python language in Java and that it's not trivial to use a expansion in the as a that the APS are all different underneath so that our pipeline implementation of the Python language in Python action subset the Python in it it's not project and so if you wanna use by by you will have to go with something that's not only be corrupted or you have to do some some facts that I want go in and also sometimes you can you will see how being a product limitation might help in some cases that i've I think I prefer to PQ representing would usually people report so now let's let's start with the you know the the merry-go-round of my from diverse all stress this is projects by these quality wrapper it has a model that's the mother with the appearance of the say it kind of inactive so just for historical interest and you that's quite SQL this wrapper that has its own interface which is going extract translation of the PQ calls to buy things and has deviated ideal module that you that you can use if you need to be the DAPI compatibility of project seems to be alive still not very active but it looks like it could be but it's that I would use that give another option with the PG sequel that's if you buy the implementation so you know if you like implementing natural particles you might know how they did this up it's kind of actor the news that there is a i.e. ODBC wrapper if for some reason you are compelled to you the the the the level the then you might try that but I don't see the point of this fall that that's is that but there is the GSN which include here calls up up because I like this that and the twisted is a non-synchronous framework for Python it's kind of like the no GS for quite some time but it has been 10 years of history is really more robust feature fill up and so you know the the programs that I would recommend it it's interesting because it it gives you access to the asynchronous features of the of the protocol which is which is nice look all drivers and do that I think it's it's important well because of the fraction Asynchronous computation is is getting so it's if you buy the implementation so that they can do things like you know you you on the right the message the soccer if you write and then you wait for something to come before that that's where being a Python implementation the protocol is used for various speech thousand and that the name actually comes from its so they they say they are the 8 thousand by from driver for calls dressed this that yeah that's that's that's the 1 more out of this was actually active apparently but it has a the API interface and it does extension which is good because I did it obviously will not come to light at large object goes up node has to work with with this like it's it's actually think about how difficult it was to get a specification for a database they pay for it they vise database access and that everyone should try to write mean Oracle people as got people all just people everyone just trying to manage to get to where you really core part so also the school they do this and then they give you the rest of the power end if you Python so if if you wanna Ronald bipartite despite this is it's jet compiled so it's I implementation that that does just so if you do calculate intensive stuff you might try to use by pi and it's it's really getting some traction right now you would use that problem but how the spiteful this wealth interesting projects but is very actively maintained and it is if you're Python implementation of the protocol the way that some C extensions for speed in it doesn't have a DVD a module but it's it's kind of know stuck in there just as you have to provide them but they they just read everything they do is they threw everything away and then read everything thinking about polls so it's very well integrated with the features of the that was dress gives you you know you have up as an interface for advisory locks you have you know all these things is that people find other databases and the different they do provide them and then there's the key points is vital so you know i it's it's a shame I itself and I think that was a good idea but it to do that in Python 3 only but until Python 3 is really you know has all the packages available as and is install a default on your workstation sort out you you would use this but it's interesting project and in the signal region which as I said anything here wrapper which is the API to and has lots of extensions that will try to go through with the uh is very active in maintaining the Committee's traits of mailing list is very responsive people know bugs get past that the condition is serious that's really good it doesn't work with Python 2 and by so 3 so you know caught here lately the already ported and you can use it with with 4 there's of language but it does pay attention to to help threading in Python works so it's that document that is that they don't care about performance in red ink environments I won't go into that even if the you if you wanna to go inside and will have we can again and so talk about how to spreading works with something that people you know there's a lot of fathers saying no Python present work so not really about that they they care about problem which is a good sign so OK so you read you're going to go with the cavity let's let's see if you examples and and talk a bit more in depth
about the future so that's a simple the DAPI sample you know you can act as a database hideous and they can be PQ is and you can't so you can use it as a mold you can use Sierra popular you all all that stuff you you you create a course on which is this has nothing to do with with with polls dress courses as indicate that and so on that this is immediately I think that you know rubs people the wrong way usually exposes people because it's it's it's kind of confusing but this is just something you use to to run the query and to store the result there's nothing more to and then execute your you know your code and then you you just use the federal function that the iterates over them and you have access to the roles each row is in this that that that you can access by index and there's actually extensions to make this accessible by name so you wouldn't say you go 0 which is back you'd say role time so you say I want the attack but by by default you get there you a list of lists each rose up and then you close the connection so that API to actually use test you like that that will change and that would be the only part of the change that's that's stuff as mandated by the most
so parameters that let's say something that bites people accused cycle we it's not that obvious if you don't the communication just riding run with it up to you if you if you have shady you use placeholders and so you say S and then get substituted at safely quoted with the with with the parameters and you have 2 ways of passing parameters either use per cent s or user name that the notation which I'll show in the next slide and the the the parameters are always a sequence so let me just show you
when you hear if you have in to execute so that about where last name equals person as without any quotes and here you pass any 1 element to pull off something with a with a single quoted this will gets accorded properties so this will get to know them quoted and and and the query run but the you see this is this needs to be a sequence which is something that people are you know something's not aware of or just forget and and that and then you don't put any quotes and they get put by the drug so that this is the main notation equal so say you know this is this is a person s but it's is gonna refer to my prefix parameter so this season piping if you repeat the same parameter price and then you can just say what our wants and not you duplicate called which increases the probability of having type so and then integers for instance you use person s as well always use person s which some people get confused by because for it kind like see print have escaped the 2 glasses so people would use person the for into and person s 4 strings and so on but with psychology you always per cent s and the the quality looking at the type of the property so this for like this will get single quoted and this will just be passed is that there is a distance function I put here called more refined that's does that you know that the buzzer interpolation but the long run it if you just want to get the SQL is the run for just 1 accorded and then no ship somewhere else longer whatever you would use more refined up so
what lexical producer break apart from being a foster DAPI implementation you get all kinds of goodies like if you pass the list it gets transported right so that's that's cool has like built-in support for poses arrays but if you posted at tool you will get it's without without the parentheses is some notation so if you do in person as impossible as we do in and the and the the the thing that using encloses you can register your own code to cast from applied to other types so lists but you can you can say that H stores get red as Python dictionaries which is which is kind natural and then you can defend Boston dictionaries and they will get serialized weights to just call and the same thing happens for interview idea you get the Python unity died in it you get the class that can wrapped in a addresses and you can write your own and you can customize the process of creating pair of running the query you can you know they're blue hooks that can get you inside and that's useful and were seen example of how this can be useful and does synchronous quite good and there is a quite a rich implementation of of synchronous features out so you might have noticed I mean I am and the synchronous truncate so that's why that's why even that so much but but yeah that's something I really like about them so that this is just an
example of how would you use that the extension mechanisms to get the whole into the execution process so say a instead of using you know that the standard course that they get when they do the connection that course I get this thing that translate queries I will use this course so that if when I execute a world this method to log the modified SQL statements and then just run the the chain up to this request and then you just you just say give the course but use my incur serve as the class and then you will get and then you will get those
of the query log that and then you can do you know you can do all the with that you can do you can try to rewrite of as well you know what it's
um and his resemble of how a store works just run a function that says a when you get something from the Q that's all this and this is obviously your responsibility to get that it can get automatically but we should probably get it yourself the doesn't really know what is a story and you can have a type in the public sphere that's called solid something with a different so you just say it this way the is a star and then you know run selects when you pass dictionaries and this is i'm logging that's but slowly and assert statement and it will be summarized 2 plates that will be transformed to a which was very convenient and and then actually really really useful if you're using expression but there
is the so yeah that's an example of or asynchronous mode you to connect you say I want this connection to the asynchronous This is the media the media extension obviously they don't contemplate a synchronous stuff and then you just say and I will wait until the connection gets open because the the connection to the process is a single well then I will get my cursor I'll I I listen on my scheme over several channels and then I will wait for this query to complete so it here it's it's waiting and it's it's weighting again and I'll just about infinitely wait for something to be available for me and I will look at the notifies the lot of so that will know you run this and it will stay there would look it humanity view untill and litigation is this provides and then you get instantly notified about that and so that the weights so that function is actually something that's already provided and what is very simple you just say you just as the connection should a reader should right where they have something for years of information and them about give control back to the user if I should read I will select with the uh with the putting the farthest apart all the connections in the readable set and I need to write and wait for the socket because right so it's really you know it's really need you got and then train understandable and and pretty
will go on to say that this subject for psychology that's transaction management transactions start automatically come out so when you run a query it will do to begin and then around your query and then you should not careful you get this this actually just the all right if you are dishonest select some stuff and then you the object in sticks around because it's you know the connection is a property of something and then it wondered garbage collected so includes the ground you will get an open transaction that will block your vacuum and and that's going to that for you so it think about that and every time you hear on something and you then or Comet oral back for the transaction or just say all Camacho and then you just you know directly past but you said what you do to to the database without incident begin on itself so this it used to be worse if you had bad experiences with that that stuff could fixed recently so I it's kind of chance alone just mice so use a recent version of of psychology because they're like to former was really good reviews before 2 I think is the current 1 they they really fixed some stuff so what's missing and what's so what you should contribute to cycle the very they don't do parts so they didn't do speakers occurrence the SQL is stringified on the client side and the central database so that the fact the parameters interpolation happens textually and the text is shipped to the database of so people will like over the years now from by someone up you says hey let's let's do the careers of France and then and people say all the interface holiday document fully explain this in the back you kind of rings the bell with some other place I know that you know so that guarantees get marooned by a kind of documentation issue the stuff so that might sound familiar to you uh but yeah it's it's it's still there and then that prepared statements are of the filters in the my hands are being Keegan's occurrences faster because you don't go strength manipulation Python just ship that other stuff was just a copy will not work with a synchronous connections but you probably won't care about that so much out and I'd really like so the the thing is used for parameters and they are a great but
if if if this is something dynamic that that your application and receives externally or reads from configuration violence in support this 1 you need to interpolate inside by Python and and do it yourself you currently here % hello cent something and have a quota for you so this is something you need to do manually and sometimes people don't understand the the parameters they get with no parameter called encoded and these are identified that you need to code yourself and about the terrible them yourself so it would be nice to have
something with user from rocks so warrants white why would you need to know right but what makes well actually I think there are advantages and so on doing this discussion to the theme of the our use cases what reaction actually would like to use a lot and it it will allow you to you know have less experience developers go faster and it will help you for small projects so it's it's it's a fact of life so just talk about that sometimes you just don't get to pick you get ask which are a should be your your your project it won't ask you should so
so the answer is simple to use SQL can we have that before we go to SQL I need to say
a few words a jungle because jungle is that this is something that you will have contact with someone right because everyone uses jungle if they do Python on the web and almost surrounded does jungle uses the built-in database relationship may that provide so sexually it might be useful so don't dismiss it I'm not saying that such a bad thing about about gender and don't dismiss it outright out it can be handy if it does provide some features for a small it's like you know what you do is you have you have you know a shopping list and just add subgraph of small online shops that's that's fine adjustment or developers really happy that implementation will be no they will have the 2 condition alongside their framework recommendation that's fine but it does have a few issues as so it doesn't support multiple-column primary keys which I for me it was the 1st thing that this this the from the project so that they had that does not support so it it's kind a generic it says OK this is our and you can use it with my sequel with posters for this life so it is it won't give you good support for positive features which we so it just people about and that kind of limits what it can give you that there was a so there there's a bunch of shortcomings of that that I won't go into but the rest a lot of people don't need last year through go so I have the the jungle shop and they have this huge problem with with transaction that get finding and lovely vacuum and you know it my shared memory and so on so that was a problem they think they worked out so this should now be much better but if and if in doubt just remember that cycle you will open the transaction by default when you 1st access the database and so in Europe jungle request-response response cycle just make sure you close the connection when the when the regressors the right so so then you be for me I know jungle shops that were great with with with cycle PG and and just and and I'm going to define issues and I know people who complain so how to cope if you need to use it and or or if you made the decision to use it you detection fuller definitely because every request response cycle will open the database connection was it so as long as and you will have no you will have to up the marks connections to fight numbers if you want look for these online transactions view was that this might be a problem you know the proactive don't wait until you write your and low explodes because of because of open transactions you cycle because you can use other DB API drivers with the giant and don't use anything else you psychology and if you want to dismiss it think about it because there is no it's not that great it's still very well integrated and you will need to know do the legwork of integrating different or should you should you choose that so think about them so this is going to a sqlalchemy which has which is not only a norm on its side it's a complete no framework toolkit where recall at that gives you everything you would need to work with sequences from my phone so there are 2 main parts there is that classical or and when you get Python objects you get your database relations and you the map together so axis things from your application and then they get trapped the database and then there's something that's like SQL about we made in Python so so can like riding a school I had on using Python constructs a museum example of this what is actually quite good up you can do everything with SQL is there something you cannot do they will consider the about so this this this is a very sad yeah we do this and you need something else out retro SQL or or whether a neuro neuro are no they they their philosophy as it needs to be possible because we we do everything to so that's as can be and if it has a learning curve there are new words will have to learn new concepts it's it's complex but once you get the hang of it you know it actually pays up to some sociologists out but you you define your models you defined tables in Python but it feels like just writing sequel statements to create a schema and then you match them together and you don't even have to map them together or you can cannot some objects to 1 database and some just another data source some objects to you know this action some to just give them enough so that's good you get separation between your business objects and your and your database it does support things that you wouldn't think ornament what spot lie on a no data-specific types say in a you know you ID in at for all that stuff operator so you do it all that funny point operators or alright store axes operators you can do that with a stuff but you can define your model so they will work with cascading deletes right so you have an and or an object you did it and then that the objects that that had a collection of those will so other around so you have the user and and and the user has power a said books right and then you do need that user and it will know that all the books of that user got if you confidence that but smart enough to the follow and you can define at that's top of something you don't see every day you mark it supports schema at scheme which that's that's right I mean you you wouldn't think of and and so on and so forth it's treaty you can do everything and it tries to get the last 20 % right so diagnosed as it's like 80 20 right we do they did per cent of the stuff right and that you're on your own with the last and they say we could little right the cost is increased complexity but
so how would how how how would this work if you treat it as an object which is just a simple python object there's nothing database or nothing here is just know your application yet there
is a so let's say it's it's basically foreign keys were on update on update cascade so you will you will it will know that if you get an object data such through this and say say that the database it will know that dependent objects will be changed so it's it's kind of a smart enough to fold in the sense that it's it's it's more it's more common to have this form of the elites that you say OK I did something of an I axis of the object and it says something's wrong date you know you deviates from from underneath you can do this with us like can you you can it knows about the database will become so we're jungle jungle just 1st delete all the dependent objects with that the state of individual parts and here you can say when I do current the database will take care of the children objects do not touch them they will not be there because I have a cascading the 4 and so
on so if you have your application objects you
have your table so ignored the magic for now and just it's not that I'm so yeah the sum of the phase so that the fingers you say I table that's called car that has these 3 columns and this one's a unique 0 column then I will limit the length it's only a primary key this carmaker and is gonna tool to to the table with West or makes as limited horrible so before we look before knowing can I wouldn't dream of alarm that will know what deferrable foreign key is and that will do the right thing with the source straight and then you know I mean prices numeric and as I have my table I had my logic object and I just mark wanted the but I don't have to say alright this map to do another thing do is and so right you have this and this and don't wanna you
know have your object separately in
the new tables separately you can use the more classical prose at other forms that actively growing things on use which is no just say this is my class that has is reflected in the database and that looks kind of the same but just in 1 Slide title of so that's so it's kind of quicker if you don't really need this was that of the of the world and the use of the word 1 word that you can go there is a little bit of time and so on way that that this or religion or were you and all of you you might try to define your models both in jungle and then separately defined like this and hope that very in the definitions mean the same so when you access it through this interface will be the same as the ones created by jungle she might try that I would advise against that but I think I would just you know if I needed something more than 10 or I think the HIT and do do migration right and right this moment and I would just yet but that but that is so I have a slide that says well water doesn't really fit with jungle that well so you might have immigration you might you know write some code look into the jungle of schema generation process and dubious locally thing that's that's actually what we do but it's excellent working you know your frame of well that your developers might get confused if you do this so it's that you want to go find 1 million StackOverflow answers to everything you need to know what's going on so urgently exposure said I just throw out the your arm and putting us look and say OK you have you have these 2 ways of defining
stuff and that was great and smoking and that's the part when you get excited is that it's it's all SQL you you you have it in you can just reach out and touch it you can take your your car which is a table object right I'm talking about this this car here and is likely magic incantation become pilots but I still think it will give you know I need this to be taxed but it's limited would by length of this was supposed to be in the middle so it's text out there's a node before foreign key that works so you can you can see page this well that's pretty thought as something that compiles down to us so that was the and now for
the expression of which part so you can use the your like in a typical way of enlightenment power I need to know its price or need to know I have this make mention of the cars of this maker they have to adjust take the make that the cars attributed to load all the cars magically find that or you can just know hand tighter SQL but by the hour so you you you get a rescue well if you think about it if you know just go over your going to hold your nose and say I'm writing SQL and ready throughout on this by biases as well they're going to find an answer to the plant adjusted being phone because you can you get select statement should be enough to a different class such as the time whatever and it'll take on what poses and and some other thing would would pack on the limit clause or ill rapidly subselect you get composability which is are you will be able to you know use the arm then in places where gender is that queries or you wanna use that is something tricky because only then you get your beloved index and you will be able to do this and like if you can't generate a statement you like to hand this is about and it should give to hold this work so
this is to right is set up a date set price equals price for us to where make lower make is just to notice a few things I was able to use a lower function I will be able to use any function I haven't the procedure have so and it'll it'll figure it out I was able to use the usual operators and it got it got put in the workflows as equals and there is no real substitute for the parameter and I was I was able to say said the price the price goes through and it knew 1 right and so should should this be a string and of say up and they can estimate this will become the that the 2 kind of like that that is changing nation poses with smart enough to take your Python and make it as well so for that this actually things go the doesn't feel like you know this is bad or and is that it's doing best separated erase this is I'm writing my wife sequel only in doing it with this being the same language as the rest of the application variables and I can use parameters so that's that feels right up so
I guess at what you'd get from using locally oral 100 SQL which is something we all like to do something become is that you can you know it's you have location in 1 language that you think the developers are happy you can test the easier you can make it composable or or more customizable if you if you do delegations concerns correct you can only generate SQL and there should enough to your no executive layer your up or your ill-prepared transaction manager that does is that the law of that more that commit you can you can do that you can ship it off to an asynchronous exactly usually kind of thinking about that they will just the slogan but you still get the event writing it in your Europe application language that you can also meant you called your your logic objects with the with with with stuff that's the way it will cost you to do this all like you can write some code to make a column of transparent interested when you start so you could say I'm sorry something reversibly encrypted like a uses a P a key or whatever and then whenever I will say this it will get trapped in the and they use in increase with the with the Quixote setting configurations and when they Rediger's strain and then again the descriptive but database only see the encrypted with only stored encryptor version and you don't have your your your objects No you won't have to always write 1st concatenate last name you will be able to just have the property that it gives you that on so you and or might be actually useful some yeah that's happened just skip over this factors were 10 running late so 1 if users alchemy you will come across this session objects that kindness sits between your connection and your code that you use to access the database of and that that keeps track of which objects are dirty and which need to be persistent database will use it this is the thing you will commit or rollback the session up and the good thing and the difference with the language it makes it easy to have persistent session objects so we won't have to use a polar if you can just store this session object in some persistent context like if you're doing web programming you would use with the and then you would have something that that executes 5 from cold and gives them generated responses to the web server and you would keep the session object inside the was the process of persistent and then you wouldn't have to use pooling because you wouldn't use a you know create a new show connections on each response request-response like are
so so that's the so but the the last part of the aquifer was kind different built by whom is of being able to run by so called inside people's dress and home on behalf of the of the bowls dress process so it's it's kind of different but it's it's a way you can be sure application for status by you might want to use if you need to step up procedures you might want to consider using Python for that so then you're your developers are happier because the energy with 1 language and they don't need to know to learn you know the sequence of so uh so this is python inside the black and it executes arbitrary cold so it's like a can run anything at all the files that Calpers soccer can read your data directory so it's just that you need to know assume that if you're in environment where you can't use untrusted languages you know like I am not if I have the same rights a few words about when or if this could change but that the simple short answers is very difficult to change that so that will be entrusted for a long time and it is vital so you actually can do crazy stuff but that's in the form of using Python cited some saw this this work but when you run peel Python called an interpreter that the whole Python runtime gets created instantiated an embedded in your in your back and process so if you want this to be fast you should promote your Python tho so you won't pay the overhead on the 1st we often no the open looking up the show criterion and so on and you want to use long connections so this so it so you want you have to fill this week we'll just be kept in memory it's well quite well integrated with people's respect system so if a pure Python function that receives a 5 apples dress integer you will see it as an integer sci-fi film and if it receives a year of say right you will see it as a list so these these things and work of the rest of the types will get serialized strings so if you have your your your own praise extension thousand what all this will do is it will call it text sampled function get the string and give it to Python so then you would have to pass it back into some object that's that would be useful to you there is an extension for a very typical case of passing exhaust into Python 10 operating as dictionaries so you might want to do that but in general you will need to be with strings if you have completed by so the our function arguments to start procedures are visible as global consumers sample so you just get to answer them as if they were arguments to the to the 2 equations functions and then you have a of magic that you can access that will allow you to run queries on the database that into context will you will be able to access the tuple that's that the trigger is called upon so we will be able to act upon that examples in a minute end up so that that which modules are visible depends on the on the Python but this is a the environment variable that's been like classpath for Java or bank for Perl so what what's available with visible depends on the and the back and process the bottom processes get for from from the possible so if you need your application code to be visible to your appeal Python functions you probably will need to be headed environment that was dressed executes and to include inside Python but your application deployment directory or or were they just something to keep in mind that that's the way to do this so like this is thing is
just 1 slide about he might feel quite and all that but if you don't play with it different actually run production stuff with that key political inside a module instead of replication don't just should database given under version control inside your application will not make all the sequel functions just like if you create a function of some arguments and and the only thing that actually happens inside a sequel thing of stored as a single function cycles rest is should get the function and you call it and you pass it know everything that's that's inside the scope of this function so this would include the argument is this will include the the trigger information of is there that would include you know the module that you used to that provides intuitive and so and then the rest happens here applications and that has the advantage of being very testable is again this function you you just no marks for that and you test it just with the rest the replication so it's kind of that's that should help and up and always be careful cause your your running full blown a full-blown application language inside your database you can hurt yourself better that said that there
is a few funny things you you can do but also there's some examples give you a taste and maybe some ideas about what can you do you can for instance important numerical library and do fast Fourier transformation inside your database directly using using Python older than calling to optimize that stuff you can write a check constraint that that checks whether a column that stick that text holds adjacent might not be the best idea but but you might do that or you might check that a by by TI is a PNG image or know where you can open connections you can you know import cycle PG collect low and how like DBI link with that you can also do it should requested invited catches on your and 0 I think list the last year was talking about using pure Python to invalidate admonished catch when something is updated in the database using a should be request that actually that's a pretty good idea to get an error and you can do you know things like axis and so on so here's
an example that that's a function that will get an extension name and that'll get the standard display this library for doing this it will get all the available except for the extensions so it uses the PO by module that that's that's something you get if it's available globally in every group functional includes an SPI interface so you can run queries just like in in the sequel you will die you'd be able to run in all selected executed that's how you do it by phone and then you get all the names and you say getting the closest matches for wrote for for what written so this is X and right here and these are the possible names that have so that would work planets
which so that sigh run fine extension it will return and the right so and nested that is supposed right right can colonists and looking for this fuzzy string give thinking of that and it will find helpful for instrument much and if I look for a it's true of it will find it so you know we
can run arbitrary cold and
use all the power of by model all the ecosystem there's a yeah
but there are 2 more examples so this 1 is a check that might be a borderline gradient by the the recommend but it can have it kind of shows what what you can do so get trigger that gets the you know the new global can splits at on the on the at science against domain name even checks if it has an annex record and if it doesn't it throws an error as well because you don't have an an extra right so so this would actually effective
Internet connection of user
so yeah OK my e-mail go
concerted but example or doesn't have endowments going so it says no 1 extracted for domains and below that's that's kind of cool
and then the last example I have here is you as a function of the show you that that you schedule in the database which works by getting downloading the tight calendar that's available on the web site and passing it was like under module and looking for events and as returning the summary of the location and the time and you will see I have 3 all parameters to our text 1 wanted timestamp and actually it would be kind to me would be able to do you know calculations binds staff and so on so forth so I mean
let's let's just look at the schedule and units that I
just downloaded that the i calendar and I passed here I have my they times and so on so we're done
we are slightly over time there's something up next day if you won't if you wanna ask questions in South in this new company after this was saying FIL