57、深入探索PostgreSQL与MATLAB的数据交互功能

深入探索PostgreSQL与MATLAB的数据交互功能

在数据分析和处理领域，将PostgreSQL数据库中的数据导入到MATLAB环境中进行分析是一项常见且重要的任务。本文将详细介绍几个用于实现这一目标的关键函数，包括 fetch 、 isopen 和 rollback ，并通过具体的示例展示它们的使用方法。

1. fetch 函数：导入PostgreSQL数据库中的数据

fetch 函数的主要作用是将PostgreSQL数据库中SQL语句的执行结果导入到MATLAB中。以下是其语法和详细说明：

1.1 语法

results = fetch(conn,sqlquery)
results = fetch(conn,sqlquery,opts)
results = fetch( ___ ,Name,Value)
[results,metadata] = fetch( ___ )

1.2 描述

• results = fetch(conn,sqlquery) ：执行SQL语句 sqlquery ，并返回所有数据行。数据以批量方式导入。
• results = fetch(conn,sqlquery,opts) ：使用 SQLImportOptions 对象自定义从执行的SQL查询中导入数据的选项。
• results = fetch( ___ ,Name,Value) ：使用一个或多个名称 - 值对参数指定额外的选项。例如， 'MaxRows',5 表示导入5行数据。
• [results,metadata] = fetch( ___ ) ：除了返回结果数据外，还返回包含导入数据元信息的元数据表。

1.3 示例

以下是几个使用 fetch 函数的示例：

1.3.1 导入所有数据

datasource = "PostgreSQLDataSource";
username = "dbdev";
password = "matlab";
conn = postgresql(datasource,username,password);

sqlquery = "SELECT * FROM productTable";
data = fetch(conn,sqlquery);
head(data,3)

max(data.unitcost)

close(conn)

上述代码首先创建了一个到PostgreSQL数据库的连接，然后执行SQL查询导入 productTable 中的所有数据，并显示前3行。最后，计算并显示所有产品的最高单位成本，最后关闭数据库连接。

1.3.2 使用导入选项导入数据

datasource = "PostgreSQLDataSource";
username = "dbdev";
password = "matlab";
conn = postgresql(datasource,username,password);

employeedata = load("employees_database.mat");
emps = employeedata.employees;
depts = employeedata.departments;
sqlwrite(conn,"employees",emps)
sqlwrite(conn,"departments",depts)

sqlquery = strcat("SELECT * from employees e join departments d ", ...
    "on (e.department_id = d.department_id) WHERE ", ...
    "(job_id = 'IT_PROG' or job_id = 'SA_MAN')");
opts = databaseImportOptions(conn,sqlquery)

vars = opts.SelectedVariableNames;
varOpts = getoptions(opts,vars)

opts = setoptions(opts,"hire_date","Type","string");
opts = setoptions(opts,"department_name","Type","categorical");
opts = setoptions(opts,"first_name","Type","char");

vars = opts.SelectedVariableNames;
varOpts = getoptions(opts,vars)

opts.SelectedVariableNames = ["first_name","hire_date","department_name"];
employees_data = fetch(conn,sqlquery,opts)

execute(conn,"DROP TABLE employees")
execute(conn,"DROP TABLE departments")
close(conn)

此示例展示了如何使用 SQLImportOptions 对象自定义导入选项。首先创建数据库连接，加载员工数据并创建相应的数据库表。然后，创建一个SQL查询并使用 databaseImportOptions 函数创建导入选项对象。接着，修改某些变量的数据类型，并选择要导入的变量。最后，使用 fetch 函数导入数据，并删除创建的数据库表，关闭连接。

1.3.3 以结构体形式导入数据

datasource = "PostgreSQLDataSource";
username = "dbdev";
password = "matlab";
conn = postgresql(datasource,username,password);

patients = readtable('patients.xls');
tablename = "patients";
sqlwrite(conn,tablename,patients)

sqlquery = strcat("SELECT * FROM ",tablename);
results = fetch(conn,sqlquery,'DataReturnFormat',"structure", ...
    'MaxRows',5)

