Configure python to connect to oracle, read excel data and write it to the database

Prerequisite: Oracle single instance has been installed locally, and you can use plsql developer to connect, or you can use TNS connection string to remotely connect to the oracle cluster

Read excel and write There are many ways to enter the database. The one introduced here is to use pandas to write, which is relatively simple. There is no need to organize the data after reading excel

(free learning recommendation:python video tutorial）

The whole process needs to be carried out in two steps:

1. Configure python Connect to Oracle and test successfully

There are many tutorials on the Internet, but most of them are not so detailed, and they do not explain the difference between connecting to a single instance and connecting to a cluster. Here we first introduce the method of connecting to an Oracle single instance. , and the method of connecting to the Oracle cluster will be added later.

Version:
window 10 64-bit
python 3.6.8
cx-Oracle 7.3.0

Installation process:

1 . Use pip to install the package that operates Oracle:

pip install cx_Oracle==7.3.0

##2. Manually configure cx_Oracle temporarily Client:

2.1. Unzip the following file

Extraction code: lz0g

oracle instant client Client usage instructions:
https://docs.oracle.com/en/database/oracle/oracle-database/19/lnoci/instant-client.html#GUID-6895DB45-97AA-4738-9959-BD677D610186
Oracle instant client download address:
https://www.oracle.com/database/technologies/instant-client/downloads.html

If no data is found, it may be that there is no emp table under the user.

import cx_Oracleimport os# 设置环境编码方式，可解决读取数据库中文乱码问题os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'# 用户名/密码@IP:端口/实例名conn = cx_Oracle.connect('kplin/12sss3456@192.168.124.102:1521/ORCL')cursor = conn.cursor()try: sql = 'select * from emp' cursor.execute(sql) ret = cursor.fetchall() print(ret) # cursor.commit()except Exception as e: print(e)finally: cursor.close()

2. Use pandas to read excel data and use sqlalchemy to assist in writing to the database

1. Install sqlalchemy , pandas

pip install pandas==1.1.4pip install sqlalchemy

2. Prepare an excel table, named test.xlsx, and write the following test data

3 , test reading and writing to the database

#!/usr/bin/env python# -*- coding:utf-8 -*-# date: 2021/3/14# filename: excel_to_db# author: kplinimport pandas as pdfrom sqlalchemy import create_enginefrom sqlalchemy import types# conn_string='oracle+cx_oracle://user:pass@host:port/dbname'conn_string='oracle+cx_oracle://KPLIN:654321@192.168.124.6:1521/ORCL'engine = create_engine(conn_string, echo=False)df = pd.read_excel('test.xlsx')# if_exists有三个可选值，'fail'：如果存在该表则报错，'append'：如果存在该表则将数据追加到列尾，'replace'：如果存在该表则替换# df.to_sql('test', con=engine, if_exists='replace')# 按上面这种写入方式name字段将被写成clob字段类型，# 如果我们希望把name改为varchar2类型，怎么做？# 我们可以利用sqlalchemy的types把name指定为varchar2()类型len = df.name.str.len().max()df.to_sql('test', engine, if_exists='replace', dtype={'name': types.VARCHAR(len)})rows = engine.execute("SELECT * FROM TEST").fetchall()print(rows)

Related free learning recommendations:python tutorial（ video)

The above is the detailed content of Configure python to connect to oracle, read excel data and write it to the database. For more information, please follow other related articles on the PHP Chinese website!