Skip to content

SQLAlchemy Basic Example

This document will guide you how to build a simple application usingPython andSQLAlchemy and implement CRUD (create, read, update, delete) functions.

SQLAlchemy is one of the most popular ORM tools in the Python language.

Prepare before starting

A brief introduction to related software:

  • SQLAlchemy: SQLAlchemy is a Python library that facilitates communication between Python programs and databases. Most of the time, this library is used as an object relational mapper (ORM) tool that converts Python classes to tables on a relational database and automatically converts function calls to SQL statements.

  • Faker: Faker is a Python library that generates fake data. False data is often used to test or populate a database with some virtual data.

Environment Configuration

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

  1. Create instance.

  2. Connect to MatrixOne through the MySQL client and create a database named test:

    mysql> create database test;

  3. Confirm that you have completed the installation Python 3.8(or plus) version.

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 sqlalchemy, pymysql, cryptography and faker tools.

Use the following code to download and install the sqlalchemy, pymysql, cryptography and faker tools:

```
pip3 install sqlalchemy
pip3 install pymysql
pip3 install cryptography
pip3 install faker

#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
pip3 install cryptography -i https://pypi.tuna.tsinghua.edu.cn/simple
pip3 install faker -i https://pypi.tuna.tsinghua.edu.cn/simple
```

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

Create a new table

As an object relational mapper (ORM) tool, SQLAlchemy allows developers to create Python classes to map tables in relational databases.

In the following code example, a Customer class is created, which defines the code of Customer equivalent to a SQL statement that represents a table named Customer in MatrixOne:

CREATE TABLE `Customer` (
`id` INT NOT NULL AUTO_INCREMENT,
`cname` VARCHAR(64) DEFAULT NULL,
`caddress` VARCHAR(512) DEFAULT NULL,
PRIMARY KEY (`id`)
)

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

from faker import Factory
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

faker = Factory.create()

engine = create_engine('mysql+pymysql://root:111@127.0.0.1:6001/test')

Session = sessionmaker(bind=engine)
session = Session()

Base = declarative_base()


class Customer(Base):
    __tablename__ = "Customer"
    id = Column(Integer, primary_key=True,autoincrement=True)
    cname = Column(String(64))
    caddress = Column(String(512))

    def __init__(self,name,address):
        self.cname = name
        self.caddress = address

    def __str__(self):
        return "cname:"+self.cname +" caddress:"+self.caddress

    def __repr__(self):
        return "cname:"+self.cname +" caddress:"+self.caddress


# Generate 10 Customer records
Customers = [Customer(name= faker.name(),address = faker.address()) for i in range(10)]

# Create the table
Base.metadata.create_all(engine)

# Insert all customer records to Customer table
session.add_all(Customers)

session.commit()

Open the terminal and run this python file with the following code:

> python3 sqlalchemy_create.py

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

mysql> show tables;
+---------------------+
| tables_in_test |
+---------------------+
| Customer |
+---------------------+
1 row in set (0.04 sec)
mysql> select * from `Customer`;
+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| id | cname | caddress |
+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | Wendy Luna | 002 Brian Plaza
Andrewhaven, SC 88456 |
| 2 | Meagan Rodriguez | USCGC Olson
FPO AP 21249 |
| 3 | Angela Ramos | 029 Todd Curve Apt. 352
Mooreville, FM 15950 |
| 4 | Lisa Bruce | 68103 Mackenzie Mountain
North Andrew, UT 29853 |
| 5 | Julie Moore | Unit 1117 Box 1029
DPO AP 87468 |
| 6 | David Massey | 207 Wayne Groves Apt. 733
Vanessashire, NE 34549 |
| 7 | David Mccann | 97274 Sanders Tunnel Apt. 480
Anthonyberg, DC 06558 |
| 8 | Morgan Price | 57463 Lisa Drive
Thompsonshire, NM 88077 |
| 9 | Samuel Griffin | 186 Patel Crossing
North Stefaniechester, WV 08221 |
| 10 | Tristan Pierce | 593 Blankenship Rapids
New Jameshaven, SD 89585 |
+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
10 rows in set (0.03 sec)

Read data

In the following example, data will be read from the Customer table in two ways.

