Python-Operating database

1. Working with MySQL

Python3 operation MySQL, need to import third-party module "Pymysql", use "Pip install Pymysql" can be installed, Python2 need to import MYSQLDB module,

Take Python3 as an example to illustrate:

1 ImportPymysql2ip='192.168.10.65'3port=33064Passwd='Root'5User='Root'6db='Test'7 8 #establishing a database connection9conn = Pymysql.connect (host=ip,user=user,port=port,passwd=passwd,db=DBTen, charset='UTF8') One #Creating Cursors ACur= Conn.cursor (cursor=pymysql.cursors.DictCursor) -sql ='INSERT INTO Nhy (id,name,sex) VALUE (5, "PJB", "female");' -SQL2 ='select * from Nhy;' the #Execute SQL - Cur.execute (SQL2) - #Get all data - Print(Cur.fetchall ()) + #get a row of data - Print(Cur.fetchone ()) + #get data in absolute position ACur.scroll (5,mode='Absolute') at #get data in a relative position -Cur.scroll (5,mode='relative') - #Submit, INSERT, delete, update must be submitted to take effect - Conn.commit () - #Close Cursors - cur.close () in #Close Connection -Conn.close ()

The following is a function to operate the MySQL database, recorded, convenient for future use of the direct access

1 ImportPymysql2 3 defOp_mysql (host,user,passwd,db,sql,port=3306,charset='UTF8'):4conn =Pymysql.connect (5host=host,user=user,passwd=passwd,port=port,db=db,charset=CharSet6     )7Cur =conn.cursor (pymysql.cursors.DictCursor) #以字典形式获取数据8 cur.execute (SQL)9     ifSql.startswith ('Select'):Tenres =Cur.fetchall () One     Else: A Conn.commit () -res = 88 - cur.close () the conn.close () -     returnRes

2. Operating Redis

Redis is a NoSQL database, a non-relational database, where data is stored in memory and can be read and written very quickly.

Python3 operation of the Redis database requires the installation of the Redis module, which can be installed using PIP install Redis

1 ImportRedis2IP ='192.168.10.19'3Port = 63794db = 155passwd ='123456'6 #Connect to Redis7R = Redis. Redis (host=ip,port=port,db=db,password=passwd)8 9 #set is a new string type, and if the new data exists, the value is updatedTenR.set ('name', [1,2,3,4]) One #can set the expiration time of key AR.setex ('a','111', 30) - #Batch Setting values -R.mset (k1='v1', k2='v2') the  - #get query k value, if key does not exist return none -Name = R.get ('name') - Print(R.get ('name')) + #the data obtained in Redis is bytes type and needs to be converted into a string using the. Decode method to continue the Operation - Print(Name.decode ()) + #get key in bulk A Print(R.mget ('K1','K2')) at  - #Delete a key -R.delete ('name') - #Bulk Delete -R.delete ('K1','K2') -  in #Operation Hash Type -R.hset ('Hname','Key','value')#value of the set hash type toR.HSETNX ('Hname','Key2','Value23')#set the key and value to name Hname, and the difference above is that the key does not exist when it is set +R.hmset ('Hname',{'K1':'v1','K2':'v2'})#Bulk Set Hash type key and value -R.hget ('name','Key')#gets the value of the hash type the Print(R.hgetall ('Hname'))#get all the keys and value in this name *R.hdel ('Hname','Key')#Delete the value specified in the name of the hash type $ Print(R.keys ())#get all the keysPanax Notoginseng  - #New Folder theR.set ('User:niuhanyang','haha')#folder name User +R.hset ('Session:byz_sys','Chendonggua','12345')#Folder name Session

Here is a simple function that uses set mode, which is recorded to make it easy to use in the future.

1 ImportRedis2 defOp_redis (host,passwd,k,v=false,time=30,db=15,port=6379):3R = Redis. Redis (host=host,password=passwd,db=db,port=Port)4     ifV:5 R.setex (k,v,time)6res = 887     Else:8res =R.get (k)9         ifRes:Tenres =Res.decode () One         Else: Ares ="" -     returnRes

Python-Operating database