Python连接数据库

Python连接DB2数据库进行操作：

#coding:utf-8
import sys

#连接DB2数据库
import ibm_db


reload(sys)
sys.setdefaultencoding('utf-8')


#定义连接db2数据库函数
def conn_db2(database='EDW',hostname='hadoop',port=60000,user='edwinst',password='edwinst'):
    #database = 'EDW'
    #hostname = '192.168.0.16'
    #port = 60000
    protocol = 'TCPIP'
    #user = 'edwinst'
    #password = 'edwinst'
    conn_str ="DATABASE=%s;HOSTNAME=%s;PORT=%d;PROTOCOL=%s;UID=%s;PWD=%s;" % (database,hostname,port,protocol,user,password) 
    
    #conn = ibm_db.connect("DATABASE=datebasename;HOSTNAME=192.168.0.101;PORT=50000;PROTOCOL=TCPIP;UID=usename;PWD=pwd;", "", "");
    try: 
       conn = ibm_db.connect(conn_str,"","")
       print "Connect to %s Succeed!" % (database)
       return conn
    except:
       print "Connect to %s Failed!" % (database)
       return
       
#db2数据库执行DML语句
def sqlDML(sql,db):
    if db :
        try :
            stmt = ibm_db.exec_immediate(db, sql)
            stmt = ibm_db.exec_immediate(db, 'commit')
        except:
            print "excuted failed"
       
#db2数据库执行select语句，返回list
def sqlSelect(sql,db):
    if db :
        stmt = ibm_db.exec_immediate(db, sql)
        result = ibm_db.fetch_both(stmt)
        #print type(result)
        list = []
        while (result):
            #for key in result:
            #    print key,result[key]
            #print result
            list.append(result)
            result = ibm_db.fetch_both(stmt)
        return list
    else:
        return 

        
print "Connect to db2 ..."
db = conn_db2()
sql = 'SELECT name,score,id FROM src.stu'
list = sqlSelect(sql,db)
#print list[0][2]
sql = 'delete from src.stu'
sqlDML(sql,db)
sql = "insert into src.stu values(3,'Jack',90)"
sqlDML(sql,db)

 

Python连接oracle数据库进行操作：

#coding:utf-8
import sys 
import cx_Oracle
import time

reload(sys)
sys.setdefaultencoding('utf-8')

#定义连接oracle数据库函数
def conn_orcl(hostname='192.168.0.18',user='jim',passwd='jim',database='orcl'):
    print time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time())),": Start connect to Oracle..."
    #user
    #passwd
    #hostname = 192.168.0.18 
    #database = 'orcl'
    conn_str=user+'/'+passwd+'@'+hostname+'/'+database
    try:
        conn = cx_Oracle.connect(conn_str)
    #conn = cx_Oracle.connect('scott/tiger1@192.168.0.18/orcl')
        print time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time())),": connect to %s Succeed" % (database)
        return conn
    except:
        print time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time())),": Connect to %s Failed" % (database)
        return
        
#执行sql语句【增加、删除、修改】       
def sqlDML(sql,db):
    #include: insert,update,delete
    cr = db.cursor()
    try:
        cr.execute(sql)
        cr.close()
        db.commit()
        print time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time())),": Excute Succeed"
    except:
        print time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time())),": Excute Failed"
        
#查询语句，返回元组tuple
def sqlSelect(sql,db):
    c = db.cursor()
    try:
        c.execute(sql)
        print time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time())),": Excute Succeed"
        return c
    except:
        print time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time())),": Excute Failed"
        return 


db = conn_orcl("192.168.0.18")

'''
date_id = 20170920  

date_id = sys.argv[1]
 
filename = '/etl/etldata/script/pk10/pk10_'+str(date_id)+'.csv'
craw_pk10_list(filename,date_id)
#将数据文件加载到表中
db = conn_orcl("192.168.11.43")
#先清空表
sql = "delete from src_pk10"
sqlDML(sql,db)
#将数据文件加载到表中
load_to_db(db,filename,'src_pk10',23)


#执行存储过程,将数据merge到ods_pk10表中
#db = conn_orcl("192.168.11.43")
c = db.cursor()
#x = c.callproc('p_src_ods_pk10')
name = time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time()))
myname = c.var(cx_Oracle.STRING)
x = c.callproc('p_src_ods_pk10',[name,myname])
#print myname
print myname.getvalue()
x = c.callproc('p_ods_pk10_tr',[name,myname])
print myname.getvalue()
x = c.callproc('p_smy_pk10_res',[name,myname])
print myname.getvalue()
c.close()
db.commit()
'''