Python reads and writes to the oracle database, and python reads and writes to the oracle database

Python reads and writes to the oracle database, and python reads and writes to the oracle database

Recently, Python was used in the project to call oracle for read/write operations, and many pitfalls have been made. The performance should not be mentioned first. There is a way to tune it later. Now let's record the code and attention.

1. Required Python tool Library

Cx_Oracle and pandas can be installed using pip on the console (installed on your computer)

 

2. query operations

# Tool library Import

Import pandas as pd

Import cx_Oracle

#Note: Setting the environment encoding method can solve the problem of reading database garbled characters.
Import OS
OS. environ ['nls _ LANG '] = 'simplified chinese_china.utf8'

# Implement query and return dataframe

Def query (table)

Host = "127.0.0.1" # Database ip Address
Port = "1521" # port
Sid = "test" # Database Name
Dsn = cx_Oracle.makedsn (host, port, sid)

# Scott is the data user name and tiger is the login password (default user name and password)
Conn = cx_Oracle.connect ("scott", "tiger", dsn)

# SQL statements, which can be customized for flexible query
SQL = 'select * from' + table

# Use the read_ SQL function of pandas to directly store data in dataframe
Results = pd. read_ SQL (SQL, conn)

Conn. close
Return results

Test_data = query (test_table) # obtain the result set.

 

3. Implement insert operations

# Tool library Import

 

Import pandas as pd

 

Import cx_Oracle

 

# Insert
Def input_to_db (data, table ):

Host = "127.0.0.1" # Database ip Address
Port = "1521" # port
Sid = "test" # Database Name
Dsn = cx_Oracle.makedsn (host, port, sid)

# Scott is the data user name and tiger is the login password (default user name and password)
Conn = cx_Oracle.connect ("scott", "tiger", dsn)

# Create a cursor
Cursor = connection. cursor ()

# SQL statement, pay attention to % s to add quotation marks, otherwise the ora-01036 error will be reported

Query = "insert into" + table + "(name, gender, age) VALUES ('% s',' % s', '% s ')"
# Insert data row by row
For I in range (len (data )):
Name = data. ix [I, 0]
Gender = data. ix [I, 1]
Age = data. ix [I, 2]

# Execute SQL statements
Cursor.exe cute (query % (name, gender, age ))

Connection. commit ()

# Closing a cursor

Cursor. close ()
Connection. close ()

 

# Test Database insertion

# Test Dataset

Test_data = pd. dataFrame ([['xiaoming ', 'male', 18], ['xiaofang', 'female ', 18], index = [1, 2], columns = ['name', 'gender', 'age'])

# Call a function to insert data
Input_to_db (test_data, test_table1)