Mongodb
SQL - 结构化查询语言 - 关系数据库全都同SQL来操作 MongoDB - MongoDB是一个NoSQL的数据库 - MongoDB是一款文档型数据库 - 数据库指的就是一个存储数据的仓库 数据库可以使我们完成对数据的持久化的操作 - MongoDB数据库中存储的数据的基本单位就是文档， MongoDB中存储的就是文档，所谓文档其实就是一个“JSON” - MongoDB中的“JSON”我们称为BSON，比普通的JSON的功能要更加的强大 - MongoDB数据库使用的是JavaScript进行操作的，在MongoDB含有一个对ES标准实现的引擎， 在MongoDB中所有ES中的语法中都可以使用 - 安装 - 配置环境变量 C:Program FilesMongoDBServer3.2in - 在c盘根目录 - 创建一个文件夹 data - 在data中创建一个文件夹db - MongoDB的基本的指令 - 启动服务器 mongod --dbpath 路径 --port 端口号 - 启动客户端 mongo - 基本概念 数据库（database） 集合（collection） 文档（document） - 在MongoDB中，数据库和集合都不需要手动创建， 当我们创建文档时，如果文档所在的集合或数据库不存在会自动创建数据库和集合 - MongoDB的CRUD的操作 - 基本操作 use 数据库 - 进入指定的数据库 show dbs - 显示所有的数据库 show collections - 显示数据库中所有的集合 db - 显示当前所在的数据库 - 向数据库中插入文档 - db.collection.insert() - insert()可以向集合中插入一个或多个文档 - 例子：向test数据库中的，stus集合中插入一个新的学生对象 {name:"孙悟空",age:18,gender:"男"} db.stus.insert({name:"孙悟空",age:18,gender:"男"} - db.collection.insertOne() - 向集合中插入一个文档 - db.collection.insertMany() - 向集合中插入多个文档 - 查询数据库中的文档 - db.collection.find() - 可以根据指定条件从集合中查询所有符合条件的文档 - 返回的是一个数组 示例： db.stu.find({age:28}) - db.collection.findOne() - 查询第一个符合条件的文档 - 返回的是一个对象 - db.collection.find().count() - 查询符合条件的文档的数量 - 修改数据库中的文档 - db.collection.update() - 可以修改、替换集合中的一个或多个文档 - db.collection.updateOne() - 修改集合中的一个文档 - db.collection.updateMany() - 修改集合中的多个文档 - db.collection.replaceOne() - 替换集合中的一个文档 - 删除集合中的文档 - db.collection.remove() - 删除集合中的一个或多个文档（默认删除多个） - db.collection.deleteOne() - 删除集合中的一个文档 - db.collection.deleteMany() - 删除集合中的多个文档 - 清空一个集合 db.collection.remove({}) - 删除一个集合 db.collection.drop() - 删除一个数据库 db.dropDatabase() 示例： 1查询文档 /* 查询 db.collection.find() - find()用来查询集合中所有符合条件的文档 - find()可以接收一个对象作为条件参数 {} 表示查询集合中所有的文档 {属性:值} 查询属性是指定值的文档 - find()返回的是一个数组 db.collection.findOne() - 用来查询集合中符合条件的第一个文档 - findOne()返回的是一个文档对象 db.collection.find({}).count() - 查询所有结果的数量 pretty() 方法以格式化的方式来显示所有文档。 ---db.col.find().pretty() */ db.stus.find({_id:"hello"}); db.stus.find({age:16 , name:"白骨精"}); db.stus.find({age:28}); db.stus.findOne({age:28}); db.stus.find({}).count(); 2插入文档 /* 向数据库插入文档 db.<collection>.insert() - 向集合中插入一个或多个文档 - 当我们向集合中插入文档时，如果没有给文档指定_id属性，则数据库会自动为文档添加_id 该属性用来作为文档的唯一标识 - _id我们可以自己指定，如果我们指定了数据库就不会在添加了，如果自己指定_id 也必须确保它的唯一性 db.collection.insertOne() - 插入一个文档对象 db.collection.insertMany() - 插入多个文档对象 */ db.stus.insert({name:"猪八戒",age:28,gender:"男"}); db.stus.insert([ {name:"沙和尚",age:38,gender:"男"}, {name:"白骨精",age:16,gender:"女"}, {name:"蜘蛛精",age:14,gender:"女"} ]); db.stus.insert({_id:"hello",name:"猪八戒",age:28,gender:"男"}); db.stus.find(); ObjectId() 3 修改文档 /* 修改 db.collection.update(查询条件,新对象) - update()默认情况下会使用新对象来替换旧的对象 - 如果需要修改指定的属性，而不是替换需要使用“修改操作符”来完成修改 $set 可以用来修改文档中的指定属性 $unset 可以用来删除文档的指定属性 - update()默认只会修改一个 db.collection.updateMany() - 同时修改多个符合条件的文档 db.collection.updateOne() - 修改一个符合条件的文档 db.collection.replaceOne() - 替换一个文档 */ db.stus.find({}); //替换 db.stus.update({name:"沙和尚"},{age:28}); db.stus.update( {"_id" : ObjectId("59c219689410bc1dbecc0709")}, {$set:{ gender:"男", address:"流沙河" }} ) db.stus.update( {"_id" : ObjectId("59c219689410bc1dbecc0709")}, {$unset:{ address:1 }} ) db.stus.updateMany( {"name" : "猪八戒"}, { $set:{ address:"猪老庄" } } ); db.stus.update( {"name" : "猪八戒"}, { $set:{ address:"呵呵呵" } } , { multi:true } ) db.stus.find() 4删除文档 /* db.collection.remove() - 删除一个或多个，可以第二个参数传递一个true，则只会删除一个 - 如果传递一个空对象作为参数，则会删除所有的 db.collection.deleteOne() db.collection.deleteMany() db.collection.drop() 删除集合 db.dropDatabase() 删除数据库 - 一般数据库中的数据都不会删除，所以删除的方法很少调用 一般会在数据中添加一个字段，来表示数据是否被删除 */ db.stu.deleteOne({name:"齐天大圣"}) db.stu.remove({name:"孙悟空"}, {justOne: false}) db.stus.insert([ { name:"zbj", isDel:0 }, { name:"shs", isDel:0 }, { name:"ts", isDel:0 } ]); db.stus.updateOne({name:"ts"},{$set:{isDel:1}}); db.stus.find({isDel:0})
/* 文档之间的关系 一对一（one to one） - 夫妻 (一个丈夫 对应 一个妻子) - 在MongoDB，可以通过内嵌文档的形式来体现出一对一的关系 一对多（one to many）/多对一(many to one) - 父母 - 孩子 用户 - 订单 文章 - 评论 - 也可以通过内嵌文档来映射一对多的关系 多对多(many to many) - 分类 - 商品 老师 - 学生 */ db.wifeAndHusband.insert([ { name:"黄蓉", husband:{ name:"郭靖" } },{ name:"潘金莲", husband:{ name:"武大郎" } } ]); db.wifeAndHusband.find(); //一对多 用户(users) 和 订单(orders) db.users.insert([{ username:"swk" },{ username:"zbj" }]); db.order.insert({ list:["牛肉","漫画"], user_id: ObjectId("59c47e35241d8d36a1d50de0") }); db.users.find() db.order.find() //查找用户swk的订单 var user_id = db.users.findOne({username:"zbj"})._id; db.order.find({user_id:user_id}); //多对多 db.teachers.insert([ {name:"洪七公"}, {name:"黄药师"}, {name:"龟仙人"} ]); db.stus.insert([ { name:"郭靖", tech_ids:[ ObjectId("59c4806d241d8d36a1d50de4"), ObjectId("59c4806d241d8d36a1d50de5") ] },{ name:"孙悟空", tech_ids:[ ObjectId("59c4806d241d8d36a1d50de4"), ObjectId("59c4806d241d8d36a1d50de5"), ObjectId("59c4806d241d8d36a1d50de6") ] } ]) db.teachers.find() db.stus.find()
示例：
//1.进入my_test数据库 use my_test //2.向数据库的user集合中插入一个文档 db.users.insert({ username:"sunwukong" }); //3.查询user集合中的文档 db.users.find(); //4.向数据库的user集合中插入一个文档 db.users.insert({ username:"zhubajie" }); //5.查询数据库user集合中的文档 db.users.find(); //6.统计数据库user集合中的文档数量 db.users.find().count(); //7.查询数据库user集合中username为sunwukong的文档 db.users.find({username:"sunwukong"}); //8.向数据库user集合中的username为sunwukong的文档，添加一个address属性，属性值为huaguoshan db.users.update({username:"sunwukong"},{$set:{address:"huaguoshan"}}); //9.使用{username:"tangseng"} 替换 username 为 zhubajie的文档 db.users.replaceOne({username:"zhubajie"},{username:"tangseng"}); //10.删除username为sunwukong的文档的address属性 db.users.update({username:"sunwukong"},{$unset:{address:1}}); //11.向username为sunwukong的文档中，添加一个hobby:{cities:["beijing","shanghai","shenzhen"] , movies:["sanguo","hero"]} //MongoDB的文档的属性值也可以是一个文档，当一个文档的属性值是一个文档时，我们称这个文档叫做 内嵌文档 db.users.update({username:"sunwukong"},{$set:{hobby:{cities:["beijing","shanghai","shenzhen"] , movies:["sanguo","hero"]}}}); db.users.find(); //12.向username为tangseng的文档中，添加一个hobby:{movies:["A Chinese Odyssey","King of comedy"]} db.users.update({username:"tangseng"},{$set:{hobby:{movies:["A Chinese Odyssey","King of comedy"]}}}) //13.查询喜欢电影hero的文档 //MongoDB支持直接通过内嵌文档的属性进行查询，如果要查询内嵌文档则可以通过.的形式来匹配 //如果要通过内嵌文档来对文档进行查询，此时属性名必须使用引号 db.users.find({'hobby.movies':"hero"}); //14.向tangseng中添加一个新的电影Interstellar //$push 用于向数组中添加一个新的元素 //$addToSet 向数组中添加一个新元素 ， 如果数组中已经存在了该元素，则不会添加 db.users.update({username:"tangseng"},{$push:{"hobby.movies":"Interstellar"}}); db.users.update({username:"tangseng"},{$addToSet:{"hobby.movies":"Interstellar"}}); db.users.find(); //15.删除喜欢beijing的用户 db.users.remove({"hobby.cities":"beijing"}); //16.删除user集合 db.users.remove({}); db.users.drop(); show dbs; //17.向numbers中插入20000条数据 7.2s for(var i=1 ; i<=20000 ; i++){ db.numbers.insert({num:i}); } db.numbers.find() db.numbers.remove({}); //0.4s var arr = []; for(var i=1 ; i<=20000 ; i++){ arr.push({num:i}); } db.numbers.insert(arr);
//查询文档时，默认情况是按照_id的值进行排列（升序） //sort()可以用来指定文档的排序的规则,sort()需要传递一个对象来指定排序规则 1表示升序 -1表示降序 //limit skip sort 可以以任意的顺序进行调用 db.emp.find({}).sort({sal:1,empno:-1}); //在查询时，可以在第二个参数的位置来设置查询结果的 投影（展示所要展示的字段）） db.emp.find({},{ename:1 , _id:0 , sal:1});
//18.查询numbers中num为500的文档 db.numbers.find({num:500}) //19.查询numbers中num大于5000的文档 db.numbers.find({num:{$gt:500}}); db.numbers.find({num:{$eq:500}}); //20.查询numbers中num小于30的文档 db.numbers.find({num:{$lt:30}}); //21.查询numbers中num大于40小于50的文档 db.numbers.find({num:{$gt:40 , $lt:50}}); //22.查询numbers中num大于19996的文档 db.numbers.find({num:{$gt:19996}}); //23.查看numbers集合中的前10条数据 db.numbers.find({num:{$lte:10}}); //limit()设置显示数据的上限 db.numbers.find().limit(10); //在开发时，我们绝对不会执行不带条件的查询 db.numbers.find(); //24.查看numbers集合中的第11条到20条数据 /* 分页 每页显示10条 1-10 0 11-20 10 21-30 20 。。。 skip((页码-1) * 每页显示的条数).limit(每页显示的条数); skip()用于跳过指定数量的数据 MongoDB会自动调整skip和limit的位置 */ db.numbers.find().skip(10).limit(10); //25.查看numbers集合中的第21条到30条数据 db.numbers.find().skip(20).limit(10); db.numbers.find().limit(10).skip(10);
//26.将dept和emp集合导入到数据库中 db.dept.find() db.emp.find() //27.查询工资小于2000的员工 db.emp.find({sal:{$lt:2000}}); //28.查询工资在1000-2000之间的员工 db.emp.find({sal:{$lt:2000 , $gt:1000}}); //29.查询工资小于1000或大于2500的员工 db.emp.find({$or:[{sal:{$lt:1000}} , {sal:{$gt:2500}}]}); //30.查询财务部的所有员工 //(depno) var depno = db.dept.findOne({dname:"财务部"}).deptno; db.emp.find({depno:depno}); //31.查询销售部的所有员工 var depno = db.dept.findOne({dname:"销售部"}).deptno; db.emp.find({depno:depno}); //32.查询所有mgr为7698的所有员工 db.emp.find({mgr:7698}) //33.为所有薪资低于1000的员工增加工资400元 db.emp.updateMany({sal:{$lte:1000}} , {$inc:{sal:400}}); db.emp.find()
pymongo模块
1.连接mongo
# -*- coding:utf-8 -*- import pymongo # 导入pymongo模块 def mongodb_init01(): """连接mongodb""" mongo = pymongo.MongoClient(host='127.0.0.1', port=27017, tz_aware=True) def mongodb_init02(): """连接mongodb""" uri = "mongodb://{}:{}".format('127.0.0.1', 27017) mongo = pymongo.MongoClient(uri, tz_aware=True)
2.操作文档
emp1 = {"_id":1,"name":"武大郎","sex":"male","age":18,"hire_date":"20170301","post":"烧饼检察官","salary":7300.33}
emp2 = {"_id":2,"name":"武松","sex":"male","age":78,"hire_date":"20150302","post":"公务员","salary":71000000.31}
emp3 = {"_id":3,"name":"宋江","sex":"male","age":81,"hire_date":"20130305","post":"公务员","salary":78300}
emp4 = {"_id":4,"name":"林冲","sex":"male","age":73,"hire_date":"20140701","post":"公务员","salary":73500}
emp5 = {"_id":5,"name":"林冲","sex":"male","age":73,"hire_date":"20140701","post":"公务员","salary":73500}
emp6 = {"_id":6,"name":"柴进","sex":"male","age":28,"hire_date":"20121101","post":"公务员","salary":72100}
emp7 = {"_id":7,"name":"卢俊义","sex":"female","age":18,"hire_date":"20110211","post":"公务员","salary":79000}
emp8 = {"_id":8,"name":"高俅","sex":"male","age":18,"hire_date":"19000301","post":"公务员","salary":730000}
emp9 = {"_id":9,"name":"鲁智深","sex":"male","age":48,"hire_date":"20101111","post":"公务员","salary":710000}
emp10 = {"_id":10,"name":"史进","sex":"female","age":48,"hire_date":"20150311","post":"打手","salary":73000.13}
emp11 = {"_id":11,"name":"李逵","sex":"female","age":38,"hire_date":"20101101","post":"打手","salary":72000.35}
emp12 = {"_id":12,"name":"周通","sex":"female","age":18,"hire_date":"20110312","post":"打手","salary":71000.37}
emp13 = {"_id":13,"name":"石秀","sex":"female","age":18,"hire_date":"20160513","post":"打手","salary":73000.29}
emp14 = {"_id":14,"name":"李忠","sex":"female","age":28,"hire_date":"20170127","post":"打手","salary":74000.33}
emp15 = {"_id":15,"name":"吴用","sex":"male","age":28,"hire_date":"20160311","post":"文人","salary":710000.13}
emp16 = {"_id":16,"name":"萧让","sex":"male","age":18,"hire_date":"19970312","post":"文人","salary":720000}
emp17 = {"_id":17,"name":"安道全","sex":"female","age":18,"hire_date":"20130311","post":"文人","salary":719000}
emp18 = {"_id":18,"name":"公孙胜","sex":"male","age":18,"hire_date":"20150411","post":"文人","salary":718000}
emp19 = {"_id":19,"name":"朱贵","sex":"female","age":18,"hire_date":"20140512","post":"文人","salary":717000}
db.emp.insertMany([emp1, emp2, emp3, emp4, emp5, emp6, emp7, emp8, emp9, emp10, emp11, emp12, emp13, emp14, emp15, emp16, emp17, emp18, emp19])
# -*- coding:utf-8 -*- import pymongo # 导入pymongo模块 from bson import ObjectId from pymongo import IndexModel from pymongo.database import Database mongo = pymongo.MongoClient(host='192.168.10.100', port=27017, tz_aware=True) def handler_db(): """操作数据库""" # 创建数据库 # db = Database(name='abc', client=mongo) db = mongo.abc print(db) # 删除数据库 print(mongo.drop_database('abc')) # 获取数据库 for db_name in mongo.list_database_names(): print(db_name) def handler_collection(): """操作集合""" # db = mongo.get_database('abc') # 获取数据库，如果没有会自动创建 db = mongo.abc # 创建一个集合 col = db.create_collection('col') # 创建集合，如果存在就会报错 print('创建的集合：', col) # 获取一个集合 # col = db.get_collection('col') # 获取集合，如果没有会自动创建集合 col = db.col # 获取集合，如果没有会自动创建集合 print('获取的集合：', col) # 获取所有集合 collection_names = db.list_collection_names() # 获取集合名称，返回list for name in collection_names: print('集合名称：', name) collections = db.list_collections() # 返回所有集合对象 for collection in collections: print('获取集合：', collection) # 删除集合 print(db.drop_collection('col')) # 删除集合，返回字典 def handler_index(): """操作索引""" # 创建升序索引 users = mongo.sxt.users r = users.create_index([('name', pymongo.ASCENDING)]) print(r) # 创建降序索引 r = users.create_index([('age', pymongo.DESCENDING)]) print(r) # 创建混合索引 r = users.create_index([('name', pymongo.ASCENDING), ('status', pymongo.DESCENDING)]) print(r) # 创建唯一索引 r = users.create_index([('user_id', pymongo.ASCENDING)], unique=True) print(r) # 删除索引 users.drop_index('name_1') # 如果索引不存在，就会报错 users.drop_indexes() # 删除所有索引 # 创建多个索引 user_id_index = IndexModel([('user_id', pymongo.ASCENDING)], unique=True) name_index = IndexModel([('name', pymongo.DESCENDING)]) r = users.create_indexes([user_id_index, name_index]) # 返回索引名称的list print(r) def handler_document(): """操作文档""" shsxt = Database(name='sxt', client=mongo) # 获取数据库 col = shsxt.get_collection(name='col') # 获取集合 # 添加 doc = {'name': 'xiaohuang', 'age': 18, 'sex': '男'} r = col.insert_one(doc) print(r.acknowledged, r.inserted_id) # acknowledged是否成功，inserted_id主键id doc1 = {'name': '老王', 'age': 20, 'sex': '男'} doc2 = {'name': '老李', 'age': 15, 'sex': '女'} rs = col.insert_many([doc1, doc2], ordered=True) print(rs.acknowledged, rs.inserted_ids) # acknowledged是否成功，inserted_id主键id # 删除 d = col.delete_one({'name': '老王'}) # 删除一条 print(d.acknowledged, d.deleted_count) # acknowledged是否成功，deleted_count删除记录数 d = col.delete_many({'name': '老李'}) # 删除多条 print(d.acknowledged, d.deleted_count) # acknowledged是否成功，deleted_count删除记录数 # 修改 upt = col.update_one(filter={'name': '老李'}, update={'$set': {'name': '小李', 'age': 19}}, upsert=True) print(upt.acknowledged, upt.matched_count, upt.modified_count, upt.raw_result, upt.upserted_id) upt = col.update_many(filter={'name': '老李'}, update={'$set': {'name': '小李', 'age': 19}}, upsert=True) print(upt.acknowledged, upt.matched_count, upt.modified_count, upt.raw_result, upt.upserted_id) # upt = col.update_one(filter={'_id': ObjectId("5b2d041fa3dbd1ad597db57e")}, update={'$set': {'name': '小李'}}) # print(upt.acknowledged, upt.matched_count, upt.modified_count, upt.raw_result, upt.upserted_id) if __name__ == '__main__': handler_document()
# -*- coding:utf-8 -*- from collections import OrderedDict import pymongo client = pymongo.MongoClient(host='192.168.10.100', port=27017, tz_aware=True) db = client.get_database('shsxt') emp = db.get_collection('emp') user = db.get_collection('user') def show(document): for d in document: print(d) def test_and(): """and操作""" ''' db.user.find({"$and":[ {"_id":{"$gte":3,"$lte":4}}, {"age":{"$gte":4}} ]}) ''' r = user.find({"$and": [ {"_id": {"$gte": 3, "$lte": 4}}, {"age": {"$gte": 4}} ]}) # 返回Cursor print(r) show(r) def test_or(): """or操作""" ''' db.user.find({"$or":[ {"_id":{"$lte":1,"$gte":0}}, {"_id":{"$gte":4}}, {"name":"tianqi"} ]}) ''' r = user.find({"$or": [ {"_id": {"$lte": 1, "$gte": 0}}, {"_id": {"$gte": 4}}, {"name": "tianqi"} ]}) show(r) def test_reg(): """正则操作""" # 匹配规则：z开头、n或u结尾，不区分大小写 # db.user.find({'name':/^z.*(n|u)$/i}) r = user.find({"name": {"$regex": '^z.*(n|u)$'}}) show(r) # db.users.find({name: /a/}) r = user.find({'name': {'$regex': 'a'}}) print() print('like %a%') show(r) # db.users.find({name: /^zh/}) // like 'pa%' r = user.find({'name': {'$regex': '^zh'}}) print() print('like zh%') show(r) # db.users.find({name: /qi$/}) // like'%ro' r = user.find({'name': {'$regex': 'qi$'}}) print() print('like %qi') show(r) def test_array(): """数组操作""" ''' #查看所有人的第1个到第3个爱好，第一个{}表示查询条件为所有，第二个是显示条件 db.user.find( {}, { "_id":0, "name":0, "age":0, "addr":0, "hobbies":{"$slice":[0,2]}, } ) ''' r = user.find({}, { "_id": 0, "name": 0, "age": 0, "addr": 0, "hobbies": {"$slice": [0, 2]}, }) show(r) def test_page(page=1, page_size=2): """分页操作""" # 获取结果集 r = user.find().skip((page - 1) * page_size).limit(page_size) show(r) # 获取总记录数 # count = db.user.find().count() count = user.count() print('总记录数：', count) def test_project(): """投影操作""" ''' db.user.find({ "name":/^z.*(n|u)$/i }, { "_id":0, "name":1, "age":1 } ) ''' r = user.find({"name": {"$regex": "^z.*(n|u)$"}}, { "_id": 0, "name": 1, "age": 1 }) show(r) def test_sort(): """排序操作""" ''' # 按姓名正序 db.user.find().sort({"name":1}) # 按年龄倒序 按id正序 db.user.find().sort({"age":-1,'_id':1}) ''' r = user.find({}, {'age': 1}).sort('age', pymongo.ASCENDING) show(r) print() r = user.find({}, {'age': -1, '_id': 1}).sort([('age', pymongo.DESCENDING), ('_id', pymongo.ASCENDING)]) show(r) def test_group(): """分组查询""" # {"$match": {"字段": "条件"}}, 可以使用任何常用查询操作符$gt,$lt,$in等 # select * from db1.emp where post='公务员'; r = db.emp.aggregate([{"$match": {"post": "公务员"}}]) # select * from db1.emp where id > 3 group by post; r = db.emp.aggregate([ {"$match": {"_id": {"$gt": 3}}}, {"$group": {"_id": "$post", 'avg_salary': {"$avg": "$salary"}}} ]) # select * from db1.emp where id > 3 group by post having avg(salary) > 10000; r = db.emp.aggregate([ {"$match": {"_id": {"$gt": 3}}}, {"$group": {"_id": "$post", 'avg_salary': {"$avg": "$salary"}}}, {"$match": {"avg_salary": {"$gt": 10000}}} ]) # {"$group": {"_id": 分组字段, "新的字段名": 聚合操作符}} # 1、将分组字段传给$group函数的_id字段即可 # {"$group": {"_id": "$sex"}} # 按照性别分组 # {"$group": {"_id": "$post"}} # 按照职位分组 # {"$group": {"_id": {"state": "$state", "city": "$city"}}} # 按照多个字段分组，比如按照州市分组 # 2、分组后聚合得结果,类似于sql中聚合函数的聚合操作符：$sum、$avg、$max、$min、$first、$last # 例1：select post,max(salary) from db1.emp group by post; r = db.emp.aggregate([{"$group": {"_id": "$post", "max_salary": {"$max": "$salary"}}}]) # 例2：取每个部门最大薪资与最低薪资 r = db.emp.aggregate( [{"$group": {"_id": "$post", "max_salary": {"$max": "$salary"}, "min_salary": {"$min": "$salary"}}}]) # 例3：如果字段是排序后的，那么$first,$last会很有用,比用$max和$min效率高 r = db.emp.aggregate([{"$group": {"_id": "$post", "first_id": {"$first": "$_id"}}}]) # 例4：求每个部门的总工资 r = db.emp.aggregate([{"$group": {"_id": "$post", "count": {"$sum": "$salary"}}}, {"$sort": {"count": 1}}]) # 例5：求每个部门的人数 r = db.emp.aggregate([{"$group": {"_id": "$post", "count": {"$sum": 1}}}, {"$sort": {"count": 1}}]) # 3、数组操作符 # {"$addToSet": expr} # 不重复 # {"$push": expr} # 重复 # 例：查询岗位名以及各岗位内的员工姓名:select post,group_concat(name) from db1.emp group by post; r = db.emp.aggregate([{"$group": {"_id": "$post", "names": {"$push": "$name"}}}]) r = db.emp.aggregate([{"$group": {"_id": "$post", "names": {"$addToSet": "$name"}}}]) show(r) def test_project02(): """投影操作""" # {"$project": {"要保留的字段名": 1, "要去掉的字段名": 0, "新增的字段名": "表达式"}} # select name,post,(age+1) as new_age from db1.emp; r = db.emp.aggregate([ { "$project": { "name": 1, "post": 1, "new_age": {"$add": ["$age", 1]} } } ]) show(r) def test_sort_limit_skip(): """排序限制跳过""" # 例1、取平均工资最高的前两个部门 r = db.emp.aggregate([ { "$group": {"_id": "$post", "平均工资": {"$avg": "$salary"}} }, { "$sort": {"平均工资": -1} }, { "$limit": 2 } ]) # 例2、取平均工资最高的第二个部门 r = db.emp.aggregate([ { "$group": {"_id": "$post", "平均工资": {"$avg": "$salary"}} }, { "$sort": {"平均工资": -1} }, { "$limit": 2 }, { "$skip": 1 } ]) show(r) def test_sample(): """随机获取""" # 随机获取3个文档 r = db.emp.aggregate([ {"$sample": {"size": 3}} ]) show(r) def test_str(): """字符串操作""" # 截取字符串 r = db.emp.aggregate([ { "$project": { "_id": 0, "str": {"$substr": ["$sex", 0, 2]} } } ]) # 拼接 r = db.emp.aggregate([ { "$project": { "name": 1, "post": 1, "name_sex": {"$concat": ["$name", "测试拼接", "$sex"]} } } ]) # 将性别的英文转为大写 r = db.emp.aggregate([{"$project": {"sex": {"$toUpper": "$sex"}}}]) show(r) if __name__ == '__main__': test_str()
3 示例：scrapy+mongo(爬取bibi排行榜相关数据)
bibi_ranke.py
import scrapy from bibi.items import BibiItem class BibiRankeSpider(scrapy.Spider): name = 'bibi_ranke' # allowed_domains = ['https://www.bilibili.com'] start_urls = ['https://www.bilibili.com/v/popular/rank/all'] def parse(self, response): titles =response.xpath('//div[@class="info"]//a[@class="title"]/text()').extract() play_nums =response.xpath('//div[@class="detail"]/span[@class="data-box"][1]/text()').extract() up_names =response.xpath("//div[@class='detail']/a//text()").extract() scores =response.xpath('//div[@class="info"]//div[@class="pts"]/div/text()').extract() play_nums=[i.strip() for i in play_nums] up_names=[i.strip() for i in up_names] # print(titles,play_nums,up_names,scores) # 打包 for title,play_num,up_name,score in zip(titles,play_nums,up_names,scores): item=BibiItem() item['play_num']=play_num item['title']=title item['up_name']=up_name item['score']=score yield item
item.py
# Define here the models for your scraped items # # See documentation in: # https://docs.scrapy.org/en/latest/topics/items.html import scrapy class BibiItem(scrapy.Item): _id =scrapy.Field() # define the fields for your item here like: title = scrapy.Field() play_num = scrapy.Field() up_name = scrapy.Field() score = scrapy.Field()
pipelines.py
# Define your item pipelines here # # Don't forget to add your pipeline to the ITEM_PIPELINES setting # See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html # useful for handling different item types with a single interface from itemadapter import ItemAdapter import pymongo class BibiPipeline: # 爬虫开始时连接数据库 def open_spider(self,spider): self.client =pymongo.MongoClient() def process_item(self, item, spider): # 存入mongodb数据库中 self.client.bilibli.rank.insert_one(item) return item # 爬虫结束关闭数据库 def close_spider(self,spider): self.client.close()
# Scrapy settings for bibi project # # For simplicity, this file contains only settings considered important or # commonly used. You can find more settings consulting the documentation: # # https://docs.scrapy.org/en/latest/topics/settings.html # https://docs.scrapy.org/en/latest/topics/downloader-middleware.html # https://docs.scrapy.org/en/latest/topics/spider-middleware.html BOT_NAME = 'bibi' SPIDER_MODULES = ['bibi.spiders'] NEWSPIDER_MODULE = 'bibi.spiders' from fake_useragent import UserAgent # Crawl responsibly by identifying yourself (and your website) on the user-agent #USER_AGENT = 'bibi (+http://www.yourdomain.com)' USER_AGENT = UserAgent().random # 随机生成UA伪装 # Obey robots.txt rules ROBOTSTXT_OBEY = False # Configure maximum concurrent requests performed by Scrapy (default: 16) #CONCURRENT_REQUESTS = 32 # Configure a delay for requests for the same website (default: 0) # See https://docs.scrapy.org/en/latest/topics/settings.html#download-delay # See also autothrottle settings and docs DOWNLOAD_DELAY = 1 # The download delay setting will honor only one of: #CONCURRENT_REQUESTS_PER_DOMAIN = 16 #CONCURRENT_REQUESTS_PER_IP = 16 # Disable cookies (enabled by default) #COOKIES_ENABLED = False # Disable Telnet Console (enabled by default) #TELNETCONSOLE_ENABLED = False # Override the default request headers: #DEFAULT_REQUEST_HEADERS = { # 'Accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8', # 'Accept-Language': 'en', #} # Enable or disable spider middlewares # See https://docs.scrapy.org/en/latest/topics/spider-middleware.html #SPIDER_MIDDLEWARES = { # 'bibi.middlewares.BibiSpiderMiddleware': 543, #} # Enable or disable downloader middlewares # See https://docs.scrapy.org/en/latest/topics/downloader-middleware.html #DOWNLOADER_MIDDLEWARES = { # 'bibi.middlewares.BibiDownloaderMiddleware': 543, #} # Enable or disable extensions # See https://docs.scrapy.org/en/latest/topics/extensions.html #EXTENSIONS = { # 'scrapy.extensions.telnet.TelnetConsole': None, #} # Configure item pipelines # See https://docs.scrapy.org/en/latest/topics/item-pipeline.html ITEM_PIPELINES = { 'bibi.pipelines.BibiPipeline': 300, } # Enable and configure the AutoThrottle extension (disabled by default) # See https://docs.scrapy.org/en/latest/topics/autothrottle.html #AUTOTHROTTLE_ENABLED = True # The initial download delay #AUTOTHROTTLE_START_DELAY = 5 # The maximum download delay to be set in case of high latencies #AUTOTHROTTLE_MAX_DELAY = 60 # The average number of requests Scrapy should be sending in parallel to # each remote server #AUTOTHROTTLE_TARGET_CONCURRENCY = 1.0 # Enable showing throttling stats for every response received: #AUTOTHROTTLE_DEBUG = False # Enable and configure HTTP caching (disabled by default) # See https://docs.scrapy.org/en/latest/topics/downloader-middleware.html#httpcache-middleware-settings #HTTPCACHE_ENABLED = True #HTTPCACHE_EXPIRATION_SECS = 0 #HTTPCACHE_DIR = 'httpcache' #HTTPCACHE_IGNORE_HTTP_CODES = [] #HTTPCACHE_STORAGE = 'scrapy.extensions.httpcache.FilesystemCacheStorage'