Connecting to an MDB database with python

Overview

bSQL is a blockchain SQL that leverages the power of relational data and blockchain security protocols. The following document explains how to integrate MDB in our python code.

Requirements

• Python >= 3.9.0
• MDB >= 1.0

Installation

Package is uploaded on PyPI.

Install with pip:

$ python3 -m pip install mdb-bp

Documentation

• python database api specification for general usage: DB-API 2.0

Connecting

Before we proceed let's look at the information we need to collect.

Most of these are self-explanatory.

• connection_protocol: should always specify a "tcp" connection.
• server_address: the IP Address of your MDB instance.
• server_port: port "5461" id the default port used for interfacing with MDB.
• username/password: your MDB credentials. It is important you use your MBD credentials, NOT the credentials associated with your blockpoint portal.
• dbname: the name of the database we are connecting to.

We can use this information to connect to our MDB instance. When connect is called, we establish a connection the MDB instance and new session is created. The code below connects to the "main" database and closes the connection.

from mdb_bp import driver

# Connect to the database
conn = driver.connect(
    username="user",
    password="password",
    connection_protocol="tcp",
    server_address="216.27.61.137",
    server_port=5461,
    database_name="main",
    parameters={"interpolateParams": True},
)

conn.close()

Preparing and executing statements

In order to execute database commands we can use the prepare and exec methods. We call exec for all database commands that don't return rows. This includes database mutations, management, security and most analytical commands. When exec is called on a database connection, a new transaction is created an executed atomically.

from mdb_bp import driver

# Connect to the database
conn = driver.connect(
    username="user",
    password="password",
    connection_protocol="tcp",
    server_address="216.27.61.137",
    server_port=5461,
    database_name="main",
    parameters={"interpolateParams": True},
)

# Prepare a statement
stmt = conn.prepare(
    "CREATE BLOCKCHAIN user TRADITIONAL ("
    + "id UINT64 PRIMARY KEY AUTO INCREMENT,"
    + " first_name STRING SIZE = 25 PACKED,"
    + " last_name STRING SIZE = 50 PACKED,"
    + " age UINT8,"
    + " username STRING SIZE = 30 PACKED UNIQUE)")

# Execute the statement.
resp = stmt.exec()

conn.close()

Executing Database Commands Using Transaction.

We can use transactions to execute mutations non-atomically. The default isolation level in bSQL is read uncommitted, you can read more about isolation levels and transaction atomicity. Using transactions gives us more control of database writes using the rollback() and commit() methods.

# Run an insertion and handle error
xact = conn.begin()

# Insert a value non-atomically
try:
    result = xact.exec(
        "INSERT user (first_name, last_name, age, username) "
        + "VALUES (\"it's NOT CHABOY\", \"Smith\", 45, \"NOT CHABOY\")")
    xact.commit()

except Exception as err:
    # Rollback the transaction if there was a failure
    print(err)
    xact.rollback()

Executing Queries.

This example queries from the user blockchain created in the first statement. A few things to note:

• query() returns a rows object that can be iterated through.
• Until the rows are closed or iterated until the end, the query remains open and additional commands will fail.

# Query from the blockchain
rows = conn.query("SELECT * FROM user")

# Print the rows
itr = iter(rows)

for row in itr:
    print(row)
    
conn.close()    

The following output is produced:

{'id': 0, 'first_name': "it's NOT CHABOY", 'last_name': 'Smith', 'age': 45, 'username': 'NOT CHABOY'}

Storing Files.

This example stores a file in the database. A few things to note:

• Files are stored using the STORE FILE command.
• All files have a primary key that can be referenced throughout a database.

# Store a file in the database
store_file_resp = conn.store_file(
    "space",
    "test_files/space.png",
    "png"
)

# FileId is returned in response
print("FileId: {}".format(store_file_resp.insert_id)

Exporting Files.

This example exports a file stored in the database. A few things to note:

• Files are exported using the EXPORT FILE command.
• The file is identified by the file_name.

# Export a file from the database
export_file_resp = conn.export_file(
    "space",
    "test_files/out_space.png",
    "png"
)