Connecting to MatrixOne with Python

MatrixOne supports Python connection, pymysql and sqlalchemy drivers are supported. This tutorial will walk you through how to connect MatrixOne by these two python drivers.

Before you start

  1. Make sure you have already installed and launched MatrixOne.
  2. Make sure you have already installed Python 3.8(or plus) version.
  1. #To check with Python installation and its version
  2. python3 -V
  1. Make sure you have already installed MySQL.

Using Python pymysql connect to MatrixOne

The PyMySQL is a pure-Python MySQL client library.

  1. Download and install pymysql and cryptography tool:

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

  2. Connect to MatrixOne by MySQL client. Create a new database named test.

    1. mysql> create database test;

  3. Create a plain text file pymysql_connect_matrixone.py and put the code below.

    1. #!/usr/bin/python3
    3. import pymysql
    5. # Open database connection
    6. db = pymysql.connect(
    7.         host='127.0.0.1',
    8.         port=6001,
    9.         user='dump',
    10.         password = "111",
    11.         db='test',
    12.         )
    13. # prepare a cursor object using cursor() method
    14. cursor = db.cursor()
    16. # execute SQL query using execute() method.
    17. cursor.execute("SELECT VERSION()")
    19. # Fetch a single row using fetchone() method.
    20. data = cursor.fetchone()
    21. print ("Database version : %s " % data)
    23. # disconnect from server
    24. db.close()

  4. Execute this python file in the command line terminal.

    1. > python3 pymysql_connect_matrixone.py
    2. Database version : 8.0.30-MatrixOne-v0.8.0

Using sqlalchemy connect to MatrixOne

SQLAlchemy is the Python SQL toolkit and Object Relational Mapper(ORM) that gives application developers the full power and flexibility of SQL.

  1. Download and install sqlalchemy tool:

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

  2. Connect to MatrixOne by MySQL client. Create a new database named test, a new table named student and insert two records.

    1. mysql> create database test;
    2. mysql> use test;
    3. mysql> create table student (name varchar(20), age int);
    4. mysql> insert into student values ("tom", 11), ("alice", "10");

  3. Create a plain text file sqlalchemy_connect_matrixone.py and put the code below,

    1. #!/usr/bin/python3
    2. from sqlalchemy import create_engine, text
    4. # Open database connection
    5. my_conn = create_engine("mysql+mysqldb://dump:111@127.0.0.1:6001/test")
    7. # execute SQL query using execute() method.
    8. query=text("SELECT * FROM student LIMIT 0,10")
    9. my_data=my_conn.execute(query)
    11. # print SQL result
    12. for row in my_data:
    13.         print("name:", row["name"])
    14.         print("age:", row["age"])

  4. Execute this python file in the command line terminal.

    1. python3 sqlalchemy_connect_matrixone.py
    2. name: tom
    3. age: 11
    4. name: alice
    5. age: 10

Reference

For the example about using Python to build a simple CRUD with MatrixOne, see Build a simple Python+SQLAlchemy CRUD demo with MatrixOne and Build a simple Python CRUD demo with MatrixOne