Python operations Database (MySQL Redis)

One, python operation MySQL database:

Database information: (e.g. 211.149.218.16 szz 123456)

Operating MySQL with Pymysql module

#操作其他数据库, install the appropriate module

Import Pymysql

ip= ' 211.149.218.16 '

port=3306

Passwd= ' 123456 '

User= ' Root '

db= ' Szz '

Conn=pymysql.connect (host=ip,user=user,port=port,passwd=passwd,db=db,charset= ' UTF8 ') #创建一个数据库连接

cur = conn.cursor (cursor=pymysql.cursors.dictcursor) #在连接上创建一个游标, using cursors to manipulate the database; Specifies the type of cursor in parentheses as a dictionary (specified as a dictionary for easy value), does not specify a tuple by default

Sql= ' INSERT into Nhy (id,name,sex) VALUE (1, ' HN ', ' NV '); ' #写sql语句

Sql2= ' select * from Nhy; '

Cur.execute (SQL) #执行sql语句

Cur.execute (SQL2)

Row_1 = Cur.fetchone () #获取查询结果的第一条数据, returns a tuple that gets a row each time it is executed

Row_2 = Cur.fetchmany (3) # Gets the first n rows of data, at which time the cursor is behind the n data

Row_3 = Cur.fetchall () # Gets all the data, when the cursor is in the last face

PS : (

The difference between Fetchall and Fetchone:

If the select itself takes more than one data:

Cursor.fetchone (): will take only the top first result, return a single tuple such as (' id ', ' title '), and then use Cursor.fetchone () multiple times to get the next result until it is empty.

Cursor.fetchall (): Returns all results, returning two-dimensional tuples, such as (' id ', ' title '), (' id ', ' title '),

If the select itself takes only one piece of data:

Cursor.fetchone (): Returns only one result, returning a single tuple such as (' id ', ' title ').

Cursor.fetchall (): will also return all results, returning two-dimensional tuples, such as (' id ', ' title '),

）

Cur.scroll (n,mode= ' absolute ') #移动游标, mode=absolute or relative, absolute refers to moving the cursor to the nth row of data, relative means to move the cursor to the position of the row +n row where the current cursor is located

Cur.scroll (0,mode= ' absolute ') #把游标移动到最前面, no matter where the cursor is

Conn.commit () #ddl (insert delete update) DCL DML statement must be committed before it takes effect, or the newly created or modified data cannot be saved

Cur.close () #关闭游标

Conn.close () #关闭数据库连接

======eg: Write the function of MySQL:

Import Pymysql

def operationdb (host,user,passwd,port,db,sql,charset= ' UTF8 '): #传参

Pymysql.connect (HOST=HOST,USER=USER,PASSWD=PASSWD, Port=port,db=db,charset=charset) #建立连接

cur = conn.cursor (cursor=pymysql.cursors.dictcursor) #建立游标

Cur.execute (SQL) #执行sql语句

If Sql.startswith (' select '): #判断字符串是不是以什么开头

Res=cur.fetchall () #判断如果是select语句, return the execution result directly, no commit.

Else

Conn.commit () #如果是insert Delete Update and other statements, you need to submit

Res=00 #如果不是select语句, return 00

Cur.close ()

Conn.close ()

return res

SS=OPERATIONDB (

Host= ' 211.149.147.233 ', user= ' Byz ', passwd= ' 123456 ', db= ' Byz ', sql= ' insert into HN (id,name,sex) VALUE ("Houning", " Female ");

#外面用单引号, use double quotation marks in the SQL statement, #调用这个函数, and pass the parameter, assign the value of this function to SS

Print (ss) #打印出ss, which is the res in the function body

Operating Redis:

Redis is a nosql type of database (installation of Redis Desktop Manager) with key-value pairs, key-value pairs of data in memory, fast read and write speeds, and a redis module for Python operation Redis, and PIP installation.

#====== The following is the action string type, key is String type

Import Redis

R = Redis. Redis (host= ' 127.0.0.1 ', port=6379,db=0,password= ' 123456 ') #指定连接redis的ip, port number, and which database, password

R.set (' name ', ' houning ') #设置key和value; name is a field, houning is a value, and if the value of name already exists, this will replace the original value;

R.setnx (' name2 ', ' value ') #设置的name的值, set if name does not exist

R.setex (' Name3 ', ' value ') #设置的name的值, and the expiration time, after 30 seconds, name3 This key will be automatically invalidated (because the data are in memory, to release), such as login to a Web page, 10 minutes later, you need to log in again, This is where Redis's expiration time is used.

R.mset (k1= ' v1 ', k2= ' v2 ') #批量设置多个值, cannot set expiration time

N=r.get (' name ') #获取name的值, Redis gets all the bytes types;

Print (N.decode ()) # The Redis store is a string, but the bytes type is obtained, and the Decode method is to convert the result of the bytes type to a string type.

Print (Json.loads (N.decode ())) #把字符串类型再转换成字典或list类型的

Print (R.mget (' K1 ', ' K2 ')) #批量获取key

R.delete (' name ') #删除值

R.delete (' K1 ', ' K2 ') #批量删除

Print (R.keys ()) #获取所有的key

#====== below is the operation hash type key

Import Redis

R = Redis. Redis (host= ' 127.0.0.1 ', port=6379,db=0,password= ' 123456 ') #指定连接redis的ip, port number, and which database, password

R.hset (' Hname ', ' hn ', ' 123456 ') #设置哈希类型hname的值, Hname is outside the KEY,HN is inside the key,123456 is inside the value of key

R.hset (' Hname ', ' ZG ', ' 456789 ') #继续给hname设置哈希类型的值

R.hsetnx (' Hname ', ' key2 ', ' value23 ') #为hname设置里面的key和value, hname This key does not exist when it is set

R.hmset (' Hname ', {' K1 ': ' v1 ', ' K2 ': ' V2 '}) #批量设置哈希类型hname的key和value

R.hget (' Hname ', ' HN ') #获取hname里hn的值, take Out is 123456

Print (R.hgetall (' Hname ')) #获取hname里面所有的key和value, is a dictionary

R.hdel (' Hname ', ' HN ') #删除哈希类型hname里, the specified HN value

Print (R.keys ()) #获取所有的key

Ps:

R.set (' user:houning ', ' 123456 ') #设置key时, if with a colon, a user's folder will be automatically built on Redis, and the key and value values stored in the folder are: user_houning,123456

R.hset (' session:test ', ' houning ', ' 123456 ') #哈希类型的同上, automatically creates a session folder with the value "test", and the key and value inside are: houning,123456

====== eg: write operations on Redis functions

def Operationredis (host,passwd,k,v=false,port=6379,db=0): #因为set值时, key and value are required, so set V to the default value parameter

R=redis. Redis (HOST=HOST,PASSWORD=PASSWD,PORT=PORT,DB=DB)

If V: #如果传了v的值, set key and value

R.set (K,V)

res=88

else: #没有传v的值

Res=r.get (k). Encode () #转换成字符串类型

return res

Python operations Database (MySQL Redis)