Friday, May 15, 2020

NoSQL Development in MySQL Using Python.

NoSQL Development in MySQL Using Python.

In This Tutorial, I will talk about below topic:-
 Introduction to NoSQL + MySQL =MySQL 8.0.
 What is XdevAPI , Xprotocol , Xplugin ?
 How Application communicate with MySQL using XdevAPI ?
 Installation online and offline.
 How to use XdevAPI to develop application using MySQL as NoSQL databases (Similar to MongoDB , ES ) ?
 Conclusion.


Introduction to NoSQL + MySQL =MySQL 8.0

Whenever we have fast changing data , lots of data it is tedious task for application developers to depend on MySQL DBA/developer to get required schema details , instead MySQL document store has new paradigm shift over traditional SQL model which is schema-less , low maintenance, no need to maintain multiple databases (likewise one database for relational model and another for document model).
Starting 8.0 version, MySQL do have NoSQL features (although MySQL 5.7 has JSON as data type to stored document data), so idea with 8.0 is that same MySQL Server can work as document model (semi-structured data that is in Json format) as well as SQL Model.
Quick doubt- Does NoSQL in MySQL has ACID transaction ?- Answer is YES!.

So question is why Json ?
• JSON is very simple key - value format
• More lightweight format to transport and process
• Based on subset of JavaScript Programming Language
• Universal data structures
• Support evolving in other languages.


What is Document ?
• A data structure that can represent complex information, similar to an Object
• Structure of the data is part of the document, no uniform structure
• JSON (=JavaScript Object Notation)
– Compact, popular and standardized
– Can be represented natively in many languages (JavaScript, Python etc)
• Other popular encoding formats are XML, YAML etc


What is XdevAPI , Xprotocol , Xplugin , Fluent API ?

XdevAPI :- The Document Store is accessible by the X DevAPI which introduces a new, modern, and easy-to-learn way to work with your data. that is implemented by all MySQL Connectors that support the X Protocol, such as MySQL Connector/Python.
Features Includes:-
 Unified interface for handling all kinds of data
 High-level database API to develop MySQL-based applications
 Fluent CRUD, NoSQL and raw SQL APIs
 Non-blocking, asynchronous calls follow common host language patterns.
 Read operations are simple and easy to understand.
Xprotocol:- X Protocol supports both CRUD and SQL operations, authentication via SASL, allows streaming (pipelining) of commands and is extensible on the protocol and the message layer. Clients compatible with X Protocol include MySQL Shell and MySQL 8.0 Connectors.
Xplugin:- X Plugin enables MySQL Server to communicate with clients using X Protocol, which is a prerequisite for using MySQL as a document store. X Plugin is enabled by default in MySQL Server as of MySQL 8.0.
Fluent API :- The X DevAPI operations are structured to be “fluent”. This means it’s possible to call multiple methods to modify an object prior to execution, which enables a fluent query builder and allows more readable, maintainable, and secure code.

More info at:- https://dev.mysql.com/doc/x-devapi-userguide/en/devapi-users-introduction.html

How it Works?




• Applications use DevAPI connectors to write database operations in native code (instead of SQL)
• Connector translates DevAPI operations to X protocol document requests
• X Plugin translates document requests to SQL
• Results sent back to application as JSON documents


Installation
Below Example I will show for using Python as lanhuage to dvelope application with MySQL Document Store.
Requirements
  1. MySQL 8.0
  2. Python 2.7 or >= 3.4
  3. Protobuf C++ (version >= 2.6.0) if installing from source
  4. Python Protobuf (version >= 3.0.0)

Online Installation
shell> pip install mysql-connector-python
Offline Installation                                                 
MySQL Community users can download binaries from below path
Below steps for Installation for Windows OS.
Suppose you have downloaded zip files of connector then execute below commands to install connector
-Go to downloaded path where .zip files are there.
-extract it
-Open Windows CMD and and goto directory where setup.py are available.
C:\python setup.py install



Connecting to MySQL Server using XdevAPI and work with CRUD Operations.
Transcript (below code can we use to performing operations with Document Store)
(Feel Free to try when developing an application using MySQL as NoSQL).
#Importing package to connect Python with MySQL

