Python与数据库（2）Oracle

用到的包：cx_Oracle

大致形式与Mysql类似，细节上有所不同

#-*-coding:GBK-*-
#author:zwg
import cx_Oracle as orc
#建立连接
dsn=orc.makedsn('127.0.0.1',1521,'ORCL')
conn=orc.connect('system','Python520',dsn)
#或者写成这种形式conn = cx_Oracle.connect('challenger/challenger@10.180.85.207/challenge')
#或者写成这种形式cx_Oracle.connect('username','pwd','IP/HOSTNAME:PORT/TNSNAME')
cursor=conn.cursor()
#cursor.execute('drop table student_copy')
sql1='create table student_copy(' \
     'a varchar(20),b varchar(20),c number(3),e varchar(10))'
sql2='select * from student_copy'
sql3='insert into system.student_copy ' \
     'select s_name,s_number,age,province from sys.student'
cursor.execute(sql1)
cursor.execute(sql3)

param1={'a':'LiSan','b':'16212044','c':26,'e':'HuNan'}
param2={'a':'LuSan','b':'16212014','c':25,'e':'HuNan'}
pa=[param1,param2]
cursor.executemany('insert into student_copy values(:a,:b,:c,:e)',pa);
#一次插入多条记录，为字典组成的列表数据，前面的mysql中是元组，且应用格式也有不同

cursor.execute(sql2)
text=cursor.fetchall()
length=len(text)
print(length)
conn.commit()
cursor.close()
conn.close()

（2）Oracle中数据写入MySQL，适用于数据量较少的情况，表较多时建议采用多线程模块同时进行

#-*-coding:GBK-*-
#author:zwg
import cx_Oracle as orc
import MySQLdb as msql
def connect_oracle(host,port,database,user,password):#建立与Oracle的连接
    dsn=orc.makedsn(host,port,database)
    conn=orc.connect(user,password,dsn)
    return conn
def connect_mysql(host,user,password,database):#建立与MySQL的连接
    conn=msql.connect(host,user,password,database)
    return conn
def write_txt(file1,text):#把数据写入txt
    a,b,c,d=text
    file1.writelines(str(a)+' '+str(b)+' '+str(c)+' '+str(d)+'\n')

conn1=connect_oracle('localhost',1521,'ORCL','system','Python520')
cursor1=conn1.cursor()
conn2=connect_mysql('127.0.0.1','root','1234','school')
cursor2=conn2.cursor()
sql1='select * from mysql_oracle'
sql2='insert into mysql_oracle values (%s,%s,%s,%s)'


cursor1.execute(sql1)
text1=cursor1.fetchall()
length1=len(text1)
length2=len(text1[0])
file1=file('mysql_oracle.txt','w+')
[write_txt(file1,text) for text in text1]
cursor2.executemany(sql2,text1)#直接将fetchall()得到的数据写入mysql
cursor2.execute(sql1)
conn2.commit()
conn1.commit()
text2=cursor2.fetchall()
print(len(text2))
cursor1.close()
conn1.close()
conn2.close()