Python访问Sql Server

转载：http://www.52harry.com/program/python/2011-11-28/649.html

 见示例代码：

注意：

当对数据库进行updata、insert、delete后，一定要调用

commit的方法，否则不会更新到数据库，上次因为这个原因

害我找了一个多小时的问题，暴汗

view plaincopy to clipboardprint?

#-*- coding:utf-8 -*-  

from walle.resultbackup.database import pymssql  

SQLSERVER = '192.168.28.193SQLEXPRESS' 

USER = 'sa' 

PASSWORD = 'walle' 

DATABASE = 'walle' 

g_Conn = None 

def openDB():  

    global g_Conn  

    g_Conn = pymssql.connect(host=SQLSERVER,user=USER,password=PASSWORD,database=DATABASE)  

      

def closeDB():  

    global g_Conn  

    g_Conn.close()  

def query(sql):  

    try:  

        openDB()  

        global g_Conn  

        cursor = g_Conn.cursor()  

        ret = cursor.execute(sql)  

        rows = cursor.fetchall()  

        return rows  

    except:  

        raise Exception, 'query database has wrong' 

    finally:  

        closeDB()  

          

def insert_many(sql, params):  

    ''''' 

    新增多条记录， 

    params: 

        sql:执行的sql语句 

        parmas:一个list，listitem为一个参数元组 

    ''' 

    try:  

        openDB()  

        global g_Conn  

        cursor = g_Conn.cursor()  

        for param in params:  

            sql_temp = sql % param  

            cursor.execute(sql_temp)  

        g_Conn.commit()  

        return True 

    except Exception,ex:  

        print '%s:%s' % (Exception, ex)  

        g_Conn.rollback()  

        raise Exception, 'insert database has wrong' 

        return False 

    finally:  

        closeDB()  

      

def insert(sql):  

    try:  

        openDB()  

        global g_Conn  

        cursor = g_Conn.cursor()  

        cursor.execute(sql)  

        g_Conn.commit()  

    except:  

        g_Conn.rollback()  

        raise Exception, 'query database has wrong' 

    finally:  

        closeDB()  

          

if __name__ == "__main__":  

    # sql = "select * from tbCasesResult where TestToolRunID = 1"  

    sql = "select * from tbTest" 

    sql = "select * from tbNew" 

    sql_insert = "insert into tbNew([name],[age]) values ('test', 113)" 

    a = query(sql)  

    b = insert(sql_insert)  

    print a