数据工程师面试必备——Python与数据库的那些事
最近小伙伴在准备python数据工程师的面试，趁这个机会整理了Python与数据库的相关问题，话不多说，直接开始。
一、关系数据库与非关系数据库
SQL（Structured Query Language）数据库，指关系型数据库。主要代表：SQL Server、Oracle、MySQL、PostgreSQL。
NoSQL（Not Only SQL）泛指非关系型数据库。主要代表：MongoDB、Redis、CouchDB。
关系数据库是以表的形式存储数据的数据库。每个表都有一个模式，即记录需要的列和类型。每个模式必须至少有一个主键来唯一标识该记录。换句话说，数据库中没有重复的行。此外，每个表可以使用外键与其他表关联。
关系数据库的一个重要方面是必须将模式中的更改应用于所有记录。这有时会在迁移期间造成破坏，因此十分麻烦。非关系数据库以不同的方式处理问题。它们本质上是无模式的，这意味着可以用不同的模式和不同的嵌套结构保存记录。记录仍然可以有主键，但是模式中的更改是在逐项基础上进行的。
一个例子
我们使用SQLite来举例，首先，导入所需的Python库并创建一个新数据库
import sqlite3
db = sqlite3.connect(':memory:') # 使用内存数据库
cur = db.cursor()接下来，创建以下三个表：
-
客户：此表包含一个主键以及客户的名字和姓氏。
-
物品：此表包含主键，物品名称和物品价格。
-
购买的项目：此表将包含订单号，日期和价格。它还将连接到“项目”和“客户”表中的主键。
id integer PRIMARY KEY,
firstname varchar(255),
lastname varchar(255) )''')
cur.execute('''CREATE TABLE IF NOT EXISTS Item (
id integer PRIMARY KEY,
title varchar(255),
price decimal )''')
cur.execute('''CREATE TABLE IF NOT EXISTS BoughtItem (
ordernumber integer PRIMARY KEY,
customerid integer,
itemid integer,
price decimal,
CONSTRAINT customerid
FOREIGN KEY (customerid) REFERENCES Customer(id),
CONSTRAINT itemid
FOREIGN KEY (itemid) REFERENCES Item(id) )''')再往表里填充一些数据
cur.execute('''INSERT INTO Customer(firstname, lastname)
VALUES ('Bob', 'Adams'),
('Amy', 'Smith'),
('Rob', 'Bennet');''')
cur.execute('''INSERT INTO Item(title, price)
VALUES ('USB', 10.2),
('Mouse', 12.23),
('Monitor', 199.99);''')
cur.execute('''INSERT INTO BoughtItem(customerid, itemid, price)
VALUES (1, 1, 10.2),
(1, 2, 12.23),
(1, 3, 199.99),
(2, 3, 180.00),
(3, 2, 11.23);''') OK，现在每个表中都有一些数据，现在我们用这些数据来回答进行下一步
SQL聚合函数
聚合函数是对结果集执行数学运算的函数。比如AVG，COUNT，MIN，MAX，和SUM。一般来说，还要使用GROUP BY和HAVING子句来搭配使用。拿AVG函数来说，可以用来计算给定结果集的平均值：
>>> cur.execute('''SELECT itemid, AVG(price) FROM BoughtItem GROUP BY itemid''')
>>> print(cur.fetchall())
[(1, 10.2), (2, 11.73), (3, 189.995)]上面sql语句就提取出数据库中购买的每个商品的平均价格。也可以显示项目名称，而不是itemid⬇️
>>> cur.execute('''SELECT item.title, AVG(boughtitem.price) FROM BoughtItem as boughtitem
... INNER JOIN Item as item on (item.id = boughtitem.itemid)
... GROUP BY boughtitem.itemid''')
...
>>> print(cur.fetchall())
[('USB', 10.2), ('Mouse', 11.73), ('Monitor', 189.995)]另一个有用的聚合函数是SUM。比如可以使用此功能显示每个客户花费的总金额⬇️
>>> cur.execute('''SELECT customer.firstname, SUM(boughtitem.price) FROM BoughtItem as boughtitem
... INNER JOIN Customer as customer on (customer.id = boughtitem.customerid)
... GROUP BY customer.firstname''')
...
>>> print(cur.fetchall())
[('Amy', 180), ('Bob', 222.42000000000002), ('Rob', 11.23)]加速SQL查询
SQL语句的执行速度取决很多因素，但主要受以下几种因素的影响：
-
连接
-
聚合
-
遍历
-
记录
连接数越多，表的复杂度越高，遍历次数也越多。在涉及多个表的数千条记录上执行多次连接非常麻烦的，因为数据库还需要缓存中间结果，所以真的需要的话就要考虑如何增加内存大小。
执行速度还受数据库中是否存在索引的影响。索引非常重要，它可以快速搜索表并找到查询中指定列的匹配项。索引以增加插入时间和一些存储为代价对记录进行排序。可以组合多个列以创建单个索引。
调试SQL查询
大多数数据库都包含一个EXPLAIN QUERY PLAN描述数据库执行查询的步骤的。对于SQLite，可以通过EXPLAIN QUERY PLAN在SELECT语句前面添加来启用此功能：
>>> cur.execute('''EXPLAIN QUERY PLAN SELECT customer.firstname, item.title,
... item.price, boughtitem.price FROM BoughtItem as boughtitem
... INNER JOIN Customer as customer on (customer.id = boughtitem.customerid)
... INNER JOIN Item as item on (item.id = boughtitem.itemid)''')
...
>>> print(cur.fetchall())
[(4, 0, 0, 'SCAN TABLE BoughtItem AS boughtitem'),
(6, 0, 0, 'SEARCH TABLE Customer AS customer USING INTEGER PRIMARY KEY (rowid=?)'),
(9, 0, 0, 'SEARCH TABLE Item AS item USING INTEGER PRIMARY KEY (rowid=?)')]该查询尝试列出所有购买商品的名字，商品标题，原始价格和购买价格。而该查询计划本应这样写⬇️
SCAN TABLE BoughtItem AS boughtitem
SEARCH TABLE Customer AS customer USING INTEGER PRIMARY KEY (rowid=?)
SEARCH TABLE Item AS item USING INTEGER PRIMARY KEY (rowid=?)二、有关非关系数据库的问题
在第一节已经说明了关系数据库和非关系数据库之间的差异，并将SQLite与Python结合使用，本节主要讲NoSQL。
以MongoDB为例
首先安装在python中使用MongoDB相关的库
$ pip install pymongo再创建数据库并插入一些数据⬇️
import pymongo
client = pymongo.MongoClient("mongodb://localhost:27017/")
# Note: This database is not created until it is populated by some data
db = client["example_database"]
customers = db["customers"]
items = db["items"]
customers_data = [{ "firstname": "Bob", "lastname": "Adams" },
{ "firstname": "Amy", "lastname": "Smith" },
{ "firstname": "Rob", "lastname": "Bennet" },]
items_data = [{ "title": "USB", "price": 10.2 },
{ "title": "Mouse", "price": 12.23 },
{ "title": "Monitor", "price": 199.99 },]
customers.insert_many(customers_data)
items.insert_many(items_data)可以发现MongoDB将数据记录存储在collection中，等价于Python中的字典列表。
使用MongoDB查询
首先尝试复制BoughtItem表，就在SQL中所做的一样。先向客户追加一个新字段。MongoDB的文档指定关键字操作符集可以用来更新一条记录，而不必写所有现有的字段:
bob = customers.update_many(
{"firstname": "Bob"},
{
"$set": {
"boughtitems": [
{
"title": "USB",
"price": 10.2,
"currency": "EUR",
"notes": "Customer wants it delivered via FedEx",
"original_item_id": 1
}
]
},
}
)实际上，可以稍微更改架构来更新另一个客户：
amy = customers.update_many(
{"firstname": "Amy"},
{
"$set": {
"boughtitems":[
{
"title": "Monitor",
"price": 199.99,
"original_item_id": 3,
"discounted": False
}
]
} ,
}
)
print(type(amy)) # pymongo.results.UpdateResult可以像在SQL中一样执行查询。首先，可以创建一个索引
>>> customers.create_index([("name", pymongo.DESCENDING)])然后，就可以更快的检索按升序排序的客户名称：
>>> items = customers.find().sort("name", pymongo.ASCENDING)还可以遍历并打印购买的物品：
>>> for item in items:
... print(item.get('boughtitems'))
...
None
[{'title': 'Monitor', 'price': 199.99, 'original_item_id': 3, 'discounted': False}]
[{'title': 'USB', 'price': 10.2, 'currency': 'EUR', 'notes': 'Customer wants it delivered via FedEx', 'original_item_id': 1}]甚至可以在数据库中检索唯一的名字列表:
>>> customers.distinct("firstname")
['Bob', 'Amy', 'Rob']现在我们已经知道数据库中客户的名称，可以创建一个查询检索有关他们的信息：
>>> for i in customers.find({"$or": [{'firstname':'Bob'}, {'firstname':'Amy'}]},
... {'firstname':1, 'boughtitems':1, '_id':0}):
... print(i)
...
{'firstname': 'Bob', 'boughtitems': [{'title': 'USB', 'price': 10.2, 'currency': 'EUR', 'notes': 'Customer wants it delivered via FedEx', 'original_item_id': 1}]}
{'firstname': 'Amy', 'boughtitems': [{'title': 'Monitor', 'price': 199.99, 'original_item_id': 3, 'discounted': False}]}写成SQL语句就是
SELECT firstname, boughtitems FROM customers WHERE firstname LIKE ('Bob', 'Amy')NoSQL与SQL
如果架构是不断变化的（例如财务监管信息），则NoSQL可以修改记录并嵌套相关信息。想象一下，如果我们有八个嵌套顺序，那么在SQL中必须执行的连接数需要多少。但是现在，如果需要运行报告，提取有关该财务数据的信息并推断结论该怎么办？在这种情况下，就需要运行复杂的查询，并且SQL在这方面往往会更快。
注意： SQL数据库（尤其是PostgreSQL）还发布了一项功能，该功能允许将可查询的JSON数据作为记录的一部分插入。虽然这可以结合两个方面的优势，但速度可能并没有很好。而从NoSQL数据库查询非结构化数据比从PostgreSQL中的JSON类型列查询JSON字段要快。
由于存在各种各样的数据库，每个数据库都有其自身的功能，因此，还需要具体分析，以决定使用哪个数据库。
三、有关缓存数据库的问题
缓存数据库保存经常访问的数据。它们与主要的SQL和NoSQL数据库并存。他们的目标是减轻负载并更快地处理请求。
上一节已经为长期存储解决方案介绍了SQL和NoSQL数据库，但是更快，更直接的存储又如何呢？数据工程师又如何更改从数据库检索数据的速度？典型的Web应用程序经常检索常用数据，例如用户的个人资料或姓名。如果所有数据都包含在一个数据库中，则数据库服务器获得的命中次数将超过最高且不必要。因此，需要更快，更直接的存储解决方案。
尽管这减少了服务器负载，但也给数据工程师，后端团队和DevOps团队带来了两个麻烦。首先，现在需要一个读取时间比主SQL或NoSQL数据库更快的数据库。但是，两个数据库的内容必须最终匹配。
所以收到请求时，首先要检查缓存数据库，然后是主数据库。这样，可以防止任何不必要和重复的请求到达主数据库的服务器。由于缓存数据库的读取时间较短，因此还能让性能提升。
以Redis为例
首先用pip安装相关的库
$ pip install redis现在，考虑一个简单的例子：从ID中获取用户名的请求：
import redis
from datetime import timedelta
r = redis.Redis()
def get_name(request, *args, **kwargs):
id = request.get('id')
if id in r:
return r.get(id)
else:
name = 'Bob'
r.setex(id, timedelta(minutes=60), value=name)
return name此代码使用id来检查名称是否在Redis中。如果不是，则使用过期时间来设置名称，现在，如果面试官问这段代码是否有问题，回答应该是没有异常处理！数据库可能有很多问题，例如连接断开，因此永远要考虑异常捕捉。
四、结束语
有关数据库相关的问题还有设计模式、ETL概念或者是大数据中的设计模式。这些就留到以后再聊。
