Skip to main content

Connect to seekdb by using MySQL-connector-python

This topic describes how to connect to and use seekdb by using the Python driver. The driver used in this topic is MySQL-connector-python.

Prerequisites​

  • You have installed Python 2.7 or Python 3.x and pip.
  • You have installed seekdb.

Procedure​

  1. Obtain the connection string of seekdb.
  2. Install the MySQL-connector-python driver.
  3. Create a test.py file and fill in the database connection information.
  4. Run the test.py file.

Step 1: Obtain the connection string of seekdb​

Contact the deployment personnel or administrator of seekdb to obtain the corresponding database connection string.

mysql -h$host -P$port -u$user_name -p$password -D$database_name

Parameter description:

  • $host: the IP address for connecting to seekdb. Replace this parameter with the actual IP address. You can also use the local IP address 127.0.0.1.
  • $port: the port for connecting to seekdb. Replace this parameter with the actual port. The default port is 2881. You can customize the port when you deploy seekdb.
  • $database_name: the name of the database to be accessed.
  • $user_name: the username of the account. Format: username.
  • $password: the password of the account.

For more information about the connection string, see Connect to seekdb by using a MySQL client.

Here is an example:

mysql -hxxx.xxx.xxx.xxx -P2881 -uroot -p****** -Dtest

Step 2: Install the MySQL-connector-python driver​

MySQL Connector/Python is an official driver provided by MySQL for connecting to a MySQL database. It is written in Python and allows you to connect to and operate on a MySQL database from Python. MySQL Connector/Python supports Python 2.7 and Python 3.x.

Open the command prompt or PowerShell terminal and run the following command to install the MySQL-connector-python driver.

pip3 install mysql-connector-python

After the installation is complete, run the following command to verify whether the installation was successful:

pip3 list | grep mysql-connector-python

Step 3: Create an application​

Step 3: Create a test.py file and fill in the database connection information​

Based on the information in Step 1: Obtain the connection string of seekdb, create a test.py file and fill in the database connection information.

  1. Create a file named test.py.

  2. Fill in the following content in the test.py file and modify the database connection information as needed.

    Here is an example of the content of the test.py file:

    import mysql.connector

    # Database connection information
    config = {
        'host': 'xxx.xxx.xxx.xxx',
        'port': 2881,
        'user': 'root',
        'password': '******',
        'database': 'test'
    }

    # Create a database connection
    conn = mysql.connector.connect(**config)
    cursor = conn.cursor()

    # Create a database
    cursor.execute("CREATE DATABASE IF NOT EXISTS test")

    # Use the database
    cursor.execute("USE test")

    # Create a table
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS test_table (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        age INT
    )
    """)

    # Insert data
    cursor.execute("""
    INSERT INTO test_table (name, age) VALUES
    ('John', 20),
    ('Lucy', 25),
    ('Tom', 30)
    """)

    # Update data
    cursor.execute("""
    UPDATE test_table SET age = 26 WHERE name = 'Lucy'
    """)

    # Query data
    cursor.execute("SELECT * FROM test_table")
    rows = cursor.fetchall()
    for row in rows:
        print(row)

    # Close the connection
    cursor.close()
    conn.close()

Step 4: Run the test.py file​

Open the command prompt or PowerShell terminal, go to the directory where the test.py file is located, and run the test.py file to query data and output the results.

  1. Go to the directory where the test.py file is located.

    Here is an example:

    cd D:\demo\demo

  2. Run the test.py file.

    Here is an example:

    python test.py

    The returned result is as follows:

    (1, 'John', 20)
    (2, 'Lucy', 26)
    (3, 'Tom', 30)

Error handling​

When you connect to seekdb by using MySQL-connector-python, you may encounter various errors. The following are some common errors and their solutions:

  1. Connection error: If you cannot connect to the database, check whether the connection parameters, including the hostname, port, username, password, and database name, are correct.

  2. Permission error: If you encounter a permission-related error, ensure that the user has sufficient permissions to perform the required operations.

  3. SQL syntax error: If the SQL statement has a syntax error, check whether the syntax of the SQL statement is correct.

  4. Data type error: If the data type of the inserted data does not match the table definition, ensure that the data type of the inserted data is correct.

In the code, you can use the try-except statement to capture and handle these errors, ensuring that the program can handle errors gracefully without crashing. You can also use the logging module to record error information for easier debugging and troubleshooting.

References​

Contents

Edit this page