The first method is to scan the full table:

select * from `Customer`

The second method is to click query:

select * from `Customer` where `cname` = 'David Mccann';

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

from sqlalchemy import create_engine, Column, Integer, Stringfrom sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

engine = create_engine('mysql+pymysql://root:111@127.0.0.1:6001/test')

Session = sessionmaker(bind=engine)
session = Session()

Base = declarative_base()

class Customer(Base):
    __tablename__ = "Customer"
    id = Column(Integer, primary_key=True,autoincrement=True)
    cname = Column(String(64))
    caddress = Column(String(512))

    def __init__(self,name,address):
        self.cname = name
        self.caddress = address

    def __str__(self):
        return "cname:"+self.cname +" caddress:"+self.caddress

    def __repr__(self):
        return "cname:"+self.cname +" caddress:"+self.caddress


# query all data
customers = session.query(Customer).all()

for customer in customers:
     print(customer.__str__() +"\n--------------------------\n")

# query with a filter condition
Mccann = session.query(Customer).filter_by(cname='David Mccann').first()
print(Mccann)
print("\n------------------------\n")

Open a terminal, run this python file with the following code and see the results:

> python3 sqlalchemy_read.py
cname:Wendy Luna caddress:002 Brian Plaza
Andrewhaven, SC 88456
--------------------------

cname:Meagan Rodriguez caddress:USCGC Olson
FPO AP 21249
--------------------------

cname:Angela Ramos caddress:029 Todd Curve Apt. 352
Mooreville, FM 15950
--------------------------

cname:Lisa Bruce caddress:68103 Mackenzie Mountain
North Andrew, UT 29853
--------------------------

cname:Julie Moore caddress:Unit 1117 Box 1029
DPO AP 87468
--------------------------

cname:David Massey caddress:207 Wayne Groves Apt. 733
Vanessashire, NE 34549
--------------------------

cname:David Mccann caddress:97274 Sanders Tunnel Apt. 480
Anthonyberg, DC 06558
--------------------------

cname:Morgan Price caddress:57463 Lisa Drive
Thompsonshire, NM 88077
--------------------------

cname:Samuel Griffin caddress:186 Patel Crossing
North Stefaniechester, WV 08221
--------------------------

cname:Tristan Pierce caddress:593 Blankenship Rapids
New Jameshaven, SD 89585
--------------------------

cname:David Mccann caddress:97274 Sanders Tunnel Apt. 480
Anthonyberg, DC 06558

------------------------

Update data

In the following example, you will be guided to update the first cname column of the Customer table to another value.

Create a new text file called sqlalchemy_update.py and copy and paste the following code into the file:

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

engine = create_engine('mysql+pymysql://root:111@127.0.0.1:6001/test')

Session = sessionmaker(bind=engine)
session = Session()

Base = declarative_base()

class Customer(Base):
    __tablename__ = "Customer"
    id = Column(Integer, primary_key=True,autoincrement=True)
    cname = Column(String(64))
    caddress = Column(String(512))

    def __init__(self,name,address):
        self.cname = name
        self.caddress = address

    def __str__(self):
        return "cname:"+self.cname +" caddress:"+self.caddress

    def __repr__(self):
        return "cname:"+self.cname +" caddress:"+self.caddress


customer = session.query(Customer).first()
print(customer)
print("\n---------------------\n")

# Rename customer
customer.cname = "Coby White"


session.commit()

# See the updated result
customer = session.query(Customer).first()
print(customer)

Open a terminal, run this python file with the following code and see the results:

> python3 sqlalchemy_update.py     
cname:Wendy Luna caddress:002 Brian Plaza
Andrewhaven, SC 88456

---------------------

cname:Coby White caddress:002 Brian Plaza
Andrewhaven, SC 88456

You can verify that the table was updated successfully using the MySQL client:

