Connect with Python¶

MatrixOne Intelligence supports Python connections and supports two drivers: pymysql and sqlalchemy.

This document will guide you through how to connect to MatrixOne Intelligence through these two python drivers.

Prepare before starting¶

Create instance has been completed.
Python 3.8(or plus) version installed.

#Check Python version number and confirm whether to install it
python3 -V

The MySQL client is installed.

Use pymysql tool to connect to MatrixOne Intelligence service¶

PyMySQL is a Python MySQL client library.

Download and install pymysql and cryptography tools:

pip3 install pymysql
pip3 install cryptography

#If you are in China mainland and have a low downloading speed, you can speed up the download by following commands.
pip3 install pymysql -i https://pypi.tuna.tsinghua.edu.cn/simple
pip3 install cryptography -i https://pypi.tuna.tsinghua.edu.cn/simple

Connect to MatrixOne Intelligence Cloud using the MySQL client. Create a new database with name test:
```
mysql> create database test;
```

Create a plain text file pymysql_connect_matrixonecloud.py Write the code to the file and modify the relevant database parameters according to your MatrixOne Intelligence database connection string:

#!/usr/bin/python3

import pymysql

# Open database connection
db = pymysql.connect(
        host='freetier-01.cn-hangzhou.cluster.matrixonecloud.cn',
        port=6001,
        user='585b49fc_852b_4bd1_b6d1_d64bc1d8xxxx:admin:accountadmin',
        password = "your_password",
        db='test',
        )
# prepare a cursor object using cursor() method
cursor = db.cursor()

# execute SQL query using execute() method.
cursor.execute("SELECT VERSION()")

# Fetch a single row using fetchone() method.
data = cursor.fetchone()
print ("Database version : %s " %data)

#disconnect from server
db.close()

Open a terminal and execute the following commands in the terminal:

> python3 pymysql_connect_matrixonecloud.py
Database version: 8.0.30-MatrixOne-v2.0.3

Connect MatrixOne Intelligence using sqlalchemy¶

SQLAlchemy is a Python SQL toolkit and object relational mapper (ORM), which provides application developers with the full functionality of SQL.

Download and install the sqlalchemy tool. The download code example is as follows:

pip3 install sqlalchemy
pip3 install pymysql
#If you are in China mainland and have a low downloading speed, you can speed up the download by following commands.
pip3 install sqlalchemy -i https://pypi.tuna.tsinghua.edu.cn/simple
pip3 install pymysql -i https://pypi.tuna.tsinghua.edu.cn/simple

Connect to MatrixOne Intelligence using the MySQL client. Create a new database with a name of test, and create a new table with a name of student, and then insert two pieces of data:

mysql> create database test;
mysql> use test;
mysql> create table student (id int primary key,name varchar(20), age int);
mysql> insert into student values ​​(1,"tom", 11), (2,"alice", "10");

Create a plain text file sqlalchemy_connect_matrixonecloud.py Write the code to the file and modify the relevant database parameters according to your MatrixOne Intelligence database connection string:

from sqlalchemy import create_engine
from sqlalchemy.orm import declarative_base as _declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker

# Create a connection string to MatrixOne using SQLAlchemy and create an engine (Engine)
username="585b49fc_852b_4bd1_b6d1_d64bc1d8xxxx%3Aadmin%3Aaccountadmin"
password="xxx"
host="freetier-01.cn-hangzhou.cluster.matrixonecloud.cn"
port="6001"
dbname="test"

connection_string = "mysql+pymysql://" +username+":"+password+"@"+host+":"+port+"/"+dbname

engine = create_engine(connection_string)

Base = _declarative_base()

#Define a Python class to map student tables.
class Student(Base):
    __tablename__ = 'student'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    age = Column(Integer)

# Create a session to execute a query using sessionmaker
Session = sessionmaker(bind=engine)
session = Session()

#Use SQLAlchemy's query interface to query data in the student table.
users = session.query(Student).all()
for user in users:
    print(f'ID: {user.id}, Name: {user.name}, Age: {user.age}')

Open a terminal and execute the following commands in the terminal:

python3 sqlalchemy_connect_matrixonecloud.py
ID: 1, Name: tom, Age: 11
ID: 2, Name: alice, Age: 10