A Simple Python Example Program for the MySQL Document Store

Last time we looked at a simple example program using the X Devapi and Node.JS. This time lets look at the Python version. Well, not actually the same. This time instead of looking for the Canadian record, the program limits the query to the first two records found.

Besides Python 2.7, you will need to install the Google Protobuf code plus the development release of the Python Connector and a recent version of MySQL 5.7.

The Code

import mysqlx

session = mysqlx.get_session({          # Authenticate to server
  'host':       'localhost',
  'port':       33060,
  'user':       'dstokes',
  'password':   'S3cR3t%'})

# Connect to Schema 'world_x'
schema = session.get_schema('world_x');

# Set collection to 'countryinfo'
collection = schema.get_collection('countryinfo')

# Ask for two records
result = collection.find().limit(2).execute()

docs = result.fetch_all()

# Print requested records
for i, data in enumerate(docs):
   print "{iteration}: {data}".format(iteration = i, data=data)

# Clean up
session.close()

A Note About the 'Collection' versus 'Table'

Take a quick peek at the table used for the example from the world_x database (see last entry for location and details for installation). There are actually two '_id's in the data. There is one that is the generated column you see below and the other is in the JSON column named doc. The collection itself is the JSON data. How to prove? Change the line starting with result in the above example and replace it with result = collection.find('GNP > 100000').limit(2).execute() or something similar with a key from the data.

mysql> desc countryinfo;
+-------+-------------+------+-----+---------+------------------+
| Field | Type        | Null | Key | Default | Extra            |
+-------+-------------+------+-----+---------+------------------+
| doc   | json        | YES  |     | NULL    |                  |
| _id   | varchar(32) | NO   | PRI | NULL    | STORED GENERATED |
+-------+-------------+------+-----+---------+------------------+
2 rows in set (0.00 sec)

Results

$python test.py
0: {"GNP": 828, "Name": "Aruba", "government": {"GovernmentForm": "Nonmetropolitan Territory of The Netherlands", "HeadOfState": "Beatrix"}, "demographics": {"LifeExpectancy": 78.4000015258789, "Population": 103000}, "_id": "ABW", "IndepYear": null, "geography": {"SurfaceArea": 193, "Region": "Caribbean", "Continent": "North America"}}
1: {"GNP": 5976, "Name": "Afghanistan", "government": {"GovernmentForm": "Islamic Emirate", "HeadOfState": "Mohammad Omar"}, "demographics": {"LifeExpectancy": 45.900001525878906, "Population": 22720000}, "_id": "AFG", "IndepYear": 1919, "geography": {"SurfaceArea": 652090, "Region": "Southern and Central Asia", "Continent": "Asia"}}

Next Time

Next time we will build on these two simple example programs.

A Simple Node.JS Example Program for the MySQL Document Store

I have chatting with others who want to use the new X Devapi Document Store features but have not been able to find simple example programs. Finding the bridge between 'Hello World' and something useful can often be discouraging.

Example Code

Here is a very short Node.JS Docstore example program that accesses the 'countryinfo' collection. More on why Node.JS and what software you need below. But for now regard this code to get one specific record from the database.

var mysqlx = require('@mysql/xdevapi');

mysqlx.getSession({             //Auth to server
        host: 'localhost',
        port: '33060',
        dbUser: 'root',
        dbPassword: 'password'
}).then(function (session) {    // use world_x.country.info
     var schema = session.getSchema('world_x');
     var collection = schema.getCollection('countryinfo');

collection                      // Get row for 'CAN'
  .find("$._id == 'CAN'")
  .limit(1)
  .execute(doc => console.log(doc))
  .then(() => console.log("\n\nAll done"));

  session.close();
})

Code Explained

On the first line the X Devapi library is loaded. Line three is authentication to the server followed by getting to the world_x/countryinfo schema. The we find the record for Canada. Is this simpler than "SELECT * FROM countryinfo WHERE _ID = 'CAN'"? Maybe, but I also see where this approach has some facets that need exploiting, er, exploring.

Results

So what does the code do?

:~/xdevn$ node demo.js
{ GNP: 598862,
  _id: 'CAN',
  Name: 'Canada',
  IndepYear: 1867,
  geography: 
   { Region: 'North America',
     Continent: 'North America',
     SurfaceArea: 9970610 },
  government: 
   { HeadOfState: 'Elisabeth II',
     GovernmentForm: 'Constitutional Monarchy, Federation' },
  demographics: { Population: 31147000, LifeExpectancy: 79.4000015258789 } }


All done

Why Node??

I like to try to expand my programming skills every year. MySQL recently released a new driver for the X Devapi Document Store for those who program in Node.JS. JavaScript has become the 'and' in the way many developers describe their jobs. They program in their favorite language 'and' in JavaScript to get their work done. PHP, Python, Rails and the like all have their coders also writing JavaScript. I have not done anything series with JavaScript since they first started trying to call it ECMAscript. At the recent ConFoo I started asking about learning modern JavaScript (and variants) and was pointed at Node.JS. Those of you looking to add Node.JS to your skill set can benefit from the books from Leanpub.com and their Node books (Inexpensive, easy to read, and wonderfully detailed).

The new Connector/Node.JS

First download the Node.JS connector for the MySQL Document Store. Also grab a copy of the world_x database, an update of the World database MySQL has been using for a long time in examples. Follow the directions for installing both of these packages. You will also need to load Google's Protobuffers.

The tutorial is very good but I needed a small stepping stone.Hopefully I can build on this and add the analogs in Python too.