How to connect Python to MySQL, MongoDB, Redis, memcache and other databases

I have been writing scripts in Python for some time, and I often operate databases (MySQL). Now I will sort out the operations on various databases. If there are new parameters later, I will add them in and gradually improve them.

1. Python operation MySQL: For details, see:
[apt-get install python-mysqldb]

The code is as follows:

#! /bin/env python
# -*- encoding: utf-8 -*-
#————————– ————————————————– —
# Purpose: example for python_to_mysql
# Author: zhoujy
# Created: 2013-06-14
# update: 2013-06-14
#—– ————————————————– ————————–
import MySQLdb
import os

#Establish a connection with the database system, format
#cOnn= MySQLdb.connect(host=’localhost’,user=’root’,passwd=’123456′,db=’test’,port=3306, charset=’utf8′)

#Specify the configuration file, determine the directory, or write the absolute path
cwd = os.path.realpath(os.path.dirname(__file__))
db_cOnf= os.path.join(cwd, ‘ db.conf’)
cOnn= MySQLdb.connect(read_default_file=db_conf,host=’localhost’,db=’test’,port=3306,charset=’utf8′)

#SQL statement to be executed
query = ‘select id from t1’

#Get the operation cursor
cursor = conn.cursor()

#Execute SQL
cursor.execute(query)

#Get a record, each record is returned as a tuple, 3 is returned, and the cursor points to the second record.
result1 = cursor.fetchone()
for i in result1:
print i
#Return the number of affected rows
print cursor.rowcount

#Get the specified number of records, return each record as a tuple, return 1, 2, the cursor starts from the 2nd record, and the cursor points to the 4th record.
result2 = cursor.fetchmany(2)
for i in result2:
for ii in i:
print ii

#Get all records, return each record as a tuple, return 3, 4, 7, 6, the cursor starts from the 4th record to the end.
result3 = cursor.fetchall()
for i in result3:
for ii in i:
print ii

#Get all records, return each record as a tuple, return 3, 4, 7, 6, the cursor starts from the 1st record
#Reset the cursor position, 0 is the offset, mode ＝absolute | relative, the default is relative
cursor.scroll(0,mode=’absolute’)
result3 = cursor.fetchall()
for i in result3:
for ii in i:
print ii

#The following two methods can be used to insert data into the database:
#(one)
for i in range (10,20):
query2 = ‘insert into t1 values(“%d” ,now())’ %i
cursor.execute(query2)
#Submit
conn.rollback()
#(two)
rows = []
for i in range (10,20):
rows.append(i)
query2 = ‘insert into t1 values(“%s”,now())’
#executemany 2 parameters, the 2nd parameter is a variable.
cursor.executemany(query2,rows)
#Submit
conn.commit()

#Select database
query3 = ‘select id from dba_hospital’
#Reselect database
conn.select_db(‘chushihua’)

cursor.execute(query3)

