python 调 mysql

教程1:

http://www.crazyant.net/2012/06/08/python%E6%93%8D%E4%BD%9Cmysql%E5%AE%9E%E4%BE%8B%E4%BB%A3%E7%A0%81%E6%95%99%E7%A8%8B%EF%BC%88%E6%9F%A5%E8%AF%A2%E6%89%8B%E5%86%8C%EF%BC%89/

教程2:帮助库:

#-*- encoding:gb2312 -*-_
'''
Created on 2012-1-12
@author: xiaojay
'''
import MySQLdb
import MySQLdb.cursors
 
STORE_RESULT_MODE = 0
USE_RESULT_MODE = 1
 
CURSOR_MODE = 0
DICTCURSOR_MODE = 1
SSCURSOR_MODE = 2
SSDICTCURSOR_MODE = 3
 
FETCH_ONE = 0
FETCH_MANY = 1
FETCH_ALL = 2
 
class PyMysql:
    def __init__(self):
        self.conn = None
        pass
    def newConnection(self,host,port,user,passwd,defaultdb):
        """
        建立一个新连接，指定host、用户名、密码、默认数据库
        """
        self.conn = MySQLdb.Connect(host,user,passwd,defaultdb,port)
        if self.conn.open == False:
            raise None
    def closeConnnection(self):
        """
        关闭当前连接
        """
        self.conn.close()
     
    def query(self,sqltext,mode=STORE_RESULT_MODE):
        """
        作用：使用connection对象的query方法，并返回一个元组(影响行数(int),结果集(result))
        参数：sqltext：sql语句
             mode=STORE_RESULT_MODE（0） 表示返回store_result，mode=USESTORE_RESULT_MODE（1） 表示返回use_result
        返回：元组(影响行数(int),结果集(result)
        """
        if self.conn==None or self.conn.open==False :
            return -1
        self.conn.query(sqltext)
        if mode == 0 :
            result = self.conn.store_result() 
        elif mode == 1 :
            result = self.conn.use_result()
        else :
            raise Exception("mode value is wrong.")
        return (self.conn.affected_rows(),result)
     
    def fetch_queryresult(self,result,maxrows=1,how=0,moreinfo=False):
        """
        参数:result： query后的结果集合
            maxrows： 返回的最大行数
            how： 以何种方式存储结果
             (0：tuple,1：dictionaries with columnname,2：dictionaries with table.columnname)
            moreinfo 表示是否获取更多额外信息（num_fields，num_rows,num_fields）
        返回：元组（数据集，附加信息（当moreinfo=False）或单一数据集（当moreinfo=True）
        """
        if result == None : return None
        dataset =  result.fetch_row(maxrows,how)
        if moreinfo is False :
            return dataset
        else :
            num_fields = result.num_fields()
            num_rows = result.num_rows()
            field_flags = result.field_flags()
            info = (num_fields,num_rows,field_flags)
            return (dataset,info)
         
    def execute(self,sqltext,args=None,mode=CURSOR_MODE,many=False):
        """
        作用：使用游标（cursor）的execute 执行query
        参数：sqltext： 表示sql语句
             args： sqltext的参数
             mode：以何种方式返回数据集
                CURSOR_MODE = 0 ：store_result , tuple
                DICTCURSOR_MODE = 1 ： store_result , dict
                SSCURSOR_MODE = 2 : use_result , tuple
                SSDICTCURSOR_MODE = 3 : use_result , dict 
             many：是否执行多行操作（executemany）
        返回：元组（影响行数（int），游标（Cursor））
        """
        if mode == CURSOR_MODE :
            curclass = MySQLdb.cursors.Cursor
        elif mode == DICTCURSOR_MODE :
            curclass = MySQLdb.cursors.DictCursor
        elif mode == SSCURSOR_MODE :
            curclass = MySQLdb.cursors.SSCursor
        elif mode == SSDICTCURSOR_MODE :
            curclass = MySQLdb.cursors.SSDictCursor
        else :
            raise Exception("mode value is wrong")
         
        cur = self.conn.cursor(cursorclass=curclass)
        line = 0
        if many == False :
            if args == None : 
                line = cur.execute(sqltext)
            else :
                line = cur.execute(sqltext,args)
        else :
            if args == None :
                line = cur.executemany(sqltext)
            else :
                line = cur.executemany(sqltext,args)
        return (line , cur )
     
    def fetch_executeresult(self,cursor,mode=FETCH_ONE,rows=1):
        """
        作用：提取cursor获取的数据集
        参数：cursor：游标
             mode：执行提取模式
              FETCH_ONE: 提取一个； FETCH_MANY :提取rows个 ；FETCH_ALL : 提取所有
             rows：提取行数
        返回：fetch数据集
        """
        if cursor == None : 
            return
        if mode == FETCH_ONE :
            return cursor.fetchone()
        elif mode == FETCH_MANY :
            return cursor.fetchmany(rows)
        elif mode == FETCH_ALL :
            return cursor.fetchall()
         
if __name__=="__main__" :
    print help (PyMysql)

使用示例:

#-*- encoding:gb2312 -*-
import PyMysql
import os
import sys
 
DATAPATH = "../data/"
DBNAME = "sharecount_"
TABLENAME = "t_sharecount_"
 
def printFlag(filename, data, mode=0, lines=0) :
	title = "filename :" +  filename  + ", lines: " +  str(lines) + "\n"
	print title

	file = open(filename, 'w');
	file.write(title);

	if mode == 0 :
		for id, sharecount, commentcount, flag in data:
			#print "%s %d" %(id, flag)
			line = id + " " + str(flag) + "\n"
			file.write(line);
	else :
		for item in data:
			print "------------"
			for key in item.keys() :
				print key, " : ", item[key]


for db_index in range(25,50):
	#建立连接
	current_db = DBNAME + str(db_index);
	print "connect to db:", current_db
	mysql = PyMysql.PyMysql()
	mysql.newConnection(
        	host="ip", 
	        port=3345,
        	user="username", 
        	passwd="pwd", 
        	defaultdb=current_db)

	for table_index in range(0,100):
		current_table = TABLENAME + str(table_index)
		current_path = DATAPATH + current_db + "/"
		if not os.path.exists(current_path):
			os.makedirs(current_path)
		filename = current_path + current_db + "_" + current_table
		sqltext = "select * from " + current_table + " where flag != 0" 
		#sqltext = "select * from " + current_table 
		lines , res = mysql.query(sqltext, mode=PyMysql.STORE_RESULT_MODE)
		#print "start deal talbe: ", current_table, " lines: ", lines, " filename: ", filename
		data = mysql.fetch_queryresult(res, maxrows=lines, how=0, moreinfo=False)
		printFlag(filename, data, 0, lines)


	#关闭连接
	mysql.closeConnnection()
	print "db:", current_db, "done."