SQLAlchemy CRUD demo

This tutorial shows you how to build a simple Python+SQLAlchemy CRUD(Create, Read, Update, Delete) application with MatrixOne. SQLAlchemy is one of the most popular ORM tools in python language.

Before you start

A brief introduction about these softwares concerned:

  • SQLAlchemy: SQLAlchemy is a python library that facilitates the communication between Python programs and databases. Most of the times, this library is used as an Object Relational Mapper (ORM) tool that translates Python classes to tables on relational databases and automatically converts function calls to SQL statements.
  • Faker: Faker is a Python library that generates fake data. Fake data is often used for testing or filling databases with some dummy data.

Setup your environment

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

  1. Make sure you have already installed and launched MatrixOne. Connect to MatrixOne and create a database by MySQL client.

    1. mysql> create database test;
  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
  3. Make sure you have already installed MySQL.

  4. Download and install sqlalchemy, pymysql, cryptography and faker tool.

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

As we have explained how to connect to MatrixOne by SQLAlchemy in the other tutorial, we will focus on the CRUD(Create, Read, Update, Delete) implementations in this tutorial.

Create

As an Object Relational Mapper(ORM) tool, SQLAlchemy allows developers to create python class to map the table in relational database. In the example below, we will create a User class which is a representation of User table in MatrixOne, the code which defines User is equal to a SQL statement as:

  1. CREATE TABLE `User` (
  2. `id` INT NOT NULL AUTO_INCREMENT,
  3. `cname` VARCHAR(64) DEFAULT NULL,
  4. `caddress` VARCHAR(512) DEFAULT NULL,
  5. PRIMARY KEY (`id`)
  6. )

Let’s now create this example in a text file named sqlalchemy_create.py, and put the following code:

  1. from faker import Factory
  2. from sqlalchemy import create_engine, Column, Integer, String
  3. from sqlalchemy.ext.declarative import declarative_base
  4. from sqlalchemy.orm import sessionmaker
  5. faker = Factory.create()
  6. engine = create_engine('mysql+pymysql://root:111@127.0.0.1:6001/test')
  7. Session = sessionmaker(bind=engine)
  8. session = Session()
  9. Base = declarative_base()
  10. class Customer(Base):
  11. __tablename__ = "Customer"
  12. id = Column(Integer, primary_key=True,autoincrement=True)
  13. cname = Column(String(64))
  14. caddress = Column(String(512))
  15. def __init__(self,name,address):
  16. self.cname = name
  17. self.caddress = address
  18. def __str__(self):
  19. return "cname:"+self.cname +" caddress:"+self.caddress
  20. def __repr__(self):
  21. return "cname:"+self.cname +" caddress:"+self.caddress
  22. # Generate 10 Customer records
  23. Customers = [Customer(name= faker.name(),address = faker.address()) for i in range(10)]
  24. # Create the table
  25. Base.metadata.create_all(engine)
  26. # Insert all customer records to Customer table
  27. session.add_all(Customers)
  28. session.commit()

Execute this file in a terminal with such command:

  1. > python3 sqlalchemy_create.py

Then we verify the table creation in MySQL client:

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

Read

In the following example, we read data from the Customer table by two ways.

The first one is a full scan, which equals to a query as:

  1. select * from `Customer`

The second one is a point query, which equals to a query as:

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

We create this example in a text file named sqlalchemy_read.py, and put the following code:

  1. from sqlalchemy import create_engine, Column, Integer, String
  2. from sqlalchemy.ext.declarative import declarative_base
  3. from sqlalchemy.orm import sessionmaker
  4. engine = create_engine('mysql+pymysql://root:111@127.0.0.1:6001/test')
  5. Session = sessionmaker(bind=engine)
  6. session = Session()
  7. Base = declarative_base()
  8. class Customer(Base):
  9. __tablename__ = "Customer"
  10. id = Column(Integer, primary_key=True,autoincrement=True)
  11. cname = Column(String(64))
  12. caddress = Column(String(512))
  13. def __init__(self,name,address):
  14. self.cname = name
  15. self.caddress = address
  16. def __str__(self):
  17. return "cname:"+self.cname +" caddress:"+self.caddress
  18. def __repr__(self):
  19. return "cname:"+self.cname +" caddress:"+self.caddress
  20. # query all data
  21. customers = session.query(Customer).all()
  22. for customer in customers:
  23. print(customer.__str__() +"\n--------------------------\n")
  24. # query with a filter condition
  25. Mccann = session.query(Customer).filter_by(cname='David Mccann').first()
  26. print(Mccann)
  27. print("\n------------------------\n")

