App Development for SQLAlchemy
This tutorial shows you how to build a simple Python application based on TiDB and SQLAlchemy. The sample application to build here is a simple CRM tool where you can add, query, and update customer and order information.
Step 1. Start a TiDB cluster
Start a pseudo TiDB cluster on your local storage:
docker run -p 127.0.0.1:$LOCAL_PORT:4000 pingcap/tidb:v5.1.0
The above command starts a temporary and single-node cluster with mock TiKV. The cluster listens on the port $LOCAL_PORT
. After the cluster is stopped, any changes already made to the database are not persisted.
Step 2. Create a database
In the SQL shell, create the
test_sqlalchemy
database that your application will use:CREATE DATABASE test_sqlalchemy;
Create a SQL user for your application:
CREATE USER <username> IDENTIFIED BY <password>;
Take note of the username and password. You will use them in your application code when initializing the project.
Grant necessary permissions to the SQL user you have just created:
GRANT ALL ON test_sqlalchemy.* TO <username>;
Step 3. Set virtual environments and initialize the project
Use Poetry, a dependency and package manager in Python, to set virtual environments and initialize the project.
Poetry can isolate system dependencies from other dependencies and avoid dependency pollution. Use the following command to install Poetry.
pip install --user poetry
Initialize the development environment using Poetry:
poetry init --no-interaction --dependency sqlalchemy poetry add git+https://github.com/pingcap/sqlalchemy-tidb.git#main
Step 4. Get and run the application code
The sample application code in this tutorial (main.py
) uses SQLAlchemy to map Python methods to SQL operations. You can save the example application code as a Python file named main.py
on your local machine.
The code performs the following operations:
- Creates the
users
andorders
tables in thetest_sqlalchemy
database as specified by theUser
andOrder
mapping classes. - Inserts data to the
users
andorders
tables. - Deletes data from orders by
oid
. - Updates
orders
byoid
. - Joins the
users
andorders
tables. - Queries the
users
andorders
tables using the sameuid
.
from sqlalchemy import Column, Integer, String, Float, ForeignKey, create_engine, Enum
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
import enum
engine = create_engine(
'tidb://{username}:{password}@{hostname}:{port}/test_sqlalchemy?charset=utf8mb4',
echo=False)
# The base class on which the objects will be defined.
Base = declarative_base()
class Gender(enum.Enum):
Female = 1
Male = 2
class User(Base):
__tablename__ = 'users'
uid = Column(Integer, primary_key=True)
name = Column(String(50))
gender = Column(Enum(Gender))
def __repr__(self):
return "<User(name='%s', gender='%s')>" % (
self.name, self.gender)
class Order(Base):
__tablename__ = 'orders'
# Every SQLAlchemy table should have a primary key named 'id'.
oid = Column(Integer, primary_key=True, autoincrement=True)
uid = Column(Integer)
price = Column(Float)
# Prints out a user object conveniently.
def __repr__(self):
return "<User(oid='%d', uid='%d', price'%f')>" % (
self.name, self.uid, self.price)
# Creates all tables by issuing CREATE TABLE commands to the database.
Base.metadata.create_all(engine)
# Creates a new session to the database by using the described engine.
Session = sessionmaker(bind=engine)
session = Session()
# Inserts users into the database.
session.add_all([
User(name='Alice', gender=Gender.Female),
User(name='Peter', gender=Gender.Male),
User(name='Ben', gender=Gender.Male),
])
session.commit()
# Inserts Order into the database.
ed_user = Order(uid=1, price=2.5)
# Adds the created users to the DB and commit.
session.add(ed_user)
session.commit()
# Inserts Orders into the database.
session.add_all([
Order(uid=1, price=0.5),
Order(uid=2, price=4.5),
Order(uid=2, price=2123.87),
Order(uid=3, price=212.5),
Order(uid=3, price=8.5),
]
)
session.commit()
# Deletes orders by oid.
session.query(Order).filter(Order.oid == 4).delete()
session.commit()
# Updates orders.
session.query(Order).filter(Order.oid == 1).update({'price': 3.5})
session.commit()
# Joins orders and users tables.
print(
session.query(User.name, Order.price)
.select_from(User)
.filter(User.uid == Order.uid)
.filter(Order.uid == 3)
.all()
)
Step 1. Update the connection parameters and connect to TiDB
In the main.py
file above, replace the string passed to create_engine()
with the connection string you have obtained when creating the database.
engine = create_engine(
'tidb://{username}:{password}@{hostname}:{port}/test_sqlalchemy?charset=utf8mb4',
echo=False)
By default, you can set the string as follows:
engine = create_engine(
'tidb://root:@127.0.0.1:4000/test_sqlalchemy?charset=utf8mb4',
echo=False)
Step 2. Run the application code
After the connection string is correctly set, run the application code:
python3 main.py
The expected output is as follows:
[('Ben', 212.5), ('Ben', 8.5)]