400-616-5551

您所在位置: 首页> 学习课程> 数据工程师面试必备:Python与数据库

数据工程师面试必备:Python与数据库

发布百知教育 来源:学习课程 2020-02-21

最近身边有小伙伴在准备python数据工程师的面试,趁这个机会整理了Python与数据库的相关问题,话不多说,直接开始。


一、关系数据库与非关系数据库

SQL(Structured Query Language)数据库,指关系型数据库。主要代表:SQL Server、Oracle、MySQL、PostgreSQL。
NoSQL(Not Only SQL)泛指非关系型数据库。主要代表:MongoDB、Redis、CouchDB。

关系数据库是以表的形式存储数据的数据库。每个表都有一个模式来记录需要的列和类型。每个模式必须至少有一个主键来唯一标识该记录。换句话说,数据库中没有重复的行。此外,每个表可以使用外键与其他表关联。

关系数据库的一个重要方面是必须将模式中的更改应用于所有记录。这有时会在迁移期间造成破坏,因此十分麻烦。非关系数据库以不同的方式处理问题。它们本质上是无模式的,这意味着可以用不同的模式和不同的嵌套结构保存记录。记录仍然可以有主键,但是模式中的更改是在逐项基础上进行的。


以SQLite为例


我们使用SQLite来举例,首先,导入所需的Python库并创建一个新数据库


import sqlite3

db = sqlite3.connect(':memory:')  # 使用内存数据库
cur = db.cursor()


接下来,创建以下三个表:

  1. 客户:此表包含一个主键以及客户的名字和姓氏。

  2. 物品:此表包含主键,物品名称和物品价格。

  3. 购买的项目:此表将包含订单号,日期和价格。它还将连接到“项目”和“客户”表中的主键。


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


OK,现在每个表中都有一些数据,现在我们用这些数据来回答进行下一步


SQL聚合函数


聚合函数是对结果集执行数学运算的函数。比如AVGCOUNTMINMAX,和SUM。一般来说,还要使用GROUP BYHAVING子句来搭配使用。拿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)]



加速SQL查询


SQL语句的执行速度取决很多因素,但主要受以下几种因素的影响:

  • 连接

  • 聚合

  • 遍历

  • 记录


连接数越多,表的复杂度越高,遍历次数也越多。在涉及多个表的数千条记录上执行多次连接非常麻烦的,因为数据库还需要缓存中间结果,所以真的需要的话就要考虑增加内存大小。

执行速度还受数据库中是否存在索引的影响。索引非常重要,它可以快速搜索表并找到查询中指定列的匹配项。索引以增加插入时间和一些存储为代价对记录进行排序。可以组合多个列以创建单个索引。

调试SQL查询


大多数数据库都包含一个EXPLAIN QUERY PLAN描述数据库执行查询的步骤。对于SQLite,可以通过EXPLAIN QUERY PLANSELECT语句前面添加来启用此功能:


>>> 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=?)')]


该查询尝试列出所有购买商品的名字,商品标题,原始价格和购买价格。而该查询在SQL中应这样写⬇️


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概念或者是大数据中的设计模式。这些就留到以后再聊。


大数据培训班:http://www.baizhiedu.com/bigdata2019




注释:本文内容来自早起python 

上一篇:发数据培训班 | 大数据技术十大核心原理

下一篇:深圳java程序员待遇怎么样,就业机会多吗

相关推荐

关闭

立即申请

www.baizhiedu.com