加载中…
个人资料
faruto
faruto 新浪个人认证
  • 博客等级:
  • 博客积分:0
  • 博客访问:1,247,193
  • 关注人气:2,113
  • 获赠金笔:0支
  • 赠出金笔:0支
  • 荣誉徽章:
正文 字体大小:

MATLAB与数据库交互基础整理-提交、修改数据库

(2015-11-29 00:39:28)
标签:

matlab

数据库

jdbc

odbc

oracle

分类: 量化投资相关

>> myconn=database('students','mytest','deepfuture','com.mysql.jdbc.Driver','jdbc:mysql://localhost:3306/students')
 
myconn =
 
       Instance: 'students'
       UserName: 'mytest'
         Driver: 'com.mysql.jdbc.Driver'
            URL: 'jdbc:mysql://localhost:3306/students'
    Constructor: [1x1 com.mathworks.toolbox.database.databaseConnect]
        Message: []
         Handle: [1x1 com.mysql.jdbc.JDBC4Connection]
        TimeOut: 0
     AutoCommit: 'on'
           Type: 'Database Object'

>> mycurs=exec(myconn,'select * from student')
 
mycurs =
 
        Attributes: []
              Data: 0
    DatabaseObject: [1x1 database]
          RowLimit: 0
          SQLQuery: 'select * from student'
           Message: []
              Type: 'Database Cursor Object'
         ResultSet: [1x1 com.mysql.jdbc.JDBC4ResultSet]
            Cursor: [1x1 com.mathworks.toolbox.database.sqlExec]
         Statement: [1x1 com.mysql.jdbc.StatementImpl]
             Fetch: 0

>> myres=fetch(mycurs)
 
myres =
 
        Attributes: []
              Data: {3x3 cell}
    DatabaseObject: [1x1 database]
          RowLimit: 0
          SQLQuery: 'select * from student'
           Message: []
              Type: 'Database Cursor Object'
         ResultSet: [1x1 com.mysql.jdbc.JDBC4ResultSet]
            Cursor: [1x1 com.mathworks.toolbox.database.sqlExec]
         Statement: [1x1 com.mysql.jdbc.StatementImpl]
             Fetch: [1x1 com.mathworks.toolbox.database.fetchTheData]

>> myres
 
myres =
 
        Attributes: []
              Data: {3x3 cell}
    DatabaseObject: [1x1 database]
          RowLimit: 0
          SQLQuery: 'select * from student'
           Message: []
              Type: 'Database Cursor Object'
         ResultSet: [1x1 com.mysql.jdbc.JDBC4ResultSet]
            Cursor: [1x1 com.mathworks.toolbox.database.sqlExec]
         Statement: [1x1 com.mysql.jdbc.StatementImpl]
             Fetch: [1x1 com.mathworks.toolbox.database.fetchTheData]

>> myres.Data

ans =

    '张三'    [25]    '1班'
    '李四'    [21]    '2班'
    '王五'    [23]    '1班'

 

get得到对象属性

>> v=get(myconn)

v =

              AutoCommit: 'on'
                 Catalog: 'students'
             Constructor: [1x1 com.mathworks.toolbox.database.databaseConnect]
                  Driver: 'com.mysql.jdbc.Driver'
                  Handle: [1x1 com.mysql.jdbc.JDBC4Connection]
                Instance: 'students'
                 Message: []
                ReadOnly: 0
                 TimeOut: 0
    TransactionIsolation: 4
                    Type: 'Database Object'
                     URL: 'jdbc:mysql://localhost:3306/students'
                UserName: 'mytest'
                Warnings: []

>>

 

>> get(myconn,'AutoCommit')

ans =

on

>> get(myres,'Data')

ans =

    '张三'    [25]    '1班'
    '李四'    [21]    '2班'
    '王五'    [23]    '1班'

>>

插入数据

>> colnames={'name','age','class'}

colnames =

    'name'    'age'    'class'

>> insert(myconn,'student',colnames,{'李小明',22,'3班'})
>> help insert
 --- help for database/insert ---

 INSERT Export MATLAB cell array data into database table.
    INSERT(CONNECT,TABLENAME,FIELDNAMES,DATA). 
    CONNECT is a database connection handle structure, FIELDNAMES
    is a string array of database column names, TABLENAME is the 
    database table, DATA is a MATLAB cell array.    For improved 
    write performance and support for additional data types, use the
    function FASTINSERT.
 
 
    Example:
 
 
    The following INSERT command inserts the contents of
    the cell array in to the database table yearlySales
    for the columns defined in the cell array colNames.
 
  
    insert(conn,'yearlySales',colNames,monthlyTotals);
 
    where 
 
    The cell array colNames contains the value:
 
    colNames = {'salesTotal'};
 
    monthlyTotals is a cell array containing the data to be
    inserted into the database table yearlySales
    
    insert(conn,'yearlySales',colNames,monthlyTotals);

 

