Python Operations Database

Source: Internet
Author: User

One, install the database

Pip Install Pymysql

Pip Install Redis

Two, Python operation MySQL Database

Import Pymysql
def op_mysql (host,user,password,db,sql,port=3306,charset= ' UTF8 '): #通用数据库操作
conn = Pymysql.connect (Host=host,user=user,
Password=password,
Port=port,
charset=charset,db=db) #建立连接
cur = conn.cursor (cursor=pymysql.cursors.dictcursor) #建立游标
cur.execute (SQL) #执行sql statement
Sql_start = Sql[:6].upper () #取sql前6个字符串, determine what type of SQL statement it is
if sql_start== ' SELECT ':
res = Cur.fetchall ()
# res = Cur.fetchone () #只有一条数据, then use Fetchone, more than one data then use Fetchall
Else: #update Insert Delete operation
conn.commit () #提交
res = ' OK '
cur.close () #关闭游标
conn.close () #关闭连接
return res
Call Function:
sql = ' SELECT * from Bt_stu limit 5; '
SQL2 = ' Update my_user Set money = '%s ' where username= '%s '; '% (Actual_account,user) #注意引号的位置
res = Op_mysql (
Host= ' 211.149.218.16 ',
User= ' Jxz ', password= ' 123456 ', #port这里一定要写int类型
port=3306,db= ' Jxz ', charset= ' UTF8 ', SQL=SQL2)
Print (RES)

Third, Python operations Redis database

Start Redis Service: Enter Redis-server, cmd, Redis-server.exe redis.windows.conf

Operating Redis

1,string type

 r = Redis. Redis (host= ' 127.0.0.1 ', port=6379,db=2) #连上redis  
r.set (' nhy_session ', ' 201801211505 ') #set数据
print (R.get (' nhy_session '). Decode ()) #redis里面取出来的数据都是bytes类型的, so use the. Decode method to turn to a string
. Span style= "font-family:"microsoft yahei"; Font-size:16px ">print (R.get (' nhy_session '))
r.delete (' nhy_session ') #删除一个
r.setex (' nhy ', ' hahah ', ') #可以指定key的失效时间, Unit is seconds '
# set get delete Setex are all for string type k-v

2,hash type (no time period)

r = Redis. Redis (host= ' 127.0.0.1 ', port=6379,db=2) #连上redis
R.hset (' Sessions ', ' nhy ', ' 123456 ') #插入数据
R.hset (' Sessions ', ' YBQ ', ' 1234562 ')
R.hset (' Sessions ', ' xsr ', ' 1234561 ')
Print (R.hget (' Sessions ', ' XSR ')) #获取数据
redis_data = R.hgetall (' sessions ') #获取到hash类型里面所有的数据
print (redis_data)
all_data = {}
For k,v in Redis_data.items (): #把hash类型里面所有的数据转成正常的字典
k = K.decode ()
v = v.decode ()
All_data[k]=v
print (all_data)
Output Result:

B ' 1234561 '
{b ' ybq ': B ' 1234562 ', B ' xsr ': B ' 1234561 ', B ' nhy ': B ' 123456 '}
{' YBQ ': ' 1234562 ', ' xsr ': ' 1234561 ', ' nhy ': ' 123456 '}

3, Hierarchy type (with folder)

 import redis  
r = Redis. Redis (host= ' 127.0.0.1 ', port=6379,db=2) #连上redis
r.set (' txz:llq ', ' llq123 ') #
r.set (' txz:llq12 ', ' llq1234 ') #
r.set (' txz:llq13:llq13 ', ' llq1234 ')
print (R.keys ()) #获取所有的key
print (R.keys (' txz* ')) #以txz开头的key
print (R.type (' Sessions ')) #获取key的类型
Output Result:

[B ' txz:llq12 ', b ' txz:llq ', b ' nhy_session ', B ' sessions ', B ' txz:llq13:llq13 ']
[B ' txz:llq12 ', b ' txz:llq ', B ' txz:llq13:llq13 ']
B ' Hash '

4, working with Redis database string type Common functions

Import Redis
def Op_redis (host,password,k,v=none,port=6379,db=0): #操作redis database String type general function
r = Redis. Redis (HOST=HOST,PASSWORD=PASSWORD,PORT=PORT,DB=DB)
If V:
R.set (k,v)
res = ' OK '
Else:
res = r.get (k)
If res: #这里是判断有没有get到数据
res = Res.decode ()
Else:
res = None
return res

Four, Redis database backup

Import Redis
# 1, establish two Redis connections
#1, src
# 2, Target
#2, get to All Key,kyes ()
# 3, determine the type of key, string hash
Import Redis
Src_redis = Redis. Redis (host= ' 211.149.218.16 ', port=6379,password= ' 123456 ', db=2) #连上redis
Target_redis = Redis. Redis (host= ' 211.149.218.16 ', port=6379,password= ' 123456 ', db=14) #连上redis
For key in Src_redis.keys ():
if Src_redis.type (key) = = B ' String ': #判断key的类型 because the Redis data is binary, so this is also used bytes
v = src_redis.get (key) #先获取到原来的数据
Target_redis.set (key,v) #再set到新的里面
Else:
all_hash_data = Src_redis.hgetall (key) #先获取到hash类型里面所有的数据
for K,v in All_hash_data.items (): #因为hash类型的获取到之后是一个字典, so here's The Loop dictionary
Target_redis.hset (key,k,v) #key是外面的大key, K is inside the small k,v is small k corresponding value

Python Operations Database

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.