4.8 Databases

Store and retrieve some data to user，簡介

Columns of database說明

ID column 最重要

  • 一般是 integer 或 string

Vote column

  • number of votes on this link

User column

  • 還會有專門的 user table

Date column

  • 此例使用 date type，不一定都支援

Lambda

證明 querying a database by writing Python code is very hard，又慢又容易錯，又蠢（說明），幸好有 database，處理大量資料的好幫手

Types of Databases

SQLite - 較省容量，lightweight

Oracle 買下 MySQL 很久，但還是讓他免費唷

這些資料庫都 work with tables and use a language SQL to manipulate

其他

  • Google App Engine Datastore

  • Dynamo by Amazon

  • NoSQL

  • Mongo

  • Couch

  • 改善 MySQL 他們的缺點

Data type called the "named tuple"

  • This is very similar to a Python Class in that it lets you refer to the properties of something by their name. You can read more about named tuples inthis thread on stackoverflow

Here is more

information

on named tuple used in this exercise.

SQL

  • A language for expressing queries

分成三部分：

Select Clause

  • * 代表全部，可以換成 url,title

From Clause

  • where we fetch data from

Where Clause

  • They are constraints which rows from our table to return

  • 他可能很長

Import SQLite3 in Python

  • 說明

  • c = cursor ( a position in the database)

  • def query()

  • c=db.excute("select * from links")

  • result = c.fetchall()

  • return result

  • # load all of the data out of database into the list of results

補充: fetchone() ，returns the first result

Iterate our cursor in tuple into link object

  • We get back the result one by one from database, we get them back as a tuple, not as our link object

  • Tuple is a reserved word

  • 但我們實際要的是 link objects

  • 做法

  • 說明

  • link = Link(*link_tuple)

  • 是一個 python 的語法

  • passing all of the parameters in this tuple as arguments to a link object

Creating this table

Where Clause - Advanced

Order by Clause

  • It says to sort the results(排序)

  • 說明

  • 原本的排序是 ascending order

  • ASC = ascending

  • DESC = descending

  • where clause is not required

Join Query

  • you can use that involves multiple tables

  • 介紹

  • 另有隱情，為何我們會不常用？

Indexes

  • 不用用眼睛搜尋

  • 就像書的目錄

  • 增快 queries 的速度

  • hashtable 就是一種例子

  • python 中可用 dict 表示

  • 介紹

輸入 link_id 回傳 object

輸入 link_id，輸出變成 dict

利用這個 dict 直接去抓你想要的資料

例子

  • def link_by_id(link_id):

  • return link_index[link_id]

建議第二行使用 link_index.get 這樣沒抓到資料會出現 none

創一個增加 link 的方程式

第二點： When we're inserting new round or table, we also update the indexes, that takes time

實際上indexes 如何運用呢？

  • count is a SQL command for counting the rows in a table

  • explain analyze 語法

  • explain what it did

  • create an index id on the id field

  • 語法： create index hotel_id on hotels(id);

  • 第一塊：name of our index

  • 第二塊：on 什麼 table,這個 case 是 hotel table

  • 第三塊括號內: which field we are going to use

  • drop index hotel_id;

Indexed for sorting

Hashtable

  • 沒有 sorting

  • just a mapping key to values

  • 增加新 element ，新加入的位置沒有規律性，可能 positio[0]才是新的

  • Looking up particular key , 檢查時間:constant time

Tree

  • Basic Data Structure

  • Sorted

  • 缺點檢查比 hashtable 慢，檢查 function: logn，n 越大數量越大

案例： Reddit

ACID

  • transaction

  • a group of statements, group multiple commands

  • 觀念說明

Google app engine data store

  • Instead of having a table for links, having a entity called link

GAE Entities

  • The column are not fixed, google 表示 whatever column you want

  • Table 是 fixed

  • Same type 的 entity 不一定有 same column

  • 開發較簡單

  • All have an ID

  • entities have the notion of parents and ancestors

results matching ""

    No results matching ""