Skip to content

Python Basic Example

This document will guide you how to usePython to build a simple application and implement the CRUD (create, read, update, delete) function.

Prepare before starting

Environment Configuration

Before you start, make sure you have downloaded and installed the following software:

Use the following code to check the Python version to confirm that the installation is successful:

```
#To check with Python installation and its version
python3 -V
```

  • Confirm that you have completed the installation of the MySQL client.

  • Download and install the pymysql and cryptography tools.

Use the following code to download and install the 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
```

You can refer to Python Connect MatrixOne Service to learn how to connect to MatrixOne through pymysql. This document will guide you how to implement CRUD (create, read, update, delete).

Create a new table

Create a new text file of create.py and copy and paste the following code into the file:

#!/usr/bin/python3

import pymysql.cursors

SQL_CONNECTION = pymysql.connect(
        host='freetier-01.cn-hangzhou.cluster.matrixonecloud.cn',
        port=6001,
        user='585b49fc_852b_4bd1_b6d1_d64bc1d8xxxx:admin:accountadmin',
        password = "passwd",
        db='test',
        cursorclass=pymysql.cursors.DictCursor,
        autocommit=True
        )

SQL = "CREATE TABLE cars (id INT NOT NULL AUTO_INCREMENT, car_model VARCHAR(45) NULL,car_brand VARCHAR(45) NULL,PRIMARY KEY (`id`))"

with SQL_CONNECTION.cursor() as cursor:
    try:
        sql_exec = cursor.execute(SQL)
        print("Table created")
    except (pymysql.Error, pymysql.Warning) as e:
        print(f'error! {e}')

    Finally:
        SQL_CONNECTION.close()

Open the terminal and run this python file using the following code. This will create a table named cars within the database test in MatrixOne.

> python3 create.py
Table created

You can use the MySQL client to verify that the table was created successfully:

mysql> show tables;
+---------------------+
| tables_in_test |
+---------------------+
| cars |
+---------------------+
1 row in set (0.03 sec)
mysql> show create table cars;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Create Table |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| cars | CREATE TABLE `cars` (
`id` INT NOT NULL AUTO_INCREMENT,
`car_model` VARCHAR(45) DEFAULT NULL,
`car_brand` VARCHAR(45) DEFAULT NULL,
PRIMARY KEY (`id`)
) |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.03 sec)

Insert data

Create a new text file of insert.py and copy and paste the following code into the file:

#!/usr/bin/python3

import pymysql.cursors

SQL_CONNECTION = pymysql.connect(
        host='freetier-01.cn-hangzhou.cluster.matrixonecloud.cn',
        port=6001,
        user='585b49fc_852b_4bd1_b6d1_d64bc1d8xxxx:admin:accountadmin',
        password = "passwd",
        db='test',
        cursorclass=pymysql.cursors.DictCursor,
        autocommit=True
        )

SQL = "INSERT INTO cars(car_model, car_brand) VALUES ('accord', 'honda')"

with SQL_CONNECTION.cursor() as cursor:
    try:
        sql_exec = cursor.execute(SQL)
        if sql_exec:
            print(sql_exec)
            print("Record Added")
        else:
            print(sql_exec)
            print("Not Added")
    except (pymysql.Error, pymysql.Warning) as e:
        print(f'error! {e}')

    Finally:
        SQL_CONNECTION.close()

Executing the following code will insert a record in the cars table:

> python3 insert.py
1
Record Added

You can verify that this record is successfully inserted in the MySQL client:

mysql> select * from cars;
+----------------------------------------+
| id | car_model | car_brand |
+-------+--------------------------+
| 1 | accord | honda |
+-------+--------------------------+
1 row in set (0.03 sec)

Query data

Create a new text file of read.py and copy and paste the following code into the file:

#!/usr/bin/python3

import pymysql.cursors

SQL_CONNECTION = pymysql.connect(
        host='freetier-01.cn-hangzhou.cluster.matrixonecloud.cn',
        port=6001,
        user='585b49fc_852b_4bd1_b6d1_d64bc1d8xxxx:admin:accountadmin',
        password = "passwd",
        db='test',
        cursorclass=pymysql.cursors.DictCursor,
        autocommit=True
        )

SQL = "SELECT * FROM cars"

with SQL_CONNECTION.cursor() as cursor:
    try:
        sql_exec = cursor.execute(SQL)
        if sql_exec:
            print(sql_exec)
            print(cursor.fetchall())
        else:print(sql_exec)
            print("No Record")
    except (pymysql.Error, pymysql.Warning) as e:
        print(f'error! {e}')

    Finally:
        SQL_CONNECTION.close()

Execute the following code query and return all records in the cars table:

> python3 read.py
1
[{'id': 1, 'car_model': 'accord', 'car_brand': 'honda'}]

Update data

Create a new text file of update.py and copy and paste the following code into the file:

#!/usr/bin/python3

import pymysql.cursors

SQL_CONNECTION = pymysql.connect(
        host='freetier-01.cn-hangzhou.cluster.matrixonecloud.cn',
        port=6001,
        user='585b49fc_852b_4bd1_b6d1_d64bc1d8xxxx:admin:accountadmin',
        password = "passwd",
        db='test',
        cursorclass=pymysql.cursors.DictCursor,
        autocommit=True
        )

SQL = "UPDATE cars SET car_model = 'explorer', car_brand = 'ford' WHERE id = '1'"

with SQL_CONNECTION.cursor() as cursor:
    try:
        sql_exec = cursor.execute(SQL)
        if sql_exec:
            print(sql_exec)
            print("Record Updated")
        else:
            print(sql_exec)
            print("Not Updated")
    except (pymysql.Error, pymysql.Warning) as e:
        print(f'error! {e}')

    Finally:
        SQL_CONNECTION.close()

Execute the following code to update the record with id '1':

> python3 update.py
1
Record Updated

You can verify that this record is updated successfully in the MySQL client:

mysql> select * from cars;
+-------+--------------------------+
| id | car_model | car_brand |
+-------+--------------------------+
| 1 | explorer | ford |
+-------+--------------------------+
1 row in set (0.02 sec)

Delete data

Create a new text file of delete.py and copy and paste the following code into the file:

#!/usr/bin/python3

import pymysql.cursors

SQL_CONNECTION = pymysql.connect(
        host='freetier-01.cn-hangzhou.cluster.matrixonecloud.cn',
        port=6001,
        user='585b49fc_852b_4bd1_b6d1_d64bc1d8xxxx:admin:accountadmin',
        password = "passwd",
        db='test',
        cursorclass=pymysql.cursors.DictCursor,
        autocommit=True
        )

SQL = "DELETE FROM cars WHERE id = '1'"

with SQL_CONNECTION.cursor() as cursor:
    try:
        sql_exec = cursor.execute(SQL)
        if sql_exec:
            print(sql_exec)
            print("Record Deleted")
        else:
            print(sql_exec)
            print("Not Deleted")
    except (pymysql.Error, pymysql.Warning) as e:
        print(f'error! {e}')

    Finally:
        SQL_CONNECTION.close()

Execute the following code to delete the record with id "1":

> python3 delete.py
1
Record Deleted

You can verify that the record was deleted successfully in the MySQL client:

mysql> select * from cars;
Empty set (0.03 sec)