设置连接,游标,或drivermanager对象的属性值,使用set

>> set(myconn,'AutoCommit','on')
>> set(myconn,'AutoCommit','off')

提交更改为commit,撤销更改为rollback,要求连接的AutoCommit属性值为off


>> myconn
 
myconn =
 
       Instance: 'students'
       UserName: 'mytest'
         Driver: 'com.mysql.jdbc.Driver'
            URL: 'jdbc:mysql://localhost:3306/students'
    Constructor: [1x1 com.mathworks.toolbox.database.databaseConnect]
        Message: []
         Handle: [1x1 com.mysql.jdbc.JDBC4Connection]
        TimeOut: 0
     AutoCommit: 'off'
           Type: 'Database Object'

>> set(myconn,'AutoCommit','on')
>> set(myconn,'AutoCommit','off')

 

>> insert(myconn,'student',colnames,{'JULY FENSE',23,'1-1'})
>> commit(myconn)
>> insert(myconn,'student',colnames,{'JAKE',22,'1-2'})
>> rollback(myconn)
>>

 

更新使用update

>> update(myconn,'student',{'age'},20,'where name=''JAKE''')
>> commit(myconn)

 

详细如下:


update(conn, 'tab', colnames, exdata, 'whereclause')
update(conn, 'tab', colnames, ...
{datA,datAA, ...; datB,datBB, ...; datn, datNN}, ...
{'where col1 = val1'; where col2 = val2'; ... 'where  coln = valn'}


Description


update(conn, 'tab', colnames, exdata, 'whereclause') exports the MATLAB variable exdata in its current format into the database table tab using the database connection conn.exdata can be a cell array, numeric matrix, or structure. Existing records in the database table are replaced as specified by the SQL whereclause command.

Specify column names for tab as strings in the MATLAB cell array colnames. If exdata is a structure, field names in the structure must exactly match field names in colnames.

The status of the AutoCommit flag determines whether update automatically commits the data to the database. View the AutoCommit flag status for the connection using get and change it using set. Commit the data by running commit or a SQL commit statement via the exec function. Roll back the data by runningrollback or a SQL rollback statement via the exec function.

To add new rows instead of replacing existing data, use fastinsert.

update(conn, 'tab', colnames, {datA, datAA, ...; datB, datBB, ...; datn,datNN}, {'where col1 = val1'; where col2 = val2'; ... 'where coln = valn'}) exports multiple records for n where clauses. The number of records in exdata must equal n.
Remarks


The order of records in your database is not constant. Use values of column names to identify records.

An error like the following may appear if your database table is open in edit mode:
[Vendor][ODBC Product Driver] The database engine could 
not lock table 'TableName' because it is already in use 
by another person or process.

 

In this case, close the table and repeat the update function.

An error like the following may appear if you try to run an update operation that is identical to one that you just ran:
??? Error using ==> database.update
Error:Commit/Rollback Problems


Examples

Example 1 — Update an Existing Record


Update the record in the Birthdays table using the database connection conn, where First_Name is Jean, replacing the current value for Age with 40.

First define a cell array containing the column name that you are updating, Age.
colnames = {'Age'}

 

Define a cell array containing the new data, 40.
exdata(1,1) = {40}

 

Run the update.
update(conn, 'Birthdays', colnames, exdata, ... 
  'where First_Name = ''Jean''')


Example 2 — Roll Back Data after Updating a Record


Update the column Date in the Error_Rate table for the record selected by whereclause, using data contained in the cell array exdata. The AutoCommit flag is off. The data is rolled back after the update operation is run.

Set the AutoCommit flag to off for database connection conn.
set(conn, 'AutoCommit', 'off')

 

Update the Date column.
update(conn, 'Error_Rate', {'Date'}, exdata, whereclause)

 

Because the data was not committed, you can roll it back.
rollback(conn)

 

The update is reversed; the data in the table is the same as it was before you ran update.
Example 3 — Update Multiple Records with Different Constraints


Given the table TeamLeagues, where column names are 'Team', 'Zip_Code', and 'New_League':
'Team1'    02116
'Team2'    02138
'Team3'    02116

 

Assign teams with a zip code of 02116 to the A league and teams with a zip code of 02138 to the B league:
update(conn, 'TeamLeagues', {'League'}, {'A';'B'}, ... 
{'where Zip_Code =''02116''';'where Zip_Code =''02138'''})

 

此外,exec也可以提交和回滚

>> exec(myconn,'rollback')
 
>> exec(myconn,'commit')


0

阅读 收藏 喜欢 打印举报/Report
  

新浪BLOG意见反馈留言板 欢迎批评指正

新浪简介 | About Sina | 广告服务 | 联系我们 | 招聘信息 | 网站律师 | SINA English | 产品答疑

新浪公司 版权所有