import mysqlx
Host=input("Enter Host Name where MySQL is Running : ")
user= input ("Enter The MySQL Username : ")
Password= input("Enter The MySQL password : ")
port=input("Enter MySQL Port")
# Connect to server on localhost
try:
    mySession = mysqlx.get_session( {'host': 'localhost', 'port': 33060,'user': user , 'password': Password } )
    print('Connection Established successfully.');
    #Another way to make a connection to database
    #mySession = mysqlx.get_session('mysqlx://root:@localhost:33060/my_schema')
except Exception as err :
    print("Error: {0}".format(err))
    print('Error!, Due to worng Credentials, please try with Valid Credentials!')
#Get All schema
AllSchema_list=mySession.get_schemas()
print(AllSchema_list)
myDb = mySession.get_schema('world_x')

#drop collections
drop_coll_salesobject = myDb.drop_collection('employee')
# Create a new collection 'employee'
create_coll_employeeobject = myDb.create_collection('employee')
create_coll_salesobject = myDb.create_collection('salescity')
#Get collections
get_Sales_coll_object = myDb.get_collection('salescity')
get_coll_employeeobject = myDb.get_collection('employee')
# Insert Single Documents
Insert_EmployeeList={'name': 'Chandan', 'City': 'Mumbai','deptname':'IT'}
Insert_salescityList=[{'City': 'Mumbai','Sales':'10000'},{'City': 'Delhi','Sales':'20000'}]
get_coll_employeeobject.add(Insert_EmployeeList).execute()
get_Sales_coll_object.add(Insert_salescityList).execute()
# Insert Single Documents
Insert_EmployeeList={'name': 'Chandani', 'City': 'Delhi','deptname':'Math'}
get_coll_employeeobject.add(Insert_EmployeeList).execute()
# Insert several documents at once
Insert_EmployeeMultiList=[{'name': 'Raja', 'City': 'Kolkata','deptname':'Sales'},{'name': 'Rani', 'City': 'Delhi','deptname':'Devlopment'},{'name': 'Shayam', 'City': 'Delhi','deptname':'Math'},{'name': 'Mukesh', 'City': 'Mumbai','deptname':'Sales'},{'name': 'Chandu', 'City': 'Delhi','deptname':'Math'}]
get_coll_employeeobject.add(Insert_EmployeeMultiList).execute()
# Find a document
find_doc=get_coll_employeeobject.find().fields().execute()
#Fetch Single Records
print(find_doc.fetch_one())
#Fetch Multiple Records
find_multi_doc=get_coll_employeeobject.find("name='Chandan'").fields(["name","City"]).execute()
print(find_multi_doc.fetch_all())
find_multi_doc2=get_coll_employeeobject.find("City='Delhi'").fields(["name","City"]).execute()
#print The Document
print(find_multi_doc2.fetch_all())
#print(find_multi_doc2[0])
#Count No of Records in a collections
print(get_coll_employeeobject.count())
#Update collections
#The modify() method works by filtering a collection to include only the documents to be modified and then applying the operations that you specify to those documents.
modify_coll_object=get_coll_employeeobject.modify("City='Delhi'").set("name",'Rajesh').execute()
#To remove content from a document, use the modify() and unset() methods. For example, the following query removes the GNP from a document that matches the search condition.
modify_ContentFrom_coll_object=get_coll_employeeobject.modify("City='Delhi'").unset("name",'Rajesh').execute()
#Remove Documents
delete_col_object=get_coll_employeeobject.remove("City='Delhi'").execute()
get_collections=get_coll_employeeobject.find().execute()
#Very The Records in collections.
print(get_collections.fetch_all())
delete_col_object=get_coll_employeeobject.remove("City='Delhi'").execute()
get_collections=get_coll_employeeobject.find().execute()
#Very The Records in collections.
#After performing DELETE , UPDATE , you must execute first find() method and then print so that you will get value.Ex- Below
#not Correct statement
print(get_coll_employeeobject.fetch_all())
#Correct statement
print(get_collections.fetch_all())
#Remove All Documents in a Collection
delete_col_object=get_coll_employeeobject.remove('true').execute()
get_coll_employeeobject.remove('City="Mumbai"').execute()
#Get collections
get_coll_employeeobject = myDb.get_collection('employee')
#Create Index
get_coll_employeeobject.create_index("IX_City",{"fields":[{"field":"City","type": "INT"}],"type":"Index"})
#showindex=mySession.sql('SHOW INDEX FROM world_x.employee')
#print(showindex)
#Drop Index
get_coll_employeeobject.drop_index("IX_City")
#Calling Stored Procedures
##Notes- in order to get results as documents you must specify find().execute
#myDb = mySession.get_schema('world_x')
storedProcedure_result = mySession.sql('CALL stp_DocumentJoinsOperation()').execute()
print(storedProcedure_result)
if storedProcedure_result.has_data():
    row=storedProcedure_result.fetch_one()
    if row:
        print('List of rows available for fetching.')
        while row:
            print(row)
            row=storedProcedure_result.fetch_one()
    else:
        print('empty list of rows')
