python做数据分析需要oracle_Python数据分析-第2章抽取数据到Oracle

在第一章从CSV中获取数据后，需要将提取的数据或经Pandas等处理后的数据进行存储。这里简单介绍下使用cx-Oracle操作数据库，和通过Oracle的数据载入工具sqlldr来匹配导入数据。

1.cx-Oracle

1.1.安装：

pip install cx_Oracle

1.2.安装oracle客户端，并添加到Path

oracle客户端下载地址

(安装过程，及Linux环境变量过程，略)

1.3.示例代码

操作Oracle工具 oracle_utils.py

class ConnectOracleDatabase(object):

def __init__(self):

self.user = DB_USER

self.password = DB_PASSWORD

self.ip = DB_IP

self.port = DB_PORT

self.service_name = DB_SERVICE

self.oci_path = ORACLE_OCI_DIR

# 这种设置环境变量，在Pycharm中运行无效

# 在菜单Run->Edit configurations 中，手动设置Environment variables，添加LD_LIBRARY_PATH的内容，即可解决问题。

# LD_LIBRARY_PATH=/home/oracle/instantclient_19_5

sys_version = platform.architecture()

if sys_version[1] is not 'WindowsPE':

os.environ['LD_LIBRARY_PATH'] = self.oci_path

else:

os.environ['PATH'] = os.environ['PATH'] + ';' + self.oci_path

# 解决中文乱码问题

os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.ZHS16GBK'

self.db_conn = None

self.db_cursor = None

self.create_connect()

def create_connect(self):

try:

self.db_conn = cx_Oracle.connect(self.user, self.password,

'%s:%s/%s' % (self.ip,

self.port,

self.service_name))

self.db_cursor = self.db_conn.cursor()

except Exception as error:

print("Error creating database connection: %s" % str(error))

# 执行DDL语句

def exec_sql_command(self, sql_command):

if sql_command:

print('ddl sql is', sql_command)

query_result = self.db_cursor.execute(sql_command)

return query_result

# 返回SQL的所有值

def query_sql(self, sql):

self.db_cursor.execute(sql)

return self.db_cursor.fetchall()

def __enter__(self):

self.create_connect()

return self

def __exit__(self, exc_type, exc_val, exc_tb):

try:

self.db_cursor.close()

self.db_conn.close()

except AttributeError as error:

print(str(error))

使用示例：

with ConnectOracleDatabase() as oracle:

oracle.exec_sql_command('create table person(id number(10),name varchar2(255))')

2.Python执行sqlldr入库

cx-Oracle可以处理少量数据，一旦数据量过大则效率就会降低。所以较大的结果数据入库一般使用数据库自身提供的工具，这里以sqlldr举例：

2.1.示例代码

主要分两部分，一是根据数据模型，生成sqlldr的控制文件。二是由Python直接调用sqlldr。

生成sqlldr控制文件ctl

# 在环境变量ORACLE_CTL_PATH指定的目录中生成ctl文件

ctl_path = os.path.join(os.environ[ORACLE_CTL_PATH], table_name+'.ctl')

if not os.path.exists(ctl_path):

with open(ctl_path, 'w', encoding='utf-8') as file:

file.write("load data"+os.linesep)

file.write("replace into table " + table_name + os.linesep)

file.write("fields terminated by '" + terminated + "'" + os.linesep)

file.write("trailing nullcols (" + os.linesep)

table_conf = zip(table_fields, table_fields_type)

colunms_tmps=[]

for fields, fields_type in table_conf:

colunms_tmp = fields

if 'NUMBER' in fields_type.upper():

colunms_tmp = colunms_tmp + ' DECIMAL EXTERNAL'

elif 'DATE' in fields_type.upper():

colunms_tmp = colunms_tmp + ' date "yyyy-mm-dd hh24:mi:ss"'

colunms_tmps.append(colunms_tmp)

file.write(','.join(colunms_tmps)+' TERMINATED BY WHITESPACE )' + os.linesep)

调用sqlldr数据入库

# 判断文件编码 ,直接结局sqlldr导入中文乱码问题

with open(data, 'rb') as f:

filedata = f.read()

print('file infos is', chardet.detect(filedata))

if 'GB' in chardet.detect(filedata)['encoding']:

os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.ZHS16GBK'

else:

os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.AL32UTF8'

# 导入数据

sqllod_command = os.environ['ORACLE_HOME']+'/sqlldr userid='+DB_USER+'/'+DB_PASSWORD+TNSNAME

sqllod_command += ' control=' + ctl_path

sqllod_command += ' data=' + data

sqllod_command += ' log=' + os.path.join(os.environ[ORACLE_LOG_PATH], table_name+'.log')

sqllod_command += ' skip_index_maintenance=true readsize=15000000 streamsize=15000000 ' \

'columnarrayrows=5000 rows=1000 errors=1000000 direct=true skip=1'

print('sqllod_command=', sqllod_command)

print(os.environ['NLS_LANG'])

os.system(sqllod_command)

更多文章，请关注：