Execute this file in a terminal with such command and we will see the query result:

  1. > python3 sqlalchemy_read.py
  2. cname:Wendy Luna caddress:002 Brian Plaza
  3. Andrewhaven, SC 88456
  4. --------------------------
  5. cname:Meagan Rodriguez caddress:USCGC Olson
  6. FPO AP 21249
  7. --------------------------
  8. cname:Angela Ramos caddress:029 Todd Curve Apt. 352
  9. Mooreville, FM 15950
  10. --------------------------
  11. cname:Lisa Bruce caddress:68103 Mackenzie Mountain
  12. North Andrew, UT 29853
  13. --------------------------
  14. cname:Julie Moore caddress:Unit 1117 Box 1029
  15. DPO AP 87468
  16. --------------------------
  17. cname:David Massey caddress:207 Wayne Groves Apt. 733
  18. Vanessashire, NE 34549
  19. --------------------------
  20. cname:David Mccann caddress:97274 Sanders Tunnel Apt. 480
  21. Anthonyberg, DC 06558
  22. --------------------------
  23. cname:Morgan Price caddress:57463 Lisa Drive
  24. Thompsonshire, NM 88077
  25. --------------------------
  26. cname:Samuel Griffin caddress:186 Patel Crossing
  27. North Stefaniechester, WV 08221
  28. --------------------------
  29. cname:Tristan Pierce caddress:593 Blankenship Rapids
  30. New Jameshaven, SD 89585
  31. --------------------------
  32. cname:David Mccann caddress:97274 Sanders Tunnel Apt. 480
  33. Anthonyberg, DC 06558
  34. ------------------------

Update

In the following example, we update the first cname column of Customer table by another value. We create this example in a text file named sqlalchemy_update.py, and put the following code:

  1. from sqlalchemy import create_engine, Column, Integer, String
  2. from sqlalchemy.ext.declarative import declarative_base
  3. from sqlalchemy.orm import sessionmaker
  4. engine = create_engine('mysql+pymysql://root:111@127.0.0.1:6001/test')
  5. Session = sessionmaker(bind=engine)
  6. session = Session()
  7. Base = declarative_base()
  8. class Customer(Base):
  9. __tablename__ = "Customer"
  10. id = Column(Integer, primary_key=True,autoincrement=True)
  11. cname = Column(String(64))
  12. caddress = Column(String(512))
  13. def __init__(self,name,address):
  14. self.cname = name
  15. self.caddress = address
  16. def __str__(self):
  17. return "cname:"+self.cname +" caddress:"+self.caddress
  18. def __repr__(self):
  19. return "cname:"+self.cname +" caddress:"+self.caddress
  20. customer = session.query(Customer).first()
  21. print(customer)
  22. print("\n---------------------\n")
  23. # Rename customer
  24. customer.cname = "Coby White"
  25. session.commit()
  26. # See the updated result
  27. customer = session.query(Customer).first()
  28. print(customer)

Execute this file in a terminal with such command and we will see the query result:

  1. > python3 sqlalchemy_update.py
  2. cname:Wendy Luna caddress:002 Brian Plaza
  3. Andrewhaven, SC 88456
  4. ---------------------
  5. cname:Coby White caddress:002 Brian Plaza
  6. Andrewhaven, SC 88456

Then we verify the record being updated in MySQL client:

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

Delete

In the following example, we delete the first record of the Customer table. We create this example in a text file named sqlalchemy_detele.py, and put the following code:

  1. from sqlalchemy import create_engine, Column, Integer, String
  2. from sqlalchemy.ext.declarative import declarative_base
  3. from sqlalchemy.orm import sessionmaker
  4. engine = create_engine('mysql+pymysql://root:111@127.0.0.1:6001/test')
  5. Session = sessionmaker(bind=engine)
  6. session = Session()
  7. Base = declarative_base()
  8. class Customer(Base):
  9. __tablename__ = "Customer"
  10. id = Column(Integer, primary_key=True,autoincrement=True)
  11. cname = Column(String(64))
  12. caddress = Column(String(512))
  13. def __init__(self,name,address):
  14. self.cname = name
  15. self.caddress = address
  16. def __str__(self):
  17. return "cname:"+self.cname +" caddress:"+self.caddress
  18. def __repr__(self):
  19. return "cname:"+self.cname +" caddress:"+self.caddress
  20. # delete the first record
  21. customer = session.query(Customer).first()
  22. session.delete(customer)
  23. session.commit()
  24. # query all data
  25. customers = session.query(Customer).all()
  26. for customer in customers:
  27. print(customer.__str__() +"\n--------------------------\n")

Execute this file in a terminal with such command and we will see the query result:

  1. > python3 sqlalchemy_delete.py
  2. cname:Meagan Rodriguez caddress:USCGC Olson
  3. FPO AP 21249
  4. --------------------------
  5. cname:Angela Ramos caddress:029 Todd Curve Apt. 352
  6. Mooreville, FM 15950
  7. --------------------------
  8. cname:Lisa Bruce caddress:68103 Mackenzie Mountain
  9. North Andrew, UT 29853
  10. --------------------------
  11. cname:Julie Moore caddress:Unit 1117 Box 1029
  12. DPO AP 87468
  13. --------------------------
  14. cname:David Massey caddress:207 Wayne Groves Apt. 733
  15. Vanessashire, NE 34549
  16. --------------------------
  17. cname:David Mccann caddress:97274 Sanders Tunnel Apt. 480
  18. Anthonyberg, DC 06558
  19. --------------------------
  20. cname:Morgan Price caddress:57463 Lisa Drive
  21. Thompsonshire, NM 88077
  22. --------------------------
  23. cname:Samuel Griffin caddress:186 Patel Crossing
  24. North Stefaniechester, WV 08221
  25. --------------------------
  26. cname:Tristan Pierce caddress:593 Blankenship Rapids
  27. New Jameshaven, SD 89585
  28. --------------------------

Then we verify the record being deleted in MySQL client:

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