欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

利用pandas连接mysql,oracle数据库进行查询和插入操作(教程)

程序员文章站 2022-07-02 20:10:19
环境配置: 操作系统:win10(64位) oracle客户端:instantclient_11_2(64位) python版本:python3.6.3(64位) pyth...

环境配置:

操作系统:win10(64位)

oracle客户端:instantclient_11_2(64位)

python版本:python3.6.3(64位)

python相关包:sqlalchemy, pandas, pymysql,cx_oracle

示例代码

# python 3.6.3

from sqlalchemy import create_engine
import pandas as pd

"""
mysql
-- 建表:
CREATE TABLE students (
    id INTEGER PRIMARY KEY AUTO_INCREMENT,
    name NVARCHAR(16),
    age INT,
    address NVARCHAR(256)
);

-- 插入数据:
INSERT INTO students(name,age,address) VALUES('zhangsan',18,'北京');
INSERT INTO students(name,age,address) VALUES('lisi',19,'上海');
INSERT INTO students(name,age,address) VALUES('wangyu',19,'天津');
INSERT INTO students(name,age,address) VALUES('xiaoliu',20,'重庆');
"""

# 导入mysql相关包
import pymysql
# MySQLdb只支持python2,python3需要用pymysql代替
pymysql.install_as_MySQLdb()

# 创建mysql连接引擎
# 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')

# 查询数据并转为pandas.DataFrame,指定DataFrame的index为数据库中的id字段
df = pd.read_sql('SELECT * FROM students', engine, index_col='id')
print(df)
# 修改DataFrame中的数据(移除age列)
dft = df.drop(['age'], axis=1)
# 将修改后的数据追加至原表,index=False代表不插入索引,因为数据库中id字段为自增字段
dft.to_sql('students', engine, index=False, if_exists='append')


"""
Oracle
-- 建表:
CREATE TABLE STUDENTS (
    ID NUMBER PRIMARY KEY KEY NOT NULL,
    NAME VARCHAR2(16),
    AGE NUMBER,
    ADDRESS VARCHAR2(256)
);
-- 创建自增序列:
CREATE SEQUENCE STUDENTS_SEQ
        MINVALUE 1
        NOMAXVALUE
        START WITH 1
        INCREMENT BY 1
        NOCYCLE
        NOCACHE

-- 创建自增触发器:
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;

-- 插入数据(语法直接复制的mysql,oracle若提示不正确,请根据提示改正):
INSERT INTO STUDENTS(NAME,AGE,ADDRESS) VALUES('ZHANGSAN',18,'北京');
INSERT INTO STUDENTS(NAME,AGE,ADDRESS) VALUES('LISI',19,'上海');
INSERT INTO STUDENTS(NAME,AGE,ADDRESS) VALUES('WANGYU',19,'天津');
INSERT INTO STUDENTS(NAME,AGE,ADDRESS) VALUES('XIAOLIU',20,'重庆');

"""

# 解决oracle中文乱码问题
import os
os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'


# 创建oracle连接引擎,需要安装cx_oracle,下面两种连接方式都可以
# engine = create_engine("oracle://username:password@host:port/servicename")
# 这种写法省略了cx_oracle,因为缺省使用cx_oracle
engine = create_engine("oracle://ORIGIN:123456@127.0.0.1:1521/ORCL")

# 这种写法是指明了使用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')

# 其它操作同mysql示例
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')