Python Code Walkthrough

Overview

This walkthrough explains how to perform a simple test of a SkySQL service using Python code, as described in our Quickstart experience.

Done with this walkthrough? Return to Step 3 of the Quickstart

Dependency

  • Python 3

Install Connector/Python

To install MariaDB Connector/Python from PyPI:

  1. Install MariaDB Connector/C, which is a dependency.

  2. Use pip3 to install MariaDB Connector/Python.

    To install the latest MariaDB Connector/Python 1.1 release from PyPI:

    $ pip3 install mariadb==1.1.10

    To install the latest MariaDB Connector/Python 1.0 release from PyPI:

    $ pip3 install mariadb==1.0.11

Additional guidance is available:

Prepare Code

Using your editor of choice, create a file named app.py

Connect

Copy the following code into app.py with the following modifications:

  • Change the host, user, and password for your service

  • Set the ssl_ca path to the full path where you saved the SkySQL certificate authority chain file.

import mariadb

# Connect w/o specifying a database & with the default of autocommit=False
connection = mariadb.connect(
    host="quickstart.mdbxxxxxxx.db.skysql.net",
    port=5046,
    ssl_ca="/path/to/skysql_chain.pem",
    user="DBXXXXXXXX",
    password="xxxxxxxxxx",
    )

# Each connection needs at least one cursor associated with it in order to
# make changes and request results. This cursor is created with the option
# that allows some per-row access using a named tuple.  There is also an
# alternate option of dictionary=True or the default of normal tuples.
cursor = connection.cursor(named_tuple=True)

cursor.execute("SET NAMES utf8")

Prepare the Table

cursor.execute("CREATE DATABASE IF NOT EXISTS quickstart")
cursor.execute("USE quickstart")

cursor.execute("""\
    CREATE TABLE programming_language (
      name VARCHAR(50) NOT NULL UNIQUE,
      rating INT
    )""")

Data In

Continuing to edit app.py with the following code that creates a cursor, inserts a single row, and inserts multiple rows:

# This single row insert specifies a tuple of values for the "?"
# placeholders, which ensures the values are always handled safely.
cursor.execute(
    "INSERT INTO programming_language VALUES (?, ?)",
    ("Python", 10))

# Multiple rows can be inserted using executemany and a list of tuples
# (or a tuple of tuples), one tuple for each row to insert.
cursor.executemany(
    "INSERT INTO programming_language VALUES (?, ?)", [
        ("Java", 9),
        ("C++", 8),
        ("JavaScript", 7),
        ("C#", 6),
        ("Go", 5),
        ("Rust", 4),
        ("PHP", 3),
    ])

# Because we didn't set autocommit=True, we need to commit the changes
# manually or they will be rolled back when the connection closes.
connection.commit()

Data Out

Continuing to edit app.py:

cursor.execute("""\
    SELECT name, rating
    FROM programming_language
    ORDER BY rating DESC""")

# The results can be treated like a regular tuple:
for name, rating in cursor:
    print(f"{rating} - {name}")

cursor.execute("""\
    SELECT name, rating
    FROM programming_language
    ORDER BY name""")

print('---')

# Or (due to the named_tuple option) the results can be accessed via their field names:
for row in cursor.fetchall():
    print(f"{row.name}: {row.rating}")

cursor.execute("""\
    SELECT name, rating
    FROM programming_language
    ORDER BY name
    LIMIT 2""")

rows = cursor.fetchall()

print('---')
print(len(rows), "rows:", rows)

# Note that numeric field values are returned as numbers by default
print('---')
print('Type of name field:', type(rows[0][0]))
print('Type of rating field:', type(rows[0][1]))

Save app.py

Run the Test

Start the application by running the following command (or equivalent option in your IDE):

$ python3 app.py

You should see the following output:

10 - Python
9 - Java
8 - C++
7 - JavaScript
6 - C#
5 - Go
4 - Rust
3 - PHP
---
C#: 6
C++: 8
Go: 5
Java: 9
JavaScript: 7
PHP: 3
Python: 10
Rust: 4
---
2 rows: [mariadb.Row(name='C#', rating=6), mariadb.Row(name='C++', rating=8)]
---
Type of name field: <class 'str'>
Type of rating field: <class 'int'>

Done with this walkthrough? Return to Step 3 of the Quickstart