Python and Compose - Scylla and Compose for MySQL

Published

Hello, and welcome back to the final Python leg of the Compose Grand Tour. The purpose of the tour is to provide the best examples of connecting to all of the databases that are available on Compose, in a wide range of languages. In this second part, we cover Pythonic connections to Scylla and MySQL for Compose.

The Grand Tour is built around an example web application where the frontend is written in Javascript, and the backend in the featured language (here: Python). A version of this code is available in a database-less version of the application to make the database connections in the subsequent examples stand out.

Each of those examples features a different Compose database handling the data persistence. Where possible, the examples incorporate good practices for secure connections such as using TLS/SSL and using certificates for server verification.

This is the third and final installment of the Python Grand Tour, so here's a short summary of what is going on. A more detailed overview of the application is in part 1 of the Tour.

The example is a web application where users can enter in word/definition pairs and see previously entered pairs. The server framework in Python uses Flask. All of the other information to get connected to the Compose databases are provided in the Connection Info of a deployment's overview page.

The first part of this series covered connecting in Python to MongoDB, PostgreSQL, and Elasticsearch on Compose. The second installment covered connecting to Redis, RethinkDB, and RabbitMQ running on Compose.

It's time now to move on to the last of the databases - Scylla and Compose for MySQL.

Scylla

The Code: The full example code is in the compose-grandtour/python/example-scylla github repository.

The Driver: Since Scylla is a drop-in replacement for Cassandra, the Cassandra Python driver can be used as the Scylla Python driver too.

The Certificate: Scylla deployments on Compose use TLS/SSL verified with a Let's Encrypt certificate. However, Python's TLS/SSL handling doesn't look to a system's CA certificates when it needs to verify a connection. To allow this verification to happen, Compose provides a copy of the Let's Encrypt certificate chain in the documentation. There are also instructions for making your own copy of the certificate chain yourself for additional integrity. Save a local copy of the certificate chain and use the path to this certificate as the value of the environment variable PATH_TO_SCYLLA_CERT.

The Connection: Scylla deployments provide a portal and a connection string to every member of the cluster. The driver accepts cluster connections in an array, and will automatically handle connections even if one of the portals is down. So to take advantage of this, add all three of the connection strings, comma separated, to an environment variable, COMPOSE_SCYLLA_URL. The urlparse function is, as in previous examples, going to handle the connection URLs we pull from the environment.

Post-parsing, there are three major steps to providing the driver all the pieces it needs to connect.

    ssl_options = {
        'ca_certs': path_to_scylla_cert,
        'ssl_version': ssl.PROTOCOL_TLSv1
    }
    auth_provider = PlainTextAuthProvider(
    username=cstr1.username,
    password=cstr1.password)
# connection strings, parsing, and certificate path
compose_scylla_url = os.environ['COMPOSE_SCYLLA_URL']  
url1,url2,url3 = compose_scylla_url.split(",")

cstr1 = urlparse(url1)  
cstr2 = urlparse(url2)  
cstr3 = urlparse(url3)

path_to_scylla_cert = os.environ['PATH_TO_SCYLLA_CERT']

# Optional dict with absolute path to CA certificate and the default Cassandra protocol ssl version.
ssl_options = {  
    'ca_certs': path_to_scylla_cert,
    'ssl_version': ssl.PROTOCOL_TLSv1_2
}

# Creates class object that supplies username/password in plain-text.
auth_provider = PlainTextAuthProvider(  
    username=cstr1.username,
    password=cstr1.password)

# Handles connection setup and information
cluster = Cluster(  
    contact_points = [cstr1.hostname,cstr2.hostname,cstr3.hostname],
    port = cstr1.port,
    auth_provider = auth_provider,
    ssl_options=ssl_options)

# Starts session
session = cluster.connect()  

In this example, we have some additional steps to set up the keyspace and table for this example (and for other grand tour Scylla examples in other languages). It creates the keyspace 'grand_tour' if it isn't already there, switches to use it, and checks for a 'words' table. If the 'words table is absent it will also create it.

# Creates keyspace on first run
session.execute("""CREATE  KEYSPACE IF NOT EXISTS grand_tour  
   WITH REPLICATION = { 
       'class' : 'SimpleStrategy', 'replication_factor' : 3 } """)

# Use keyspace
session.execute("""USE grand_tour""")

#Check for table and create on first run
session.execute("""CREATE TABLE IF NOT EXISTS words (  
    id UUID primary key,
    word text,
    definition text) """)

The Read: To retrieve the existing entries in the table, we use the CQL SELECT statement for the word and definition columns. The results then have to be JSONified for page display. The results come back to us as cluster results object, but we can iterate through each row of the results and pull out the "words" into one list and the "definitions" into a second list. Then we zip the two lists together and format each pair into an object that is passed to json.dumps and returned for display on the page.

# queries and formats results for display on page
def display_find():  
    # query for all the words in the table
    get_words = session.execute("""SELECT word, definition FROM words""")

    # creates two lists for the words, definitions
    word_list = []
    definition_list =[]
    for entry in get_words:
        word_list.append(entry[0])
        definition_list.append(entry[1])

    # create an object containing formatted data from the words and definition lists zipped together
    results_list = [{'word': word, 'definition': definition} 
        for word, definition in zip(word_list, definition_list)]

    # returns the object as JSON for display on page        
    return json.dumps(results_list)