sqlquery = strcat("DROP TABLE ",tablename);
execute(conn,sqlquery)
close(conn)

该示例将 patients 表中的数据以结构体形式导入，并且只导入前5行。最后删除表并关闭连接。

1.3.4 检索导入数据的元信息

datasource = "PostgreSQLDataSource";
username = "dbdev";
password = "matlab";
conn = postgresql(datasource,username,password);

outages = readtable("outages.csv");
tablename = "outages";
sqlwrite(conn,tablename,outages, ...
    'ColumnType',["varchar(120)","timestamp","numeric(38,16)", ...
    "numeric(38,16)","timestamp","varchar(150)"])

sqlquery = "SELECT * FROM outages";
[results,metadata] = fetch(conn,sqlquery);

metadata.Properties.RowNames
metadata.VariableType
metadata.FillValue
metadata.MissingRows

index = metadata.MissingRows{5,1};
nullrestoration = results(index,:);
head(nullrestoration)

sqlstr = "DROP TABLE ";
sqlquery = strcat(sqlstr,tablename);
execute(conn,sqlquery)
close(conn)

此示例展示了如何检索导入数据的元信息，包括变量名称、数据类型、缺失值和缺失行的索引。最后，根据元信息找出包含缺失恢复时间值的前8行数据，并删除表和关闭连接。

1.4 输入参数

• conn ：PostgreSQL原生接口数据库连接对象。
• sqlquery ：SQL语句，可以是任何有效的SQL语句，包括嵌套查询和存储过程。
• opts ：数据库导入选项，为 SQLImportOptions 对象。

1.5 名称 - 值对参数

• MaxRows ：返回的最大行数，指定为正数值标量。默认情况下， fetch 函数返回执行的SQL查询的所有行。
• DataReturnFormat ：数据返回格式，可以是 'table' （默认）、 'cellarray' 、 'numeric' 或 'structure' 。
• VariableNamingRule ：变量命名规则，可以是 "preserve" （默认）或 "modify" 。

1.6 输出参数

• results ：结果数据，返回为表、单元格数组、结构体或数值矩阵。
• metadata ：元信息，返回为包含变量类型、缺失值和缺失行索引的表。

1.7 数据类型映射

PostgreSQL数据类型	MATLAB数据类型
Boolean	logical
Smallint	double
Integer	double
Bigint	double
Decimal	double
Numeric	double
Real	double
Double precision	double
Smallserial	double
Serial	double
Bigserial	double
Money	double
Varchar	string
Char	string
Text	string
Bytea	string
Timestamp	datetime
Timestampz	datetime
Abstime	datetime
Date	datetime
Time	duration
Timez	duration
Interval	calendarDuration
Reltime	calendarDuration
Enum	categorical
Cidr	string
Inet	string
Macaddr	string
Uuid	string
Xml	string

1.8 限制

• 当指定 'VariableNamingRule' 名称 - 值对参数并将 'DataReturnFormat' 设置为 cellarray 、 structure 或 numeric 时， fetch 函数会返回错误。
• 当将 SQLImportOptions 对象的 VariableNamingRule 属性设置为 "preserve" 并将 'DataReturnFormat' 设置为 structure 时， fetch 函数会返回警告。
• 当将 'VariableNamingRule' 名称 - 值对参数与 SQLImportOptions 对象 opts 一起使用时， fetch 函数会返回错误。
• 当 'VariableNamingRule' 设置为 'modify' 时，变量名 Properties 、 RowNames 和 VariableNames 是表数据类型的保留标识符，且每个变量名的长度必须小于 namelengthmax 返回的值。

2. isopen 函数：判断数据库连接是否打开

isopen 函数用于判断PostgreSQL原生接口数据库连接是否打开。

2.1 语法

i = isopen(conn)

2.2 描述

如果数据库连接打开， i 返回1；如果连接关闭或无效， i 返回0。

2.3 示例

datasource = "PostgreSQLDataSource";
username = "dbdev";
password = "matlab";
conn = postgresql(datasource,username,password);

