Lindorm:Use mysql-connector-python to develop applications

Prerequisites

• Python 3.8 or a later version is installed.

• 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

1. Install mysql-connector-python of the 8.0.11 version. You can also run the pip install mysql-connector-python==8.0.11 command to install mysql-connector-python.

2. Establish a connection and configure parameters.

   connection = mysql.connector.connect(host='<LindormTable endpoint for MySQL.>', port=33060, user='<Username>', passwd='<Password>', database='<Database name>')
   cursor = connection.cursor(prepared=True)
   

   Parameters

   Parameter	Description
   host	The LindormTable endpoint for MySQL with the colon and port number (:33060) removed. For more information about how to obtain the endpoint, see View endpoints. Important If your application is deployed on an Elastic Compute Service (ECS) instance and the ECS instance is in the same VPC as the Lindorm instance, we recommend that you connect to the Lindorm instance through VPC. Otherwise, connect to the Lindorm instance over the Internet. To connect to a Lindorm instance over the Internet, you must enable the public endpoint of the instance. For more information, see Activate LindormSearch.
   port	The port used to connect to LindormTable by using MySQL. The value of this parameter is fixed to 33060.
   user	If you forget your password, you can change the password in the cluster management system of LindormTable. For more information, see Manage users.
   passwd
   database	The name of the database to which you want to connect. By default, your client is connected to a database named default.

3. Use LindormTable SQL to perform operations in LindormTable. The following code block provides an example on how to use LindormTable SQL to create a table:

   sql_create_table = ("create table if not exists test_python(c1 integer, c2 integer, c3 varchar, primary key(c1))")
   print(sql_create_table)
   cursor.execute(sql_create_table)
   

Complete sample code

The following code provides a complete example on how to use Java Low Level REST Client to connect to LindormSearch and perform operations in LindormSearch:

#!/usr/bin/python
# -*- coding: UTF-8 -*-
import json

import mysql.connector

# Establish a database connection. 
# Set the host parameter to the LindormTable endpoint for MySQL. 
# Set the port parameter to the port used to connect to LindormTable by using MySQL, which is 33060 by default. 
# Set the user parameter to the username used to connect to LindormTable. 
# Set passwd to the password used to connect to LindormTable. 
# Set the database parameter to the database name in LindormTable. 


connection = mysql.connector.connect(host='ld-bp1hn6yq0yb34****-proxy-sql-lindorm-public.lindorm.rds.aliyuncs.com', port=33060,user='root', passwd='test',database='default')

# Create a cursor and specify the prepared=True condition.
cursor = connection.cursor(prepared=True)

# Create a table.
sql_create_table = ("create table if not exists test_python(c1 integer, c2 integer, c3 varchar, primary key(c1))")
print(sql_create_table)
cursor.execute(sql_create_table)

# Insert data to the table.
sql_upsert = "upsert into test_python(c1, c2, c3) values(?, ?, ?)"
print(sql_upsert)

# Insert single rows to the table.
cursor.execute(sql_upsert, (1, 1, '1'))
cursor.execute(sql_upsert, (2, 2, json.dumps({"key": "value2"})))

# Insert multiple rows to the table at the same time.
data = [
    (3, 3, '3'),
    (4, 4, json.dumps({"key": "value4"})),
]
cursor.executemany(sql_upsert, data)

# Delete data from the table.
sql_delete = "delete from test_python where c1 = ?"
print(sql_delete)
cursor.execute(sql_delete, (3,))

# Modify data in the table.
sql_update = "upsert into test_python(c1, c2, c3) values(?, ?, ?)"
print(sql_update)
cursor.execute(sql_update, (1, 2, '2'))

# Query specified data in the table.
sql_select = "select * from test_python where c1 = ?"
print(sql_select)
cursor.execute(sql_select, (4,))
rows = cursor.fetchall()
print(rows)

# Query all data in the table.
sql_select_all = "select * from test_python"
print(sql_select_all)
cursor.execute(sql_select_all)
rows = cursor.fetchall()
print(rows)

# Close the cursor.
cursor.close()

# Close the connection to the database.
connection.close()