从MSSQL库读oralce数据(含连接串mssql、oracle、access、mysql及python实现)

 MSSQL连接字符串

（1）OLEDB
Windows 身份验证
"Provider=SQLOLEDB;Data Source=PEK7-6TKX23X\SQLEXPRESS;Initial Catalog=Test;Trusted_Connection=Yes;";
SQL Server 身份验证
"Provider=SQLOLEDB;Data Source=127.0.0.1\SQLEXPRESS;Initial Catalog=Test;User Id=sa;Password=123456;";
（2）SqlConnection
Windows 身份验证
"Data Source=PEK7-6TKX23X\SQLEXPRESS;Initial Catalog=Test;Trusted_Connection=Yes;";
SQL Server 身份验证
"server=127.0.0.1\SQLEXPRESS;database=Test;Uid=sa;PassWord=123456";
（3）ODBC
Driver={SQL Server}; Server=服务器IP; Database=数据库名; Uid=用户名; Pwd=密码;
（4）DSN方式
DSN=DSN名; Uid=用户名; Pwd=密码;

Oracle连接字符串

OLEDB 
"Provider=MSDAORA;Data Source=MyDatabaseAlias;User ID=MyUID;Password=MyPassword";

ACCESS连接字符串

//OLEDB 
//C#连接Access的方式

//Access 2003   Provider=Microsoft.Jet.OLEDB.4.0
//Access 2007   Provider=Microsoft.Ace.OleDb.12.0

//(1)无数据库密码，无用户密码，共享方式打开
//"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\qjx文档\GenericDemo\Data\Student.accdb;"
//(2)有数据库密码，没有用户密码,这种方式以独占方式打开
//"Provider=Microsoft.Jet.OleDB.4.0;Data Source=D:\qjx文档\GenericDemo\Data\Student.accdb;Jet OleDb:DataBase Password=123"
//(3)有数据库密码，有用户密码 可以用该方法,但用户密码处就算设有密码也必须为空，这种方式以独占方式打开
//"Provider=Microsoft.Jet.OleDB.4.0;Data Source=D:\qjx文档\GenericDemo\Data\Student.accdb;Jet OleDb:DataBase Password=12345;Persist Security Info=true;password=;user id=Admin"



MS ACCESS ODBC开放式接口连接字符串

标准链接：

"Driver= {MicrosoftAccessDriver(*.mdb)};DBQ=C:\App1\你的数据库名.mdb;Uid=你的用户名;Pwd=你的密码;"

如果ACCESS数据库未设置用户名和密码，请留空。下同。

WorkGroup方式（工作组方式）连接：

"Driver={Microsoft Access Driver (*.mdb)}; Dbq=C:\App1\你的数据库名.mdb; SystemDB=C:\App1\你的数据库名.mdw;"

采用独占方式进行连接：

"Driver={Microsoft Access Driver (*.mdb)}; DBQ=C:\App1\你的数据库名.mdb; Exclusive=1; Uid=你的用户名; Pwd=你的密码;"

MS ACCESS OLEDB & OleDbConnection （.NET下的OleDb接口）进行链接

普通方式（最常用）连接ACCESS数据库：

"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\App1\你的数据库名.mdb; User Id=admin; Password="

使用工作组方式（系统数据库）连接ACCESS数据库：

"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\App1\你的数据库名.mdb; Jet OLEDB:System Database=c:\App1\你的系统数据库名.mdw"

连接到带有密码的ACCESS数据库：

"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\App1\你的数据库名.mdb; Jet OLEDB:Database Password=你的密码"

连接到处于局域网主机上的ACCESS数据库：

"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=\\Server_Name\Share_Name\Share_Path\你的数据库名.mdb"

连接到处于远程服务器上的ACCESS数据库：

"Provider=MS Remote; Remote Server=http://远程服务器IP; Remote Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\App1\你的数据库名.mdb"

MS ACCESS ODBC开放式接口连接字符串

标准链接：

"Driver= {MicrosoftAccessDriver(*.mdb)};DBQ=C:\App1\你的数据库名.mdb;Uid=你的用户名;Pwd=你的密码;"

如果ACCESS数据库未设置用户名和密码，请留空。下同。

WorkGroup方式（工作组方式）连接：

"Driver={Microsoft Access Driver (*.mdb)}; Dbq=C:\App1\你的数据库名.mdb; SystemDB=C:\App1\你的数据库名.mdw;"

