Python for MySql, Sqlite, Mongodb

December, 2020
home

Contents

01. To Get Started

                ----- code  -----
                sum = 0
                mylist = [1,2,3,4,5]
                for x in mylist:
                    sum += x
                print(sum)

                ----- test  ------
                $python
                >>> sum = 0
                >>> mylist = [1,2,3,4,5]
                >>> for x in mylist:
                ---     sum += x
                hit enter
                >>> print(sum)
                15
                exit()
                $
                -----   comments   ---------
                You execute one line at a time, including compilation and execution.
                The variables like sum, mylist are in the memory until you execute exit command.

02. Using Python for MySql

                import mysql.connector

                mydb = mysql.connector.connect(
                    host="my05.winhost.com",
                    user="xxxxxx",
                    password="xxxxxxxx",
                    database="mysql_55668_mydatabase"
                )

                mycursor = mydb.cursor()

                mycursor.execute("SHOW DATABASES")

                for x in mycursor:
                    print(x)

03. Using Python for Sqlite

                using sqlite in python.

                ---  lab 1. test python module sqlite3-------------------------
                * create a folder, myfolder, and make sure pythons' version is 3.
                * create a file, myfile.py, enter one line
                      import sqlite3   
                * enter python myfile.py     
                * if no break, to confirm python module sqlite3 can be accessed.
            
                ---  lab 2.  create a database in a files
                * enter the 2nd line as below:
                    conn = sqlite3.connect('Test.db')
                * If the database does not exist, it will be created.
                * And the database file will be created under folder myfolder.
                * variable conn wil be created for further use.

                ---  lab 3.  create table and insert records
                c = conn.cursor()

                #Droping users table if already exists
                c.execute("DROP TABLE IF EXISTS users")
                print("Table dropped... ")

                #create table
                sql = "CREATE TABLE uSers(id integer pRIMARY KeY,namE varchar, fav inT)"
                c.execute(sql)

                c.execute('''INSERT INTO users(
                            id, name, fav) VALUES(1,'Peter', 369)''')

                c.execute('''INSERT INTO users(
                            ID, NAME, fav) VALUES(2,'Tairo', 123)''')

                c.execute('''INSERT INTO users(
                            Id, name, FAV) VALUES(3,'Emi', 911)''')
              
                #commit the changes to db			
                conn.commit()
                #close the connection
                conn.close()
                print("table users created, insert 3 record") 
            
                ---  lab 4. fetch data -------------------------
                import sqlite3      
                conn = sqlite3.connect('Test.db')
                c = conn.cursor()
                
                c.execute("SELECT * FROM users")
                rows = c.fetchall()

                for row in rows:
                    print(row)	
                
                conn.close()

04.Using Python for Mongodb

--- 4.1 start mongodb server ---

--- 4.2 create a python scrip and run it to confirm the installation of mongodb driver, the connection... ---

--- 4.3 create a database -----

            mydb = myclient["mydatabase"]
                 
                 note 1: The syntax is defined in pymongo documnts.
                 note 2: mydb is a python variable, used in the python code.
                 note 3: mydatabase is used within mongodb.

--- 4.4 create Collection(like table in sql )-----

            mycol = mydb["customers"]

--- 4.5 Mongodb document, insert and read one document-----

The following is the python code for one insert and read
            import pymongo
            myclient = pymongo.MongoClient("mongodb://localhost:27017/")
            mydb = myclient["mydatabase"]
            # cleanup
            mycol = mydb["customers"]
            mycol.drop()  
            # returns true if success. return false if doest not exist.

            mycol = mydb["customers"]
            mydict = { "name": "Peter", "address": "Highway 38" }
            x = mycol.insert_one(mydict)

            x = mycol.find_one()
            print("find_one() return {}", x)

--- 4.6 document value is JSON array ---

            mydict = { "name": "Mary", 
                       "age": 55, 
                       "cars":["Ford", "Honda", "Toyota"]}

--- 4.7 Nested JSON Objects ---

            mydict = { "name": "John", 
                       "age": 55, 
                       "cars":
                        {
                          "car1":"Ford",
                          "car2":"Honda",
                          "car3":"Toyota"
                        }
                      }

----- 4.8 why mongodb -----

----- 4.9 A demo to use mongo db -----

            import pymongo
            myclient = pymongo.MongoClient("mongodb://localhost:27017/")
            mydb = myclient["mydatabase"]  # the [] syntax to create mongo database
           
            mycol = mydb["customers"]    # the [] syntax to create mongo collection
            mycol.drop()     # cleanup
            mycol = mydb["customers"]    # returns true if success. return false if doest not exist.
            
            mylist = [                                      
                        { "name": "Peter", "age": 55},
                        { "name": "Paul",  "address": "25 Blue St, Quincy,MA", 
                                   "cars":["Honda", "Toyota"]} # python list for JSON array
                        { "name": "Tairo", "phones": {                            
                                                        "phone1":"111-111-1111",                                    
                                                        "phone2":"222-222-2222"                   
                                                     }  # python dictionaries for nested JSON object 
                        }                                  
                     ]     
            mycol.insert_many(mylist)

            myquery = {"name": "Paul"}
            docs = mycol.find(myquery)

            for doc in docs:
                print(doc)                            #only one top-level document

                cars = doc["cars"]
                print("cars = {}", cars)              #['Honda', 'Toyota']

                for car in cars:                      # Honda
                    print("car = {}", car)            # Toyota


            myquery2 = {"phones.phone1": "111-111-1111"}    # key in the nested document
            docs2 = mycol.find(myquery2)
            for doc in docs2:
                print(doc)                         #only one top-level document

                phones = doc["phones"]             # key is "phones"
                print("phones = {}", phones)       # value is a python list for dictionary

                print('phone2 = {}', phones["phone2"])

----- 4.10 Others and My Summary on Mongo -----

    BSON
  • Technically, BSON is a serialization format used in MongoDB.
  • It is a superset of JSON with some more data types.
  • BSON is just binary JSON.
  • The binary type is for binary byte array.
  • GridFS is the MongoDB specification
    for storing and retrieving large files
    such as images, audio files, video files, etc.
    My Summary on Mongo
  • You can use sql database or nosql database like mongo for any data storage.
  • Mongodb is more efficient for less constraint in data engine scope.
  • Each document in a collection is isolated wtih others.