Connect to seekdb by using SQLAlchemy

This topic describes how to connect to seekdb by using SQLAlchemy and perform basic database operations, including creating tables, inserting data, updating data, and querying data.

Prerequisites

You have installed Python 3.x and pip.
You have installed seekdb.

Procedure

Obtain the connection string of seekdb.
Install the SQLAlchemy library.
Create a test.py file and enter the database connection information.
Run the test.py file.

Step 1: Obtain the connection string of seekdb

Contact the seekdb deployment personnel or administrator 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 it with the actual IP address. You can use the local IP address or 127.0.0.1.
$port: the port for connecting to seekdb. Replace it with the actual port number. The default value is 2881, which can be customized when you deploy seekdb.
$database_name: the name of the database to be accessed.
$user_name: the username for connecting to the database. Format: username.
$password: the password for 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 SQLAlchemy library

SQLAlchemy is one of the most popular ORM (object-relational mapping) libraries in Python. It provides SQL toolkits and an ORM system to simplify database operations. SQLAlchemy supports multiple database backends, including MySQL, PostgreSQL, and SQLite.

Open the command prompt or PowerShell terminal and run the following command to install the SQLAlchemy library.

pip install sqlalchemy mysqlclient

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

pip list | grep SQLAlchemy

info

SQLAlchemy is a powerful ORM library that provides two usage methods: Core and ORM. Core provides an SQL expression language, while ORM provides object-relational mapping functionality. This topic mainly uses the ORM method.

Step 3: Create a `test.py` file and enter the database connection information

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

Create a file named test.py.

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

Here is an example of the test.py file:

from sqlalchemy import create_engine, Column, Integer, String, MetaData, Table
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

# Database connection information
DB_USER = 'test_user001@mysql001'
DB_PASSWORD = '******'
DB_HOST = 'xxx.xxx.xxx.xxx'
DB_PORT = 2881
DB_NAME = 'test'

# Create a database connection URL
DATABASE_URL = f"mysql+mysqldb://{DB_USER}:{DB_PASSWORD}@{DB_HOST}:{DB_PORT}/{DB_NAME}"

# Create an engine
engine = create_engine(DATABASE_URL)

# Create a base class
Base = declarative_base()

# Define a model
class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String(50), nullable=False)
    age = Column(Integer)

    def __repr__(self):
        return f"<User(id={self.id}, name='{self.name}', age={self.age})>"

# Create tables
Base.metadata.create_all(engine)

# Create a session
Session = sessionmaker(bind=engine)
session = Session()

# Insert data
new_users = [
    User(name='John', age=20),
    User(name='Lucy', age=25),
    User(name='Tom', age=30)
]
session.add_all(new_users)
session.commit()

# Update data
user = session.query(User).filter_by(name='Lucy').first()
if user:
    user.age = 26
    session.commit()

# Query data
users = session.query(User).all()
for user in users:
    print(user)

# Close the session
session.close()

Step 4: Run the `test.py` file

Open the command prompt or PowerShell terminal, run the test.py file, query data, and output the results.

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

Here is an example:
```
cd D:\demo\demo
```

Run the test.py file.

Here is an example:

python test.py

The returned result is as follows:

<User(id=1, name='John', age=20)>
<User(id=2, name='Lucy', age=26)>
<User(id=3, name='Tom', age=30)>

Error handling

When you use SQLAlchemy to connect to seekdb, you may encounter various errors. The following are some common errors and their solutions:

Connection error: If you cannot connect to the database, check whether the connection parameters are correct, including the host name, port, username, password, and database name.
Permission error: If you encounter a permission-related error, ensure that the user has sufficient permissions to perform the required operations.
SQL syntax error: If the SQL statement has a syntax error, check whether the SQL statement is correctly written.
Data type error: If the data type of the inserted data does not match the table definition, ensure that the data type is correct.

In the code, you can use the try-except statement to capture and handle these errors to ensure that the program can handle errors gracefully instead of crashing directly. Additionally, you can use the logging module to record error information for easier debugging and troubleshooting.

References

For more information about how to connect to seekdb, see Overview of connection methods.

Prerequisites​

Procedure​

Step 1: Obtain the connection string of seekdb​

Step 2: Install the SQLAlchemy library​

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

Step 4: Run the test.py file​

Error handling​

References​

Contents