采用独占方式进行连接：

"Driver={Microsoft Access Driver (*.mdb)}; DBQ=C:\App1\你的数据库名.mdb; Exclusive=1; Uid=你的用户名; Pwd=你的密码;"

MS ACCESS OLEDB & OleDbConnection （.NET下的OleDb接口）进行链接

普通方式（最常用）连接ACCESS数据库：

"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\App1\你的数据库名.mdb; User Id=admin; Password="

使用工作组方式（系统数据库）连接ACCESS数据库：

"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\App1\你的数据库名.mdb; Jet OLEDB:System Database=c:\App1\你的系统数据库名.mdw"

连接到带有密码的ACCESS数据库：

"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\App1\你的数据库名.mdb; Jet OLEDB:Database Password=你的密码"

连接到处于局域网主机上的ACCESS数据库：

"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=\\Server_Name\Share_Name\Share_Path\你的数据库名.mdb"

连接到处于远程服务器上的ACCESS数据库：

"Provider=MS Remote; Remote Server=http://远程服务器IP; Remote Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\App1\你的数据库名.mdb"

 

--------------------------------------------------------------------------------------------------------------------------------

1,在目标数据库新增服务器对象->连接到服务器->新建服务器连接

注：

查询的方法基本语法：select * from [连接服务器名称]..[数据库所有者名称].[表名]

2,编写数据同步业务PROC

ALTER PROC TestProc
AS
BEGIN
    --打开完全事物回滚
    SET XACT_ABORT ON;
    SET NOCOUNT ON;

    --开始异常拦截
    BEGIN TRY
        --开始事物
        BEGIN TRANSACTION;
        PRINT '开始同步数据';
        MERGE INTO dbo.TestTable AS T
        USING
        (
            SELECT ROW_NUMBER() OVER (ORDER BY CREATE_DATE DESC) AS Id,
                   NAME,
                   PHONE
            FROM [TEST_OGU].[Hyx_Finance_DB].[dbo].[TBL_CUSTOMER_RELATIONSHIP]
        ) AS d
        ON T.Name = d.NAME
           AND T.Phone = d.PHONE
        --是否存在(存在更新)
        WHEN MATCHED THEN
            UPDATE SET Name = d.NAME,
                       Phone = d.PHONE
        --不存在数据（新增数据）
        WHEN NOT MATCHED THEN
            INSERT
            (
                Name,
                Phone
            )
            VALUES
            (d.NAME, d.PHONE);
        PRINT '同步数据结束'; 
        --提交事物
        COMMIT TRANSACTION;
    --结束异常拦截    
    END TRY
    BEGIN CATCH
        --回滚事物
        IF XACT_STATE() <> 0
        BEGIN
            ROLLBACK TRANSACTION;
        END;
    END CATCH;

    --还原事物设置
    SET XACT_ABORT OFF;
END;

3，设置定时作业

1、打开本地数据连接，在左边资源管理器中找到Sql Server代理（在最下面）

2、【新建】一个作业

3、在【常规】中填写名称即可，说明看个人习惯

4、打开【步骤】，点击【新建】，填写【名称】，选择【数据库】，在命令空白处填写T-sql语句，然后点分析。

5、打开【计划】，点击【新建】，设置你想要的定时计划，然后保存。

6、点击【Sql Server代理】，右键启动，即定时作业启动。

7、如果想让定时作业开机自动启动，则在 服务中找到SQL Server 代理 (MSSQLSERVER)，设置成【自动】模式

第三步：把定时作业生成脚本

点击【SQL Server 代理】下的作业，选中你创建的作业——右键编写作业脚本——create到，然后复制脚本语言即可.

---------------------------------------------------------------------------------------------------------------------------------

从别的oracle数据源抽向mysql数据库，用python实现简单的数据抽取与写入。       

开发环境：python2.7.15，开发软件：VS2013

1.首先下载一个VS2013软件，解压后直接默认安装就行，时间可能有点长，慢慢等待。

2.然后从官网上下载python安装包，32位，64位均可，下载完之后解压后直接安装，默认安装就可以；然后需要配置一下环境变量，找到Path，在后面加上    ;python27，点击确定即可（注意分号不能少）。

3.下载一个python插件（PTVS 2.2 VS 2013.msi）的镜像，点击安装即可

4.打开VS2013,配置Python的环境OK
 

