MongoDB Database

Common RDB(Relational Database): PostgreSQL, MySQL, Orcal, MS SQL Server and SQLite.

database –collection – document– field – index.

Install in LInux

mongoDB _id

_id 类型为 ObjectId, 是一个 12 字节 的 BSON 类型字符串，按照字节顺序，依次代表：

4字节：时间戳

3字节：机器 ID

2字节：进程 ID

3字节：计数器

CRUD

CRUD operations create, read, update, and create documents.

Cteate

• db.collection.insertOne() New in version 3.2
• db.collection.insertMany() New in version 3.2

db.collection.insertOne(
	{
		"name" : "aaa",
		"age: 26
	}
)

db.collection.insertMany(
	[
		{ ... },
		{ ... }
	]
)

Read

Query a collection for document

db.collections.find(
	{ age: { $gt: 18 } }
).limit(5)

Update

db.inventory.updateOne(
   { item: "paper" },
   {
     $set: { "size.uom": "cm", status: "P" },
     $currentdate: { lastModified: true }
   }
)
# created filed: 
# "lastModified" : ISODate("2018-10-26T08:59:26.038+0000")

• $set to update the value
• $currentDate operator to update the value of the lastModified field to the current date, If lastModified field does not exist, $currentDate will create the field.

Update many: to update document on inventory where qty is less than 50:

db.inventory.updateMany(
   { "qty": { $lt: 50 } },
   {
	   #same as updateOne()
   }
)

Replace:

db.inventory.replaceOne(
	# or updateOne() updateMany()
   { item: "paperpaper" },
   { item: "paper", instock: [ { warehouse: "A", qty: 60 }, { warehouse: "B", qty: 40 } ] },
   { upsert: true }
)

If there are matching documents, then the upsert operation modifies or replaces the matching document or documents.

Delate

MongoDb provides the followinng methods to delete documents of a collection:

• db.collection.deleteOne()
• db.collection.deleteMany()
• db.collection.remove()

Process in terminal:

./mongo.exe
> show dbs
> use [db name] #create db
> db #see db
> db.createCollection(name, options) #create collections
> show collections
> db.colname.insert({"xx", "xx"})
> db.collection_name.find()
> db.collection_name.find().pretty() #show in formatted

batch import .json file:

mongoimport --db users --collection contacts --file xx.json

Restore

mongorestore -h <hostname><:port> -d dbname <path>

mongodump and mongorestore

Flask-PyMongo

Install: pip install flask_pymongo

in nginxig.py:

class Config:
    MONGO_URI = "mongodb://localhost:27017/myDatabase"

in app/___init__.py:

from flask_pymongo import PyMongo
from config import config
mongo = PyMongo()
mono.init_app(app)

in views.py:

from app import mongo
@main.route('/', methods=['GET', 'POST'])
def index():
	data = mongo.db.mycol.find()
	# mycol is the name of collections

Python SQL

Take SQLite3 for example:

cur.execute("CREATE TABLE demo(num int, str varchar(20));")，DB-API规范，创建cur游标对象用于执行SQL命令。Source Code

conn = sqlite3.connect('test.db')
cur = conn.cursor()
cur.execute("INSERT INTO demo VALUES (%d, '%s')" % (1, 'aaa'))

SQLite3

Create table

format the table:

sqlite> .header on
sqlite> .mode column
sqlite> SELECT * FROM COMPANY;

update:

sqlite> UPDATE COMPANY SET ADDRESS = 'Texas' WHERE ID = 6;
 update users set confirmed = 1 where id=4;

register:

from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()