1.通过cx_Oracle读取SQL
import pandas as pd
import cx_Oracle
def load_data(sql):
db=cx_Oracle.connect('username','password','10.10.10.10:1521/orcl')
cr=db.cursor()
cr.execute(sql)
rs = cr.fetchall()
cols = cr.description
col = []
for i in cols:
col.append(i[0])
zz = pd.DataFrame(rs, columns=col)
cr.close()
db.close()
return zz
如果查询字段有lob字段,上面的方法会报错,需要先调用read读取lob字段内容,函数升级如下
def load_data(sql):
db=connect('username','password','10.10.10.10:1521/orcl')
cr=db.cursor()
cr.execute(sql)
rs = cr.fetchall()
# 获取lob字段
for i in range(len(rs)):
rs[i] = list(rs[i])
try:
for j in range(len(rs[i])):
rs[i][j] = rs[i][j].read()
except:
continue
cols = [k[0] for k in cr.description]
zz = DataFrame(rs, columns=cols)
cr.close()
db.close()
return zz
2.直接通过pandas也可读取SQL:
import cx_Oracle
import pandas as pd
sql = 'SELECT * from tab_name'
db=cx_Oracle.connect('username','password','10.10.10.10:1521/orcl')
pd.read_sql(sql,db)
用cx_Oracle会弹出警告,可以用sqlalchemy代替cx_Oracle:
from sqlalchemy import create_engine
db= create_engine('oracle://username:password@10.10.10.10:1521/orcl')
pd.read_sql(sql,db)