Develop applications based on a Python ORM framework - 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 whitelists.

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	The user name and password used to connect to LindormTable. If you forget your password, you can change the password in the cluster management system of LindormTable. For more information, see Manage users.
password
lindorm_mysql_addr	The LindormTable endpoint for MySQL. 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 a local server and needs to connect to the Lindorm instance over the Internet, you can perform the following steps to enable the public endpoint for the instance in the Lindorm console: In the left-side navigation pane, select Database Connections > Wide Table Engine. On the Wide Table Engine tab, click Enable Public Endpoint. If your application needs to connect to the Lindorm instance over VPC, set this parameter to the LindormTable VPC endpoint for MySQL. If your application needs to connect to the Lindorm instance over Internet, set this parameter to the LindormTable Internet endpoint for MySQL.
database	The name of the database to which you want to connect. By default, your client is connected to a database named default.

Connect to and use LindormTable The following code block provides an example on how to create a table and populate data to the table:

# Create a Lindorm wide 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 the Lindorm wide table.
session.add(new_player)
# Write multiple rows of data to the Lindorm wide table.
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 code provides a complete example on how to connect to and use LindormTable by using the SQLAlchemy framework:

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):
    # Specify the table name.
    __tablename__ = 'player'
    # Specify the table schema.
    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 a connection to the database. Replace the parameters with actual values.
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 Lindorm wide 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 the Lindorm wide table.
session.add(new_player)
# Write multiple rows of data to the Lindorm wide table.
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 row in which the player_id column is 1001.
rows = session.query(Player).filter(Player.player_id == 1001).all()
print([row.__str__() for row in rows])


# Delete the row in which the player_id column is 1002.
session.query(Player).filter(Player.player_id == 1002).delete()
session.commit()

# Update the row in which the player_id column is 1003.
session.query(Player).filter(Player.player_id == 1003).update({"player_name":"brown"})
session.commit()

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

The following result is returned if the query is successful:

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