How to use a database ORM in Python

hi there serdar here for infoworld at idg today  in smartpython i'll be demonstrating an example   of an orm or object relation mapper used in  many languages to abstract access to a database   in a previous episode of smart python i did a  little run through of how to use the database   with python specifically sqlite the little  standalone database that ships with python   but one of the difficulties involved with any  database is working with sql this is the querying   and management language used by most databases  writing raw sql is fraught with difficulties   and dangers especially for a database that can  be accessed by the general public hence orms   which abstract away the details of working with  the database and make it both easier and safer   python has a number of orms that wrap  databases inside python's object model   instead of working with sql strings you  just work with python objects like classes   one of my favorite orms which i'll use  for this demo is a project called peewee   it's small and lightweight but powerful and that  makes it suited to quick demo because we don't   have to do a lot of jumping through hoops to get  things set up the first thing we typically do with   an orm is to find the underlying database object  with sqlite this is just a file we open and pwe   has native support for sqlite through its sqlite  database object so to set up an sqlitedb all we   have to do is create an instance of that object  and pass the name we want to use as a parameter   next we define the objects inside the  database the tables and again we do that   by way of using objects provided by peewee the  basic unit for such things is the model class   all tables in p we are created with it and  note that in pwe with each model we create   we have to associate it explicitly with a database  object of some kind using an internal meta class   now to save us having to do this for every  table we create we can just subclass model   with our database reference included  which is what i've done here   each model or table is expressed as a class and  each field in the table is defined with a class   level variable which is assigned to an instance  of an object that correlates to some data type   for instance here we have a text field  a date field and an integer field   each one of these fields on this table is created  with parameters that describe the field's behavior   for instance we can use the null parameter  to indicate if the field is nullable   that is if it can contain no value at all and  we use index to indicate if we should build an   index for that field or not to make it easier  to search and sort on that particular field   now note that we haven't actually created the  table described here in the database we've   just provided a schema in the abstract we've  just described what it's going to look like   to actually create the table and to  fill it with data that's a separate step   so let's go to that now in the second code  example here first we connect to the database   you have to do this step explicitly so that the  databases state can be tracked and then we create   the tables we defined earlier by just passing  their schema objects to the createtables method   now let's create a first record in the table  this we do by taking the model and using its   create method and then passing along parameters  that correspond to the data in the fields that   we want to fill in once the record's created we  can save a reference to the record in a variable   and then make further manipulations on that  record by just changing the object's attributes   or using its methods for instance for blade runner  we can set the rating for the film separately from   the creation step but we have to explicitly  save the changes we make to the object so   that they are reflected in the underlying database  otherwise they will just exist in our local copy   of the database record and not in the actual  database itself now we don't have to preserve   each correct each record we create as an object in  a variable as we did here in the next few lines of   code we're just looping through some tuples and  creating objects in the database for each one   anonymously no need to keep a reference to each  one here because we can just access them later   when we want to read data back out of the database  we also use methods on the table's class object   and pb does this in a way that's reminiscent  of an actual sql query which makes it a little   easier for people familiar with sql to do it  so in this example we'd start by just selecting   every record in movies and to do that we would  just say movie dot select easy enough and for   that we get everything back although it's  not in any particular order it's not filtered   so if we just want the movies that start  with blade we can use a chained where clause   again this particular query syntax is peculiar  to peewee but it's reminiscent of the actual sql   query one might construct to do this sql has  a where clause in its syntax and this is a an   imitation of the way that it looks and the same  also goes for queries when we want to filter or   sort records in this example here we're including  only records for movies that were made in 1971 or   later note that we have to compare against a date  value for filtering for this field because that's   the data type for that field for sorting we  can use order by either ascending or descending   to perform sort operations in the first case  we're sorting by title and as you can see one   of the problems with using an alphabetical  sort on text is that sometimes you'll have   something that'll start with a number and that  can produce sorting that's counterintuitive   but sorting by rating and descending order  is simple enough that's just an integer   changing data with an orm again works much the  same way as any of the previous things we just use   methods on the table class in the record objects  in this example i'm going to create two movies   first the matrix and this we're going to create  and save without keeping an object association   and then another movie which we're going  to save a reference to in a variable   for the matrix we can change it by way of  an update query since we don't have a local   reference to it we'll use the where clause  to specify which records to change according   to what's in the update queries uh parameters but  for the other movie since we already have a local   object reference to it we just have to change the  rating on the local object and then invoke the   save method to make those changes propagate to the  back end the basic idea as i've demonstrated here   is that any actions with the database creating  schemas for data creating data itself changing   and searching for it all of these things are  done using python objects in ways that are   familiar to python programmers they're not  done by manually constructing sql commands   now that said it's going to be really helpful to  start by learning sql first and database generally   before working with an orm because then you'll  have a better understanding of what's going on   under the hood but with an orm you can  manage all the pieces that you shouldn't   have to manage with your bare hands that's  it for this video if you liked it leave a   comment below and don't forget to subscribe  to the idg tech talk channel on youtube and   for more smart python be sure to follow  us on facebook youtube and inforwil.com you

Channel: IDG TECHtalk

Views: 4,356

Rating: 4.9487181 out of 5

Keywords: Python tutorial, SQLite, Python databases, ORM, Peewee

Id: Vk6Ptnvqr4M

Length: 7min 4sec (424 seconds)

Published: Mon Jan 25 2021