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