Help Center/ GaussDB/ M-Compatibility Developer Guide(Centralized)/ Application Development Guide/ PyMySQL-based Development/ Development Process
Updated on 2025-10-23 GMT+08:00
View PDF
Share

Development Process

Before the operation, you need to create the database and user by referring to Creating an M-compatible Database and User.

  1. Set GUC parameters and start the database.
  2. Run the pip command to install the PyMySQL library (version 1.0.2 or later). If RSA is used, run the pip command to install the cryptography library and set the GUC parameter plat_compat_allow_public_key_retrieval to on.
  1. Load the PyMySQL library.

    Before creating a database connection, you need to import PyMySQL. 
    1
    		 
    import  pymysql

  2. Connect to a database.

    Connect to the database in non-SSL mode.

    1. Use the pymysql.connect function to obtain the connection object.
    2. Use the connection object to create a cursor object.

    Connect to the database in SSL mode (TLS 1.2 or later).

    When you use PyMySQL to connect to M-compatible, you can enable SSL to encrypt the communication between the client and server. To enable SSL, you must have the client certificate, and private key files. For details on how to obtain these files, see related documents and commands of OpenSSL.

    1. Use the .ini file (the configparser package of Python can parse this type of configuration file) to save the configuration information about the database connection.
    2. Add SSL connection parameters ca, key and cert to the connection options.
      1. ssl_disabled: specifies whether to enable SSL communication.
      2. ssl_cert: Path of the client public key.
      3. ssl_ca: Path of the CA certificate for SSL communication.
      4. ssl_key: Path of the client private key.
      5. ssl_verify_cert: specifies whether the client verifies the server certificate.
      6. ssl_verify_identity: specifies whether the client verifies the server address.
    3. Use the pymysql.connect function to obtain the connection object.
    4. Use the connection object to create a cursor object.

  3. Run SQL statements.

    1. Construct an operation statement and use %s as a placeholder. During execution, PyMySQL will replace the placeholder with the parameter value. You can add the RETURNING clause to obtain the automatically generated column values.
    2. Use the cursor.execute method to execute one row of SQL statement, and use the cursor.executemany method to execute multiple rows of SQL statements.

  4. Process the result set.

    1. cursor.fetchone(): fetches the next row in a query result set and returns a sequence. If no data is available, null is returned.
    2. cursor.fetchall(): fetches all remaining rows in a query result and returns a list. An empty list is returned when no rows are available.
    3. cursor.fetchmany(size=None): This routine obtains the size row query result row and returns a list. If size is not set, all remaining rows are returned. An empty list is returned when no rows are available.

    For database-specific data types, such as tinyint, the corresponding columns in the query result are character strings.

  5. Disable the connection.

    After you complete required data operations in a database, close the database connection. Call the close method such as connection.close() to close the connection.

    This method closes the database connection and does not automatically call commit(). If you just close the database connection without calling commit() first, changes will be lost.