Oracle Python

Bulk insert to Oracle with Python

投稿日:

Overview

There is an example code to insert data in bulk to Oracle database with python cx_Oracle

Environments

python 3.7.3
Oracle 18c Express Edition

Step1 : Creating the table

Creating the table ‘oracle_insert’ in the schema ‘USER01’.

CREATE TABLE USER01.ORACLE_INSERT(
	col1 int
	,col2 int
	,col3 int
)

Step2 : Insert in bulk with executemany

  • dataset is the dataset to be inserted
  • Making a connection to database with cx_Oracle
  • Inserting in bulk with using cur.executemany()
import cx_Oracle

dataset =[
    [1,2,3]
    ,[4,5,6]
    ,[7,8,9]
    ,[10,11,12]
    ,[13,14,15]
    ,[16,17,18]
    ,[19,20,21]
] 

HOST = 'localhost'
PORT = '1521'
DB_NAME = 'xepd1'
USER = 'user01'
PASSWORD = 'password01'
SERVICE_NAME = 'xepdb1'
 
tns = cx_Oracle.makedsn(HOST, PORT, service_name =SERVICE_NAME)
conn = cx_Oracle.connect(USER,PASSWORD,tns)
cur = conn.cursor()
cur.executemany("insert into user01.oracle_insert (col1, col2, col3) values (:1, :2 ,:3)", dataset)
conn.commit()
conn.close()

Summary

The code to insert some rows in bulk is not difficult and simple. But the performance about it is not tested in here, I’ll try it in future.

-Oracle, Python
-,

執筆者:


comment

Your email address will not be published. Required fields are marked *

関連記事

【Python】Changing images periodically with tkinter

Contents1 Overview2 Python library to be used3 Step1 : Display a image with tkintertkinter4 Step2 : Changing images5 Summary Overview There is a good library in python to make GUI, that is tkinter. It can display images too. Here, we are trying to change images periodically with tkinter. Python library to be used Following three libraries are used. tkinter ‘tkinter’ is used to make GUI. PIL(pillow) ‘PIL’ is used to deal with images in the python. threading ‘threading’ is used to change images automatically. Detail about that will be explained later. By the way, if you are using anaconda which …

【Python】Not getting all rows with BeautifulSoup

Contents1 Overview2 Environment3 Problem occurred example3.0.1 Result4 The way to fix it Overview When Scraping with Beautiful Soup a problem occurred like not getting all rows of the table but a few of them. This example shows how to fix it Environment Python 3.7.3 Problem occurred example This is a example table you want to scrape. NumberName1Sato2Kato3Ito4Goto I saw the html code in a web browser by pushing F12 key. <table class="test_table"> <thead> <tr> <th>Number</th> <th>Name</th> </tr> </thead> <tbody> <tr> <td>1</td> <td>Sato</td> </tr> </tbody> <tbody> <tr> <td>2</td> <td>Kato</td> </tr> </tbody> <tbody> <tr> <td>3</td> <td>Ito</td> </tr> </tbody> <tbody> <tr> <td>4</td> <td>Goto</td> …

【Oracle】How to fix ORA-65096 at creating user

Contents1 Ovewivew2 Environments3 Error4 Step1 : Checking database 5 Step2 : Create user again6 Summary Ovewivew I encountered ORA-65096 when I tried to create user at Oracle 18c Express Edition. This is a example to fix ORA-65096. Environments OS : Windows10Oracle : 18c Express Edition Error SQL> CREATE USER user01 identified by password01 default tablespace USERS; ORA-65096: ORA-65096: invalid common user or role name Step1 : Checking database Oracle has two databases ,’CDB’ and ‘PDB’. It seems it’s not possible to create local user on CDB, so we have to check which database are used. SQL> show con_name; CON_NAME —————————— …

Connection to PostgreSQL, Oracle&MySQL from Python

Contents1 Overview2 How to connect PostgreSQL3 Connect to MySQL4 Oracleの場合 Overview There are some samples to connect PostgreSQL, Oracle, MySQL from Python. How to connect PostgreSQL Package installation pip install psycopg2 Example import psycopg2 HOST = ‘your_host’ PORT = ‘5432’ DB_NAME = ‘your_db_name’ USER = ‘your_user_name’ PASSWORD = ‘your_password’ conn = psycopg2.connect("host=" + HOST + " port=" + PORT + " dbname=" + DB_NAME + " user=" + USER + " password=" + PASSWORD ) cur = conn.cursor() cur.execute("select version()") rows = cur.fetchall() cur.close() conn.close() print(rows) Connect to MySQL Package installation pip install mysqlclient Example import MySQLdb HOST = ‘your_host’ …

Anaconda installation on CentOS

Contents1 Overview2 Environments3 What is Anaconda4 Step1 : pyenvのインストール5 Step2 : Installation of Anaconda3 Overview Anaconda package is very useful tool for data analysis by python language. This article shows how to install it on CentOS. Environments OS:CentOS7Anaconda3:3-5.3.1 What is Anaconda Nowadays the usage of python language is being more popular than before for the purpose of machine learning or processing data. Anaconda package is consists of not only python engine but also IDE, web-base editor or some useful tools. When you just install anaconda, you can use these bundled useful tools. CentOS7 has python 2 as a default but …

Language Switcher

Categories