Python operation MySQL and Redis (advanced)

python operation MySQL, Redisphase One, MySQL business

It is mainly used to deal with data with large operation and high complexity. For example, in the Personnel Management system, you delete a person, you need to delete the basic information of the person, but also to delete the information related to the person, such as mailbox, articles and so on, so that these database operation statements constitute a transaction!

Transactions can be used to maintain the integrity of the database, to ensure that batches of SQL statements are either fully executed or not executed at all.

Transactions are used to manage INSERT, UPDATE, DELETE statements

A transaction must meet 4 conditions (ACID): atomicity (atomicity, or indivisibility), consistency (consistency), isolation (isolation, also known as independence), persistence (durability).

Atomicity: All operations in a transaction (transaction) are either complete or not complete and do not end up in the middle of a session. When an error occurs during execution, the transaction is rolled back (Rollback) to the state before the transaction begins, as if the transaction had never been executed.

Consistency: The integrity of the database is not compromised until the transaction begins and after the transaction has ended.

Isolation: The ability of a database to read and write and modify its data at the same time for multiple concurrent transactions, which prevents inconsistencies in data resulting from cross-execution when multiple transactions are executing concurrently.

Persistence: After the transaction is finished, modifications to the data are permanent, even if the system failure is not lost.

Start transaction;    # Open Transaction ? # inserting data insert into account value (1, +);? commit;   # Commit a transaction ? rollback;   # rolling back

Stage Two, python operation MySQL

pip install pymysqlpip install Redisworkon PY3ENV?CD/etc/mysql/mysql.conf.d/sudo vim mysqld.cnf?0.0.0.0#all IPs have access to the127.0.0.1#only local access to? CD/etc/redis/sudo vim redis.conf? Virtual machine interpreter does not need to change the port, directly fill3306 6379#to operate the database, first establish a connection with the database, configure the Pymysql.connect connection database:Con =Pymysql.connect (Host='Host', Port=Port, User='User name', Password='Password', DB='Database name', CharSet='UTF8')Print(con)?#Defining Cursorscursor =con.cursor () cursor.execute ('Show Databases')#Execute SQLone = Cursor.fetchone ()#Take out a piece of dataall = Cursor.fetchall ()#Remove All dataPrint(one)Print(All)?#Build TableTB =" "CREATE TABLE TB (ID int primary key auto_increment,name char )" "Cur.execute (TB)?#Insertrow = Cursor.execute ("INSERT INTO Test (name,sex) VALUES (%s,%s)", ('Shiwei','male'))?#Updaterow = Cursor.execute ("Update test set Name= ' Zhang San'WHERE id =%s ", (2,))?#DeleteCursor.execute ('Delete from user where id=%s', (1,) )?#Close Connection#transactions, when data is changedCur.execute ('Commit') Con.commit ()#Submit ThingsCursor.close ()#Close CursorsCon.close ()#Close Connection?## Federated QueryUnion =" "Select S.name, c.name,d.name from ' student ' s left join ' select ' se on se.s_id = s.s_idleft Join course C on se.c_ ID = c.idleft Join Department d on s.dept_id = D.idorder by s.name;" "#Cursor.execute (Union)#find =cursor.fetchall ()

Phase III, Python operation Redis

1. Module installation

Pip Install Redis

2. Basic operation

#Create a connectionRe = Redis. Redis (host='127.0.0.1', port='55555')## TestRe.set ('Num', 15)Print(Re.get ('Num'))?## set ChineseRe.set ('name','Zhang San')Print(Re.get ('name'). Decode ('UTF8') )?## # Most of the commands are the same as the Redis operationdifference: Re.expire ('user_name', 20)#Add an Expiration timeRe.ttl ('user_name')## # can't see negative numbers -1-2Re.mset (A=1, b=2)## Mget A B with key valueRE.INCR ('Read_count')## incr can add parameters instead of Incrby get Read_countRE.DECR ()## DECR can add parameters instead of Decrby? lrange list_1 0-1Lpush list_12 3 4) 5 3Re.lrem ('list_1', 3, 0)## num put it in the backRe.hmset ()#multiple inserts, with dictionariesRe.hmset ('Users', {'name':'Shiwei',' Age': 18})

Python operation MySQL and Redis (advanced)