On snakes and elephants
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
Urbanski, Jan
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.
 in case people come from better talk. So there's a saying that says that every situation in life is actually a completely destroyed by a next-guys-cd comic. So that's mine for this talk. And so the talk is going to be about Python and the general ecosystem of Python surrounding postures. So my name's Jan. That's my company and that's my email. And so if you want to get the slides, you can get them from there. It's called snakes and elephant PDF. If you want to follow with the code, you can try doing that. But I recommend just doing that afterwards with the slides if you want to. So... Where? Make virtual... Make virtual... Yeah, there's a type. Yeah. So it's just, you know, if you want to get the slides following or back up and stuff. So the basic idea of this talk was to give some guidance to people that are either consulting on projects that use Python or their companies are starting to use Python and they kind of need to make decisions on that. Or for DBAs that work in Python shops and the task developers are giving them trouble and they need to kind of understand what's going on with it. So we're going to do a quick glance of the language and the pointy question of which version to choose. Then we're going to talk about drivers or how do you actually talk to the database? Because that's a long history with Python. Then we're going to take a look at ORMs because if you do application development, you might end up having contact with ORM and you might have to deal with it. And then we'll see PL Python which is embedded Python inside Postgres and we're going to try to have some fun with that. So Python is an old boring enterprise technology. So unlike all those, you know, hip languages that cool kids use like Java, PHP, you know, these kind of new projects like Postgres, Python was released in 1991, first version, so it is of legal drinking age in 2012 and like the other projects. It subscribes to a philosophy. Alright, fine. So I'm just going to touch the loop. Okay. So it subscribes to a philosophy that says there should be one way to do it as opposed to a parallel. So it really tries to be maintainable, readable, you know, it's a do-go-er language as people would call it. And then it's used by big shops like Google and Assa. It's really widespread. So it's something you might consider and it's quite popular. So what problems as Postgres people do we have with Python? So there's so many drivers. You can just, you know, if you Google Python driver for Postgres, you'll get lots of stuff and you'll get really old stuff, really obsolete stuff, you'll get misconceptions. So we're going to try to clean up that. There are quite a few RMS. There's kind of confusion of which one to use. I'd like to give some visibility to the ones I consider that are good. Then there's this Python 2, Python 3 mess that if people are not really into Python, they kind of ask, should I use the latest version? I mean, that's a question that comes up repeatedly. 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 talk them over. The idea is to, you know, you go and do your research, but these are kind of my recommendations. So, yeah, the version. Right, 2.7, we're done. Let's move on. Actually, so Python 3 is the future of the project. It will come, it will become the standard. It's not the standard yet. So just stick with 2. Why? Because everything works with 2 and not everything works with 3. And there's no reason why to, you know, go to the bleeding edge that's gaining an option, but still not there, while you can stick with the thing that's now widespreadly used. If you write your stuff in a correct way and use a recent version, you will not have that much pain or ink. So think about it, but stay with Python 2. Preferably 2.7. So if you do the things right, you will have a path to Python 3 upgrade in the future, but for now, 2.7. And this whole talk is kind of, I will be saying, so this works with 2, this works with 3, but 2.7 is the thing you should be doing. Okay, let's see how can you talk to the database from Python. So there's something called dbapi20. This is a standard blessed by the community of how do you talk to a database. You might come across it as, people refer to it as PEP 249. PEPs are like RFCs for Python. So that describes the baseline of what a driver should do. It's really bare bones. It's supposed to work with any database. So it's kind of the lowest common denominator of database access for Python. And so many drivers just take this and then they implement their own stuff, because it doesn't contemplate the features you can get from, like, Posgris. This, in theory, dbapi should be used identically with the SQLite, mySQL, you know, db2, Posgris. So main drivers provide extensions, and you will have to use extensions if you won't really, you know, use the database fully, or even kind of usefully. And so a lot of people, especially, I've come across a lot of people in the Posgris community that kind of hate on dbapi, but it is really useful. It has proven to be, so it's good to have a baseline, and you have tools that can assume, okay, so this is at least dbapi plus some stuff, but you have things like, you know, wrappers or proxies or kind of plugins that, by the virtue of being able to assume this driver does dbapi too, they can provide useful things. So you might not really like it, but it is a useful standard, so to speak. So we're going to do a quick overview of the existing drivers, and there's quite a few drivers, right? I bordered them from the least interesting to the most interesting, in my opinion. So I'm just going to say a few words about each one of them, and as you can see from the previous slide, we're going to go and talk about PsychoPG in detail after. So, right, that's basically what should be said about drivers, but let's kind of look at each one at least to kind of know what they provide. So drivers come into varieties for post-dress. There are things that wrap libpq, and they provide, you know, a Python API above the C API that does libpq, and they tend to be fast because of that, because the actual protocol parsing database, blah, blah, is done in C. They tend to do things right because if libpq does something wrong, you might argue it's, you know, it is kind of the reference implementation, so they tend to get the protocol right. Stuff like, you know, pgport or you have your.pgpass. If you're using a libpq wrapper, it will work, which is great. And then, so yeah, basically that's the advantage of doing it as a C wrapper. And then there's a variety that do just implement the protocol in pure Python. They just speak, you know, ship bytes over the network and kind of implement that spec. And so the advantage would be to be able to run this kind of driver in an environment when you can't use AC extension. So in, for instance, with Python interpreters that are not C Python. So I don't know if you're familiar with PyPy or JSON. JSON is an implementation of the Python language in Java, and it's not trivial to use a C extension in there because I mean the APIs are all different underneath. So yeah, PyPy is an implementation of the Python language in Python. Actually, this is an episode of Python. It's an awesome project. And so if you want to use PyPy, you will have to go with something that's not a libpq wrapper, you will have to do some hacks that I won't go into. And sometimes you can, we'll see how being a protocol implementation might help in some cases, but I think I prefer libpq wrappers and PyPyGee is a libpq wrapper. So yeah, let's start with the merry-go-round of Python drivers with Postgres. There's this project, PyPyGee SQL. It's a libpq wrapper. It has a module that's compatible with dbAPI, so they say. It's kind of inactive, so just for historical interest, don't use that. There's Pygresql, that's a libpq wrapper, that has its own interface, which is kind of a straight translation of libpq calls to Python, and then it has dbAPI as a separate module that you can use if you need dbAPI compatibility. The project seems to be alive still, not very active, but it looks like it breathes. But I wouldn't use that given other options. There's vpgql, that's a pure Python implementation, so if you fancy implementing network protocols, you might look how they did this. It's kind of inactive, don't use that. There is an odbc wrapper. If for some reason you are compelled to use odbc, you love odbc, then you might try that, but I don't really see the point. But yeah, that's it. There is pgss, which I include here, because I like Twisted. And Twisted is an asynchronous framework for Python. It's kind of the Node.js for Python, but it has kind of like 10 years of history. It's really robust and featureful. And so even though the project is dead, I want to recommend it. It's interesting because it gives you access to the asynchronous features of the protocol, which is nice, not all drivers kind of do that. But it's important because of the traction, asynchronous computation it's getting. So it's a pure Python implementation so that they can do things like, you know, you only write the message to the socket if it's writeable, and then you wait for something to come on the socket. That's where being a Python implementation of the protocol is useful. There is pg8000, and the name actually comes from it. So they say they are the 8000th Python driver for Postgres. It's like, yeah, let's just do one more. But this one is actually active, apparently. It has a dbapi interface, and it does extensions, which is good, because dbapi obviously will not contemplate large objects, because it has to work with SQLite. It's actually, think about how difficult it was to get a specification for a database access API that everyone subscribes to. I mean, Oracle people, SQLite people, Postgres people, everyone just kind of managed to get to a really core part. So that's cool. They do this, and then they give you the rest of the power. And it's pure Python. So if you want to run on PyPy, because PyPy is fast, it's a JIT compiled. So it's a Python implementation that does JIT. So if you do calculate intensive stuff, you might try to use PyPy, and it's really getting some traction right now. You would use that, probably. So, the other spy Postgres SQL is a very interesting project. It's very actively maintained. It is a pure Python implementation of the protocol with some C extensions for speed. It does have a dbapi module, but it's kind of stuck in there just because you have to provide it. But they just read it everything. It looks like it threw everything away and then read it everything, thinking about Postgres, so it's very well integrated with the features that Postgres gives you. You have an interface for AdvisorLocks. All these thingies that you don't find in other databases are very different. They do provide them. And then there's a sticky point. It's Python 3.0. So you wouldn't use that. It's a shamming. And I don't think that was a good idea to do that in Python 3.0 only, but until Python 3.0 has all the packages available and is installed by default on your workstation, so on, you wouldn't use this. But it's a very interesting project. And then there's a QPG, which is, as I said, a.l.e.p.q. wrapper, which is dbapi2.0 and has lots of extensions that we'll try to go through quickly. It is very actively maintained. The community is great. The mailing list is very responsive. People, you know, bugs get fixed. Documentation is serious. That's really good. It does work with Python 2.0 and Python 3.0, so you're covered here. They already ported it, and you can use it with 4,000 language. It does pay attention to how threading in Python works. So they do document that in depth they do care about performance in threading environments. I won't go into that if you want to go inside it and we'll have time. I can kind of talk about how Python threading works, because it's something that people, you know, there's a bit of fud saying, oh, Python threads won't work. So not really, but they care about that problem, which is a good sign. So, okay, CyclePG, we're going to go with CyclePG. Let's see a few examples and talk a bit more in depth about the features. So that's a simple dbapi example. You know, you connect to the database, you give it a DSN, like a LipeQ DSN. So you can use SSL mode. You can use CA root path. You can use all that stuff. You create a cursor, which has nothing to do with Postgres cursors, as in declare, so on. This is a dbapi, I think, that rubs people the wrong way, usually, like Postgres people, because it's kind of confusing, but this is just something you use to run the query and to store the result. There's nothing more to it. And then you execute your code, and then you just use the fetch all function that it writes over them, and you have access to the rows. Each row is a list that you can access by index. And there's actually an extension to make this accessible by name, so you wouldn't say row zero, which is attack. You'd say row attack. So you would say, I want the attack column. But by default, you get a list of lists, each row is listed. And then you close the connection. So there's dbapi2. Should you use SQLite? That would change, and that would be the only part that would change. That stuff is mandated by dbapi mostly. So parameters. That's something that bites people that use PsychoPG. That's not that obvious if you don't read the documentation just trying to run with it. If you have a query string, you use placeholders. So you say %s and then get substituted, safely quoted with the parameters. You have two ways of passing parameters, either use %s or use a named notation, which I'll show in the next slide. And the parameters are always a sequence. So let me just show you what I mean here. If you execute select blah blah, where last name equals %s without any quotes, and here you pass a one element tuple of something with a single quote in it, this will get quoted properly, so this will get double quoted, and the query will run. But you see, this needs to be a sequence, which is something that people are sometimes not aware of, or just forget. And there you don't put any quotes in there. They get put in by the driver. So this is the name notation. You can also say, this is %s, but it is going to refer to my prefix parameter. So this saves you some typing if you repeat the same parameter twice, and then you can just say ohara once, and not duplicate code, which increases probability of having a typo and so on. And then for integers, for instance, you use %s as well. You always use %s, which some people get confused by, because it's kind of like C, printf, escape sequences, so people would use %d for integers and %s for strings and so on. But with PsychoPG, you always use %s, and it does the quoting looking at the type of the parameter. So this will get single quoted, and this will just be past this. This little function I put here is called mogrify, that does the interpolation, but doesn't run it. So if you just want to get the SQL that's going to be run, or just want to quote it and then ship it somewhere else, log it, whatever, you would use mogrify. So what makes PsychoPG so great, apart from being a fast DBAPI implementation? You get all kinds of goodies, like if you pass it a list, it gets transferred in an array, so that's good. It has built-in support for post-virus arrays. If you pass it a tuple, you will get it with the parentheses's notation. So if you do in %s and pass it a tuple, it will do in and the thing that you use in incloses. You can register your own code to cast from types to other types. So lists are built-in, but you can say that h-stores get read as Python dictionaries, which is kind of natural, and then you can pass in dictionaries, and they will get serialized to h-stores, which is cool. And the same happens for UID. You get the Python UID type. In it, you get a class that kind of wraps in it addresses, and you can write your own. You can customize the process of creating, of running the query. There are little hooks that can get you inside, and that's useful, and we'll see an example of how this can be useful. So the asynchronous is quite good. There's a quite rich implementation of asynchronous features. So you might have noticed I am in the synchronous junkie, so that's why I don't know that so much. But yeah, that's something I really like about them. So this is just an example of how would you use the extension mechanism to get the hook into the execution process? So you would say, instead of using the standard cursor that I get when I do connection.cursor and I get this thing that runs my queries, I want to use this cursor that if when I execute, I overload this method to log the demographed SQL statement and then just run the chain up to the superclass. And then you just say, give me the cursor, but use my login cursor as the class. And then you will get the query logged, and you can do auditing with that. You can try to do rewrites of the SQL. You can do lots of stuff. And here's an example of how hstore works. You just run a function that says, hey, when you get something from LEPQ, that's OID this, and this is obviously your responsibility to get. It can get it automatically, but you should probably get it yourself. It doesn't really know what is hstore. I mean, you can have a type in the public schema that's called hstore, but it's something completely different. So you just say, this OID is hstore. And then you run a select where you pass dictionaries. And this is our login cursor that's logging the select statement, and it will be serialized to hstore. It will be transformed to an hstore. So that's very convenient and actually really, really useful if you're using extension types. That's an example of asynchronous mode. You do connect. You say, I want this connection to be asynchronous. This is a DBA API extension, obviously. They don't contemplate asynchronous stuff. And then you just say, I will wait until the connection gets open, because the connection buildup process is async as well. Then I will get my cursor. I listen on my schema versus error channel. And then I will wait for this query to complete. So here it's waiting again. And then I'll just, infinitely, wait for something to be available for me. And I will look at the notifies that are on the connection. So that will, you know, you run this, and it will stay there. It will not consume any CPU until a notification arrives, and then you will get instantly notified about this. And so the wait select function is actually something that's already provided. It's very simple. You just say, you just ask the connection, should I read? Should I write? Or do you have something for me? If you have something for me, then I'll give control back to the user. If I should read, I will select with the, with the, putting the file descriptor of the connection in the readable set. And if I need to write, I will wait for the socket to become readable. So it's really, you know, it's really neatly wrapped, and then kind of understandable, and pretty well done. So there's this subject for Cyclo-PG, that's transaction management. Transactions start automatically. So when you run a query, it will do begin, and then run your query. And then if you're not careful, you will get this, this transaction will just stay open. Right? So if you, I just want to select some stuff. And then you, the object sticks around because it's, you know, the connection is a property of something, and then it won't get garbage collected, so it will stick around. You will get an open transaction that will block your vacuum, and that's going to be bad for you. So you need to think about that. And every time you, you run something, you then, or commit, or roll back the transaction. Or you just say, auto commit through, and then it'll just, you know, directly pass what you say, what you do to, to the database without inserting that begin on its own. So this, it used to be worse. If you had bad experiences with that, that stuff got fixed recently. So now it kind of chugs along, chugs along nicely. So use a recent version of Cyclo-PG because the, like 2.4 was really a good release. 2.4.2 I think is the current one. They, they really fixed some stuff. So what's missing and what's, what you should contribute to Cyclo-PG? They, they don't do params. So they didn't do PQXX params. The SQL is stringified on the client side and then sent to the database. So that the parameters interpolation happens textually and then the text is shipped to database. So people like for years now, from time to time someone appears and says, hey, let's, let's do PQXX params. And then people say, what would the interface be? How would I document that? How would I explain this in the manual? It kind of rings a bell with some other community. I know that, you know, stuff that everyone needs get marooned by kind of documentation issues or stuff. So that might sound familiar to you. But yeah, it's, it's, it's still not there. And then prepared statements are more difficult because you need to do them by hand. I mean, PQXX params is faster because you don't do string manipulation in Python. You just, you know, ship the stuff to Postgres. Copy will not work with asynchronous connections, but you probably won't care about that so much. And I'd really like, so the thing is, you use parameters and they are great. But if, if, if this is something dynamic that, that your application and it receives externally or reads from configuration problems and so forth, this one you need to interpolate inside Python and do it yourself. You can't like, here say percent L or percent something and have it quoted for you. So this is something you need to do manually. And sometimes people don't understand these are parameters that get quoted with, you know, parameter coding code and these are identifiers that you need to quote yourself and care about them, care about them yourself. So it would be nice to have something a bit more user friendly there. Right. So ORMs, why, why would you need an ORM? Right? No one likes ORMs. Well, actually, I think there are advantages. So I'm going to go into this discussion too deeply, but there are use cases where you actually would like to use an ORM. It will allow you to, you know, have less experience, developers go faster. It will help you for small projects. So it's, it's effective life. So let's just talk about that. Sometimes you just don't get to pick. You get asked which ORM should we use? Your, your, your project lead won't ask you, should we use an ORM? So, so the answer is simple. Use SQL alchemy. But before we go to SQL alchemy, I need to say a few words about Django ORM because Django is the, this is something that you will have contact with some of the writer because everyone uses Django if they do Python on the web. And almost everyone that does Django uses the built-in database relationship mapper that they provide. So it's actually, it might be useful. So don't dismiss it. I'm going to say a bunch of bad things about Django ORM, but don't dismiss it outright. It can be handy. It does provide some features for a small project. If, like, you know, what you do is you have a, you have, you know, a shopping list and you just add stuff or you have a small online shop. That's, that's fine. It's just going to work. Your developers will be happy. The documentation will be, you know, they will have the documentation alongside their framework documentation. That's fine. But it does have a few issues. So it doesn't support multiple column primary keys, which I, for me, it was the first thing that dismissed it from the project. It does not support, so it, it's kind of generic. It says, okay, this is an ORM. You can use it with my SQL with posters with SQLite. So it is, it won't give you good support for posters features, which we, so it tries to be polyglot and that kind of limits what it can give you. There was a, so there's a bunch of shortcomings that I won't go into. But there was a lot of people telling me last year, two years ago, so I have this big Django shop and I have this huge problem with, with transactions that get hanging and block my vacuum and, you know, eat 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 in, if in doubt, just remember that PsychoPG will open a transaction by default when you first access the database. And so in your Django request response cycle, just make sure to close that connection when the, when the request is done. Right? So, so then you'll be safe. I mean, I know Django shops that work great with, with, with PsychoPG and, and just, and Django and they don't have any issues. And no people that complain about. So how to cope if you need to use the Django or M or if you made the decision to use it. Use a connection pooler. Definitely because every request response cycle will open the database connection and close it. So it's going to be slow. And you will have, you know, you will have to up the max connections to high numbers if you want. So these long line transactions be aware that this might be a problem, you know, be proactive, don't wait until your, your, your load explodes because of, because of open transactions. Use PsychoPG because you can use other DBA PA drivers with the Django or M. Don't use anything else. Use PsychoPG. And if you want to dismiss it, think about it because even though it's not that great, it's still very well integrated. So you will need to, you know, do the like work of integrating a different ORM should you should you choose this. So think about it. So let's go into SQL Alchemy, which is, which is not only an ORM. It's a, it's a complete, you know, framework toolkit, whatever you call it, that gives you everything you would need to work with SQL from Python. So there are two main parts. There is the classical ORM. When you get Python objects, you get your database relations and you, they map together to be access things from your application. And then they get returned to the database. And then there's something that's like SQL, but made in Python. So it's kind of like writing SQL by hand on using Python constructs. And we see an example of how this process actually quite good. You can do everything with SQL Alchemy. If there's something you cannot do, they will consider the bug. So that's, that's great. It's not like they say, yeah, we do this and if you need something else, right, draw SQL or whatever. I mean, you're on your own. No, they, their philosophy is it needs to be possible because we, we do everything SQL. So that's good. And, and it has a learning curve. There are new words you will have to learn, new concepts. It's, it's complex, but once you get the hang of it, it's, you know, it actually pays off. So, so yeah, like I said, you, you define your models, you define tables in Python, but it feels like just writing SQL statements to create a schema and then you map them together. And you don't even have to map them together or you can map some objects to one database and some of just another database or some objects to, you know, this connection and some just just keeps them unmapped. 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 an ORM would support like, you know, database specific types, say, in, you know, UID, in-app, HSTOR, all that stuff. Operators. So you do, you know, all that funny point operators or, or HSTOR, access operators. You can do them with SQL. You can define your model. So they will work with cascading deletes, right? So you have an ORM object. You delete it. And then the object that, that had a collection of those will, so the other way around. So you have your user and, and the user has her, say, books, right? And then you delete that user and it will know that all the books of that user are gone if you configure it like that. So it's smart enough to, to follow that and you can define that. That's awesome. It's something you don't see every day in an ORM. It supports schemas, like post-girsch schemas, which that's, that's great. I mean, you wouldn't think of that and, and so on and so forth. It's, it's really, you can do everything. And it tries to get the last 20% right. So Django says it's like 80-20, right? We do the 80% of the stuff, right? And that you're on your own with the last 20. And they say, we're going to do it all right. If the cost is increased complexity, but hey. So how would, how, how would this work? You'd create just an object, which is just a simple Python object. There's nothing database or I'm nothing here. It's just, you know, your application. Yep. Yep. So let's say it's basically foreign keys with on update, on update cascade. So you will, you will, it will know that if you get an object, update its attribute and say, save it to the database. It will know that dependent objects will be changed. So it's, it kind of is smart enough to follow that. And the same with the leads. It's, it's more, it's, it's more common to have this problem with the leads that you say, okay, I deleted something and then I access this other object. And it says something's wrong with it, you know, you deleted it from, from underneath me. You can't do this with SQL. You can, it knows that the database will take care of that. So with Django, Django would just first delete all the dependent objects with that delete statement and then delete your parent. And here you can say, when I delete my parents, the database will take care of the children objects. Do not touch them. They will not be there because I have a cascading delete foreign key. So, so okay, you have your application object. You have your table. So ignore the magic for now. And just it's not that. So yeah, there's some other place. It's not so bad. The thing is, you say, I want a table that's called car that has these three columns. And this one's a unique code column. Then I want to live into length. It's going to be a primary key. This one's going to be a car make. And it's going to refer to, to the table with, when I store my makes and it's going to be deferrable. So before, look, before knowing SQL team, I wouldn't dream of an ORM that will know what the variable foreign key is. And that will do the right thing with it. So it was great. And then, you know, I'm on price as numeric. And, and so I have my table. I have my logic object. And then I just say map one to the other. But I don't have to say that. Or I can map it to, to another table. I can do this dynamically. And so, right, you have this and this. And then if you don't want to, you know, have your object separately and then your tables separately, you can use the more classical approach that other ORMs like active re-correcting and so on use. Which is, you know, just say this is my class that has, that is reflecting the database. And then it looks kind of the same, but just in one slide. Right? So, so that's, so that's kind of quicker if you don't really need this separation. Yep. So, by the way, for Django apps, it's just easy to build a model and have it work with us. Or it's just generating for the model. Yep. Is it uncommon to just create that middle class that just conforms? Can you use SQL without breaking your Django app for all your queries? But you can use Django to build the tables and everything that's used to this. And Django, you don't have to do anything after you build the model because of the integration. So, this or the Django orm? I guess the question is, could you still build an entire Django app and use it for building the tables and everything that works really well and upgrading and everything like that Django's itself? And then, but somehow, maybe like this while you're in internal queries for the website, would you use SQL Alchemy? You might try to define your models both in Django and then separately define them like this. And hope that the definitions mean the same. So, when you access it through this interface, it will be the same as the ones created by Django. So, you might try that. I would advise against that. I think I would just, you know, if I needed something more than Django orm, I would take the hit and do migrations by hand, write the schema by hand. I would just do that. There is a migration tool for SQL Alchemy though. Yeah, but then, so I'll have a slide that says why it doesn't really fit with Django that well. So, you might have migration, you might, you know, write some code, hook into the Django schema generation process and do the SQL Alchemy thing. That's actually what we do. But it's extra like work. You need to know your framework well. Your developers might get confused if you do this. So, it's not standard. You won't, you know, find one million stack overflow answers to everything. You will need to know what's going on. So, bear not mix. I just throw out the URL and put it in SQL Alchemy. So, okay, you have these two ways of defining stuff. And then what's great about SQL Alchemy, and that's the part when you get excited, is that it's all SQL. You have it, you know, you can just reach out and touch it. You can take your car, which is a table object, right? I'm talking about this car here. And use a lightly magic incantation to compile it to an SQL statement. And we'll give you, you know, I needed this to be text that's limited by length. This was supposed to be unlimited, so it's text. There's, you know, the variable foreign key that works. So, you can, you can see touch the SQL. It's truly thought as something that compiles down to SQL. So, that was the URL. And now for the expression language part. So, you can use the URL, like in a typical way of, you know, I have my car. I need to know its price, or I need to know, I have this make, and I need to know the cars of this make that I have. So, I just take the make, the cars attribute, and it will load all the cars magically. You can do that. Or you can just, you know, hand type your SQL, but in Python. So, you get to write SQL. If you think about it, if you know, just cover your, I mean, hold your nose and say, I'm writing SQL, I'm writing SQL, only it's Python, but it's SQL. So, you're going to be fine. And actually, there are advantages to it being Python, because you can, you know, get the select statement, ship it off to a different class subsystem, whatever, and it will tack on work clauses, and then some other thing will, will tack on the limit clause, or, you know, wrap it in a sub select. You get composability, which is great. You will be able to, you know, use the URM and then in places when it generates bad queries or you want to use, you want to do something tricky because only then you get your beloved index count, you will be able to do this. And like I said, if you can't generate a statement you would like to write by hand, this is a bug, and it should get fixed. So, how would this work? So, this is a SQL, right? Update car set price equals price plus two, where make, lower make is Chevy. So, notice a few things. I was able to use a lower function. I would be able to use any function I have, any stored procedure I have, so, and it'll, it'll figure it out. I was able to use the equals operator and it got, it got put in the work flows as the equals, and it's going to be subsidiary for the parameter. And I was, I was able to say, set the price to the price plus two and it, it knew what I meant. Right? And so, should, should this be a string and I would say, up end, you can s to it. This would become the, the, the two kind of pipes that, that is string and I'm not sure. So, it's smart enough to take your Python and, and make it SQL. So, this, this, this actually feels good. This doesn't feel like, you know, this bad or I'm is, is doing bad stuff on my database. This is, I'm writing my, my SQL only I'm doing it with this in the same language as the rest of the application. And I use variables and I can use parameters and so on. That's, that feels right. So, like I said, what you get from using SQL Alchemy over, you know, handwriting your SQL, which is something we all like to do, but sometimes we can't, is that you can, you know, it's, you have your application in one language, which is a good thing. Your developers are happier. You can test easier. You can make it, you know, composable or, or more customizable if you, if you do delegation of concerns correctly. You can only generate SQL and then ship it off to your, you know, executing layer or your, or your, you know, prepare transaction manager that does that, that, that multi-stab commit. You can, you can do that. You can ship it off to an asynchronous execution kind of thingy that, that they will just receive SQL and run it. But you still get the advantage of writing it in your application language. You can augment your code, your, your logic objects with, with, with stuff that you would, it would cost you to do this all in SQL. And then you can, you can write some code to make a column transparently encrypted when you store it. So if you say, I'm storing something I need reversibly encrypted, like a user's API key, whatever. And then whenever I will save this, it will get wrapped in a, in a PGP, some encrypt with a key that I set in configuration. And then when I read it, it's just going to be a plain string. I'm going to get it decrypted. And then you will see the encrypted, only store the encrypted version. And you can have your, your, your objects, you know, you won't have to always write first name, concatenate last name. You will be able to just have a property that gives you that automatically. So an ORM might be actually useful. Yeah, let's, I'm going to just skip over this slide because we're kind of running late. So when if you use SQL Alchemy, you will come across this session object that kind of sits between your connection and your code that you use to access the database. And that, that keeps track of which objects are dirty and which need to be persisted database. This is a thing you will commit to rollback your session. And then the good thing and the difference with Django that it makes it easy to have persistent session objects. So you won't have to use a pooler if you can just store the session object in some persistent context. Like if you're doing web programming, you would use Whiskey and then you will have something that, that executes Python code and, and gives the, the generated response to the web server. And you would keep the session object inside the Whiskey process that's persistent. And then you wouldn't have to use pooling because you wouldn't use, you wouldn't, you know, create and destroy connections on each response, request response cycle. So, so let's talk, so yeah, the last part is PL Python, which is kind of different. PL Python is being able to run Python code inside PostDress and on behalf of the, of the PostDress process. So it's, it's kind of different, but it's, it's a way you can, if your application, if your stack is Python, you might want to use, if you need stored procedures, you might want to consider using Python for that. So then your, your developers are happier because they only deal with one language and they don't need to, you know, learn PL, PG SQL and so on. So, so this is Python inside the backend. It executes arbitrary code so it's like, it can run anything. It opens files. It can open sockets. It can delete your data directory. So it's untrusted. You, you need to, you know, assume that if you're in an environment when you can't use untrusted languages, you're out of luck. And if I have the time, you might say a few words about when or if this could change, but the simple, the short answer is, it's very difficult to change that. So it will be untrusted for a long time. And it's, it's Python so you actually can do crazy stuff. But that's kind of the fun of using Python inside your database. So how does this work? When you run PL Python code and interpreter, like the whole Python runtime gets created, instantiated and embedded in your, in your backend process. So if you want this to be fast, you should preload PL Python. So you won't pay the overhead on the first query of, you know, the open, looking up the shell, the library and so on. And you want to use long-lived connections of this. So you, so you won't even have to preload that. So it will just be kept in memory. It's, well, quite well integrated with a POSGRESS type system. So if a PL Python function receives a POSGRESS integer, you will see it as an integer inside Python. And if it receives a, let's say, array, you will see it as a list. So these things kind of work. The rest of the types will get serialized to strings. So if you have your own crazy extension type, what POSGRESS will do is it will call its text out-of-function, get the string and give it to Python. So then you would have to parse it back into some object that's, that would be useful to you. There is an extension for the very typical case of passing h-stores into Python and operating on it as dictionaries. So you might want to do that, but in general, you will need to deal with strings if you have composite types and so on. Function arguments to stored procedures are visible as global. We'll see an example. So you just get to access them as if they were arguments to the, to the Python function. And then you have a bit of magic that you can access that will allow you to run queries on the database that in a trigger 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 tuple. We'll see examples in a minute. And so the, the, which modules are visible depends on the, on the Python path. This is a, a environment variable that's kind of like class path for Java or Inc for Perl. So what's available, what's visible depends on that. And the back end process goes, the back end processes get forked from, from the postmaster. So if you need your application code to be visible to your PL Python functions, you probably will need to edit the environment that the post address executes in to include inside Python path, your application deployment directory or whatever. There's just something to keep in mind that that's the way to do this probably. So like, this is, I think this is just one slide about how to make PL Python not that bad if you don't want to play with it, if you want to actually run production stuff with it. Keep all your code inside the module, inside your application. Don't just ship it into the database. Keep it under version control inside your application. Make all the SQL functions as two lines. You create a function, you get some arguments, and then the only thing that actually happens inside SQL, the only thing that gets stored as a SQL function inside Postgres is you get the function and you call it and you pass it, you know, everything that's inside the scope of this function. So this will include the arguments, this will include the, the trigger information if it's there that will include, you know, the module that you use to, that provides utilities and so on. And then the rest happens inside your application. And that has the advantage of being better testable because you get this function, you just pass it, you know, mocks for that and you test it just with the rest of the application. So it's kind of, that's, that should help and always be careful because you are running full blown, a full blown application language inside your database, you can hurt yourself bad. That said, there's a few funny things you can do. So there are some examples, you know, give you a taste and then maybe some ideas, good or bad. What can you do? You can, for instance, import the numerical library and do fast Fourier transformations inside your database directly using, using Python code that then calls into optimized C that does stuff fast. You can write a check constraint that checks whether a column that's declared text holds JSON. Might not be the best idea, but you might do that. Or you might check that a byte, byte here is a PNG image or, you know, whatever. You can open connections, you can, you know, import cycle PG, connect to the database and have like DBI link with that. You can, you know, do HTTP requests, you can invalidate caches on your, I know, I think, Madness last year was talking about using PL Python to invalidate a varnish cache when something got updated in the database using an HTTP request. That's actually, that's a pretty good idea, I guess. And you can do, you know, things like network access and so on. So here's an example. That's a function that will get an extension name and then it will get the standard diff label that's library for doing diffs. It will get all the available except the extensions. So it uses the PL Py module that's something you get that's available globally in every PL Python function that includes an SPI interface. So you can run queries just like in PGPL SQL, you would be able to run, you know, select and execute it. That's how you do it in Python. And then you get all the names and you say, get me the closest matches for what I wrote, for what I've written. So this is X name right here. And these are the possible names that I have. So that would work. Then it's, wait. So let's say I run find extension. It will return an array. So I will unnest it. And it's a poll just array, right? I can call unnest. And I'm looking for this fuzzy string give thingy that's there. And it will find, you know, fuzzy string match. And if I look for hstrah, it will find hstrah. So, you know, we can run arbitrary code and use all the power of Python modules, all the ecosystem, everything. There's a, yeah, there are two more examples. So this one is a check that might be a borderline good idea. I think I would recommend this, but it kind of shows what you can do. So you get a trigger that gets the email of the new tuple, splits it on the yet sign, so gets the domain name, and then checks if it has an MX record. And if it doesn't, it throws an error and says, whoa, you don't have an MX record. Right? So this would actually, if I have internet connection, users. So yeah, okay, my email got inserted, but example org doesn't have an MX domain. So it says no MX record for domain example org. That's kind of cool. And then the last example I have here is a function that will show you the PG con schedule in the database, which works by getting, downloading the iCalendar that's available on the website, you know, parsing it with the iCalendar module, then looking for events, and just returning the summary, the location, and the time. And you will see, I have three out parameters, two are text, one is a timestamp, and actually it will be timestamp. It will be able to do, you know, calculations, time zone stuff, and so on and so forth. So let's just look at the schedule, and here it is. I just downloaded the iCalendar and I parsed it, and here I have my date times and so on. So we're done. We are slightly over time, and only there's something up next. If you want, if you want to ask questions you can just ask, or if you can just come talk to me after. Thanks for listening.
 in case people come from better talk.  So there's a saying that says that every situation in life is actually a  completely destroyed by a next-guys-cd comic.  So that's mine for this talk.  And so the talk is going to be about Python and the general ecosystem of  Python surrounding postures.  So my name's Jan.  That's my company and that's my email.  And so if you want to get the slides, you can get them from there.  It's called snakes and elephant PDF.  If you want to follow with the code, you can try doing that.  But I recommend just doing that afterwards with the slides if you want to.  So...  Where?  Make virtual...  Make virtual...  Yeah, there's a type.  Yeah.  So it's just, you know, if you want to get the slides following or back up and stuff.  So the basic idea of this talk was to give some guidance to people that are either  consulting on projects that use Python or their companies are starting to use Python  and they kind of need to make decisions on that.  Or for DBAs that work in Python shops and the task developers are giving them trouble  and they need to kind of understand what's going on with it.  So we're going to do a quick glance of the language and the pointy question of which  version to choose.  Then we're going to talk about drivers or how do you actually talk to the database?  Because that's a long history with Python.  Then we're going to take a look at ORMs because if you do application development,  you might end up having contact with ORM and you might have to deal with it.  And then we'll see PL Python which is embedded Python inside Postgres and we're going to  try to have some fun with that.  So Python is an old boring enterprise technology.  So unlike all those, you know, hip languages that cool kids use like Java, PHP,  you know, these kind of new projects like Postgres, Python was released in 1991,  first version, so it is of legal drinking age in 2012 and like the other projects.  It subscribes to a philosophy.  Alright, fine.  So I'm just going to touch the loop.  Okay.  So it subscribes to a philosophy that says there should be one way to do it as opposed to  a parallel. So it really tries to be maintainable, readable, you know, it's a do-go-er language  as people would call it.  And then it's used by big shops like Google and Assa.  It's really widespread.  So it's something you might consider and it's quite popular.  So what problems as Postgres people do we have with Python?  So there's so many drivers.  You can just, you know, if you Google Python driver for Postgres, you'll get lots of stuff  and you'll get really old stuff, really obsolete stuff, you'll get misconceptions.  So we're going to try to clean up that.  There are quite a few RMS.  There's kind of confusion of which one to use.  I'd like to give some visibility to the ones I consider that are good.  Then there's this Python 2, Python 3 mess that if people are not really into Python,  they kind of ask, should I use the latest version?  I mean, that's a question that comes up repeatedly.  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 talk them over.  The idea is to, you know, you go and do your research, but these are kind of my recommendations.  So, yeah, the version.  Right, 2.7, we're done. Let's move on.  Actually, so Python 3 is the future of the project.  It will come, it will become the standard.  It's not the standard yet.  So just stick with 2.  Why?  Because everything works with 2 and not everything works with 3.  And there's no reason why to, you know, go to the bleeding edge that's gaining an option,  but still not there, while you can stick with the thing that's now widespreadly used.  If you write your stuff in a correct way and use a recent version,  you will not have that much pain or ink.  So think about it, but stay with Python 2.  Preferably 2.7.  So if you do the things right, you will have a path to Python 3 upgrade in the future,  but for now, 2.7.  And this whole talk is kind of, I will be saying, so this works with 2, this works with 3,  but 2.7 is the thing you should be doing.  Okay, let's see how can you talk to the database from Python.  So there's something called dbapi20.  This is a standard blessed by the community of how do you talk to a database.  You might come across it as, people refer to it as PEP 249.  PEPs are like RFCs for Python.  So that describes the baseline of what a driver should do.  It's really bare bones.  It's supposed to work with any database.  So it's kind of the lowest common denominator of database access for Python.  And so many drivers just take this and then they implement their own stuff,  because it doesn't contemplate the features you can get from, like, Posgris.  This, in theory, dbapi should be used identically with the SQLite, mySQL, you know, db2, Posgris.  So main drivers provide extensions, and you will have to use extensions if you won't really, you know,  use the database fully, or even kind of usefully.  And so a lot of people, especially, I've come across a lot of people in the Posgris community  that kind of hate on dbapi, but it is really useful.  It has proven to be, so it's good to have a baseline, and you have tools that can assume,  okay, so this is at least dbapi plus some stuff, but you have things like, you know,  wrappers or proxies or kind of plugins that, by the virtue of being able to assume  this driver does dbapi too, they can provide useful things.  So you might not really like it, but it is a useful standard, so to speak.  So we're going to do a quick overview of the existing drivers, and there's quite a few drivers, right?  I bordered them from the least interesting to the most interesting, in my opinion.  So I'm just going to say a few words about each one of them, and as you can see from the previous slide,  we're going to go and talk about PsychoPG in detail after.  So, right, that's basically what should be said about drivers,  but let's kind of look at each one at least to kind of know what they provide.  So drivers come into varieties for post-dress.  There are things that wrap libpq, and they provide, you know, a Python API above the C API that does libpq,  and they tend to be fast because of that, because the actual protocol parsing database, blah, blah, is done in C.  They tend to do things right because if libpq does something wrong, you might argue it's, you know,  it is kind of the reference implementation, so they tend to get the protocol right.  Stuff like, you know, pgport or you have your.pgpass.  If you're using a libpq wrapper, it will work, which is great.  And then, so yeah, basically that's the advantage of doing it as a C wrapper.  And then there's a variety that do just implement the protocol in pure Python.  They just speak, you know, ship bytes over the network and kind of implement that spec.  And so the advantage would be to be able to run this kind of driver in an environment when you can't use AC extension.  So in, for instance, with Python interpreters that are not C Python.  So I don't know if you're familiar with PyPy or JSON.  JSON is an implementation of the Python language in Java,  and it's not trivial to use a C extension in there because I mean the APIs are all different underneath.  So yeah, PyPy is an implementation of the Python language in Python.  Actually, this is an episode of Python.  It's an awesome project.  And so if you want to use PyPy, you will have to go with something that's not a libpq wrapper,  you will have to do some hacks that I won't go into.  And sometimes you can, we'll see how being a protocol implementation might help in some cases,  but I think I prefer libpq wrappers and PyPyGee is a libpq wrapper.  So yeah, let's start with the merry-go-round of Python drivers with Postgres.  There's this project, PyPyGee SQL.  It's a libpq wrapper. It has a module that's compatible with dbAPI, so they say.  It's kind of inactive, so just for historical interest, don't use that.  There's Pygresql, that's a libpq wrapper, that has its own interface,  which is kind of a straight translation of libpq calls to Python,  and then it has dbAPI as a separate module that you can use if you need dbAPI compatibility.  The project seems to be alive still, not very active, but it looks like it breathes.  But I wouldn't use that given other options.  There's vpgql, that's a pure Python implementation,  so if you fancy implementing network protocols, you might look how they did this.  It's kind of inactive, don't use that.  There is an odbc wrapper. If for some reason you are compelled to use odbc,  you love odbc, then you might try that, but I don't really see the point.  But yeah, that's it.  There is pgss, which I include here, because I like Twisted.  And Twisted is an asynchronous framework for Python.  It's kind of the Node.js for Python, but it has kind of like 10 years of history.  It's really robust and featureful.  And so even though the project is dead, I want to recommend it.  It's interesting because it gives you access to the asynchronous features of the protocol,  which is nice, not all drivers kind of do that.  But it's important because of the traction, asynchronous computation it's getting.  So it's a pure Python implementation so that they can do things like, you know,  you only write the message to the socket if it's writeable,  and then you wait for something to come on the socket.  That's where being a Python implementation of the protocol is useful.  There is pg8000, and the name actually comes from it.  So they say they are the 8000th Python driver for Postgres.  It's like, yeah, let's just do one more.  But this one is actually active, apparently.  It has a dbapi interface, and it does extensions, which is good,  because dbapi obviously will not contemplate large objects,  because it has to work with SQLite.  It's actually, think about how difficult it was to get a specification  for a database access API that everyone subscribes to.  I mean, Oracle people, SQLite people, Postgres people, everyone just kind of managed  to get to a really core part.  So that's cool. They do this, and then they give you the rest of the power.  And it's pure Python.  So if you want to run on PyPy, because PyPy is fast, it's a JIT compiled.  So it's a Python implementation that does JIT.  So if you do calculate intensive stuff, you might try to use PyPy,  and it's really getting some traction right now.  You would use that, probably.  So, the other spy Postgres SQL is a very interesting project.  It's very actively maintained.  It is a pure Python implementation of the protocol with some C extensions for speed.  It does have a dbapi module, but it's kind of stuck in there just because you have to provide it.  But they just read it everything.  It looks like it threw everything away and then read it everything,  thinking about Postgres, so it's very well integrated with the features that Postgres gives you.  You have an interface for AdvisorLocks.  All these thingies that you don't find in other databases are very different.  They do provide them.  And then there's a sticky point. It's Python 3.0.  So you wouldn't use that.  It's a shamming.  And I don't think that was a good idea to do that in Python 3.0 only,  but until Python 3.0 has all the packages available and is installed by default on your workstation, so on,  you wouldn't use this.  But it's a very interesting project.  And then there's a QPG, which is, as I said, a.l.e.p.q. wrapper,  which is dbapi2.0 and has lots of extensions that we'll try to go through quickly.  It is very actively maintained. The community is great.  The mailing list is very responsive.  People, you know, bugs get fixed.  Documentation is serious. That's really good.  It does work with Python 2.0 and Python 3.0, so you're covered here.  They already ported it, and you can use it with 4,000 language.  It does pay attention to how threading in Python works.  So they do document that in depth they do care about performance in threading environments.  I won't go into that if you want to go inside it and we'll have time.  I can kind of talk about how Python threading works,  because it's something that people, you know, there's a bit of fud saying,  oh, Python threads won't work.  So not really, but they care about that problem, which is a good sign.  So, okay, CyclePG, we're going to go with CyclePG.  Let's see a few examples and talk a bit more in depth about the features.  So that's a simple dbapi example.  You know, you connect to the database, you give it a DSN, like a LipeQ DSN.  So you can use SSL mode. You can use CA root path.  You can use all that stuff.  You create a cursor, which has nothing to do with Postgres cursors, as in declare, so on.  This is a dbapi, I think, that rubs people the wrong way, usually, like Postgres people,  because it's kind of confusing, but this is just something you use to run the query  and to store the result. There's nothing more to it.  And then you execute your code, and then you just use the fetch all function  that it writes over them, and you have access to the rows.  Each row is a list that you can access by index.  And there's actually an extension to make this accessible by name,  so you wouldn't say row zero, which is attack. You'd say row attack.  So you would say, I want the attack column. But by default, you get a list of lists,  each row is listed. And then you close the connection.  So there's dbapi2. Should you use SQLite? That would change,  and that would be the only part that would change.  That stuff is mandated by dbapi mostly.  So parameters. That's something that bites people that use PsychoPG.  That's not that obvious if you don't read the documentation just trying to run with it.  If you have a query string, you use placeholders.  So you say %s and then get substituted, safely quoted with the parameters.  You have two ways of passing parameters, either use %s or use a named notation,  which I'll show in the next slide.  And the parameters are always a sequence.  So let me just show you what I mean here.  If you execute select blah blah, where last name equals %s without any quotes,  and here you pass a one element tuple of something with a single quote in it,  this will get quoted properly, so this will get double quoted, and the query will run.  But you see, this needs to be a sequence, which is something that people are sometimes not aware of,  or just forget. And there you don't put any quotes in there.  They get put in by the driver.  So this is the name notation.  You can also say, this is %s, but it is going to refer to my prefix parameter.  So this saves you some typing if you repeat the same parameter twice,  and then you can just say ohara once, and not duplicate code,  which increases probability of having a typo and so on.  And then for integers, for instance, you use %s as well.  You always use %s, which some people get confused by,  because it's kind of like C, printf, escape sequences,  so people would use %d for integers and %s for strings and so on.  But with PsychoPG, you always use %s, and it does the quoting looking at the type of the parameter.  So this will get single quoted, and this will just be past this.  This little function I put here is called mogrify,  that does the interpolation, but doesn't run it.  So if you just want to get the SQL that's going to be run,  or just want to quote it and then ship it somewhere else, log it, whatever,  you would use mogrify.  So what makes PsychoPG so great, apart from being a fast DBAPI implementation?  You get all kinds of goodies, like if you pass it a list,  it gets transferred in an array, so that's good.  It has built-in support for post-virus arrays.  If you pass it a tuple, you will get it with the parentheses's notation.  So if you do in %s and pass it a tuple, it will do in and the thing that you use in incloses.  You can register your own code to cast from types to other types.  So lists are built-in, but you can say that h-stores get read as Python dictionaries,  which is kind of natural, and then you can pass in dictionaries,  and they will get serialized to h-stores, which is cool.  And the same happens for UID.  You get the Python UID type.  In it, you get a class that kind of wraps in it addresses, and you can write your own.  You can customize the process of creating, of running the query.  There are little hooks that can get you inside, and that's useful,  and we'll see an example of how this can be useful.  So the asynchronous is quite good.  There's a quite rich implementation of asynchronous features.  So you might have noticed I am in the synchronous junkie,  so that's why I don't know that so much.  But yeah, that's something I really like about them.  So this is just an example of how would you use the extension mechanism  to get the hook into the execution process?  So you would say, instead of using the standard cursor that I get  when I do connection.cursor and I get this thing that runs my queries,  I want to use this cursor that if when I execute,  I overload this method to log the demographed SQL statement  and then just run the chain up to the superclass.  And then you just say, give me the cursor, but use my login cursor as the class.  And then you will get the query logged,  and you can do auditing with that.  You can try to do rewrites of the SQL. You can do lots of stuff.  And here's an example of how hstore works.  You just run a function that says, hey, when you get something from LEPQ,  that's OID this, and this is obviously your responsibility to get.  It can get it automatically, but you should probably get it yourself.  It doesn't really know what is hstore.  I mean, you can have a type in the public schema that's called hstore,  but it's something completely different.  So you just say, this OID is hstore.  And then you run a select where you pass dictionaries.  And this is our login cursor that's logging the select statement,  and it will be serialized to hstore.  It will be transformed to an hstore.  So that's very convenient and actually really, really useful if you're using extension types.  That's an example of asynchronous mode.  You do connect. You say, I want this connection to be asynchronous.  This is a DBA API extension, obviously.  They don't contemplate asynchronous stuff.  And then you just say, I will wait until the connection gets open,  because the connection buildup process is async as well.  Then I will get my cursor.  I listen on my schema versus error channel.  And then I will wait for this query to complete.  So here it's waiting again.  And then I'll just, infinitely, wait for something to be available for me.  And I will look at the notifies that are on the connection.  So that will, you know, you run this, and it will stay there.  It will not consume any CPU until a notification arrives,  and then you will get instantly notified about this.  And so the wait select function is actually something that's already provided.  It's very simple.  You just say, you just ask the connection, should I read?  Should I write?  Or do you have something for me?  If you have something for me, then I'll give control back to the user.  If I should read, I will select with the, with the,  putting the file descriptor of the connection in the readable set.  And if I need to write, I will wait for the socket to become readable.  So it's really, you know, it's really neatly wrapped,  and then kind of understandable, and pretty well done.  So there's this subject for Cyclo-PG, that's transaction management.  Transactions start automatically.  So when you run a query, it will do begin, and then run your query.  And then if you're not careful, you will get this, this transaction will just stay open.  Right?  So if you, I just want to select some stuff.  And then you, the object sticks around because it's, you know, the connection is a property of something,  and then it won't get garbage collected, so it will stick around.  You will get an open transaction that will block your vacuum, and that's going to be bad for you.  So you need to think about that.  And every time you, you run something, you then, or commit, or roll back the transaction.  Or you just say, auto commit through, and then it'll just, you know,  directly pass what you say, what you do to, to the database without inserting that begin on its own.  So this, it used to be worse.  If you had bad experiences with that, that stuff got fixed recently.  So now it kind of chugs along, chugs along nicely.  So use a recent version of Cyclo-PG because the, like 2.4 was really a good release.  2.4.2 I think is the current one. They, they really fixed some stuff.  So what's missing and what's, what you should contribute to Cyclo-PG?  They, they don't do params.  So they didn't do PQXX params.  The SQL is stringified on the client side and then sent to the database.  So that the parameters interpolation happens textually and then the text is shipped to database.  So people like for years now, from time to time someone appears and says,  hey, let's, let's do PQXX params.  And then people say, what would the interface be?  How would I document that? How would I explain this in the manual?  It kind of rings a bell with some other community.  I know that, you know, stuff that everyone needs get marooned by kind of documentation issues or stuff.  So that might sound familiar to you.  But yeah, it's, it's, it's still not there.  And then prepared statements are more difficult because you need to do them by hand.  I mean, PQXX params is faster because you don't do string manipulation in Python.  You just, you know, ship the stuff to Postgres.  Copy will not work with asynchronous connections, but you probably won't care about that so much.  And I'd really like, so the thing is, you use parameters and they are great.  But if, if, if this is something dynamic that, that your application and it receives externally  or reads from configuration problems and so forth, this one you need to interpolate inside Python  and do it yourself. You can't like, here say percent L or percent something and have it quoted for you.  So this is something you need to do manually.  And sometimes people don't understand these are parameters that get quoted with, you know,  parameter coding code and these are identifiers that you need to quote yourself and care about them, care about them yourself.  So it would be nice to have something a bit more user friendly there.  Right. So ORMs, why, why would you need an ORM?  Right? No one likes ORMs.  Well, actually, I think there are advantages.  So I'm going to go into this discussion too deeply, but there are use cases where you actually would like to use  an ORM. It will allow you to, you know, have less experience, developers go faster.  It will help you for small projects.  So it's, it's effective life.  So let's just talk about that.  Sometimes you just don't get to pick.  You get asked which ORM should we use?  Your, your, your project lead won't ask you, should we use an ORM?  So, so the answer is simple.  Use SQL alchemy. But before we go to SQL alchemy, I need to say a few words about Django ORM because Django is the,  this is something that you will have contact with some of the writer because everyone uses Django if they do Python on the web.  And almost everyone that does Django uses the built-in database relationship mapper that they provide.  So it's actually, it might be useful. So don't dismiss it.  I'm going to say a bunch of bad things about Django ORM, but don't dismiss it outright.  It can be handy. It does provide some features for a small project.  If, like, you know, what you do is you have a, you have, you know, a shopping list and you just add stuff or you have a small online shop.  That's, that's fine. It's just going to work.  Your developers will be happy. The documentation will be, you know, they will have the documentation alongside their framework documentation.  That's fine. But it does have a few issues.  So it doesn't support multiple column primary keys, which I, for me, it was the first thing that dismissed it from the project.  It does not support, so it, it's kind of generic. It says, okay, this is an ORM.  You can use it with my SQL with posters with SQLite.  So it is, it won't give you good support for posters features, which we, so it tries to be polyglot and that kind of limits what it can give you.  There was a, so there's a bunch of shortcomings that I won't go into.  But there was a lot of people telling me last year, two years ago, so I have this big Django shop and I have this huge problem with, with transactions that get hanging and block my vacuum and, you know, eat 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 in, if in doubt, just remember that PsychoPG will open a transaction by default when you first access the database.  And so in your Django request response cycle, just make sure to close that connection when the, when the request is done.  Right? So, so then you'll be safe.  I mean, I know Django shops that work great with, with, with PsychoPG and, and just, and Django and they don't have any issues.  And no people that complain about.  So how to cope if you need to use the Django or M or if you made the decision to use it.  Use a connection pooler.  Definitely because every request response cycle will open the database connection and close it.  So it's going to be slow.  And you will have, you know, you will have to up the max connections to high numbers if you want.  So these long line transactions be aware that this might be a problem, you know, be proactive, don't wait until your, your, your load explodes because of, because of open transactions.  Use PsychoPG because you can use other DBA PA drivers with the Django or M.  Don't use anything else.  Use PsychoPG.  And if you want to dismiss it, think about it because even though it's not that great, it's still very well integrated.  So you will need to, you know, do the like work of integrating a different ORM should you should you choose this.  So think about it.  So let's go into SQL Alchemy, which is, which is not only an ORM.  It's a, it's a complete, you know, framework toolkit, whatever you call it, that gives you everything you would need to work with SQL from Python.  So there are two main parts.  There is the classical ORM.  When you get Python objects, you get your database relations and you, they map together to be access things from your application.  And then they get returned to the database.  And then there's something that's like SQL, but made in Python.  So it's kind of like writing SQL by hand on using Python constructs.  And we see an example of how this process actually quite good.  You can do everything with SQL Alchemy.  If there's something you cannot do, they will consider the bug.  So that's, that's great.  It's not like they say, yeah, we do this and if you need something else, right, draw SQL or whatever.  I mean, you're on your own.  No, they, their philosophy is it needs to be possible because we, we do everything SQL.  So that's good.  And, and it has a learning curve. There are new words you will have to learn, new concepts.  It's, it's complex, but once you get the hang of it, it's, you know, it actually pays off.  So, so yeah, like I said, you, you define your models, you define tables in Python,  but it feels like just writing SQL statements to create a schema and then you map them together.  And you don't even have to map them together or you can map some objects to one database and some of just another database or some objects to, you know, this connection and some just just keeps them unmapped.  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 an ORM would support like, you know, database specific types, say, in, you know, UID,  in-app, HSTOR, all that stuff.  Operators. So you do, you know, all that funny point operators or, or HSTOR, access operators.  You can do them with SQL.  You can define your model. So they will work with cascading deletes, right?  So you have an ORM object. You delete it.  And then the object that, that had a collection of those will, so the other way around.  So you have your user and, and the user has her, say, books, right?  And then you delete that user and it will know that all the books of that user are gone if you configure it like that.  So it's smart enough to, to follow that and you can define that. That's awesome.  It's something you don't see every day in an ORM.  It supports schemas, like post-girsch schemas, which that's, that's great.  I mean, you wouldn't think of that and, and so on and so forth.  It's, it's really, you can do everything.  And it tries to get the last 20% right.  So Django says it's like 80-20, right?  We do the 80% of the stuff, right?  And that you're on your own with the last 20.  And they say, we're going to do it all right.  If the cost is increased complexity, but hey.  So how would, how, how would this work?  You'd create just an object, which is just a simple Python object.  There's nothing database or I'm nothing here.  It's just, you know, your application.  Yep.  Yep.  So let's say it's basically foreign keys with on update, on update cascade.  So you will, you will, it will know that if you get an object, update its attribute and say, save it to the database.  It will know that dependent objects will be changed.  So it's, it kind of is smart enough to follow that.  And the same with the leads.  It's, it's more, it's, it's more common to have this problem with the leads that you say, okay, I deleted something and then I access this other object.  And it says something's wrong with it, you know, you deleted it from, from underneath me.  You can't do this with SQL.  You can, it knows that the database will take care of that.  So with Django, Django would just first delete all the dependent objects with that delete statement and then delete your parent.  And here you can say, when I delete my parents, the database will take care of the children objects.  Do not touch them.  They will not be there because I have a cascading delete foreign key.  So, so okay, you have your application object.  You have your table.  So ignore the magic for now.  And just it's not that.  So yeah, there's some other place.  It's not so bad.  The thing is, you say, I want a table that's called car that has these three columns.  And this one's a unique code column.  Then I want to live into length.  It's going to be a primary key.  This one's going to be a car make.  And it's going to refer to, to the table with, when I store my makes and it's going to be deferrable.  So before, look, before knowing SQL team, I wouldn't dream of an ORM that will know what the variable foreign key is.  And that will do the right thing with it.  So it was great.  And then, you know, I'm on price as numeric.  And, and so I have my table.  I have my logic object.  And then I just say map one to the other.  But I don't have to say that.  Or I can map it to, to another table.  I can do this dynamically.  And so, right, you have this and this.  And then if you don't want to, you know, have your object separately and then your tables separately,  you can use the more classical approach that other ORMs like active re-correcting and so on use.  Which is, you know, just say this is my class that has, that is reflecting the database.  And then it looks kind of the same, but just in one slide.  Right?  So, so that's, so that's kind of quicker if you don't really need this separation.  Yep.  So, by the way, for Django apps, it's just easy to build a model and have it work with us.  Or it's just generating for the model.  Yep.  Is it uncommon to just create that middle class that just conforms?  Can you use SQL without breaking your Django app for all your queries?  But you can use Django to build the tables and everything that's used to this.  And Django, you don't have to do anything after you build the model because of the integration.  So, this or the Django orm?  I guess the question is, could you still build an entire Django app and use it for building the tables and everything that works really well  and upgrading and everything like that Django's itself?  And then, but somehow, maybe like this while you're in internal queries for the website, would you use SQL Alchemy?  You might try to define your models both in Django and then separately define them like this.  And hope that the definitions mean the same.  So, when you access it through this interface, it will be the same as the ones created by Django.  So, you might try that.  I would advise against that.  I think I would just, you know, if I needed something more than Django orm, I would take the hit and do migrations by hand,  write the schema by hand.  I would just do that.  There is a migration tool for SQL Alchemy though.  Yeah, but then, so I'll have a slide that says why it doesn't really fit with Django that well.  So, you might have migration, you might, you know, write some code, hook into the Django schema generation process  and do the SQL Alchemy thing.  That's actually what we do.  But it's extra like work.  You need to know your framework well.  Your developers might get confused if you do this.  So, it's not standard.  You won't, you know, find one million stack overflow answers to everything.  You will need to know what's going on.  So, bear not mix.  I just throw out the URL and put it in SQL Alchemy.  So, okay, you have these two ways of defining stuff.  And then what's great about SQL Alchemy, and that's the part when you get excited, is that it's all SQL.  You have it, you know, you can just reach out and touch it.  You can take your car, which is a table object, right?  I'm talking about this car here.  And use a lightly magic incantation to compile it to an SQL statement.  And we'll give you, you know, I needed this to be text that's limited by length.  This was supposed to be unlimited, so it's text.  There's, you know, the variable foreign key that works.  So, you can, you can see touch the SQL.  It's truly thought as something that compiles down to SQL.  So, that was the URL.  And now for the expression language part.  So, you can use the URL, like in a typical way of, you know, I have my car.  I need to know its price, or I need to know, I have this make, and I need to know the cars of this make that I have.  So, I just take the make, the cars attribute, and it will load all the cars magically.  You can do that.  Or you can just, you know, hand type your SQL, but in Python.  So, you get to write SQL.  If you think about it, if you know, just cover your, I mean, hold your nose and say,  I'm writing SQL, I'm writing SQL, only it's Python, but it's SQL.  So, you're going to be fine.  And actually, there are advantages to it being Python, because you can, you know,  get the select statement, ship it off to a different class subsystem, whatever,  and it will tack on work clauses, and then some other thing will,  will tack on the limit clause, or, you know, wrap it in a sub select.  You get composability, which is great.  You will be able to, you know, use the URM and then in places when it generates bad queries  or you want to use, you want to do something tricky because only then you get your  beloved index count, you will be able to do this.  And like I said, if you can't generate a statement you would like to write by hand,  this is a bug, and it should get fixed.  So, how would this work?  So, this is a SQL, right?  Update car set price equals price plus two, where make, lower make is Chevy.  So, notice a few things.  I was able to use a lower function.  I would be able to use any function I have, any stored procedure I have,  so, and it'll, it'll figure it out.  I was able to use the equals operator and it got, it got put in the work flows as the equals,  and it's going to be subsidiary for the parameter.  And I was, I was able to say, set the price to the price plus two and it, it knew what I meant.  Right? And so, should, should this be a string and I would say, up end, you can s to it.  This would become the, the, the two kind of pipes that, that is string and I'm not sure.  So, it's smart enough to take your Python and, and make it SQL.  So, this, this, this actually feels good.  This doesn't feel like, you know, this bad or I'm is, is doing bad stuff on my database.  This is, I'm writing my, my SQL only I'm doing it with this in the same language as the rest of the application.  And I use variables and I can use parameters and so on.  That's, that feels right.  So, like I said, what you get from using SQL Alchemy over, you know, handwriting your SQL,  which is something we all like to do, but sometimes we can't, is that you can, you know, it's,  you have your application in one language, which is a good thing.  Your developers are happier.  You can test easier.  You can make it, you know, composable or, or more customizable if you, if you do delegation of concerns correctly.  You can only generate SQL and then ship it off to your, you know, executing layer or your,  or your, you know, prepare transaction manager that does that, that, that multi-stab commit.  You can, you can do that.  You can ship it off to an asynchronous execution kind of thingy that, that they will just receive SQL and run it.  But you still get the advantage of writing it in your application language.  You can augment your code, your, your logic objects with, with, with stuff that you would,  it would cost you to do this all in SQL.  And then you can, you can write some code to make a column transparently encrypted when you store it.  So if you say, I'm storing something I need reversibly encrypted, like a user's API key, whatever.  And then whenever I will save this, it will get wrapped in a, in a PGP,  some encrypt with a key that I set in configuration.  And then when I read it, it's just going to be a plain string.  I'm going to get it decrypted.  And then you will see the encrypted, only store the encrypted version.  And you can have your, your, your objects, you know, you won't have to always write first name, concatenate last name.  You will be able to just have a property that gives you that automatically.  So an ORM might be actually useful.  Yeah, let's, I'm going to just skip over this slide because we're kind of running late.  So when if you use SQL Alchemy, you will come across this session object that kind of sits between your connection and your code that you use to access the database.  And that, that keeps track of which objects are dirty and which need to be persisted database.  This is a thing you will commit to rollback your session.  And then the good thing and the difference with Django that it makes it easy to have persistent session objects.  So you won't have to use a pooler if you can just store the session object in some persistent context.  Like if you're doing web programming, you would use Whiskey and then you will have something that, that executes Python code and, and gives the, the generated response to the web server.  And you would keep the session object inside the Whiskey process that's persistent.  And then you wouldn't have to use pooling because you wouldn't use, you wouldn't, you know, create and destroy connections on each response, request response cycle.  So, so let's talk, so yeah, the last part is PL Python, which is kind of different.  PL Python is being able to run Python code inside PostDress and on behalf of the, of the PostDress process.  So it's, it's kind of different, but it's, it's a way you can, if your application, if your stack is Python, you might want to use, if you need stored procedures, you might want to consider using Python for that.  So then your, your developers are happier because they only deal with one language and they don't need to, you know, learn PL, PG SQL and so on.  So, so this is Python inside the backend.  It executes arbitrary code so it's like, it can run anything.  It opens files.  It can open sockets.  It can delete your data directory.  So it's untrusted.  You, you need to, you know, assume that if you're in an environment when you can't use untrusted languages,  you're out of luck.  And if I have the time, you might say a few words about when or if this could change, but the simple, the short answer is, it's very difficult to change that.  So it will be untrusted for a long time.  And it's, it's Python so you actually can do crazy stuff.  But that's kind of the fun of using Python inside your database.  So how does this work?  When you run PL Python code and interpreter, like the whole Python runtime gets created, instantiated and embedded in your, in your backend process.  So if you want this to be fast, you should preload PL Python.  So you won't pay the overhead on the first query of, you know, the open, looking up the shell, the library and so on.  And you want to use long-lived connections of this.  So you, so you won't even have to preload that.  So it will just be kept in memory.  It's, well, quite well integrated with a POSGRESS type system.  So if a PL Python function receives a POSGRESS integer, you will see it as an integer inside Python.  And if it receives a, let's say, array, you will see it as a list.  So these things kind of work.  The rest of the types will get serialized to strings.  So if you have your own crazy extension type, what POSGRESS will do is it will call its text out-of-function, get the string and give it to Python.  So then you would have to parse it back into some object that's, that would be useful to you.  There is an extension for the very typical case of passing h-stores into Python and operating on it as dictionaries.  So you might want to do that, but in general, you will need to deal with strings if you have composite types and so on.  Function arguments to stored procedures are visible as global.  We'll see an example.  So you just get to access them as if they were arguments to the, to the Python function.  And then you have a bit of magic that you can access that will allow you to run queries on the database that in a trigger 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 tuple.  We'll see examples in a minute.  And so the, the, which modules are visible depends on the, on the Python path. This is a, a environment variable that's kind of like class path for Java or Inc for Perl.  So what's available, what's visible depends on that.  And the back end process goes, the back end processes get forked from, from the postmaster. So if you need your application code to be visible to your PL Python functions, you probably will need to edit the environment that the post address executes in to include inside Python path, your application deployment directory or whatever.  There's just something to keep in mind that that's the way to do this probably.  So like, this is, I think this is just one slide about how to make PL Python not that bad if you don't want to play with it, if you want to actually run production stuff with it.  Keep all your code inside the module, inside your application.  Don't just ship it into the database.  Keep it under version control inside your application.  Make all the SQL functions as two lines.  You create a function, you get some arguments, and then the only thing that actually happens inside SQL, the only thing that gets stored as a SQL function inside Postgres is you get the function and you call it and you pass it, you know, everything that's inside the scope of this function.  So this will include the arguments, this will include the, the trigger information if it's there that will include, you know, the module that you use to, that provides utilities and so on.  And then the rest happens inside your application.  And that has the advantage of being better testable because you get this function, you just pass it, you know, mocks for that and you test it just with the rest of the application.  So it's kind of, that's, that should help and always be careful because you are running full blown, a full blown application language inside your database, you can hurt yourself bad.  That said, there's a few funny things you can do.  So there are some examples, you know, give you a taste and then maybe some ideas, good or bad.  What can you do? You can, for instance, import the numerical library and do fast Fourier transformations inside your database directly using, using Python code that then calls into optimized C that does stuff fast.  You can write a check constraint that checks whether a column that's declared text holds JSON.  Might not be the best idea, but you might do that.  Or you might check that a byte, byte here is a PNG image or, you know, whatever.  You can open connections, you can, you know, import cycle PG, connect to the database and have like DBI link with that.  You can, you know, do HTTP requests, you can invalidate caches on your, I know, I think, Madness last year was talking about using PL Python to invalidate a varnish cache when something got updated in the database using an HTTP request.  That's actually, that's a pretty good idea, I guess.  And you can do, you know, things like network access and so on.  So here's an example. That's a function that will get an extension name and then it will get the standard diff label that's library for doing diffs.  It will get all the available except the extensions.  So it uses the PL Py module that's something you get that's available globally in every PL Python function that includes an SPI interface.  So you can run queries just like in PGPL SQL, you would be able to run, you know, select and execute it.  That's how you do it in Python.  And then you get all the names and you say, get me the closest matches for what I wrote, for what I've written.  So this is X name right here.  And these are the possible names that I have.  So that would work.  Then it's, wait. So let's say I run find extension.  It will return an array. So I will unnest it.  And it's a poll just array, right?  I can call unnest.  And I'm looking for this fuzzy string give thingy that's there.  And it will find, you know, fuzzy string match.  And if I look for hstrah, it will find hstrah.  So, you know, we can run arbitrary code and use all the power of Python modules, all the ecosystem, everything.  There's a, yeah, there are two more examples.  So this one is a check that might be a borderline good idea.  I think I would recommend this, but it kind of shows what you can do.  So you get a trigger that gets the email of the new tuple, splits it on the yet sign, so gets the domain name,  and then checks if it has an MX record.  And if it doesn't, it throws an error and says, whoa, you don't have an MX record.  Right? So this would actually, if I have internet connection,  users.  So yeah, okay, my email got inserted, but example org doesn't have an MX domain.  So it says no MX record for domain example org.  That's kind of cool.  And then the last example I have here is a function that will show you the PG con schedule in the database,  which works by getting, downloading the iCalendar that's available on the website,  you know, parsing it with the iCalendar module, then looking for events,  and just returning the summary, the location, and the time.  And you will see, I have three out parameters, two are text, one is a timestamp,  and actually it will be timestamp. It will be able to do, you know,  calculations, time zone stuff, and so on and so forth.  So let's just look at the schedule, and here it is.  I just downloaded the iCalendar and I parsed it, and here I have my date times and so on.  So we're done. We are slightly over time, and only there's something up next.  If you want, if you want to ask questions you can just ask,  or if you can just come talk to me after. Thanks for listening.
