How to connect python to databases such as MySQL, MongoDB, Redis, and memcache

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

I,Python for MySQL: For details, see:
[Apt-get install python-mysqldb]

Copy codeThe 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 to the database system in the format
# Conn = MySQLdb. connect (host = 'localhost', user = 'root', passwd = '000000', db = 'test', port = 123456, 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'

# Retrieve operation cursor
Cursor = conn. cursor ()

# Execute SQL
Cursor.exe cute (query)

# Obtain a record. Each record is returned as a tuples. 3 is returned, and the cursor refers to 2nd records.
Result1 = cursor. fetchone ()
For I in result1:
Print I
# Return the number of affected rows
Print cursor. rowcount

# Obtain the specified number of records. Each record is returned as a tuples. 1 and 2 are returned. The cursor starts from 2nd records and the cursor points to 4th records.
Result2 = cursor. fetchmany (2)
For I in result2:
For ii in I:
Print ii

# Obtain all records. Each record is returned as a tuples. The values 3, 4, 7, and 6 are returned. The cursor starts from 4th records to the end.
Result3 = cursor. fetchall ()
For I in result3:
For ii in I:
Print ii

# Obtain all records. Each record is returned as a tuples. The values 3, 4, 7, and 6 are returned. The cursor starts from 1st records.
# Reset the cursor position. 0 indicates the offset, mode = absolute | relative. The default value is relative.
Cursor. scroll (0, mode = 'absolute ')
Result3 = cursor. fetchall ()
For I in result3:
For ii in I:
Print ii

# You can insert data to the database in either of the following ways:
# (One)
For I in range (10, 20 ):
Query2 = 'insert into t1 values ("% d", now () '% I
Cursor.exe cute (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, and 2nd parameters are variables.
Cursor.exe cute.pdf (query2, rows)
# Submit
Conn. commit ()

# Selecting a database
Query3 = 'select id from dba_hospital'
# Reselect Database
Conn. select_db ('chunuhua ')

Cursor.exe cute (query3)

Result4 = cursor. fetchall ()
For I in result4:
For ii in I:
Print ii
# Directly execute the statement without defining the query:
Cursor.exe cute ("set session binlog_format = 'mixed '")

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

'''
+ ------ + --------------------- +
| Id | modifyT |
+ ------ + --------------------- +
| 3 | 00:00:00 |
| 1 | 00:00:00 |
| 2 | 00:00:00 |
| 3 | 00:00:00 |
| 4 | 17:04:54 |
| 7 | 17:05:36 |
| 6 | 17:05:17 |
+ ------ + --------------------- +

'''

Note: In the script, the password is easily exposed in the script, so that you can save the password in a configuration file, such as db. conf:

Copy codeThe Code is as follows:
[Client]
User = root
Password = 123456

II,Python operations on MongoDB:

Copy codeThe 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 = '192. 0.0.1 ', port = 127)

# Admin database has an account, connection-Authentication-switch Database
Db_auth = conn. admin
Db_auth.authenticate ('sa ', 'sa ')
# Connecting to a database
Db = conn. abc

# Connecting tables
Collection = db. stu

# View all table names
Db. collection_names ()
# Print db. collection_names ()

# Access table data, specifying Columns
Item = collection. find ({}, {"sname": 1, "course": 1, "_ id": 0 })
For rows in item:
Print rows. values ()

# Accessing a table's data row
Print collection. find_one ()

# Obtain 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)

# Obtain the number of rows
Print collection. find (). count ()
Print collection. find ({"sno": 100 })

# Sort by the value of a column. Pymongo. DESCENDING: reverse; pymongo. ASCENDING: ASCENDING. Sort by sno in reverse order
Item = collection. find (). sort ('sno', pymongo. DESCENDING)
For rows in item:
Print rows. values ()

# Multi-column sorting
Item = collection. find (). sort ([('sno', pymongo. DESCENDING), ('A', pymongo. ASCENDING)])

# Update. The first parameter is a condition, and the second parameter is an update operation. For example, $ set, % inc, $ push, $ ne, $ addToSet, and $ rename.
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 a condition, and the second parameter is a delete operation.
Collection. remove ({"sno": 101 })

'''
Sno: Student ID; 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": "", "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": "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:

Copy codeThe Code is as follows:
Import pymongo

Conn = pymongo. Connection (host = '192. 0.0.1 ', port = 127)
Db = conn. abc # abc document
For tb_name in db. collection_names (): # loop out the names of each set
Count = db [tb_name]. count () # calculate the number of each set
If Count> 2: # filter Condition
Print tb_name + ':' + str (Count)

'''
Conn = pymongo. Connection (host = '192. 0.0.1 ', port = 127)
Db = conn. abc
For tb_name in db. collection_names ():
Print tb_name + ':'
Exec ('print '+ 'db.' + tb_name + '. count ()') # processing method of variable when set

OR

Conn = pymongo. Connection (host = '192. 0.0.1 ', port = 127)
Db = conn. abc
For tb_name in db. collection_names ():
Mon_dic = db. command ("collStats", tb_name) # returned in dictionary form
Print mon_dic.get ('ns'), mon_dic.get ('Count ')

'''

3,Python operations on Redis:

Copy codeThe 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 = 127, db = 15)
Rc. hset ('name: '+ UserName, 'email', Email)
Rc. hset ('name: '+ UserName, 'Password', Pwd)
F. close ()

Alluser = rc. keys ('*')
# Print alluser
Print "= "data =========================================="
For user in alluser:
Print '#'. join (user. split (':') [1], rc. hget (user, 'Password'), rc. hget (user, 'email ')))

IV,Python for memcache operations:

Copy codeThe Code is as follows:
Import memcache
Mc = memcache. Client (['127. 0.0.1: 100'], debug = 1)

Copy codeThe 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: 100'], debug = 1)
T1 = time. time ()
Value = mc. get ('zhoujinyay ')
If value = None:
T1 = time. time ()
Print t1
Query = "select company, email, sex, address from uc_user_offline where realName = 'zhoujinya '"
Cursor = mysql_conn.cursor ()
Cursor.exe cute (query)
Item = cursor. fetchone ()
T2 = time. time ()
Print t2
T = round (t2-t1)
Print "from mysql cost % s sec" % t
Print item
Mc. set ('zhoujinyay', 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 = '2017. 0.0.1 ', user = 'root', passwd = '000000', db = 'member', port = 123456, charset = 'utf8 ')
Get_data (mysql_conn)