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 version 8.0.11 of mysql-connector-python. You can also run the pip install mysql-connector-python==8.0.11 command to install it.

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 Lindorm Wide Table SQL Address. Remove the trailing colon and port number :33060. For more information about how to obtain the endpoint, see View connection addresses. 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 of the MySQL protocol for LindormTable. The value is fixed at 33060.
   user	If you forget your user password, you can modify it in the LindormTable cluster management system. For more information, see Modify a user password.
   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)
   

Code examples

You can use mysql-connector-python to connect to LindormTable from Python in two modes:

• Direct connection mode: This mode is suitable for single operations or infrequent access. A connection is created and closed for each operation.

• Connection pool mode: This mode is suitable for high-frequency access. It improves performance and reduces resource overhead by reusing connections.

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

import mysql.connector

# Open a database connection.
# host: The endpoint for connecting to LindormTable using the MySQL protocol.
# port: The port for connecting to LindormTable using the MySQL protocol. The default value is 33060.
# user: The username for connecting to LindormTable.
# passwd: The password that corresponds to the username.
# database: The name of the database 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. Set prepared=True.
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.
sql_upsert = "upsert into test_python(c1, c2, c3) values(?, ?, ?)"
print(sql_upsert)

# Execute a single insert statement.
cursor.execute(sql_upsert, (1, 1, '1'))
cursor.execute(sql_upsert, (2, 2, json.dumps({"key": "value2"})))

# Perform a batch write to insert two rows of data at a time.
sql_upsert_batch = ("upsert into test_python(c1, c2, c3) values(?, ?, ?), (?, ?, ?)")
cursor.execute(sql_upsert_batch, (3, 3, '3' , 4, 4, json.dumps({"key": "value4"})))

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

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

# Query specific data.
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.
connection.close()

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

import mysql.connector
from mysql.connector import pooling

# Create a connection pool.
# pool_name: The name of the connection pool.
# pool_size: The size of the connection pool. This is the maximum number of connections in the pool. Modify this value as needed.
# host: The endpoint for connecting to LindormTable using the MySQL protocol.
# port: The port for connecting to LindormTable using the MySQL protocol. The default value is 33060.
# user: The username for connecting to LindormTable.
# passwd: The password that corresponds to the username.
# database: The name of the database in LindormTable.
connection_pool = pooling.MySQLConnectionPool(
    pool_name="mypool",
    pool_size=20,
    host='11.166.XX.X',
    port=33060,
    user='root',
    password='root',
    database='default',
)

# Get a connection from the connection pool.
connection = connection_pool.get_connection()

# Create a cursor. Set prepared=True.
cursor = connection.cursor(prepared=True)

# Drop the table.
sql_drop_table = "drop table if exists test_python"
print(sql_drop_table)
cursor.execute(sql_drop_table)

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

# Execute a single insert statement.
sql_upsert = "insert into test_python(c1, c2, c3) values(?, ?, ?)"
print(sql_upsert)
cursor.execute(sql_upsert, (1, 1, '1'))
cursor.execute(sql_upsert, (2, 2, '2'))


# Perform a batch write to insert three rows of data at a time.
sql_upsert_batch = "insert into test_python(c1, c2, c3) values(?, ?, ?), (?, ?, ?), (?, ?, ?)"
cursor.execute(sql_upsert_batch, (3, 3, '3', 4, 4, '4', 5, 5, '5'))

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

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

# Query specific data.
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. The connection is returned to the connection pool instead of being permanently closed.
connection.close()