On_snakes_and_elephants_0_131.png On_snakes_and_elephants_132_2063.png On_snakes_and_elephants_2064_2867.png On_snakes_and_elephants_2868_4535.png On_snakes_and_elephants_4536_6203.png On_snakes_and_elephants_6204_7751.png On_snakes_and_elephants_7752_9659.png On_snakes_and_elephants_9660_12503.png On_snakes_and_elephants_12504_12971.png On_snakes_and_elephants_12972_13163.png On_snakes_and_elephants_13164_13403.png On_snakes_and_elephants_13404_16823.png On_snakes_and_elephants_16824_17363.png On_snakes_and_elephants_17364_18059.png On_snakes_and_elephants_18060_18359.png On_snakes_and_elephants_18360_18755.png On_snakes_and_elephants_18756_20243.png On_snakes_and_elephants_20244_22235.png On_snakes_and_elephants_22236_24035.png On_snakes_and_elephants_24036_25619.png On_snakes_and_elephants_25620_25787.png On_snakes_and_elephants_25788_28115.png On_snakes_and_elephants_28116_29135.png On_snakes_and_elephants_29136_34523.png On_snakes_and_elephants_34524_35651.png On_snakes_and_elephants_35652_35951.png On_snakes_and_elephants_35952_37235.png On_snakes_and_elephants_37236_38771.png On_snakes_and_elephants_38772_39671.png On_snakes_and_elephants_39672_41639.png On_snakes_and_elephants_41640_43799.png On_snakes_and_elephants_43800_44831.png On_snakes_and_elephants_44832_44915.png On_snakes_and_elephants_44916_45119.png On_snakes_and_elephants_45120_46163.png On_snakes_and_elephants_46164_49835.png On_snakes_and_elephants_49836_51347.png On_snakes_and_elephants_51348_51467.png On_snakes_and_elephants_51468_53507.png On_snakes_and_elephants_53508_56591.png On_snakes_and_elephants_56592_58799.png On_snakes_and_elephants_58800_60275.png On_snakes_and_elephants_60276_60503.png On_snakes_and_elephants_60504_60707.png On_snakes_and_elephants_60708_64475.png On_snakes_and_elephants_64476_64523.png On_snakes_and_elephants_64524_64583.png On_snakes_and_elephants_64584_65051.png On_snakes_and_elephants_65052_65783.png On_snakes_and_elephants_65784_68027.png On_snakes_and_elephants_68028_70319.png On_snakes_and_elephants_70320_75239.png On_snakes_and_elephants_75240_76175.png On_snakes_and_elephants_76176_81671.png On_snakes_and_elephants_81672_83687.png On_snakes_and_elephants_83688_83903.png On_snakes_and_elephants_83904_85559.png On_snakes_and_elephants_85560_86831.png On_snakes_and_elephants_86832_87275.png On_snakes_and_elephants_87276_87359.png On_snakes_and_elephants_87360_87431.png On_snakes_and_elephants_87432_87575.png On_snakes_and_elephants_87576_87671.png On_snakes_and_elephants_87672_87755.png On_snakes_and_elephants_87756_88571.png On_snakes_and_elephants_88572_88619.png On_snakes_and_elephants_88620_88739.png On_snakes_and_elephants_88740_88787.png On_snakes_and_elephants_88788_88859.png On_snakes_and_elephants_88860_88907.png On_snakes_and_elephants_88908_88967.png On_snakes_and_elephants_88968_89219.png On_snakes_and_elephants_89220_89303.png On_snakes_and_elephants_89304_90191.png On_snakes_and_elephants_90192_90239.png On_snakes_and_elephants_90240_90371.png On_snakes_and_elephants_90372_90419.png On_snakes_and_elephants_90420_90467.png On_snakes_and_elephants_90468_90515.png On_snakes_and_elephants_90516_90575.png On_snakes_and_elephants_90576_90623.png On_snakes_and_elephants_90624_90695.png On_snakes_and_elephants_90696_90755.png On_snakes_and_elephants_90756_90887.png On_snakes_and_elephants_90888_90983.png On_snakes_and_elephants_90984_91031.png On_snakes_and_elephants_91032_91127.png On_snakes_and_elephants_91128_91197.png