常用数据库连接方式

MySQL

import pymysql
import pandas as pd

设置连接

host = '15.11.111.111'
user = 'root'
password = 'admin'
dbname = 'course'

连接数据库

def ConnMysql(sql):
    conn = pymysql.connect(host,user,password,dbname) #建立连接
    curs = conn.cursor() #建立游标
    curs.execute(sql) #执行
    res = cursor.fetchall() #格式化结果
    df = pd.DataFrame(res)
    curs.close() #关闭游标
    conn.close() #关闭连接
    return df

Vertica

import vertica_python
import pandas as pd

设置连接

conn_info = {
      'host':'11.11.111.11',
      'port':1111,
      'user':'admin',
      'password':'passcode',
      'database':'abcd',
      'unicode_error':'strict',
      'ssl':False,
      'connection_timeout':10
}

连接数据库

def ConnVertica(sql):
      with vertica_python.connect(**conn_info) as conn:
            cur = conn.cursor()  #默认返回列表形式,conn.cursor(dict)返回字典形式
            cur.execute(sql)
            res = cur.fetchall()
            data.pd.DataFrame(res)
      return data

---

Oracle

import cx_Oracle
import pandas as pd
import sys
import os

设置中文编码

sys.getdefaultencoding()
os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.AL32UTF8'

连接数据库

def ConnOracle(sql):
    conn = cx_Oracle.connect('user/password@host:port/sid') #sid连接
    """
    通过服务名service_name连接
    service = cx_Oracle.makedsn(host,port,service_name = service_name)
    conn = cx_Oracle_connect(user,password,service) 
    """
    curs = conn.cursor()
    curs.execute(sql)
    res = curs.fetchall()
    df = pd.DataFrame(res)
    curs.close()
    conn.close()
    return df

小tips

"""当数据库某列数据为clob类型时，连接读取方式如下"""
"""   
res = curs.fetchall()
df = pd.DataFrame(res)
"""
#以上原部分替换为下列形式，row[1]为clob类型数据
res = pd.DataFrame()
for row in curs():
    df_tmp = pd.DataFrame([
        [row[0],row[1].read(),row[2]]
    ],columns = ['c1','c2','c3'])
    res = res.append(df_tmp,ignore_index = True)
df = res