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

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.