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 ')