Python ??????????????????

??????Python?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????

????????????????

???????????????SQL??????????NoSQL??????????????????????????????????????

1.1 SQL???

SQL?????????????????????????????????????

SQL Server

Oracle

MySQL

PostgreSQL

SQL???????

???????????????????

?????????????????

???????????????????

1.2 NoSQL???

NoSQL???????????????????????????????????????

MongoDB

Redis

CouchDB

NoSQL???????

???????????????

????????????????

?????????????????????

1.2 SQLite??

?????????????SQLite??????SQLite?????????????????????

import sqlite3# ???????db = sqlite3.connect(':memory:')cur = db.cursor()# ?????cur.execute('''CREATE TABLE IF NOT EXISTS Customer(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,  FOREIGN KEY (customerid) REFERENCES Customer(id), 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)''')

1.3 SQL????

?????????????????????????? AVG?COUNT?MAX?MIN ? SUM??????????????

# ???????????cur.execute('''SELECT itemid, AVG(price) FROM BoughtItem GROUP BY itemid''')print(cur.fetchall())  # ??: [(1, 10.2), (2, 11.73), (3, 189.995)]# ???????????cur.execute('''SELECT item.title, AVG(boughtitem.price) FROM BoughtItem AS boughtitemINNER 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)]# ??????????cur.execute('''SELECT customer.firstname, SUM(boughtitem.price) FROM BoughtItem AS boughtitemINNER JOIN Customer AS customer ON (customer.id = boughtitem.customerid)GROUP BY customer.firstname''')print(cur.fetchall())  # ??: [('Amy', 180), ('Bob', 222.42), ('Rob', 11.23)]

1.4 ??SQL??

???SQL?????????????????

????????????????

???????????????????

???????????????????

1.5 ??SQL??

??????????????????????? EXPLAIN QUERY PLAN ????

cur.execute('''EXPLAIN QUERY PLANSELECT customer.firstname, item.title, item.price, boughtitem.priceFROM BoughtItem AS boughtitemINNER JOIN Customer AS customer ON (customer.id = boughtitem.customerid)INNER JOIN Item AS item ON (item.id = boughtitem.itemid)''')print(cur.fetchall())

????????????MongoDB

MongoDB????????NoSQL????????????????????MongoDB??????

import pymongo# ??MongoDB???$ pip install pymongo# ??MongoDB???client = pymongo.MongoClient("mongodb://localhost:27017/")# ????????db = client.example_databasecustomers = db.customersitems = 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)

2.2 MongoDB????

??????????

# ???????????for i in db.customers.find({"firstname": "Bob"}):    print(i["boughtitems"])

2.3 MongoDB????

?????????????????????

# ??????????db.customers.create_index([("name", pymongo.DESCENDING)])

????????Redis

Redis???????????????????????????????Redis??????

import redisfrom datetime import timedelta# ??Redis???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), name)        return name