Python操作数据库两例

访问MySQL批量调用URL

1.安装pymssql；

2.python脚本访问数据库，获取指定字段信息；

3.字段信息作为参数，拼接URL；

#!/usr/bin/env python


import pymssql
import httplib
import time

conn = pymssql.connect(host='xxx.xxx.xxx.xxx', user='xx', password='xxxx', database='xxx')
cur = conn.cursor()

cur.execute('SELECT UserID,Cover.CoverID FROM CoverDetail INNER JOIN Cover ON CoverDetail.CoverID = Cover.CoverID WHERE SoundPath LIKE \'20%\'')

row = cur.fetchone()

count = 1

while row:
    httpconn = httplib.HTTPConnection("xxx.xxx.xxx.xxx:xxxx")
    httpconn.request("GET", "/covers/del?uid="+str(int(row[0]))+"&coverid="+str(int(row[1])))
    rps = httpconn.getresponse()
    print str(count)+" "+rps.read()
    row = cur.fetchone()
    time.sleep(0.1)
    count = count + 1

conn.close()

读取文本批量更新MySQL

1.从制定目录读取文本文件，根据文件内容更新Mysql数据库相应字段信息；

2.依赖MySQLdb库，单独下载安装；

Linux下安装方法：yum install MySQL-python
Windows下需下载安装包

3.MySQL使用UTF-8编码，在数据库连接串中增加相应配置

#!/usr/bin/python
#-*- coding:utf-8 -*-

import MySQLdb
import time
import os

def comment_update(commentid):
    try:
        conn=MySQLdb.connect(host='xxx.xxx.xxx.xxx',user='xx',passwd='xxxxx',db='xxxx',port=xxxx,charset='utf8')
        cur=conn.cursor()
        if cur.execute('update comment set content = "xxxxx" where id = ' + commentid) == 1:
            conn.commit()
            cur.close()
            conn.close()
            return 0
        else:
            return 1
        
    except MySQLdb.Error,e:
        #print "Mysql Error %d: %s" % (e.args[0], e.args[1])
        return 1
        
if __name__ == '__main__':

    while 1:
        
        if os.path.exists('CommentID.txt'):
            time.sleep(5)
            os.rename('CommentID.txt','CommentID_process.txt')
        else:
            for i in range(0,30):
                print time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time())) + " Countdown " + str(30-i)
                time.sleep(1)
            continue

        if os.path.exists('CommentResult.txt'):
            os.remove('CommentResult.txt')
        
        commentid_object = open('CommentID_process.txt', 'r')
        commentresult_object=open('CommentResult.txt','w+')
        
        try:
            for line in commentid_object:
                if comment_update(line) == 0:
                    print time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time())) +" "+ line.replace("\n","")+" processed successfully"
                    commentresult_object.write(time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time())) +" "+ line.replace("\n","")+" processed successfully \n")
                else:
                    print time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time())) +" "+ line.replace("\n","")+" processed failed"
                    commentresult_object.write(time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time())) +" "+ line.replace("\n","")+" processed failed \n")   
        finally:
            commentid_object.close()
            commentresult_object.close()

            os.rename('CommentID_process.txt','CommentID_'+time.strftime('%Y%m%d%H%M%S',time.localtime(time.time()))+'.txt')