14.5 修改数据 Modifying Data

http://docs.peewee-orm.com/en/latest/peewee/query_examples.html#modifying-data
查询数据一切都很好，但是在某些时候您可能想要将数据放入数据库中！本节处理插入、更新和删除信息。像这样更改数据的操作统称为数据操作语言或 DML。

在前面的部分中，我们向您返回了您执行的查询的结果。由于我们在本节中所做的修改不会返回任何查询结果，因此我们会向您显示您应该处理的表的更新内容。
Querying data is all well and good, but at some point you’re probably going to want to put data into your database! This section deals with inserting, updating, and deleting information. Operations that alter your data like this are collectively known as Data Manipulation Language, or DML.

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

14.5.1 向表中插入一些数据 Insert some data into a table

俱乐部正在增加一个新设施——一个水疗中心。我们需要将它添加到设施表中。使用以下值：facid：9，名称：‘Spa’，membercost：20，guestcost：30，initialoutlay：100000，monthlymaintenance：800
The club is adding a new facility - a spa. We need to add it into the facilities 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)

python">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())

14.5.2 向表中插入多行数据 Insert multiple rows of data into a table

在上一个练习中，您学习了如何添加设施。现在您将在一个命令中添加多个设施。使用以下值：

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 --

python">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()

14.5.3 将计算数据插入表中 Insert calculated data into a table

让我们再次尝试将水疗中心添加到设施表中。不过，这一次，我们希望自动生成下一个 facid 的值，而不是将其指定为常量。对其他所有内容使用以下值：名称：‘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;

python">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()

14.5.4 更新一些现有数据 Update some existing data

我们在输入第二个网球场的数据时出错。最初的支出是 10000 而不是 8000：您需要更改数据以修复错误。

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

python">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())

14.5.5 同时更新多行多列 Update multiple rows and columns at the same time

我们想为会员和客人提高网球场的价格。将费用更新为会员 6 美元和客人 30 美元。

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

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

14.5.6 根据另一行的内容更新一行 Update a row based on the contents of another row

我们想改变第二个网球场的价格，使它比第一个网球场贵 10%。尝试在不使用价格常量的情况下执行此操作，以便我们可以根据需要重用该语句。

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.1FROM 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'

python">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())

14.5.7 删除所有预订 Delete all bookings

作为我们数据库清除的一部分，我们希望从预订表中删除所有预订。

DELETE FROM bookings;

python">nrows = Booking.delete().execute()

14.5.8 从 cd.members 表中删除一个成员 Delete a member from the cd.members table

我们想从我们的数据库中删除从未进行过预订的会员 37。

DELETE FROM members WHERE memid = 37;

python">nrows = Member.delete().where(Member.memid == 37).execute()

14.5.9 基于子查询删除 Delete based on a subquery

我们如何才能更笼统地删除所有从未进行过预订的成员？

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

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