i = isopen(conn)

sqlquery = "SELECT * FROM productTable ORDER BY productNumber";
data = fetch(conn,sqlquery);
head(data,3)

max(data.unitcost)

close(conn)

i = isopen(conn)

上述代码首先创建数据库连接，使用 isopen 函数检查连接是否打开。然后执行SQL查询，显示前3行数据并计算最高单位成本。最后关闭连接，并再次使用 isopen 函数检查连接是否关闭。

2.4 输入参数

• conn ：PostgreSQL原生接口数据库连接对象。

3. rollback 函数：撤销对PostgreSQL数据库的更改

rollback 函数用于撤销使用 sqlwrite 等函数对数据库所做的更改。

3.1 语法

rollback(conn)

3.2 描述

rollback 函数会撤销自上次 COMMIT 或 ROLLBACK 操作以来对数据库所做的所有更改。要使用此函数，必须将连接对象的 AutoCommit 属性设置为 off 。

3.3 示例

datasource = "PostgreSQLDataSource";
username = "dbdev";
password = "matlab";
conn = postgresql(datasource,username,password);

conn.AutoCommit = "off";

data = table([30;40],[500000;600000],[1000;2000],[25;30], ...
    ["Rubik's Cube";"Doll House"],'VariableNames',["productNumber" ...
    "stockNumber" "supplierNumber" "unitCost" "productDescription"]);

tablename = "toytable";
sqlwrite(conn,tablename,data)

rows = sqlread(conn,tablename)

rollback(conn)

data = sqlfind(conn,tablename)

close(conn)

上述代码首先创建数据库连接，并将 AutoCommit 属性设置为 off 。然后创建一个包含两个产品数据的MATLAB表，并将其插入到名为 toytable 的新表中。接着使用 sqlread 函数读取表中的数据。最后，使用 rollback 函数撤销更改，再次查找表时发现表已不存在，最后关闭连接。

3.4 输入参数

• conn ：PostgreSQL原生接口数据库连接对象。

4. 流程图

graph TD;
    A[创建数据库连接] --> B[执行SQL查询];
    B --> C{是否需要自定义选项};
    C -- 是 --> D[使用SQLImportOptions对象];
    C -- 否 --> E[直接导入数据];
    D --> E;
    E --> F[处理数据];
    F --> G{是否需要撤销更改};
    G -- 是 --> H[设置AutoCommit为off并执行rollback];
    G -- 否 --> I[关闭数据库连接];
    H --> I;

通过以上介绍，我们详细了解了如何使用 fetch 、 isopen 和 rollback 函数在MATLAB和PostgreSQL数据库之间进行数据交互，包括数据导入、连接状态检查和更改撤销等操作。这些函数为我们在数据分析和处理过程中提供了强大的工具。

深入探索PostgreSQL与MATLAB的数据交互功能

5. 各函数使用总结

为了更清晰地展示 fetch 、 isopen 和 rollback 函数的使用场景和关键信息，下面以表格形式进行总结：
| 函数名称 | 使用场景 | 关键参数 | 操作步骤 |
| — | — | — | — |
| fetch | 从PostgreSQL数据库导入数据到MATLAB | conn 、 sqlquery 、 opts 、 MaxRows 、 DataReturnFormat 、 VariableNamingRule | 1. 创建数据库连接；2. 定义SQL查询语句；3. 可选：创建 SQLImportOptions 对象；4. 调用 fetch 函数导入数据；5. 处理数据；6. 关闭数据库连接。 |
| isopen | 判断PostgreSQL数据库连接是否打开 | conn | 1. 创建数据库连接；2. 调用 isopen 函数检查连接状态；3. 执行相关操作；4. 关闭连接；5. 再次调用 isopen 函数检查连接状态。 |
| rollback | 撤销对PostgreSQL数据库的更改 | conn | 1. 创建数据库连接；2. 将 AutoCommit 属性设置为 off ；3. 执行数据插入等更改操作；4. 调用 rollback 函数撤销更改；5. 关闭数据库连接。 |

