Saturday, May 16, 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:-

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

Below Example I will show for using Python as lanhuage to dvelope application with MySQL Document Store.
  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 are available.
C:\python 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
    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
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'}]
# Insert Single Documents
Insert_EmployeeList={'name': 'Chandani', 'City': 'Delhi','deptname':'Math'}
# 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'}]
# Find a document
#Fetch Single Records
#Fetch Multiple Records
#print The Document
#Count No of Records in a collections
#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.
#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.
#Remove Documents
#Very The Records in collections.
#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
#Correct statement
#Remove All Documents in a Collection
#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')
#Drop Index
#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()
if storedProcedure_result.has_data():
    if row:
        print('List of rows available for fetching.')
        while row:
        print('empty list of rows')
    print('no row result')
#Joins Two collections
get_Sales_coll_object = myDb.get_collection('salescity')
#Convert Collections to Table
Employee_asTable = myDb.get_collection("employee")
salescity_asTable = myDb.get_collection("salescity")
res=mySession.sql('select * from  world_x.salescity')

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"]))
    print('Got Nothing')
    #for seq in JoinedResults:
    print("Got Nothing")
##Writing in SQL Style
current_db=mySession.sql("USE world_x").execute()
mySession.sql("show databases").execute()
listallrecords=mySession.sql("select * from").execute()
for row in listallrecords.fetch_all():
    print("ID: {0}, Name: {1}".format(row["ID"], row["Name"]))
#Handling Transaction
    Insert_List={'name': 'James', 'City': 'Mumbai','deptname':'IT'}
    # Commit the transaction if everything went well
    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
    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

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


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.


  1. Great Content. It will useful for knowledge seekers. Keep sharing your knowledge through this kind of article.
    React Js Framework
    React Js Development

  2. At MySQL Designers our experienced team of database professionals and software developers are revel in meeting the technical requirements of a wide range projects for our customers. Below is a very high level list of the work we enjoy undertaking just to give you a flavour of what makes us tick:
    Visit at : mysql database manager

  3. This Content is simply amazing, and helpful for students and business people. Gathered lots of information and waiting to see more updates.
    Use of Salesforce
    Why Salesforce

  4. Thanks for this blog keep sharing your thoughts like this...
    Applications of Matlab
    What is Matlab?

  5. This is really too useful and have more ideas and keep sharing many techniques. Eagerly waiting for your new blog keep doing more.
    DevOps Course in Chennai
    DevOps Online Course
    DevOps Course in Coimbatore