Use pandas to connect to mysql and oracle databases for query and insertion (Tutorial), pandasoracle

Use pandas to connect to mysql and oracle databases for query and insertion (Tutorial), pandasoracle
Environment Configuration:

Operating System: win10 (64-bit)

Oracle client: instantclient_11_2 (64-bit)

Python version: python3.6.3 (64-bit)

Python packages: sqlalchemy, pandas, pymysql, cx_oracle

Sample Code

# Python 3.6.3from sqlalchemy import create_engineimport pandas as pd "mysql -- create table: create table students (id integer primary key AUTO_INCREMENT, name NVARCHAR (16), age INT, address NVARCHAR (256); -- INSERT data: insert into students (name, age, address) VALUES ('hangsan ', 18, 'beijing'); insert into students (name, age, address) VALUES ('lisi', 19, 'shanghai'); insert into students (name, age, address) VALUES ('hangyu ', 19, 'tianjin '); INSE Rt into students (name, age, address) VALUES ('xiaoliu', 20, 'chongqing '); "" # import mysql-related packages import pymysql # MySQLdb only supports python2, python3 needs to replace pymysql with pymysql. install_as_MySQLdb () # create a mysql connection engine # engine = create_engine ('mysql + mysqldb: // username: password @ host: port/dbname? Charset = utf8') engine = create_engine ('mysql + mysqldb: // root: 123456@127.0.0.1: 3306/marsapp? Charset = utf8') # query data and convert it to pandas. dataFrame, specifying the index of DataFrame as the id field df = pd in the database. read_ SQL ('select * FROM students ', engine, index_col = 'id') print (df) # modify the data in DataFrame (remove the age column) dft = df. drop (['age'], axis = 1) # append the modified data to the original table. index = False indicates no index is inserted, because the id field in the database is an auto-incrementing field dft. to_ SQL ('students', engine, index = False, if_exists = 'append') "" Oracle -- CREATE TABLE: CREATE TABLE students (ID NUMBER PRIMARY KEY NOT NULL, NAME VARCHAR2 (16), age number, ADDRESS VARCHAR2 (256); -- create an auto-incrementing sequence: create sequence STUDENTS_SEQ MINVALUE 1 nomaxvalue start with 1 increment by 1 nocycle nocache -- CREATE auto-increment trigger: create or replace trigger STUDENTS_TG before insert on students for each row when (NEW. id is null) begin select STUDENTS_SEQ.NEXTVAL INTO: NEW. id from dual; END; -- INSERT data (mysql directly copied by syntax. If the oracle prompt is incorrect, correct it as prompted): insert into students (NAME, AGE, ADDRESS) VALUES ('hangsan ', 18, 'beijing'); insert into students (NAME, AGE, ADDRESS) VALUES ('lisi', 19, 'shanghai '); insert into students (NAME, AGE, ADDRESS) VALUES ('wangyu ', 19, 'tianjin'); insert into students (NAME, AGE, ADDRESS) VALUES ('xiaoliu ', 20, 'chongqing '); "" # solve oracle Chinese garbled problem import osos. environ ['nls _ LANG '] = 'simplified chinese_china.utf8' # create an oracle connection engine and install cx_oracle. The following two connection methods can be used: # engine = create_engine ("oracle: // username: password @ host: port/servicename ") # cx_oracle is omitted because cx_oracleengine = create_engine (" oracle: // ORIGIN: 123456@127.0.0.1: 1521/ORCL ") # This statement indicates the use of cx_oracle # engine = create_engine (" oracle + cx_oracle: // username: password @ host: port/servicename ") engine = create_engine ('oracle + cx_oracle: // ORIGIN: 123456@127.0.0.1: 1521/ORCL ') # other operations with mysql example df = pd. read_ SQL ('select * FROM STUDENTS ', engine, index_col = 'id') print (df) dft = df. drop (['age'], axis = 1) dft. to_ SQL ('students', engine, index = 0, if_exists = 'append ')