mysql> select * from `Customer`;
+------+------------------+-----------------------------------------------------+
| id   | cname            | caddress                                            |
+------+------------------+-----------------------------------------------------+
|    1 | Coby White       | 002 Brian Plaza
Andrewhaven, SC 88456               |
|    2 | Meagan Rodriguez | USCGC Olson
FPO AP 21249                            |
|    3 | Angela Ramos     | 029 Todd Curve Apt. 352
Mooreville, FM 15950        |
|    4 | Lisa Bruce       | 68103 Mackenzie Mountain
North Andrew, UT 29853     |
|    5 | Julie Moore      | Unit 1117 Box 1029
DPO AP 87468                     |
|    6 | David Massey     | 207 Wayne Groves Apt. 733
Vanessashire, NE 34549    |
|    7 | David Mccann     | 97274 Sanders Tunnel Apt. 480
Anthonyberg, DC 06558 |
|    8 | Morgan Price     | 57463 Lisa Drive
Thompsonshire, NM 88077            |
|    9 | Samuel Griffin   | 186 Patel Crossing
North Stefaniechester, WV 08221  |
|   10 | Tristan Pierce   | 593 Blankenship Rapids
New Jameshaven, SD 89585     |
+------+------------------+-----------------------------------------------------+
10 rows in set (0.02 sec)

Delete data

In the following example, you will be guided to delete the first piece of data from the Customer table.

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

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

engine = create_engine('mysql+pymysql://root:111@127.0.0.1:6001/test')

Session = sessionmaker(bind=engine)
session = Session()

Base = declarative_base()

class Customer(Base):
    __tablename__ = "Customer"
    id = Column(Integer, primary_key=True,autoincrement=True)
    cname = Column(String(64))
    caddress = Column(String(512))

    def __init__(self,name,address):
        self.cname = name
        self.caddress = address

    def __str__(self):
        return "cname:"+self.cname +" caddress:"+self.caddress

    def __repr__(self):
        return "cname:"+self.cname +" caddress:"+self.caddress


# delete the first record
customer = session.query(Customer).first()

session.delete(customer)
session.commit()

# query all data
customers = session.query(Customer).all()

for customers in customers:
     print(customer.__str__() +"\n--------------------------\n")

Open the terminal, run this python file with the following code and view the results:

> python3 sqlalchemy_delete.py
cname:Meagan Rodriguez caddress:USCGC Olson
FPO AP 21249
------------------------------------

cname:Angela Ramos caddress:029 Todd Curve Apt. 352
Mooreville, FM 15950
------------------------------------

cname:Lisa Bruce caddress:68103 Mackenzie Mountain
North Andrew, UT 29853
------------------------------------

cname:Julie Moore caddress:Unit 1117 Box 1029
DPO AP 87468
------------------------------------

cname:David Massey caddress:207 Wayne Groves Apt. 733
Vanessashire, NE 34549
------------------------------------

cname:David Mccann caddress:97274 Sanders Tunnel Apt. 480
Anthonyberg, DC 06558
------------------------------------

cname:Morgan Price caddress:57463 Lisa Drive
Thompsonshire, NM 88077
------------------------------------

cname:Samuel Griffin caddress:186 Patel Crossing
North Stefaniechester, WV 08221
------------------------------------

cname:Tristan Pierce caddress:593 Blankenship Rapids
New Jameshaven, SD 89585
------------------------------------

You can use the MySQL client to verify that the records in the table are deleted successfully:

mysql> select * from `Customer`;
+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| id | cname | caddress |
+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 2 | Meagan Rodriguez | USCGC Olson
FPO AP 21249 |
| 3 | Angela Ramos | 029 Todd Curve Apt. 352
Mooreville, FM 15950 |
| 4 | Lisa Bruce | 68103 Mackenzie Mountain
North Andrew, UT 29853 |
| 5 | Julie Moore | Unit 1117 Box 1029
DPO AP 87468 |
| 6 | David Massey | 207 Wayne Groves Apt. 733
Vanessashire, NE 34549 |
| 7 | David Mccann | 97274 Sanders Tunnel Apt. 480
Anthonyberg, DC 06558 |
| 8 | Morgan Price | 57463 Lisa Drive
Thompsonshire, NM 88077 |
| 9 | Samuel Griffin | 186 Patel Crossing
North Stefaniechester, WV 08221 |
| 10 | Tristan Pierce | 593 Blankenship Rapids
New Jameshaven, SD 89585 |
+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
9 rows in set (0.04 sec)