Modifying Data

Querying data is all well and good, but at some point you’re probably going towant to put data into your database! This section deals with inserting,updating, and deleting information. Operations that alter your data like thisare collectively known as Data Manipulation Language, or DML.

In previous sections, we returned to you the results of the query you’veperformed. Since modifications like the ones we’re making in this section don’treturn any query results, we instead show you the updated content of the tableyou’re supposed to be working on.

Insert some data into a table

The club is adding a new facility - a spa. We need to add it into thefacilities table. Use the following values: facid: 9, Name: ‘Spa’,membercost: 20, guestcost: 30, initialoutlay: 100000, monthlymaintenance: 800

INSERT INTO "facilities" ("facid", "name", "membercost", "guestcost",
"initialoutlay", "monthlymaintenance") VALUES (9, 'Spa', 20, 30, 100000, 800)

res = Facility.insert({
    Facility.facid: 9,
    Facility.name: 'Spa',
    Facility.membercost: 20,
    Facility.guestcost: 30,
    Facility.initialoutlay: 100000,
    Facility.monthlymaintenance: 800}).execute()
 
# OR:
res = (Facility
       .insert(facid=9, name='Spa', membercost=20, guestcost=30,
               initialoutlay=100000, monthlymaintenance=800)
       .execute())

Insert multiple rows of data into a table

In the previous exercise, you learned how to add a facility. Now you’re goingto add multiple facilities in one command. Use the following values:

facid: 9, Name: ‘Spa’, membercost: 20, guestcost: 30, initialoutlay: 100000,monthlymaintenance: 800.

facid: 10, Name: ‘Squash Court 2’, membercost: 3.5, guestcost: 17.5,initialoutlay: 5000, monthlymaintenance: 80.

-- see above --

data = [
    {'facid': 9, 'name': 'Spa', 'membercost': 20, 'guestcost': 30,
     'initialoutlay': 100000, 'monthlymaintenance': 800},
    {'facid': 10, 'name': 'Squash Court 2', 'membercost': 3.5,
     'guestcost': 17.5, 'initialoutlay': 5000, 'monthlymaintenance': 80}]
res = Facility.insert_many(data).execute()

Insert calculated data into a table

Let’s try adding the spa to the facilities table again. This time, though, wewant to automatically generate the value for the next facid, rather thanspecifying it as a constant. Use the following values for everything else:Name: ‘Spa’, membercost: 20, guestcost: 30, initialoutlay: 100000,monthlymaintenance: 800.

INSERT INTO "facilities" ("facid", "name", "membercost", "guestcost",
  "initialoutlay", "monthlymaintenance")
SELECT (SELECT (MAX("facid") + 1) FROM "facilities") AS _,
        'Spa', 20, 30, 100000, 800;

maxq = Facility.select(fn.MAX(Facility.facid) + 1)
subq = Select(columns=(maxq, 'Spa', 20, 30, 100000, 800))
res = Facility.insert_from(subq, Facility._meta.sorted_fields).execute()

Update some existing data

We made a mistake when entering the data for the second tennis court. Theinitial outlay was 10000 rather than 8000: you need to alter the data to fixthe error.

UPDATE facilities SET initialoutlay = 10000 WHERE name = 'Tennis Court 2';

res = (Facility
       .update({Facility.initialoutlay: 10000})
       .where(Facility.name == 'Tennis Court 2')
       .execute())
 
# OR:
res = (Facility
       .update(initialoutlay=10000)
       .where(Facility.name == 'Tennis Court 2')
       .execute())

Update multiple rows and columns at the same time

We want to increase the price of the tennis courts for both members andguests. Update the costs to be 6 for members, and 30 for guests.

UPDATE facilities SET membercost=6, guestcost=30 WHERE name ILIKE 'Tennis%';

nrows = (Facility
         .update(membercost=6, guestcost=30)
         .where(Facility.name.startswith('Tennis'))
         .execute())

Update a row based on the contents of another row

We want to alter the price of the second tennis court so that it costs 10%more than the first one. Try to do this without using constant values for theprices, so that we can reuse the statement if we want to.

UPDATE facilities SET
membercost = (SELECT membercost * 1.1 FROM facilities WHERE facid = 0),
guestcost = (SELECT guestcost * 1.1 FROM facilities WHERE facid = 0)
WHERE facid = 1;
 
-- OR --
WITH new_prices (nmc, ngc) AS (
  SELECT membercost * 1.1, guestcost * 1.1
  FROM facilities WHERE name = 'Tennis Court 1')
UPDATE facilities
SET membercost = new_prices.nmc, guestcost = new_prices.ngc
FROM new_prices
WHERE name = 'Tennis Court 2'

sq1 = Facility.select(Facility.membercost * 1.1).where(Facility.facid == 0)
sq2 = Facility.select(Facility.guestcost * 1.1).where(Facility.facid == 0)
 
res = (Facility
       .update(membercost=sq1, guestcost=sq2)
       .where(Facility.facid == 1)
       .execute())
 
# OR:
cte = (Facility
       .select(Facility.membercost * 1.1, Facility.guestcost * 1.1)
       .where(Facility.name == 'Tennis Court 1')
       .cte('new_prices', columns=('nmc', 'ngc')))
res = (Facility
       .update(membercost=SQL('new_prices.nmc'), guestcost=SQL('new_prices.ngc'))
       .with_cte(cte)
       .from_(cte)
       .where(Facility.name == 'Tennis Court 2')
       .execute())

Delete all bookings

As part of a clearout of our database, we want to delete all bookings fromthe bookings table.

DELETE FROM bookings;

nrows = Booking.delete().execute()

Delete a member from the cd.members table

We want to remove member 37, who has never made a booking, from our database.

DELETE FROM members WHERE memid = 37;

nrows = Member.delete().where(Member.memid == 37).execute()

Delete based on a subquery

How can we make that more general, to delete all members who have never madea booking?

DELETE FROM members WHERE NOT EXISTS (
  SELECT * FROM bookings WHERE bookings.memid = members.memid);

subq = Booking.select().where(Booking.member == Member.memid)
nrows = Member.delete().where(~fn.EXISTS(subq)).execute()