The Write: To store a new word/definition pair into the database, we use a prepared CQL INSERT query. Notice that for the table, there is a primary key uuid for each new word. When we go to execute the command, we do so while supplying a uuid generated with Python's uuid library, and the word/definition pulled from the field's on the web page.

# triggers on hitting the 'Add' button; inserts word/definition into table
def handle_words():  
    # INSERT statement
    new_word = session.prepare("""INSERT INTO words(id, word, definition) VALUES(?, ?, ?)""")
    # creates new entry, executes INSERT
    session.execute(new_word, [uuid.uuid4(), request.form['word'], request.form['definition']])
    return ('', 204)

And we return a 204 status as there's nothing to return to the client.

MySQL

The Code: The full example code is in the compose-grandtour/python/example-mysql github repository.

The Driver: The driver this example uses is MySQL Connector/Python. It can be installed via pip: pip3 install mysql-connector-python.

The Certificate: Compose for MySQL deployments use TLS/SSL with a self-signed certificate. Grab a copy of the certificate from the Overview page of the deployment and save a copy locally. Use the path to the certificate in an environment variable PATH_TO_MYSQL_CERT.

The Connection: Set one of the connection strings provided from the deployment to the environment variable COMPOSE_MYSQL_URL. The urllib parses the information that is then passed as parameters to the connect function. The MySQL driver needs a database to connect to upon login. A default "compose" database is provided with every deployment and it's included in the connection string, so that information is parsed and passed in as well. The certificate path is set to the ssl_ca parameter.

# connection string and initialization - sets cursor class to handle python dicts
compose_mysql_url = os.environ['COMPOSE_MYSQL_URL']  
path_to_mysql_cert = os.environ['PATH_TO_MYSQL_CERT']  
parsed = urlparse(compose_mysql_url)  
db = parsed.path.replace("/", "")

# establishes a connection
conn = mysql.connector.connect(  
    host=parsed.hostname,
    port=parsed.port,
    user=parsed.username,
    password=parsed.password,
    ssl_ca=path_to_mysql_cert,
    database=db)

The next few lines make a connection and set up a "grand_tour" database, should it not already be present. This can also be used for other Compose for MySQL grand tour examples in other languages. Similarly, it checks for the "words" table and creates it if not found.

# checks for database, creates if non-existent
cur = conn.cursor()  
cur.execute("""CREATE DATABASE IF NOT EXISTS grand_tour""")  
cur.execute("""USE grand_tour""")

# checks for table, creates if non-existent
cur.execute("""CREATE TABLE IF NOT EXISTS words (  
    id serial primary key,
    word varchar(256) NOT NULL,
    definition varchar(256) NOT NULL) """)

The Read: The first step is querying the 'words' table for all the existing words and definitions with SQL SELECT. Then, we parse the results from the SQL query. The results only contain the entries in the table, not their associated labels, but luckily these can be retrieved from the cursor object's 'description' attribute. We zip the labels with each row of the result (which contains the word/definition pair) and make each into a Python dictionary object. All the objects are compiled into a list, which is then JSONified and returned for display to the web page.

# query for all the words in the table, returns as json for display on the page.
def display_find():  
    cur = conn.cursor()

    # SQL query for all the rows in the table, stores rows in an object
    cur.execute("""SELECT word, definition FROM words""")
    cursor_obj = cur.fetchall()

    # grabs column names from the table
    labels = [column[0] for column in cur.description]

    # makes a list from the dict of the zip of column names and the results object
    results_list = []
    for row in cursor_obj:
        results_list.append(dict(zip(labels, row)))

    # makes json from the list for display on the page
    return json.dumps(results_list)

The Write: Inserting a new word into the table is relatively straightforward SQL. The new word and its definition are pulled from the text fields and placed into the SQL INSERT. The driver executes the SQL and commits the transaction to the database.

# triggers on hitting the 'Add' button; inserts word/definition into 'words' table
def handle_words():  
    cur = conn.cursor()
    cur.execute("""INSERT INTO words (word, definition)
        VALUES (%s, %s)""", (request.form['word'], request.form['definition']))
    conn.commit()
    return ('', 204)

etcd3

etcd3 is a relative newcomer to the Compose roster of databases. It's also a special case because driver support and other ecosystem things are still catching up to the major changes implemented in etcd3 vs etcd2. Most drivers out there currently, including the Python driver, are in Alpha and Beta development phases. Not only that but there are currently some experimental features in the most recent etcd 3.3 release that enables v2 API Emulation with v3 Storage and use of the v2 HTTP endpoints. This means that some drivers and languages might not need to adopt much to talk to the newer etcd3. We're keeping tabs on the ecosystem as it develops, and follow-up stop on the tour will be published to complete the full set of Compose databases.

Part 3 Wrap-up

That concludes the final part of the Grand Tour in Python. For a look back at where we've been, we covered connecting to every database that Compose offers, with the exception of etcd3. It's been fun riding through connection examples to MongoDB, PostgreSQL, and Elasticsearch in Part 1. Rolling through connections to Redis, RethinkDB, and RabbitMQ in Part 2. And finally coasting to an end here with Scylla and Compose for MySQL in part 3. If you enjoyed the Python, check out the Compose Grand Tour in Go, starting with part 1, Go and Compose - MongoDB, Elasticsearch and PostgreSQL.


Read more articles about Compose databases - use our Curated Collections Guide for articles on each database type. If you have any feedback about this or any other Compose article, drop the Compose Articles team a line at articles@compose.com. We're happy to hear from you.

attribution Alexas_Fotos

Conquer the Data Layer

Spend your time developing apps, not managing databases.