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

  1. If you are in China mainland and have a low downloading speed, you can speed up the download by following commands.

  2. pip3 install sqlalchemy -i https://pypi.tuna.tsinghua.edu.cn/simple
  3. pip3 install pymysql -i https://pypi.tuna.tsinghua.edu.cn/simple
  4. pip3 install cryptography -i https://pypi.tuna.tsinghua.edu.cn/simple
  5. 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

6. faker = Factory.create()

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

10. Session = sessionmaker(bind=engine)
11. session = Session()

13. Base = declarative_base()

16. class Customer(Base):
17. __tablename__ = "Customer"
18. id = Column(Integer, primary_key=True,autoincrement=True)
19. cname = Column(String(64))
20. caddress = Column(String(512))

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

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

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

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

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

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

42. 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

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

7. Session = sessionmaker(bind=engine)
8. session = Session()

10. Base = declarative_base()

12. class Customer(Base):
13. __tablename__ = "Customer"
14. id = Column(Integer, primary_key=True,autoincrement=True)
15. cname = Column(String(64))
16. caddress = Column(String(512))

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

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

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

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

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

35. # query with a filter condition
36. Mccann = session.query(Customer).filter_by(cname='David Mccann').first()
37. print(Mccann)
38. 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. --------------------------

6. cname:Meagan Rodriguez caddress:USCGC Olson
7. FPO AP 21249
8. --------------------------

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

14. cname:Lisa Bruce caddress:68103 Mackenzie Mountain
15. North Andrew, UT 29853
16. --------------------------

18. cname:Julie Moore caddress:Unit 1117 Box 1029
19. DPO AP 87468
20. --------------------------

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

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

30. cname:Morgan Price caddress:57463 Lisa Drive
31. Thompsonshire, NM 88077
32. --------------------------

34. cname:Samuel Griffin caddress:186 Patel Crossing
35. North Stefaniechester, WV 08221
36. --------------------------

38. cname:Tristan Pierce caddress:593 Blankenship Rapids
39. New Jameshaven, SD 89585
40. --------------------------

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

45. ------------------------

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

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

7. Session = sessionmaker(bind=engine)
8. session = Session()

10. Base = declarative_base()

12. class Customer(Base):
13. __tablename__ = "Customer"
14. id = Column(Integer, primary_key=True,autoincrement=True)
15. cname = Column(String(64))
16. caddress = Column(String(512))

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

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

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

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

33. # Rename customer
34. customer.cname = "Coby White"

37. session.commit()

39. # See the updated result
40. customer = session.query(Customer).first()
41. 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

5. ---------------------

7. cname:Coby White caddress:002 Brian Plaza
8. 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

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

7. Session = sessionmaker(bind=engine)
8. session = Session()

10. Base = declarative_base()

12. class Customer(Base):
13. __tablename__ = "Customer"
14. id = Column(Integer, primary_key=True,autoincrement=True)
15. cname = Column(String(64))
16. caddress = Column(String(512))

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

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

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

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

32. session.delete(customer)
33. session.commit()

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

38. for customer in customers:
39. 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. --------------------------

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

10. cname:Lisa Bruce caddress:68103 Mackenzie Mountain
11. North Andrew, UT 29853
12. --------------------------

14. cname:Julie Moore caddress:Unit 1117 Box 1029
15. DPO AP 87468
16. --------------------------

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

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

26. cname:Morgan Price caddress:57463 Lisa Drive
27. Thompsonshire, NM 88077
28. --------------------------

30. cname:Samuel Griffin caddress:186 Patel Crossing
31. North Stefaniechester, WV 08221
32. --------------------------

34. cname:Tristan Pierce caddress:593 Blankenship Rapids
35. New Jameshaven, SD 89585
36. --------------------------

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)