首页 > 代码库 > python连接mysql、oracle小例子
python连接mysql、oracle小例子
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 = http://www.mamicode.com/{"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=http://www.mamicode.com/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小例子