python连接mysql、mongodb、redis、memcache等数据库的方法

用python写脚本也有一段时间了,经常操作数据库(mysql),现在就整理下对各类数据库的操作,如后面有新的参数会补进来,慢慢完善。

一,python 操作 mysql:详情见:【apt-get install python-mysqldb】

代码如下:

#!/bin/env python# -*- encoding: utf-8 -*-#——————————————————————————-# purpose: example for python_to_mysql# author: zhoujy# created: 2013-06-14# update: 2013-06-14#——————————————————————————-import mysqldbimport os

#建立和数据库系统的连接,格式#conn = mysqldb.connect(host=’localhost’,user=’root’,passwd=’123456′,db=’test’,port=3306,charset=’utf8′)

#指定配置文件,确定目录,或则写绝对路径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语句query = ‘select id from t1’

#获取操作游标cursor = conn.cursor()

#执行sqlcursor.execute(query)

#获取一条记录,每条记录做为一个元组返回,返回3,游标指到第2条记录。result1 = cursor.fetchone()for i in result1: print i#返回影响的行数 print cursor.rowcount

#获取指定数量记录,每条记录做为一个元组返回,返回1,2,游标从第2条记录开始,游标指到第4条记录。result2 = cursor.fetchmany(2)for i in result2: for ii in i: print ii

#获取所有记录,每条记录做为一个元组返回,返回3,4,7,6,游标从第4条记录开始到最后。result3 = cursor.fetchall()for i in result3: for ii in i: print ii

#获取所有记录,每条记录做为一个元组返回,返回3,4,7,6,游标从第1条记录开始#重置游标位置,0为偏移量,mode=absolute | relative,默认为relativecursor.scroll(0,mode=’absolute’)result3 = cursor.fetchall()for i in result3: for ii in i: print ii

#以下2种方法都可以把数据插入数据库:#(one)for i in range (10,20): query2 = ‘insert into t1 values(“%d”,now())’ %i cursor.execute(query2) #提交 conn.rollback()#(two)rows = []for i in range (10,20): rows.append(i)query2 = ‘insert into t1 values(“%s”,now())’#executemany 2个参数,第2个参数是变量。cursor.executemany(query2,rows)#提交conn.commit()

#选择数据库query3 = ‘select id from dba_hospital’#重新选择数据库conn.select_db(‘chushihua’)

cursor.execute(query3)

result4 = cursor.fetchall()for i in result4: for ii in i: print ii#不定义query,直接执行:cursor.execute(“set session binlog_format=’mixed'”)

#关闭游标,释放资源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 |+——+———————+

”’

注意:在脚本中,密码写在脚本里面很容易暴露,这样可以用一个配置文件的方式来存密码,如db.conf:

代码如下:

[client]user=rootpassword=123456

二,python 操作 mongodb:

代码如下:

#!/bin/env python# -*- encoding: utf-8 -*-#——————————————————————————-# purpose: example for python_to_mongodb# author: zhoujy# created: 2013-06-14# update: 2013-06-14#——————————————————————————-import pymongoimport os

#建立和数据库系统的连接,创建connection时,指定host及port参数conn = pymongo.connection(host=’127.0.0.1′,port=27017)

#admin 数据库有帐号,连接-认证-切换库db_auth = conn.admindb_auth.authenticate(‘sa’,’sa’)#连接数据库db = conn.abc

#连接表collection = db.stu

#查看全部表名称db.collection_names()#print db.collection_names()

#访问表的数据,指定列item = collection.find({},{“sname”:1,”course”:1,”_id”:0})for rows in item: print rows.values()

#访问表的一行数据print collection.find_one()

#得到所有的列for rows in collection.find_one(): print rows

#插入collection.insert({“sno”:100,”sname”:”jl”,”course”:{“d”:80,”s”:85}})#或u = dict(sno=102,sname=’zjjj’,course={“d”:80,”s”:85})collection.insert(u)

#得到行数print collection.find().count()print collection.find({“sno”:100})

#排序,按照某一列的值。pymongo.descending:倒序;pymongo.ascending:升序。按照sno倒序item = collection.find().sort(‘sno’,pymongo.descending) for rows in item: print rows.values()

#多列排序item = collection.find().sort([(‘sno’,pymongo.descending),(‘a’,pymongo.ascending)])

#更新,第一个参数是条件,第二个参数是更新操作,$set,%inc,$push,$ne,$addtoset,$rename 等collection.update({“sno”:100},{“$set”:{“sno”:101}})#更新多行和多列collection.update({“sno”:102},{“$set”:{“sno”:105,”sname”:”ssss”}},multi=true)

#删除,第一个参数是条件,第二个参数是删除操作。collection.remove({“sno”:101})

”’sno:学号;sname:姓名;course:科目

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”:”赵五”,”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}})

”’

计算mongodb文档中各集合的数目:

代码如下:

import pymongo

conn = pymongo.connection(host=’127.0.0.1′,port=27017)db = conn.abc #abc文档for tb_name in db.collection_names(): #循环出各集合名 count = db[tb_name].count() #计算各集合的数量 if count > 2: #过滤条件 print tb_name + ‘:’ + str(count)

”’conn = pymongo.connection(host=’127.0.0.1′,port=27017)db = conn.abcfor tb_name in db.collection_names(): print tb_name + ‘:’ exec(‘print ‘ + ‘db.’+tb_name+’.count()’) #变量当集合的处理方式

or

conn = pymongo.connection(host=’127.0.0.1′,port=27017)db = conn.abcfor tb_name in db.collection_names(): mon_dic=db.command(“collstats”, tb_name) #以字典形式返回 print mon_dic.get(‘ns’),mon_dic.get(‘count’)

”’

三,python 操作 redis:

代码如下:

#!/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 alluserprint “===================================读出存进去的数据===================================”for user in alluser: print ‘ # ‘.join((user.split(‘:’)[1],rc.hget(user,’password’),rc.hget(user,’email’)))

四,python 操作 memcache:

代码如下:

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

代码如下:

#!/usr/bin/env python#coding=utf-8import mysqldbimport memcacheimport sysimport 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 valueif __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)