PROGRAMMING EXAMPLES

M ONGODB INESRT Insert Into Collection A document in MongoDB is the same as a record in SQL databases. To insert a record, or document as it is called in MongoDB, into a collection, we use the insert_one() method. The first parameter of the insert_one() method is a dictionary containing the name(s) and value(s) of each field in the document you want to insert. Insert a record in the "customers" collection: import pymongo myclient = pymongo.MongoClient("mongodb://localhost:27017/") mydb = myclient["mydatabase"] mycol = mydb["customers"] mydict = { "name": "John", "address": "Highway 37" } x = mycol.insert_one(mydict) #The insert_one() method returns a InsertOneResult object print(x.inserted_id) #inserted_id, that holds the id of the inserted document. Insert Multiple Documents To insert multiple documents into a collection in MongoDB, we use the insert_many() method. The first param

M ONGODB CREATE DATABASE & COLLECTION Creating a Database To create a database in MongoDB, start by creating a MongoClient object, then specify a connection URL with the correct ip address and the name of the database you want to create. MongoDB will create the database if it does not exist, and make a connection to it. import pymongo myclient = pymongo.MongoClient("mongodb://localhost:27017/") mydb = myclient["mydatabase"] Important: In MongoDB, a database is not created until it gets content! MongoDB waits until you have created a collection (table), with at least one document (record) before it actually creates the database (and collection ). Check if Database Exists Remember: In MongoDB, a database is not created until it gets content, so if this is your first time creating a database, you should complete the next two chapters (create collection and create document) before you check if the database exists! You can check if a database e

M ONGODB MongoDB MongoDB stores data in JSON-like documents, which makes the database very flexible and scalable. To be able to experiment with the code examples in this tutorial, you will need access to a MongoDB database. You can download a free MongoDB database at https://www.mongodb.com. Or get started right away with a MongoDB cloud service at https://www.mongodb.com/cloud/atlas. PyMongo Python needs a MongoDB driver to access the MongoDB database. In this tutorial we will use the MongoDB driver "PyMongo". We recommend that you use PIP to install "PyMongo". PIP is most likely already installed in your Python environment. Navigate your command line to the location of PIP, and type the following: Test PyMongo To test if the installation was successful, or if you already have "pymongo" installed, create a Python page with the following content: import pymongo If the above code was executed with no errors, "pymongo" is

M YSQL JOIN Join Two or More Tables You can combine rows from two or more tables, based on a related column between them, by using a JOIN statement. Consider you have a "users" table and a "products" table: users { id: 1, name: 'John', fav: 154}, { id: 2, name: 'Peter', fav: 154}, { id: 3, name: 'Amy', fav: 155}, { id: 4, name: 'Hannah', fav:}, { id: 5, name: 'Michael', fav:} products { id: 154, name: 'Chocolate Heaven' }, { id: 155, name: 'Tasty Lemons' }, { id: 156, name: 'Vanilla Dreams' } These two tables can be combined by using users' fav field and products' id field. Join users and products to see the name of the users favorite product: import mysql.connector mydb = mysql.connector.connect(   host="localhost",   user="yourusername",   password="yourpassword",   database="mydatabase" ) mycursor = mydb.c

M YSQL LIMIT Limit the Result You can limit the number of records returned from the query, by using the "LIMIT" statement: Select the 5 first records in the "customers" table: import mysql.connector mydb = mysql.connector.connect(   host="localhost",   user="yourusername",   password="yourpassword",   database="mydatabase" ) mycursor = mydb.cursor() mycursor.execute("SELECT * FROM customers LIMIT 5") myresult = mycursor.fetchall() for x in myresult:   print(x) (1, 'John', 'Highway 21') (2, 'Peter', 'Lowstreet 27') (3, 'Amy', 'Apple st 652') (4, 'Hannah', 'Mountain 21') (5, 'Michael', 'Valley 345') Start From Another Position If you want to return five records, starting from the third record, you can use the "OFFSET" keyword: Start from position 3, and return 5 records: import mysql.connect

M YSQL UPDATE TABLE Update Table You can update existing records in a table by using the "UPDATE" statement: Overwrite the address column from "Valley 345" to "Canyon 123": import mysql.connector mydb = mysql.connector.connect( host="localhost", user="yourusername", password="yourpassword", database="mydatabase" ) mycursor = mydb.cursor() sql = "UPDATE customers SET address = 'Canyon 123' WHERE address = 'Valley 345'" mycursor.execute(sql) mydb.commit() print(mycursor.rowcount, "record(s) affected") 1 record(s) affected Important!: Notice the statement: mydb.commit(). It is required to make the changes, otherwise no changes are made to the table. Notice the WHERE clause in the UPDATE syntax: The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated! Prevent SQL Injec

M YSQL DROP TABLE Delete a Table You can delete an existing table by using the "DROP TABLE" statement: import mysql.connector mydb = mysql.connector.connect( host="localhost", user="yourusername", password="yourpassword", database="mydatabase" ) mycursor = mydb.cursor() sql = "DROP TABLE customers" mycursor.execute(sql) Drop Only if Exist If the table you want to delete is already deleted, or for any other reason does not exist, you can use the IF EXISTS keyword to avoid getting an error. import mysql.connector mydb = mysql.connector.connect( host="localhost", user="yourusername", password="yourpassword", database="mydatabase" ) mycursor = mydb.cursor() sql = "DROP TABLE IF EXISTS customers" mycursor.execute(sql)