result4 = cursor.fetchall()
for i in result4:
for ii in i:
print ii
#Do not define query, execute directly:
cursor.execute(” set session binlog_format=’mixed'”)

#Close the cursor and release resources
cursor.close()

”’
+——+———————+
| id | modifyT |
+——+———————+
| 3 | 2010-01-01 00:00:00 |
| 1 | 2010-01-01 00:00:00 |
| 2 | 2010-01-01 00:00:00 |
| 3 | 2010-01-01 00:00:00 |
| 4 | 2013-06-04 17:04:54 |
| 7 | 2013-06-04 17:05:36 |
| 6 | 2013-06-04 17:05:17 |
+——+———————+

”’

Note: In the script, the password is easily exposed if it is written in the script. In this way, you can use a configuration file to store the password, such as db.conf:

The code is as follows:

[client ]
user=root
password=123456

Second, Python operation MongoDB:

The code is as follows:

#! /bin/env python
# -*- encoding: utf-8 -*-
#————————– ————————————————– —
# Purpose: example for python_to_mongodb
# Author: zhoujy
# Created: 2013-06-14
# update: 2013-06-14
#—– ————————————————– ————————–
import pymongo
import os

#Establish a connection with the database system. When creating a Connection, specify the host and port parameters
cOnn= pymongo.Connection(host=’127.0.0.1′,port=27017)

#admin database has an account, connect-authentication-switch library
db_auth = conn.admin
db_auth.authenticate(‘sa’,’sa’)
#Connect to database
db = conn .abc

#Connection table
collection = db.stu

#View all table names
db.collection_names()
#print db.collection_names()

#Access table data, specify columns
item = collection.find({},{“sname”:1,”course”:1,”_id”:0})
for rowsin item:
print rows.values()

#Access a row of data in the table
print collection.find_one()

#Get all columns
for rows in collection.find_one():
print rows

#Insert
collection.insert({“sno”:100,”sname”:”jl”,”course”:{“D”:80,”S”:85}})
# Or
u = dict(sno=102,sname=’zjjj’,course={“D”:80,”S”:85})
collection.insert(u)

#Get the number of rows
print collection.find().count()
print collection.find({“sno”:100})

#Sort by the value of a certain column. pymongo.DESCENDING: reverse order; pymongo.ASCENDING: ascending order. According to sno reverse order
item = collection.find().sort(‘sno’,pymongo.DESCENDING)
for rows in item:
print rows.values()

#Multiple column sort
item = collection.find().sort([(‘sno’,pymongo.DESCENDING),(‘A’,pymongo.ASCENDING)])

#Update, the first parameter is the condition, the second parameter is the update operation, $set,%inc,$push,$ne,$addToSet,$rename, etc.
collection.update({“sno” :100},{“$set”:{“sno”:101}})
#Update multiple rows and columns
collection.update({“sno”:102},{“$set”: {“sno”:105,”sname”:”SSSS”}},multi=True)

#Delete, the first parameter is the condition, and the second parameter is the delete operation.
collection.remove({“sno”:101})

”’
sno: student number; sname: name; course: subject

db.stu.insert({“sno”:1,”sname”:”张三”,”course”:{“A”:95,”B”:90,”C”:65,”D “:74,”E”:100}})
db.stu.insert({“sno”:2,”sname”:”李思”,”course”:{“A”:90,”B “:85,”X”:75,”Y”:64,”Z”:95}})
db.stu.insert({“sno”:3,”sname”:”Zhao Wu”,” course”:{“A”:70,”B”:56,”F”:85,”G”:84,”H”:80}})
db.stu.insert({“sno”: 4,”sname”:”zhoujy”,”course”:{“A”:64,”B”:60,”C”:95,”T”:94,”Y”:85}})
db.stu.insert({“sno”:5,”sname”:”abc”,”course”:{“A”:87,”B”:70,”Z”:56,”G”:54, “H”:75}})
db.stu.insert({“sno”:6,”sname”:”杨六”,”course”:{“A”:65,”U”:80, “C”:78,”R”:75,”N”:90}})
db.stu.insert({“sno”:7,”sname”:”陈二”,”course”:{ “A”:95,”M”:68,”N”:84,”S”:79,”K”:89}})
db.stu.insert({“sno”:8,”sname “:”zhoujj”,”course”:{“P”:90,”B”:77,”J”:85,”K”:68,”L”:80}})
db.stu. insert({“sno”:9,”sname”:”ccc”,”course”:{“Q”:85,”B”:86,”C”:90,”V”:87,”U”: 85}})

”’

Count the number of collections in the Mongodb document:

The code is as follows:

import pymongo

cOnn= pymongo.Connection(host=’127.0.0.1′,port=27017)
db = conn.abc #abc document
for tb_name in db.collection_names(): #Loop out each collection name
Count = db[tb_name].count() #Calculate the number of each collection
if Count > 2: #Filter conditions
print tb_name + ‘:’ + str(Count)

”’
cOnn= pymongo.Connection(host=’127.0.0.1′,port=27017)
db = conn.abc
for tb_name in db.collection_names():
print tb_name + ‘:’
exec(‘print ‘ + ‘db.’+tb_name+’.count()’) #How to handle variables as collections

OR

cOnn= pymongo.Connection(host=’127.0.0.1′,port=27017)
db = conn.abc
for tb_name in db.collection_names():
mon_dic=db.command( “collStats”, tb_name) #Return in dictionary form
print mon_dic.get(‘ns’),mon_dic.get(‘count’)

”’

Three, Python operation Redis:

The code is as follows:

#! /bin/env python
# -*- encoding: utf-8 -*-
#————————– ————————————————– —
# Purpose: example for python_to_mongodb
# Author: zhoujy
# Created: 2013-06-14
# update: 2013-06-14
#—– ————————————————– ————————–

import redis

f = open(‘aa.txt’)
while True:
line = f.readline().strip().split(‘ # ‘)
if line == [” ]:
break
UserName,Pwd,Email = line
# print name.strip(),pwd.strip(),email.strip()
rc = redis.StrictRedis(host=’ 127.0.0.1′,port=6379,db=15)
rc.hset(‘Name:’ + UserName,’Email’,Email)
rc.hset(‘Name:’ + UserName,’Password’ ,Pwd)
f.close()

alluser = rc.keys(‘*’)
#print alluser
print “========================== =========Read the stored data==================================== “
for user in alluser:
print ‘ # ‘.join((user.split(‘:’)[1],rc.hget(user,’Password’),rc.hget(user,’ Email’)))

Four, Python operates memcache:

The code is as follows:

import memcache
mc = memcache.Client([‘127.0.0.1:11211’],debug=1)

The code is as follows:

#! /usr/bin/env python
#coding=utf-8
import MySQLdb
import memcache
import sys
import time

def get_data(mysql_conn):
# nn = raw_input(“press string name:”)
mc = memcache.Client([‘127.0.0.1:11211’],debug=1)
t1 =time.time()
value = mc.get(‘zhoujinyia’)
if value == None:
t1 = time.time()
print t1
query = ” select company,email,sex,address from uc_user_offline where realName = ‘zhoujinyia'”
cursor= mysql_conn.cursor()
cursor.execute(query)
item = cursor.fetchone()
t2 = time.time()
print t2
t = round(t2-t1)
print “from mysql cost %s sec” %t
print item
mc.set(‘zhoujinyia ‘,item,60)
else :
t2 = time.time()
t=round(t2-t1)
print “from memcache cost %s sec” %t
print value
if __name__ ==’__main__’:
mysql_cOnn= MySQLdb.connect(host=’127.0.0.1′,user=’root’,passwd=’123456′,db=’member’,port=3306 ,charset=’utf8′)
get_data(mysql_conn)
print t1
query = “select company,email,sex,address from uc_user_offline where realName = ‘zhoujinyia'”
cursor= mysql_conn.cursor()
cursor.execute(query)
item = cursor .fetchone()
t2 = time.time()
print t2
t = round(t2-t1)
print “from mysql cost %s sec” %t
print item
mc.set(‘zhoujinyia’,item,60)
else :
t2 = time.time()
t=round(t2-t1)
print “from memcache cost %s sec” %t
print value
if __name__ ==’__main__’:
mysql_cOnn= MySQLdb.connect(host=’127.0.0.1′,user=’root’,passwd=’123456′,db =’member’,port=3306,charset=’utf8′)
get_data(mysql_conn)