Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

[python Learning] python connection database instance

2025-04-01 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)06/01 Report--

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)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report