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() '''