1. Introduction
In application development, data management, such as data storage and retrieval, is a fundamental requirement. Regardless of the type or scale of the application, data storage management is essential. In most business applications, the two most commonly used types of databases are NoSQL databases, such as Redis, and relational databases, such as MySQL.
This article will detail how to implement operations on Redis and MySQL databases using Python.
2. Database Operations
2.1. Redis Database
Redis is a widely used NoSQL database that supports a variety of data types: strings, lists, sets, sorted sets, hashes, etc. It can support a wide range of application scenarios, including caching, counters, distributed locks, and publish/subscribe.
Official website:https://redis.io/
Next, let’s see how to integrate and operate the Redis service in Python.
2.1.1. Environment Setup
To operate Redis in Python, a specific library needs to be installed.
pip install redis
# If the default installation fails, you can specify a mirror source for installation
pip install redis -i https://pypi.doubanio.com/simple
2.1.2. Basic Usage
Using the Redis client library just installed, here is a basic connection example:
import redis
# Create Redis connection (default parameters)
r = redis.Redis( host='localhost', # Redis server address port=6379, # Port, default 6379 db=0, # Database number, default 0 password=None, # Password (omit if no password) decode_responses=True # Automatically decode bytes to string)
# Test if the connection is successful
try: response = r.ping() print("Connection successful" if response else "Connection failed")
except redis.exceptions.ConnectionError: print("Unable to connect to Redis")
Basic operations:
# Basic operations
# Set key-value pair
r.set('name', '小王')
# Get value
name = r.get('name')
print(name)
2.1.3. Using Connection Pool
In practical applications, the cost of network connections is relatively high. Creating a new connection each time and destroying it after use can lead to resource waste and affect application performance. Therefore, we can use a pooling solution like this:
import redis
from redis import ConnectionPool
# Create connection pool
pool = ConnectionPool( host='localhost', port=6379, max_connections=10, # Maximum number of connections decode_responses=True)
# Get connection from pool
r_pool = redis.Redis(connection_pool=pool)
# Use connection
r_pool.set('key', '小王')
print(r_pool.get('key'))
2.2. MySQL Database
2.2.1. Environment Setup
MySQL is a widely used relational database responsible for application data persistence. How do we operate MySQL in Python? I have a local environment with a spider database; let’s see how to operate it:
Install MySQL client:
pip install pymysql
2.2.2. Basic Usage
We can establish a connection and perform queries on the product table in the spider database:
import pymysql
# Create connection
conn = pymysql.connect( host='127.0.0.1', port=3310, user='root', password='admin', database='spider', charset='utf8mb4' # Recommended character set)
# Basic CRUD operations
cur = conn.cursor()
# Query data
sql = 'SELECT * FROM good'
cur.execute(sql)
rows = cur.fetchall()
for row in rows: print(row[1])
Other operations are similar to querying data, mainly involving writing SQL scripts and managing transactions. Like this:
# Commit transaction, rollback transaction
try: conn.start_transaction() cursor.execute("INSERT INTO users (name) VALUES ('小王')") cursor.execute("UPDATE accounts SET balance = balance - 100 WHERE user_id = 1") conn.commit() # Commit transaction
except Error as e: conn.rollback() # Rollback transaction print(f"Transaction failed: {e}")