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 MySQL 8.0 Python 2.7 or >= 3.4 Protobuf C++ (version >= 2.6.0) if installing from source 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 https://dev.mysql.com/downloads/connector/python/ For MySQL Enterprise Binaries and connector can be Downloaded from below path https://edelivery.oracle.com/osdc/faces/Home.jspx;jsessionid=TKuRFjYJUf38xKriqMm-Fnr0jaJPVRhCuClNoIZqzszdZW_5MXr8!1656027531 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.