Python's way to connect MySQL, MongoDB, Redis, Memcache, and other databases _python

Use Python to write a script for some time, often operate the database (MySQL), now on the collation of various types of database operations, such as the following new parameters will be filled in, slowly improve.

One,python operation MySQL: See details:
"Apt-get Install Python-mysqldb"

Copy Code code 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

#建立和数据库系统的连接, format
#conn = MySQLdb.connect (host= ' localhost ', user= ' root ', passwd= ' 123456 ', db= ' test ', port=3306,charset= ' UTF8 ')

#指定配置文件, 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语句
query = ' SELECT id from t1 '

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

#执行SQL
Cursor.execute (query)

#获取一条记录, each record is returned as a tuple, returns 3, and the cursor refers to the 2nd record.
RESULT1 = Cursor.fetchone ()
For I in RESULT1:
Print I
#返回影响的行数
Print Cursor.rowcount

#获取指定数量记录, each record is returned as a tuple, returns 1, 2, the cursor begins with the 2nd record, and the cursor refers to the 4th record.
RESULT2 = Cursor.fetchmany (2)
For I in RESULT2:
For II in I:
Print II

#获取所有记录, each record is returned as a tuple, returns 3,4,7,6, and the cursor starts from the 4th record to the last.
RESULT3 = Cursor.fetchall ()
For I in RESULT3:
For II in I:
Print II

#获取所有记录, each record is returned as a tuple, returns 3,4,7,6, and the cursor begins with the 1th record
#重置游标位置, 0 for offsets, Mode=absolute | Relative, default is relative
Cursor.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 parameters, and the 2nd argument is a variable.
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, direct execution:
Cursor.execute ("Set session binlog_format= ' mixed '")

#关闭游标, releasing 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 in the script, so you can use a configuration file to save the password, such as db.conf:

Copy Code code as follows:

[Client]
User=root
password=123456

Second,python operation MongoDB:

Copy Code code 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

#建立和数据库系统的连接, when creating connection, specify host and port parameters
conn = Pymongo. Connection (host= ' 127.0.0.1 ', port=27017)

#admin database has account number, connection-authentication-switch Library
Db_auth = Conn.admin
Db_auth.authenticate (' sa ', ' sa ')
#连接数据库
db = Conn.abc

#连接表
Collection = Db.stu

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

#访问表的数据, specify the column
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": +, "sname": "JL", "course": {"D": "S": 85}})
#或
U = dict (sno=102,sname= ' zjjj ', course={"D": *, "S": 85})
Collection.insert (U)

#得到行数
Print Collection.find (). Count ()
Print Collection.find ({"Sno": 100})

#排序, according to the value of a column. Pymongo. Descending: Reverse; Pymongo. Ascending: Ascending. Follow Sno Reverse
item = Collection.find (). Sort (' Sno ', Pymongo. Descending)
For rows in item:
Print rows.values ()

#多列排序
item = Collection.find (). Sort ([' Sno ', Pymongo. Descending), (' A ', Pymongo. Ascending)])

#更新, 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}})
#更新多行和多列
Collection.update ({"Sno": 102},{"$set": {"sno": ", sname": "SSSS"}},multi=true)

#删除, the first argument is a condition, and the second parameter is a delete operation.
Collection.remove ({"Sno": 101})

'''
Sno: School number; sname: Name; Course: Subject

Db.stu.insert ({"Sno": 1, "sname": "John", "course": {"A": "," B ":", "C": "," "D": "," "E": 100}})
Db.stu.insert ({"Sno": 2, "sname": "Dick", "course": {"A": "The", "B": "," X ":", "Y": "," Z ": 95}})
Db.stu.insert ({"Sno": 3, "sname": "Zhao Five", "course": {"A": "" B ":", "F": "," G ":", "H": 80}})
Db.stu.insert ({"Sno": 4, "sname": "Zhoujy", "course": {"A": "," B ":", "C": "," T ":", "Y": 85}})
Db.stu.insert ({"Sno": 5, "sname": "abc", "Course": {"A": "," "B": "," Z ":", "G": "," H ": 75}})
Db.stu.insert ({"Sno": 6, "sname": "Yang Six", "course": {"A": "," U ":", "C": "," "R": "," N ": 90}})
Db.stu.insert ({"Sno": 7, "sname": "Chen II", "course": {"A": "", "M": "," N ":", "" S ":", "89}})
Db.stu.insert ({"Sno": 8, "sname": "Zhoujj", "course": {"P": "A", "B": "," J ":", "K": "," L ": 80}})
Db.stu.insert ({"Sno": 9, "sname": "CCC", "course": {"Q": "", "B": "," C ":", "V": "," U ": 85}})

'''

Calculate the number of collections in the MongoDB document:

Copy Code code as follows:

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.abc
For 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.abc
For Tb_name in Db.collection_names ():
Mon_dic=db.command ("Collstats", Tb_name) #以字典形式返回
Print mon_dic.get (' ns '), Mon_dic.get (' count ')

'''

Three,python operation Redis:

Copy Code code 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 operation Memcache:

Copy Code code as follows:

Import Memcache
MC = Memcache. Client ([' 127.0.0.1:11211 '],debug=1)

Copy Code code 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)