6. 操作注意事项

在使用上述函数进行数据交互时，有一些操作注意事项需要牢记：
- fetch 函数 ：
- 当使用 VariableNamingRule 参数时，要注意其与 DataReturnFormat 参数的搭配限制，避免出现错误或警告。
- 对于不同的数据返回格式，如 'table' 、 'cellarray' 、 'numeric' 或 'structure' ，要根据实际需求进行选择，同时注意数据类型的转换。
- isopen 函数 ：在进行数据库操作前，务必检查连接状态，确保连接已打开，避免因连接问题导致操作失败。
- rollback 函数 ：使用该函数前，必须将 AutoCommit 属性设置为 off ，否则无法撤销更改。

7. 进一步应用拓展

除了上述基本的使用场景，这些函数还可以在以下方面进行进一步的应用拓展：
- 批量数据处理 ：结合 fetch 函数的 MaxRows 参数，可以实现批量导入数据，减轻内存压力。例如：

datasource = "PostgreSQLDataSource";
username = "dbdev";
password = "matlab";
conn = postgresql(datasource,username,password);

tablename = "large_table";
sqlquery = strcat("SELECT * FROM ", tablename);
batch_size = 100;
total_rows = 1000;
for i = 1:batch_size:total_rows
    results = fetch(conn, sqlquery, 'MaxRows', batch_size, 'Offset', i - 1);
    % 处理每一批数据
    % ...
end
close(conn);

• 数据监控与恢复 ：利用 isopen 函数可以实时监控数据库连接状态，当连接异常关闭时，可以及时进行处理。同时，结合 rollback 函数，可以在数据出现错误时进行恢复。例如：

datasource = "PostgreSQLDataSource";
username = "dbdev";
password = "matlab";
conn = postgresql(datasource,username,password);

while true
    if ~isopen(conn)
        % 重新连接数据库
        conn = postgresql(datasource,username,password);
    end
    % 执行数据操作
    % ...
    try
        % 执行可能出错的操作
        % ...
    catch ME
        % 撤销更改
        conn.AutoCommit = "off";
        rollback(conn);
    end
    pause(1); % 每秒检查一次
end
close(conn);

8. 总结与展望

通过对 fetch 、 isopen 和 rollback 函数的详细介绍和示例展示，我们可以看到它们在PostgreSQL与MATLAB数据交互中发挥着重要作用。 fetch 函数提供了灵活的数据导入方式， isopen 函数确保了数据库连接的稳定性， rollback 函数则为数据更改提供了安全保障。

在未来的数据分析和处理中，随着数据量的不断增大和业务需求的不断变化，这些函数的应用场景也将更加广泛。我们可以进一步探索它们与其他MATLAB函数和工具的结合使用，实现更复杂的数据处理和分析任务。同时，也可以关注数据库技术和MATLAB的发展，不断优化数据交互的效率和性能。

9. 操作流程列表总结

为了方便大家快速回顾操作流程，下面以列表形式总结各函数的操作步骤：
- fetch 函数操作流程 ：
1. 定义数据源、用户名和密码，创建数据库连接。
2. 编写SQL查询语句。
3. 可选：创建 SQLImportOptions 对象，自定义导入选项。
4. 调用 fetch 函数，根据需要设置名称 - 值对参数。
5. 处理导入的数据。
6. 关闭数据库连接。
- isopen 函数操作流程 ：
1. 创建数据库连接。
2. 调用 isopen 函数检查连接状态。
3. 执行相关数据库操作。
4. 关闭数据库连接。
5. 再次调用 isopen 函数检查连接状态。
- rollback 函数操作流程 ：
1. 创建数据库连接。
2. 将 AutoCommit 属性设置为 off 。
3. 执行数据插入等更改操作。
4. 调用 rollback 函数撤销更改。
5. 关闭数据库连接。

通过以上的介绍和总结，希望大家能够熟练掌握 fetch 、 isopen 和 rollback 函数的使用，在实际的数据交互和处理中发挥它们的最大作用。