else:
    print('no row result')
#Joins Two collections
print(get_coll_employeeobject)
get_Sales_coll_object = myDb.get_collection('salescity')
print(get_Sales_coll_object)
#Convert Collections to Table
Employee_asTable = myDb.get_collection("employee")
print(myDb.get_name())
salescity_asTable = myDb.get_collection("salescity")
print(salescity_asTable.get_name())
res=mySession.sql('select * from  world_x.salescity')
print(res.fetch_all())
## JOINS TWO COLLECTIONS

JoinedResults=mySession.sql('SELECT t1.doc->"$.name" as name , t1.doc->"$.City" as City, t2.doc->"$.Sales" as Sales FROM world_x.employee t1 join world_x.salescity t2 on t1.doc->"$.City"=t2.doc->"$.City"').execute()
if JoinedResults.has_data():
    print("Got some data")
    for row in JoinedResults.fetch_all():
        print("name: {0}, City: {1}".format(row["name"], row["City"]))
else:
    print('Got Nothing')
    #for seq in JoinedResults:
        #print(seq[0])
        #print(seq.find().execute())
else:
    print("Got Nothing")
##Writing in SQL Style
current_db=mySession.sql("USE world_x").execute()
print(current_db)
mySession.sql("show databases").execute()
listallrecords=mySession.sql("select * from world_x.city").execute()
for row in listallrecords.fetch_all():
    print("ID: {0}, Name: {1}".format(row["ID"], row["Name"]))
  
#Handling Transaction
mySession.start_transaction()
try:
    Insert_List={'name': 'James', 'City': 'Mumbai','deptname':'IT'}
    get_coll_employeeobject.add(Insert_List)
    # Commit the transaction if everything went well
    mySession.commit()
    print("Data Inserted successfully ")
    # handle warnings
    if result.get_warnings_count() > 0:
        for warning in result.get_warnings():
             print('Type [{0}] (Code {1}): {2}'.format(warning['level'], warning['code'], warning['msg']))
except Exception as err:
    # Rollback the transaction in case of an error
    mySession.rollback()
    print('Bad Records Insert due to error : ');
# Support for raw SQL statements

# Create a table
mySession.sql("CREATE TABLE `world_x`.`person`(name VARCHAR(20), age INT)").execute()
# Insert rows
mySession.sql("INSERT INTO `world_x`.`person` (name, age) VALUES ('Nuno', 42);").execute()
# Fetch rows
result = mySession.sql("SELECT name, age FROM `world_x`.`person`").execute()
for row in result.fetch_all():
    #print("Name: {0}, Age: {1}".format(row["name"], row["age"]))
#close the mySession
mySession.close()

if __name__ == "__main__":
import sys
import mysql.connector



Conclusion

MySQL can be utilize for all kind of workloads, both schema-less and schema is in in same stack.
Developer can be benefited with flexibility ,operations/maintenence is easy because single database can be decided at run time for SQL workloads or NoSQL workloads. data management - single repository reliefs concerns on data synchronization .


Feel free to share your feedback !.
Do share your experiences which NoSQL Database you are using for day to day activities.


Let me know any wants demo , how it works etc, happy to do so.

No comments:

Post a Comment