from airflow.hooks import oracle_hook
from airflow.hooks import mysql_hook
from airflow import DAG
from airflow.operators.python_operator import PythonOperator
from airflow.operators.dummy_operator import DummyOperator
from datetime import  datetime,timedelta
import  time

default_args = {
    "owner":"airflow",
    "depends_on_past":False,
    "start_date":datetime(2018,1,22),
    "retries":1,
    "retry_delay":timedelta(minutes=5)
}

dag = DAG('ora_mys_crm',default_args=default_args,schedule_interval="0 1 * * *")
def write_to_mysql(**kwargs):
    """
    将oracle获取的数据写入的mysql
    :param kwargs:
    :return:
    """

    ora_hook = oracle_hook.OracleHook("oracle_crm_nmsqms")  # oracle 连接id
    mys_hook = mysql_hook.MySqlHook("mysql_healthrecord_health") #mysql 连接id

    sql_ora = """
            SELECT AREA_CODE,SUM(case when IPTV_NBR IS NULL  then 0 else 1 end),
            SUM(case when ACC_NBR IS NULL  then 0 else 1 end),COUNT(*)
            from GAT_SQMS.GAT_SQMS_BAND_IPTV_VIEW group BY AREA_CODE
            """

    conn_oracle = ora_hook.get_conn()
    conn_mysql = mys_hook.get_conn()

    cur=conn_oracle.cursor()
    mys_cur=conn_mysql.cursor()
    mys_cur.execute('SET autocommit = 0')
    try:
       cur.execute(sql_ora)
       rs = cur.fetchall()
       for line in rs:
           time_=time.strftime('%Y-%m-%d',time.localtime(time.time()))
           area_code = line[0]
           itv_count = line[1]
           band_count = line[2]
           all_count = line[3]
           sql_mys = "insert into crm_reschain_count (date,area_code,itv_count,band_count,all_count) value ('%s','%s',%d,%d,%d)"%(time_,area_code,itv_count,band_count,all_count)
           mys_cur.execute(sql_mys)

    except Exception as e:
        sql_del = "delete from crm_reschain_count where date='%s'"%time_
        mys_cur.execute(sql_del)
        conn_mysql.rollback()
        raise e
    conn_mysql.commit()
    cur.close()
    conn_oracle.close()
    mys_cur.close()
    conn_mysql.close()

with dag:
    t1 = PythonOperator(python_callable=write_to_mysql,task_id="write_mysql")
    t2 = DummyOperator(task_id="dummy")

t1 >> t2
----------------------------------------------------------------------------------------------

将Oracle中单表的数据定期导入至MySQL，注意此处对数据的时效性要求不高。要求高可以利用goldengate

 

首先，需要将visit表中的vid,userid,goodid,time4个字段导出来

export ORACLE_HOME=/usr/lib/oracle/11.2/client64
export TNS_ADMIN=$ORACLE_HOME
export PATH=$PATH:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
export NLS_LANG="AMERICAN_AMERICA.UTF8"
MYDB=visit   #MySQL数据库名
MYUSER=vis_my_user  #MySQL用户名
MYPASS=is_my_pass  #MySQL密码
ORA_NAME=visit       #Oracle实例名
ORA_USER=visuser    #Oracle用户名
ORA_PASS=vispass    #Oracle密码
  
id=`mysql -u$MYUSER -p$MYPASS $MYDB -N -e "select max(vid) from visit;"`
#MySQL -N参数可以忽略第一行输出
 
sqlplus -S ${ORA_USER}/${ORA_PASS}@${ORA_NAME} << EOF 
set echo off;
set feedback off;
set newpage none;
set verify off;
set pagesize 0;
set term off;
set trims on;
set linesize 600;
set heading off;
set timing off;
set numwidth 38;
SPOOL /tmp/visit_result.txt;   #导出结果位置，以下为查询语句
select 
        vid || ',' || 
        userid || ',' || 
        goodsid || ',' || 
        time
from
        VISIT
WHERE
        to_char(time,'yyyymmdd') >= to_char(SYSDATE-30,'yyyymmdd')
AND 
        vid > $id
order by
        CREATETIME asc;
SPOOL OFF;
exit
EOF
 
mysql -u$MYUSER -p$MYPASS $MYDB -e "load data local infile '/tmp/visit_result.txt' into table visit FIELDS TERMINATED BY ',' (userid,goodsid,time,vid);"
#此处为将Oracle数据导入MySQL