python操作oracle数据库

前言：最近处理迁移数据库事项，oracle导入没什么好的工具导入数据(plsql没mac版)，试用下py速度挺好的。仅此记录下

安装连接oracle的库

看了下别的数据库，貌似oracle真的麻烦的多..

import cx_Oracles

 

　在当前项目使用的venv目录下 teminal， pip install -i https://pypi.douban.com/simple cx_oracle 下载(py3.7+)

如图即安装成功！

 

 

尝试连接时，报错can not create oracle client，忘截图了。此处会给个提示解决问题的网站。需要安装oracle的即时客户端。

https://cx-oracle.readthedocs.io/en/latest/user_guide/installation.html#installing-cx-oracle-on-macos-intel-x86

实战示例

可先查看各个版本是否ok

import sqlalchemy as sqla
import pandas as pd
import cx_Oracle

# Test to see if it will print the version of sqlalchemy
print(sqla.__version__)    # this returns 1.2.15 for me

# Test to see if the cx_Oracle is recognized
print(cx_Oracle.version)   # this returns 8.0.1 for me

# This fails for me at this point but will succeed after the solution described below
cx_Oracle.clientversion()

 

import cx_Oracle # 导入库

cx_Oracle.init_oracle_client(lib_dir="/xx/Downloads/instantclient_19_8")# 需要初始化客户端才行

connection = cx_Oracle.connect("username", "password", "ip/dbname") # 建立连接，也可建立连接池，再获取连接
cursor = connection.cursor() # 获得游标

file_object = open('xx.txt') # 获取文件
list_all = file_object.readlines() # 获取所有行

for line in list_all: # 遍历执行，这里可以exectemany批量执行
    cursor.execute(line)

connection.commit()

for result in cursor.execute("select count(*) from table"):
    print(result)

cursor.close()
connection.close()

exectemany批量执行

students = [ [10,'萧十一',32], [11,'何十二',40], [12,'穆十三',35] ]

cur.executemany('insert into table (id, name, age) values (:1, :2, :3)', students)

connection.commit()

连接池连接 

# 创建连接池
pool = cx_Oracle.SessionPool("username", "password",
        "ip:1521/dbname", min=2, max=5, increment=1, encoding="UTF-8")

# 从连接池中获取一个连接
connection = pool.acquire()

# 使用连接进行查询
cursor = connection.cursor()
for result in cursor.execute("select * from table"):
    print(result)

# 将当前的连接放回连接池
pool.release(connection)

# 关闭连接池
pool.close()