[python Learning] python connection database instance

The method of connecting MySQL, MongoDB, Redis, memcache and other databases by python

1. Python operation MySQL: for more information, please see:

[apt-get install python-mysqldb]

The code is as follows:

#! / bin/env python#-*-encoding: utf-8-*-import MySQLdbimport os# establishes a connection to the database system in the format # conn = MySQLdb.connect (host='localhost',user='root',passwd='123456',db='test',port=3306,charset='utf8') # specifies 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 operations # get operation cursor cursor = conn.cursor () # execute SQLcursor.execute (query) # to get a record Each record is returned as a tuple, returning 3 The cursor points to the second record. Result1 = cursor.fetchone () for i in result1: print i# returns the number of rows affected print cursor.rowcount# to get the specified number of records. Each record is returned as a tuple, returning 1 Magi 2. The cursor starts from 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# gets all the records, each record is returned as a tuple, and the cursor starts from the fourth record to the end. Result3 = cursor.fetchall () for i in result3: for ii in i: print ii# gets all the records. Each record is returned as a tuple, returning 3, 4, 7, 6. The cursor starts with the first record # resets the cursor position, 0 is the offset Mode=absolute | relative, which defaults to relativecursor.scroll (0makeshift trainers) result3 = cursor.fetchall () for i in result3: for ii in i: print ii# can insert data into the database by either of the following two methods: # (one) for i in range (10Power20): query2 = 'insert into T1 values ("% d") Now ()'% I cursor.execute (query2) # submits two parameters conn.rollback () # (two) rows = [] for i in range (10 insert into 20): rows.append (I) query2 = 'insert into T1 values ("% s", now ())' # executemany, the second 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 Release resource 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 scripts, passwords are easily exposed when written in scripts, so that passwords can be stored in a configuration file, such as db.conf:

The copy code is as follows:

[client]

User=root

Password=123456

Second, python operation MongoDB:

The code is as follows:

#! / bin/env python#-*-encoding: utf-8-*-import pymongoimport os# establishes a connection with the database system. When creating Connection, specify host and port parameters conn = pymongo.Connection (host='127.0.0.1',port=27017) # admin database has an account Connection-Authentication-switch Library db_auth = conn.admindb_auth.authenticate ('sa','sa') # connection Database db = conn.abc# connection Table collection = db.stu# View all table names db.collection_names () # print db.collection_names () # access the data of the table Specify the column item = collection.find ({}, {"sname": 1, "course": 1, "_ id": 0}) for rows in item: print rows.values () # access a row of data in the table print collection.find_one () # get all the 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 According to the value of a column. Pymongo.DESCENDING: reverse order; pymongo.ASCENDING: ascending order. Sort multiple columns according to sno reverse order item = collection.find (). Sort ('sno',pymongo.DESCENDING) for rows in item: print rows.values () # multiple columns item = collection.find (). Sort ([(' sno',pymongo.DESCENDING), ('Aging pyrmongo.assiding)]) # Update, the first parameter is the condition, and the second parameter is the update operation Set,%inc,$push,$ne,$addToSet,$rename and other collection.update ({"sno": 100}, {"$set": {"sno": 1010}) # update multiple rows and columns of collection.update ({"sno": 102}, {"$set": {"sno": 105, "sname": "SSSS"}}, multi=True) # delete, the first parameter is the condition, the second parameter is the delete operation. Collection.remove ({"sno": 101})''sno: student number; sname: name Course: subject db.stu.insert ({"sno": 1, "sname": "Zhang San", "course": {"A": 95, "B": 90, "C": 65, "D": 74, "E": 100}}) db.stu.insert ({"sno": 2, "sname": "Li Si", "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) ) db.stu.insert ({"sno": 5, "sname": "abc", "course": {"A": 87, "B": 70, "Z": 56, "G": 54, "H": 75}) db.stu.insert ({"sno": 6, "sname": "Yang Liu", "course": {"A": 65, "U": 80, "C": 78, "R" 75) "N": 90}}) db.stu.insert ({"sno": 7, "sname": "Chen er", "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})'

Calculate the number of collections in the Mongodb document:

The code is as follows:

Import pymongoconn = pymongo.Connection (host='127.0.0.1' Port=27017) db = conn.abc # abc document for tb_name in db.collection_names (): # cycle out each collection name Count = DB [TB _ name] .count () # calculate the number of each collection if Count > 2: # filter condition 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 ()') # variable when the collection is handled ORconn = 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) # returns print mon_dic.get ('ns'), mon_dic.get (' count')'in dictionary form

3. Python operation Redis:

The code is as follows:

#! / bin/env python#-*-encoding: utf-8-*-import redisf = 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 "= = read the stored data =" for user in alluser: print'# '.join ((user.split (':) [1], rc.hget (user,'Password'), rc.hget (user,'Email')

4. Python operation memcache:

The code is as follows:

Import memcachemc = memcache.Client (['127.0.0.1ve11211'], debug=1)

The code is as follows:

#! / usr/bin/env python#coding=utf-8import MySQLdbimport memcacheimport sysimport timedef get_data (mysql_conn): # nn = raw_input ("press string name:") mc = memcache.Client (['127.0.0.1 raw_input 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)