Python reads and writes to Oracle database

The recent project requires Python to call Oracle for read and write operations, stepping on a lot of pits, and finally realizing it through hard work. Performance how to say first, there are ways to tune the back. Now record the code and attention points.

1. Required Python Tool Library

Cx_oracle,pandas, can be installed using PIP from the console (already installed on the PC)

2. Implementing Query Operations

#工具库导入

Import Pandas as PD

Import Cx_oracle

# Note: Set the environment encoding method, can solve the problem of reading database garbled
Import OS
os.environ[' Nls_lang '] = ' simplified Chinese_china. UTF8 '

#实现查询并返回dataframe

def query (table)

Host = "127.0.0.1" #数据库ip
Port = "1521" #端口
Sid = "Test" #数据库名称
DSN = CX_ORACLE.MAKEDSN (host, port, SID)

#scott是数据用户名, Tiger is the login password (default user name and password)
conn = Cx_oracle.connect ("Scott", "Tiger", DSN)

#SQL语句, can be customized, to achieve flexible query
sql = ' SELECT * from ' + table

# using Pandas's Read_sql function, you can store data directly in Dataframe
Results = Pd.read_sql (sql,conn)

Conn.close
return results

Test_data = Query (test_table) # can get result set

3. Implementing the Insert Operation

#工具库导入

Import Pandas as PD

Import Cx_oracle

#实现插入功能
def input_to_db (data,table):

Host = "127.0.0.1" #数据库ip
Port = "1521" #端口
Sid = "Test" #数据库名称
DSN = CX_ORACLE.MAKEDSN (host, port, SID)

#scott是数据用户名, Tiger is the login password (default user name and password)
conn = Cx_oracle.connect ("Scott", "Tiger", DSN)

#建立游标
cursor = Connection.cursor ()

#sql语句, note that%s should be quoted, or ora-01036 error will be reported

query = "INSERT into" +table+ "(name,gender,age) VALUES ('%s ', '%s ', '%s ')"
#逐行插入数据
For I in range (len (data)):
Name= data.ix[i,0]
Gender= data.ix[i,1]
Age= data.ix[i,2]

# Execute SQL statement
Cursor.execute (query% (name,gender,age))

Connection.commit ()

# Close Cursors

Cursor.close ()
Connection.close ()

#测试插入数据库

#测试数据集

Test_data = PD. DataFrame ([' xiaoming ', ' Male ', 18],[' small Fang ', ' female ', 18]],index = [1,2],columns=[' name ', ' gender ', ' age '])

#调用函数实现插入
input_to_db (Test_data,test_table1)

Python reads and writes to Oracle database