python串連mysql、oracle小例子

標籤：arch   資料   date   sid   osi   mysq   arc   pap   base   

import  MySQLdb
import  cx_Oracle   as  ora
import  pandas  as  pd
from    sqlalchemy import create_engine
from    settings    import  DATABASES
from sqlalchemy.sql.sqltypes import *
from math import isnan

//串連mysql

conn_Mysql = MySQLdb.connect(host=DATABASES["Stock_Diagnosis"].get("host"), user=DATABASES["Stock_Diagnosis"].get("user"), passwd=DATABASES["Stock_Diagnosis"].get("passwd"), db=DATABASES["Stock_Diagnosis"].get("db"), charset="utf8")

engine_Mysql = create_engine(DATABASES["Stock_Diagnosis"].get("engine") + "://" + DATABASES["Stock_Diagnosis"].get("user") + ":" + DATABASES["Stock_Diagnosis"].get("passwd") + "@" + DATABASES["Stock_Diagnosis"].get("host") + "/" + DATABASES["Stock_Diagnosis"].get("db") + "?charset=utf8")

 

//串連oracle

dsn = ora.makedsn(DATABASES["datacenter"].get("host", "127.0.0.1"),
                  DATABASES["datacenter"].get("port", "1521"),
                  DATABASES["datacenter"].get("sid", "upapp"))
oraconn = ora.connect(DATABASES["datacenter"].get("user"),
                      DATABASES["datacenter"].get("passwd"),
                      dsn)
oraengine = create_engine(DATABASES["datacenter"].get("engine")
                         + "://"
                         + DATABASES["datacenter"].get("user")
                         +":"
                         +DATABASES["datacenter"].get("passwd")
                         +"@"
                         +DATABASES["datacenter"].get("host")
                         +"/"
                         +DATABASES["datacenter"].get("sid")
                         +"?charset=utf8")

#查詢資料

strsql = "select c.INDU_NAME from upapp.PUB_INDU_CODE c where c.INDU_UNI_CODE  in(select b.INDU_UNI_CODE from upapp.PUB_COM_INDU_RELA b where b.INDU_SYS_CODE=‘16‘and b.COM_UNI_CODE  in(select a.COM_UNI_CODE from upapp.STK_BASIC_INFO a where a.STK_CODE=‘600000‘))"
dfOra = pd.read_sql(strsql, oraconn)  #返回一個DataFrame

#寫庫

#構建DataFrame，將 資金面 資料 存到 stk_money_flow_fac 表中

#self._stkpool_uni、codes、end_date。。。這些都是list（append對應的值就行了，不過幾個list的元素個數得相同）
        dfData = {"STK_UNI_CODE":self._stkpool_uni, "STK_CODE":codes, "END_DATE":end_date,
                  "SCORE_FAC":score, "DIS_FAC":descri, "RAT_FAC":star, "MAC_FAC":mac}
        df = pd.DataFrame(data=dfData)
        dttype = {"STK_UNI_CODE":INT, "STK_CODE":VARCHAR(10), "END_DATE" :DATE,
                  "SCORE_FAC":FLOAT, "DIS_FAC":VARCHAR(256), "RAT_FAC":VARCHAR(10), "MAC_FAC":VARCHAR(200)}
        df.to_sql("stk_money_flow_fac", oraengine,  if_exists=‘append‘,  dtype=dttype)

 

python串連mysql、oracle小例子