Design of data processing associated with database based on python+mysql+redis cache
1. Add a table
CREATE TABLE tb_signin (
Id INT
User_name VARCHAR (10)
Signin_num INT
Signin_time DATETIME
Gold_coin INT
);
INSERT INTO tb_signin
VALUES (1, 'ma', 0, NULL, 0)
(2, 'he', 0, NULL, 0)
(3, 'yu', 0, NULL, 0)
(4, 'hai', 0, NULL, 0)
(5, 'fang', 0, NULL, 0)
2. Design of redis cache key value.
Key value
Table name: primary key value: column name column value
Or:
Table name: primary key value: column value 1: column name 1
Example: to store the check-in times of a person with an id of 1 (assuming 5) in redis, you can do the following:
Set ('tb_signin_rank:1:signin_num', 5)
Like a database, other values can be obtained through the primary key
3. Data processing mode of redis associated database:
As shown in the figure, first determine whether there is a cache (usually based on key), and if so, read from the cache, otherwise read from the database and update the cache
Applicable scenarios: low real-time requirements for data, less frequent updates
As shown in the following figure, first write to redis and then write to the database regularly using daemons and other methods
As shown in the following figure, first write to the database, and then update to the cache
#
Import ConfigParserimport sysimport redisimport MySQLdb__name__ =: pool=redis.ConnectionPool (=, =, =) r=redis.Redis (= pool) config=ConfigParser.ConfigParser (): dbcon=MySQLdb.connect (=, =) MySQLdb.Error,e:, e sys.exit (): db_cursor=dbcon.cursor () id (,): db_cursor.execute Id) db_cursor.execute () r.zincrby (, id,) e: (% e) db_cursor.execute () db_cursor.close () () id (,): result=r.zscore (, id) result:: db_cursor=dbcon.cursor () db_cursor.execute ( Id) result=db_cursor.fetchone () [] r.zadd (, id,result) e:% e db_cursor.close (): () result= (result) (% (id,result))
# # #
Zadd: the command is used to add one or more member elements and their fractional values to an ordered set
Zscore: the command returns an ordered set of members in key scores. Returns nil if the member does not exist in the sorted collection or the key does not exist.
