Python Learning Note (ix): manipulating the database

When we write code, we often operate databases, additions and deletions, databases have many types, relational databases and non-relational databases, here we introduce how Python operates MySQL, Redis and MongoDB.

One,python operation MySQL database python3 operation MySQL data need to install a third-party module, PYMYSQL, use pip install Pymysql installation, in Python2 is MySQLdb module, There are no MYSQLDB modules in Python3, so use Pymysql.

    ImportPymysql#Create a connection, specify the IP address of the database, account number, password, port, database to manipulate, character setconn = Pymysql.connect (host='127.0.0.1', port=3306, user='Root', passwd='123456', db='Data', charset='UTF8')    #Creating Cursorscursor =conn.cursor ()#executes SQL and returns the number of affected rowsEffect_row = Cursor.execute ("Update students set name = ' Niuhy ' where id = 1;")    #executes SQL and returns the number of rows affected    #Effect_row = cursor.execute ("Update students set name = ' Niuhy ' where id =%s;", (1,))    #executes SQL and returns the number of rows affectedEffect_row = Cursor.executemany ("INSERT into students (Name,age) values (%s,%s);", [("Andashu", 18), ("12345", 20)])    #Execute SELECT statementCursor.execute ("SELECT * from students;")    #gets the first data for the query result, and returns a tupleRow_1 =Cursor.fetchone ()#get top N rows of dataRow_2 = Cursor.fetchmany (3)    #Get all dataRow_3 =Cursor.fetchall ()#Commit, or you cannot save the newly created or modified dataConn.commit ()#get the latest self-increment IDnew_id =Cursor.lastrowidPrint(new_id)#Close Cursorscursor.close ()#Close ConnectionConn.close () above, get the return result is a tuple, if you want to get the result is a dictionary type, you can use the following actionsImportPymysql#Create a connection, specify the IP address of the database, account number, password, port, database to manipulate, character setconn = Pymysql.connect (host='127.0.0.1', port=3306, user='Root', passwd='123456', db='Data', charset='UTF8')    #Creating Cursorscursor =conn.cursor () cursor= Coon.cursor (Cursor=pymysql.cursors.dictcursor)#you need to specify the type of cursor, dictionary type    #Execute SQLCursor.execute ("select * from user;")    #gets the return result, this time the return result is a dictionaryres = Cursor.fetchone ()#returns a piece of data if the result is more than one.    Print(res) Res2= Cursor.fetchall ()#all the data is returned together

second, the operation of Redis Redis is a nosql type of database with data in memory, fast read and write speeds, Python operation Redis using Redis module, PIP installation

    ImportRedis R= Redis. Redis (host='127.0.0.1', port=6379,db=0)#Specify the port and IP to connect to Redis and which databaseR.set ('name','value')#value of Set String typeR.SETNX ('name2','value')#sets the value of name, if name does not exist, is setR.setex ('Name3','value', 3)#set the name value, and the time-out period, after which the key will automatically expireR.mset (k1='v1', k2='v2')#Batch Setting valuesR.get ('name')#Get Value    Print(R.mget ('K1','K2'))#get key in bulkR.delete ('name')#Delete ValueR.delete ('K1','K2')#Bulk Delete    #====== The following is the operation hash typeR.hset ('Hname','Key','value')#value of the set hash typeR.hset ('Hname','Key1','value2')#value of the set hash typeR.HSETNX ('Hname','Key2','Value23')#set the key and value to name Hname, and the difference above is when key does not exist    #will be setR.hmset ('Hname',{'K1':'v1','K2':'v2'})#Bulk Set Hash type key and valueR.hget ('name','Key')#gets the value of the hash type    Print(R.hgetall ('Hname'))#get all the keys and value in this nameR.hdel ('Hname','Key')#Delete the value specified in the name of the hash type    Print(R.keys ())#get all the keys

Python Learning Note (ix): manipulating the database