Python and the MySQL Document Store

The MySQL Document Store and X Devapi have a lot of very interesting features but right now my programming language of choice, PHP, is not yet supported. My Python is rusty and learning Node.JS is progressing. But the ability to search data from a database without knowing Structured Query Language (SQL) is going to appeal to many.

Example One

import mysqlx
import string

session = mysqlx.get_session({
'host': 'localhost',
'port': 33060,
'user': 'dstokes',
'password': 'Hell0Dave!'})

schema = session.get_schema('world_x');
collection = schema.get_collection('countryinfo')

print "Find three records***\n"
result = collection.find().limit(3).execute()
docs = result.fetch_all()

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

print "Find USA***\n"
result = collection.find('_id = "USA"').execute()
row = result.fetch_all()

for i, data in enumerate(row):
    print "{iteration}: {data}".format(iteration = i, data=data)

session.close()

Is result = collection.find().limit(3).execute() simpler than SELECT * FROM countryinfo LIMIT 3;? Maybe not. But the second query result = collection.find('_id = "USA"').execute() is more likely a better example. Now SELECT * FROM countryinfo WHERE _ID = 'USA'; is where you can start to see the value of the document store.

Variables can be bound to queries:

country = "Mexico"

result = collection.find('Name = :param').bind('param', country).execute()

Or an expanded version:

for country in ("Mexico", "Canada", "Brazil"):
    result = collection.find('Name = :param').bind('param', country).execute()

This is closer to what most programming languages teach today. SQL is a descriptive language. The differences between object-oriented/procedural languages and descriptive are subtle. But the subtlety escapes novices (and is often ignored by those at higher skill levels.

Of course it would be more efficient to make one dive into the database with a
SELECT * FROM countryinfo WHERE Name = 'Mexico' OR Name = 'Canada' OR Name = 'Brazil'
or
SELECT FROM countryinfo WHERE Name in ('Mexico', 'Canada', 'Brazil')

But those are not going to work.

Remember the data we are peeking at is in a JSON column.

So we would use
SELECT * FROM countryinfo where doc->"$.Name" = "Mexico" OR doc->"$.Name" = "Canada" OR doc->"$.Name" = "Brazil"
or
select * from countryinfo WHERE doc->"$.Name" IN ("Canada","Mexico","Brazil")
(note use of the -> shorthand in place of JSON_EXPLAIN)

That gets much more complicated. And complication can introduce errors. Novices should have few problems if they keep working in their chosen language without have to make a context switch mentally to write some SQL. And they days very few are being taught SQL.