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:

Note that the computer here is 64-bit, and the instant client used is also 64-bit. For 32-bit, you need to go to the download address below to find it

2.1. Unzip the following file

Link: https://pan.baidu.com/s/1ARcdK8kgNKfQsNbP5ZEbKg

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

2.2. Place it in a certain location on the D drive, for example:

2.3. Configure environment variables

Control Panel-System and Security-System

2.4. Restart the computer to make the newly configured environment variables take effect

2.5. Test whether the configuration is successful

Although importing cx_Oracle has Red wavy lines are generally considered to be unsuccessful in importing, but you can ignore it here and run the test code directly. If there is no error, it means there is no problem.

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

The pandas version is specified here because the latest version of pandas will report some strange errors when reading and writing excel. Just change to version 1.1.4.

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!