Develop applications based on a Python ORM framework for Lindorm - Lindorm

This topic describes how to use a Python ORM framework to connect to and use LindormTable.

Prerequisites

Python 3.7 or a later version is installed.
The LindormTable version is 2.6.1 or later. For more information about how to view or upgrade the version of LindormTable, see Release notes of LindormTable and Upgrade the minor engine version of a Lindorm instance.
The MySQL compatibility feature is enabled for the instance. For more information, see Enable the MySQL compatibility feature.
The IP address of your client is added to the whitelist of your Lindorm instance. For more information, see Configure a whitelist.

Procedure

Run the following command to install the PyMySQL library that is used to connect to MySQL databases:
```
pip install PyMySQL
```
Run the following command to install the SQLAlchemy framework:
```
pip install SQLAlchemy
```

Configure connection parameters.

engine = create_engine('mysql+pymysql://<user>:<password>@<lindorm_mysql_addr>:33060/<database>', echo=True)

Parameters

Parameter	Description
user	If you forget your user password, you can change it in the LindormTable cluster management system. For more information, see Modify a user password.
password
lindorm_mysql_addr	The MySQL-compatible endpoint of LindormTable. For more information about how to obtain the endpoint, see View endpoints. Important If your application is deployed on an ECS instance, we recommend that you use a VPC to connect to the Lindorm instance to ensure higher security and lower network latency. If your application is deployed on-premises, enable a public endpoint in the console before you connect to the Lindorm instance over the Internet. To enable the public endpoint, choose Database Connections > Wide Table Engine in the console. On the Wide Table Engine tab, click Enable Public Endpoint. If you connect to the Lindorm instance over a VPC, set lindorm_mysql_addr to the MySQL-compatible VPC endpoint. If you connect to the Lindorm instance over the Internet, set lindorm_mysql_addr to the MySQL-compatible Internet endpoint.
database	The name of the database to which you want to connect. By default, your client is connected to a database named default.

This example shows how to connect to and use LindormTable to create a table and insert data.

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

# Create a session object.
session = Session()
# Create a Player object.
new_player = Player(player_id = 1001, player_name = "john", player_height = 2.08)
# Write data to Lindorm.
session.add(new_player)
# Write multiple rows of data to Lindorm.
session.add_all([
    Player(player_id=1002, player_name="bob", player_height=1.65),
    Player(player_id=1003, player_name="smith", player_height=1.82),
])
session.commit()

Sample code

The following shows the complete sample code:

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


Base = declarative_base()
# Define a Player object.
class Player(Base):
    # Table name:
    __tablename__ = 'player'
    # Table structure:
    player_id = Column(Integer, primary_key=True,autoincrement=False)
    player_name = Column(String(255))
    player_height = Column(Float)
    def __str__(self):
        return " ".join(str(item) for item in (self.player_id,self.player_name,self.player_height))


# Initialize the database connection. Change the connection parameters as needed.
engine = create_engine('mysql+pymysql://user:test@ld-bp1l39h99192d****-proxy-sql-lindorm-public.lindorm.rds.aliyuncs.com:33060/default', echo=True)
Session = sessionmaker(bind=engine)

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

# Create a session object.
session = Session()
# Create a Player object.
new_player = Player(player_id = 1001, player_name = "john", player_height = 2.08)
# Write data to Lindorm.
session.add(new_player)
# Write multiple rows of data to Lindorm.
session.add_all([
    Player(player_id=1002, player_name="bob", player_height=1.65),
    Player(player_id=1003, player_name="smith", player_height=1.82),
])
session.commit()

# Query the data where player_id is 1001.
rows = session.query(Player).filter(Player.player_id == 1001).all()
print([row.__str__() for row in rows])


# Delete the data where player_id is 1002.
session.query(Player).filter(Player.player_id == 1002).delete()
session.commit()

# Update the data where player_id is 1003.
session.query(Player).filter(Player.player_id == 1003).update({"player_name":"brown"})
session.commit()

# Query all data.
rows = session.query(Player).all()
print([row.__str__() for row in rows])

A successful execution returns the following query results:

['1001 john 2.08']
['1001 john 2.08', '1003 brown 1.82']