Matlab与数据库的数据交互.doc

章名13Matlab与数据库的数据交互郑志勇 ()1 案例背景随着计算机数据库技术的应用与发展，科学研究与生产生活中的大量数据都按一定的规则方式存储在数据库中，例如：个人的各种账户（包括银行账户、证券账户、手机账户、论坛账户等等）及账户涉及到的各种信息都存储在数据库中。若能将大量数据导入到Matlab中，利用Matlab优异的数值技术与图形展示技术，更好的处理或分析科学研究与生产生活的数据，进行实证性研究或者潜在规则的挖掘。本章使用的编程环境为Matlab2009a, SQL Server 2005 Express Edition。其中SQL Server 2005 Express Edition为SQL Server 2005 的免费版本，读者可以从微软网站上下载相关安装文件，SQL Server 2005 Express Edition的安装方法本章节不再详述，读者可以参考微软的相关说明文档。数据的获取方式除了从数据库之间获取外，还可以通过网络获取所需数据。而且，目前网络已经成为数据重要的来源之一，Matlab可以一次性按格式从网络中读取大量数据。在第三节，笔者以matlab读取Yahoo财经数据与Google财经数据为例进行实例讲解。2 MATLAB实现2.1 Database工具箱简介Mathworks公司为Matlab与数据库连接提供了有效接口Database工具箱。Database工具箱帮助用户使用Matlab的可视化技术与数据分析技术处理数据库中的信息。在Matlab的工作环境下，用户可以使用SQL（structured query language）标准数据查询语言从数据读取数据或将数据写入数据库。目前，Matlab可以支持与主要厂商的数据库产品进行连接，例如Oracle、Sybase、Microsoft，SQL Server, and Informix等数据库。 Matlab的Database工具箱还自带了Visual Query Builder交互式界面方便用户使用数据。2.2 Database工具箱函数Database工具箱函数，具体分为数据库访问数据、数据库游标访问函数、数据库元数据访问函数。函数具体功能见下表，由于相关函数较多，在本节不再详述相关函数语法，在实例中将具体讲解实例使用到时函数语法。表1：数据库访问函数函数名称函数功能clearwarnings清除数据库连接警告close关闭数据库连接commit数据库改变参数database连接数据库exec执行SQL语句和打开游标get得到数据库属性insert导出MATLAB单元数组数据到数据库表isconnection判断数据库连接是否有效isreadonly判断数据库连接是否只读ping得到数据库连接信息rollback撤销数据库变化set设置数据库连接属性sql2native转换JDBC SQL 语法为系统本身的SQL语法update用MATLAB单元数组数据代替数据库表的数据表2：数据库游标访问函数函数名称函数功能attr获得的数据集的列属性close关闭游标cols获得的数据集的列数值columnnames获得的数据集的列名称fetch导入数据到MATLAB单元数组get得到游标对象属性querytimeout数据库SQL查询成功的时间rows获取数据集的行数set设置游标获取的行限制width获取数据集的列宽attr获得的数据集的列属性close关闭游标cols获得的数据集的列数值columnnames获得的数据集的列名称fetch导入数据到MATLAB单元数组表3：数据库元数据函数：函数名称函数功能bestrowid得到数据库表唯一行标识columnprivileges得到数据库列优先权columns得到数据库表列名称crossreference得到主健和外健信息dmd创建数据库元数据对象exportedkeys得到导出外部健信息get得到数据库元数据属性importedkeys得到导入外健信息indexinfo得到数据库表的索引和统计primarykeys从数据库表或结构得到主健信息procedurecolumns得到目录存储程序参数和结果列procedures得到目录存储程序supports判断是否支持数据库元数据tableprivileges得到数据库表优先权tables得到数据库表名称上述仅列出函数名称与函数的主要功能，函数的具体使用请读者参考matlab的Database工具箱相关帮助信息。2.3 数据库数据读取数据库数据读取主要由数据库连接，获取数据库信息，执行SQL查询语言查询数据，关闭数据连接等几个主要步骤组成。在进行Matlab与数据库的交互前首先要对数据源进行配置(参见附录一)（1）数据库连接函数database database函数语法conn =database(datasourcename,username,password)输入参数：datasourcename：数据库名称(连接对象的名称，如果不是本地数据，需输入网址或者IP地址及端口)username: 数据库用户名password：数据库密码输出参数：Conn：建立数据连接对象（内含连接信息、参数）函数测试：m文件DatabaseRead.m%DatabaseReadTest%code by %建立数据连接conn=database(ARIS_SQL,sa,ariszheng)%数据库名称为 “ARIS_SQL” 为本数据库之间输入数据名称即可%如果不是本地数据，需输入网址或者IP地址及端口%数据库用户名为”sa”%数据库密码为“ariszheng”结果输出:conn = Instance: ARIS_SQL %数据名称 UserName: sa %用户名称 Driver: URL: Constructor: 1x1 com.mathworks.toolbox.database.databaseConnect Message: Handle: 1x1 sun.jdbc.odbc.JdbcOdbcConnection TimeOut: 0 AutoCommit: on %连接成功 Type: Database Object注释：AutoCommit: on 表示数据库链接成功 AutoCommit: off 表示数据库链接失败（2）获取数据库连接信息函数ping ping函数语法ping(conn)通过ping函数可以获得数据库连接的数据版本、数据名称、驱动程序、URL地址等等。输入参数：Conn：数据库连接对象输出参数：DatabaseProductName: 数据库产品名称DatabaseProductVersion: 数据库产品版本JDBCDriverName: JDBC驱动名称JDBCDriverVersion: JDBC驱动版本MaxDatabaseConnections: 数据库最大连接数量CurrentUserName: 使用的数据库名称DatabaseURL: 数据库URL地址AutoCommitTransactions: 是否连接参数测试：m文件DatabaseRead.m%获取数据库连接信息ping(conn);结果输出:ans = DatabaseProductName: Microsoft SQL Server%数据库为Microsoft SQL Server DatabaseProductVersion: 09.00.1399%数据库版本为09.00.1399 JDBCDriverName: JDBC-ODBC Bridge (SQLSRV32.DLL)%数据库的驱动程序为“JDBC-ODBC Bridge” JDBCDriverVersion: 2.0001 (03.85.1132)%驱动程序版本“2.0001 (03.85.1132)” MaxDatabaseConnections: 0%数据库最大连接数（未设置） CurrentUserName: dbo%当前用户名称“dbo” DatabaseURL: jdbc:odbc:ARIS_SQL%数据库连接地址jdbc:odbc:ARIS_SQL AutoCommitTransactions: True（3）执行SQL语句和打开游标函数execexec函数语法curs = exec(conn, sqlquery)输入参数Conn: 数据库连接对象Sqlquery: sql数据库查询语句输出参数Curs：结构体（游标）函数测试：m文件DatabaseRead.m该程序的目标是从数据库表StockData.dbo.Hs300 中查询2008-01-01到2010-01-01之间是沪深300指数的点位.SQL查询语言的框架为Use 数据库Select 数据内容From 数据表名称（查询目标表）Where 查询条件Older by 排序方式本节对SQL语言的语法不进行详细讲解，若读者需要可见参考文献2.表8.2-4：StockData数据表结构字段类型DateDatetime类型PriceDouble类型VolDouble类型Sqlquery：SQL语言SELECT ALL Price FROM StockData.dbo.Hs300 WHERE Date BETWEEN 2008-01-01 AND 2010-01-01Matlab语言%查询数据curs = exec(conn,SELECT ALL Price FROM StockData.dbo.Hs300 WHERE Date BETWEEN 2008-01-01 AND 2010-01-01 )输出结果: Attributes: Data: 0 DatabaseObject: 1x1 database RowLimit: 0 SQLQuery: 1x92 char Message: Type: Database Cursor Object ResultSet: 1x1 sun.jdbc.odbc.JdbcOdbcResultSet Cursor: 1x1 com.mathworks.toolbox.database.sqlExec Statement: 1x1 sun.jdbc.odbc.JdbcOdbcStatement Fetch: 0注释：执行Sql语句的查询，你可能还没有等到你想要的数据，需要对查询结果进行fetch处理，将数据导入到Matlab的数组中。（4）导入数据到MATLAB单元数组函数fetchfetch函数语法curs = fetch(curs)输入参数Curs：exec执行后获得的结果（游标）输出参数Curs：经fetch处理后的数据结果函数测试：m文件DatabaseRead.m%导入数据到MATLAB单元数组函数e = fetch(e)e.data%查询的结果数据存储在对象e的data中输出结果:e = Attributes: Data: 490x1 cell %数据数量 DatabaseObject: 1x1 database RowLimit: 0 SQLQuery: 1x92 char Message: Type: Database Cursor Object ResultSet: 1x1 sun.jdbc.odbc.JdbcOdbcResultSet Cursor: 1x1 com.mathworks.toolbox.database.sqlExec Statement: 1x1 sun.jdbc.odbc.JdbcOdbcStatement Fetch: 1x1 com.mathworks.toolbox.database.fetchTheDataans = 5.3383e+003 5.3851e+0035.4220e+003（5）关闭数据库连接closeClose函数语法close(curs)：关闭查询游标close(conn)：关闭数据连接函数测试：m文件DatabaseRead.mclose(conn)注释：在数据库连接或数据查询结束后，应当关闭数据库连接或查询游标，避免重复连接，重复查询浪费系统资源，使得计算机处理速度降低。注释：Matlab的数据读写使用的Java接口进行的，读取数据的大小受到内存限制，若读者读取的数据量较大，可以采用分批读取的方式进行，避免数据的内存溢出。2.4 数据库数据写入与数据的读取一样，数据库数据写入主要由数据库连接，获取数据库信息，执行SQL查询语言写入数据几个主要步骤组成。（1）将数据插入数据库函数fastinsertFastinsert函数语法fastinsert(conn, tablename, colnames, exdata)输入参数Conn：数据库连接对象Tablename:数据写入的目标表名称（数据表需已经在数据库中建立完成）Colnames:数据写入的列名称Exdata:写入数据函数测试：m文件DatabaseWrite.m将2010-6-21沪深300的指数2780.66 交易量5526万插入数据库StockData.dbo.Hs300表中。“StockData.dbo.Hs300”表示StockData数据库中的dbo.Hs300表%code by %2010-6-21conn=database(ARIS_SQL,sa,ariszheng)%数据库名称：ARIS_SQL,%数据库用户名：sa%数据库用户名对应的密码：ariszhengping(conn)%查询数据库连接状态load Hs300%输入数据 格式：时间 数据expData= 2010-6-21 2780.66 55260000%将数据插入表StockData.dbo.Hs300fastinsert(conn, StockData.dbo.Hs300,Date;Price;Vol, expData);查询验证数据是否写入成功%查询数据，看数据是否写入成功e=exec(conn,SELECT Price,Vol FROM StockData.dbo.Hs300 WHERE Date= 2010-06-21 )e = fetch(e)e.data%关闭连接close(conn)结果输出：conn = Instance: ARIS_SQL UserName: sa Driver: URL: Constructor: 1x1 com.mathworks.toolbox.database.databaseConnect Message: Handle: 1x1 sun.jdbc.odbc.JdbcOdbcConnection TimeOut: 0 AutoCommit: on Type: Database Objectans = DatabaseProductName: Microsoft SQL Server DatabaseProductVersion: 09.00.1399 JDBCDriverName: JDBC-ODBC Bridge (SQLSRV32.DLL) JDBCDriverVersion: 2.0001 (03.85.1132) MaxDatabaseConnections: 0 CurrentUserName: dbo DatabaseURL: jdbc:odbc:ARIS_SQL AutoCommitTransactions: TrueexpData = 2010-6-21 2.7807e+003 55260000e = Attributes: Data: 0 DatabaseObject: 1x1 database RowLimit: 0 SQLQuery: 1x67 char Message: Type: Database Cursor Object ResultSet: 1x1 sun.jdbc.odbc.JdbcOdbcResultSet Cursor: 1x1 com.mathworks.toolbox.database.sqlExec Statement: 1x1 sun.jdbc.odbc.JdbcOdbcStatement Fetch: 0e = Attributes: Data: 2.7807e+003 55260000 DatabaseObject: 1x1 database RowLimit: 0 SQLQuery: 1x67 char Message: Type: Database Cursor Object ResultSet: 1x1 sun.jdbc.odbc.JdbcOdbcResultSet Cursor: 1x1 com.mathworks.toolbox.database.sqlExec Statement: 1x1 sun.jdbc.odbc.JdbcOdbcStatement Fetch: 1x1 com.mathworks.toolbox.database.fetchTheDataans = 2.7807e+003 55260000（2）插入多行数据上述案例讲解的是如何插入一组数据，可以使用循环的方式实现插入多组数据。函数测试：m文件DatabaseWrite2.m插入多行数据，可以采用循环插入方法%load Hs300%N为数据个数N=length(Hs300Price)for i=1:N expData=Hs300Date(i),Hs300Price(i),Hs300Vol(i); fastinsert(conn, StockData.dbo.Hs300,Date;Price;Vol, expData);endclose(conn)3 网络数据读取随着科技的发展，我们可以得到的数据越来越多，网络已经成为数据重要的来源之一，Matlab可以一次性按格式从网络中读取大量数据。本节以matlab读取Yahoo财经数据与Google财经数据为例进行实例讲解。3.1 Yahoo数据 MyYahoo函数是网络开源的matlab检索Yahoo财经数据的函数，函数主要使用urlread函数读取网页数据。由于设计到比较复杂的字符串处理，本节不具体讲解函数的技术细节，主要介绍其使用方法：MyYahoo函数语法：stock_Price=MyYahoo(StockName, StartDate, EndDate, Freq)输入参数：stockName: 证券代码，主要参考yahoo的证券代码形式Yahoo采用的证券编码形式为：证券代码.交易所例如：武钢股份（600005）Yahoo 代码600005.SS 深发展 ( 000001) yahoo 代码000001.SZ IBM IBM Yahoo 代码IBM（纽约交易所） IBM.F(法兰克福交易所)StartDate: 开始时间EndDate: 截至时间Freq: 数据频率 d日、w周、m月输出参数：stock_Price：证券数据MyYahoo函数源码,MyYahoo.mfunction stock_Price=MyYahoo(StockName, StartDate, EndDate, Freq) % This engine is used for a rapid searching in Yahoo!Finance for retriving% Financial Data. %数据时间区间startdate=StartDate;enddate=EndDate;%字符串变化ms=num2str(str2num(datestr(startdate, mm)-1);ds=datestr(startdate, dd);ys=datestr(startdate, yyyy);me=num2str(str2num(datestr(enddate, mm)-1);de=datestr(enddate, dd);ye=datestr(enddate, yyyy); url2Read=sprintf(/table.csv?s=%s&a=%s&b=%s&c=%s&d=%s&e=%s&f=%s&g=%s&ignore=.csv, StockName, ms, ds, ys, me, de, ye, Freq);s=urlread(url2Read); Date Open High Low Close Volume AdjClose=strread (s, %s %s %s %s %s %s %s, delimiter, ,); Date(1)=;AdjClose(1)=; row=size(Date, 1);for i = 1:row Date_temp(i, 1)=datenum(cell2mat(Date(i), yyyy-mm-dd); AdjClose_temp(i, 1)=str2num(cell2mat(AdjClose(i); end stock_Price=Date_temp, AdjClose_temp; root=pwd, ;filename=root, StockName, .mat;save(filename, stock_Price) ; end实例演示：testMyYahoo.m提取武钢股份日行情数据%提取数据 武钢股份（上海交易所）A=MyYahoo(600005.ss, 01/01/2005, 12/31/2008, d)%将A数据 A的格式价格、日期 采用的matlab编码形式，以整数编码 %将A数据 转变为时间序列stock=fints(A)%画图plot(stock);函数计算结果：A = 1.0e+005 * 7.3377 0.0000 7.3377 0.0000 7.3377 0.0000 7.3377 0.0000 7.3377 0.0001 7.3377 0.0001 7.3377 0.0001 7.3376 0.0001 7.3376 0.00017.3376 0.0001Stock = desc: (none) freq: Unknown (0) dates: (1032) series1: (1032) 03-Jan-2005 3.1100 04-Jan-2005 2.9700 05-Jan-2005 3.0400 06-Jan-2005 2.9600 07-Jan-2005 2.850010-Jan-2005 2.9000图1 武钢股份股价图3.2 Google数据 googleprices函数是网络开源的matlab检索Google财经数据的函数，函数主要使用urlwrite函数读取网页数据。由于设计到比较复杂的字符串处理，本节不具体讲解函数的技术细节，主要介绍其使用方法：googleprices函数语法：ds = googleprices(stockTicker, startDate, endDate)参数输入：stockTicker: 证券代码，主要参考Google的证券代码形式Google采用的证券编码形式为：交易所:证券代码例如：武钢股份（600005）Google代码SHA:600005 思科系统 （CSCO） Google 代码NASDAQ:CSCOstartDate: 开始时间endDate: 截至时间注释：目前使用googleprices读取中国A数据错误，原因不明。参数输出：Ds:证券历史行情数据Googleprices函数源文件，googleprices.mfunction ds = googleprices(stockTicker, startDate, endDate)% PURPOSE: Download the historical prices for a given stock from Google% Finance and converts it into a MATLAB dataset format.%-% USAGE: ds = googleprices(stockTicker, startDate, endDate)% where: stockTicker = Google stock ticker (ExchangeSymbol:SecuritySymbol),% ex. NASDAQ:CSCO for Cisco Stocks.% startDate: start date of the prices series. It could be either in% serial matlab form or in Google Date form (mmm+dd,yyyy).% endDate: end date of the prices series. It could be either in% serial matlab form or in Google Date form% (mmm+dd,yyyy).%-% RETURNS: A dataset representing the retrieved prices.%-% REFERENCES: a references for the google formats could be found here:% /article.cfm/an_introduction_to_go% ogle_finance%- % Version: 1.0% Written by:% Display Name: El Moufatich, Fayssal% Windows: Microsoft Windows NT 5.2.3790 Service Pack 2% Date: 15-Jun-2010 17:38:18 if isnumeric(startDate) startDate = datestr(startDate, mmm+dd,yyyy);end if exist(exportFormat, var)