python之Oracle学习

2019-04-25

Python之Oracle

简单查询操作

import cx_Oracle
#创建数据库连接
conn = cx_Oracle.connect('dbuser/password@servername/sid')
#获取操作游标
cursor = conn.cursor()
#执行查询SQL，返回值1
cursor.execute("""select 1 from dual""")
#获取返回结果一条记录
rs = cursor.fetchone()
#执行完成，打印提示信息
print 'result = %s'%rs
result = 1
#关闭连接，释放资源
cursor.close()
conn.close()

执行DDL建表操作

1	cursor.execute('creat table tmp_user(id number,name varchar2(20))')

执行DML插入记录

1 2	#插入一条记录 cursor.execute('insert into tmp_user values(1,'admin')')

1
2
3

#才用个绑定变量方式插入一条记录
param={'id':2,'name':'system'}
cursor.execute('insert into tmop_user values(:id,:name)',param)

1
2
3

#一次插入多条数据，参数为字典列表
param=[{'id':3,'name':'John'},{'id':4,'name':'Mary'}，{'id':5,'name':'Steven'}]
cursor.executemany('insert into tmp_user values(:id,:name)',param)

#再一次插入多条数据，参数为元组列表形式
param=[]
for i in range(6,8):
    param.append((i,'user'+str(i)))
cursor.executemany('insert into tmp_user values(:1,:2)',param)

查询返回多条记录的情况

cursor.execute('select * from tmp_user')
r2 = cursor.fetchmany(2)
print(r2[0],r2[1])
#获取剩余所有记录
rn = cursor.fetchmany()
for row in rn:
    print(row)
#带条件的查询
cursor.prepare('select * from tmp_user where id>:id')
cursor.execute(None,{'id':7}) #注意None的大小写
for row in cursor:
    print(row)

源码：

    import cx_Oracle
def queryJpnl():
    conn = cx_Oracle.connect('用户名/密码@host:端口号/数据库名')
    c = conn.cursor()
    c.execute(
        'select D1.SYS_DRUG_NAME,A1.PATI_ID,A1.PATIENT_NO,A1.INPATIENT_NO,A1.OUTP_PATIENT_NO,A1.DATA_TYPE,to_char(A1.INDEX_TIME,\'yyyy-mm-dd,hh:mi:ss\'),A1.INDEX_RULE,A1.HOSPITAL_CODE,A1.SEX from ALL_VISIT_VIEW A1 left join DW_T_TMP_ORDER D1 on A1.OUTP_PATIENT_NO=D1.OUTP_PATIENT_NO AND D1.SYS_DRUG_NAME=\'��������\'')

    # res=c.fetchall()
    # print(list(res))
    for elem in c.fetchall():
        list_elem = list(elem)
        if list_elem[0]:
            list_elem[0] = 1
        else:
            list_elem[0] = 0
        insertJpnl(list_elem)
    c.close()
    conn.close()


def insertJpnl(list_elem):
    conn = cx_Oracle.connect('用户名/密码@host:端口号/数据库名')
    c = conn.cursor()
    param=list_elem
    data = ['SYS_DRUG_NAME', 'PATI_ID', 'PATIENT_NO', 'INPATIENT_NO', 'OUTP_PATIENT_NO', 'DATA_TYPE', 'INDEX_TIME',
            'INDEX_RULE', 'HOSPITAL_CODE', 'SEX']
    zip_data = zip(data, param)
    dict_data = dict(zip_data)
    sql = 'insert into JP values(:SYS_DRUG_NAME,:PATI_ID,:PATIENT_NO,:INPATIENT_NO,:OUTP_PATIENT_NO,:DATA_TYPE,:INDEX_TIME,:INDEX_RULE,:HOSPITAL_CODE,:SEX)'
    print(dict_data)
    c.execute(sql, dict_data)
    c.execute('commit')
    conn.commit()
    c.close()
    conn.close()
if __name__ == '__main__':
    queryJpnl()