59、利用MATLAB实现PostgreSQL数据库数据的读写操作

白露未晞593

于 2025-06-29 16:34:22 发布

阅读量83

点赞数

CC 4.0 BY-SA版权

分类专栏：数据库工具箱：MATLAB数据操作指南文章标签： MATLAB PostgreSQL sqlread

本文链接：https://blog.youkuaiyun.com/bash7scripter/article/details/149382134

数据库工具箱：MATLAB数据操作指南专栏收录该内容

76 篇文章 ¥499.90

订阅专栏¥69.90

会员秒杀 ¥9.9 重磅福利

超级会员免费看

利用MATLAB实现PostgreSQL数据库数据的读写操作

在数据分析和处理中，经常需要从数据库中读取数据到MATLAB进行分析，或者将MATLAB中的数据插入到数据库中。本文将详细介绍如何使用MATLAB的 sqlread 和 sqlwrite 函数实现与PostgreSQL数据库的数据交互。

1. `sqlread` 函数：从PostgreSQL数据库表导入数据到MATLAB

sqlread 函数用于从PostgreSQL数据库表中导入数据到MATLAB，其语法如下：

data = sqlread(conn,tablename)
data = sqlread(conn,tablename,opts)
data = sqlread( ___ ,Name,Value)
[data,metadata] = sqlread( ___ )

data = sqlread(conn,tablename) ：从指定的PostgreSQL数据库表中导入数据到MATLAB，并返回一个表。此操作相当于在ANSI SQL中执行 SELECT * FROM tablename 语句。
data = sqlread(conn,tablename,opts) ：使用 SQLImportOptions 对象自定义从数据库表导入数据的选项。
data = sqlread( ___ ,Name,Value) ：使用一个或多个名称 - 值对参数指定额外的选项。
[data,metadata] = sqlread( ___ ) ：除了返回导入的数据表 data 外，还返回包含导入数据元信息的元数据表 metadata 。

1.1 输入参数

conn ：PostgreSQL原生接口数据库连接对象。
tablename ：数据库表名，以字符向量或字符串标量表示。
opts ：数据库导入选项，为 SQLImportOptions 对象。
名称 - 值对参数：
Catalog ：数据库目录名，以字符向量或字符串标量表示。
Schema ：数据库模式名，以字符向量或字符串标量表示。
MaxRows ：返回的最大行数，为正数值标量。
VariableNamingRule ：变量命名规则，可选值为 "preserve" （默认）或 "modify" 。

1.2 输出参数

data ：导入的数据，以表的形式返回。
metadata ：元信息，以表的形式返回，包含 VariableType 、 FillValue 和 MissingRows 等变量。

1.3 数据类型转换

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.4 示例

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

示例1：使用PostgreSQL原生接口从数据库表导入数据

datasource = "PostgreSQLDataSource";
username = "dbdev";
password = "matlab";
conn = postgresql(datasource,username,password);
tablename = "productTable";
data = sqlread(conn,tablename);
head(data,3)
close(conn)

示例2：使用导入选项从数据库表导入数据

datasource = "PostgreSQLDataSource";
username = "dbdev";
password = "matlab";
conn = postgresql(datasource,username,password);
patients = readtable("patients.xls");
tablename = "patients";
sqlwrite(conn,tablename,patients);
opts = databaseImportOptions(conn,tablename);
vars = opts.SelectedVariableNames;
varOpts = getoptions(opts,vars);
opts = setoptions(opts,{'gender','location','selfassessedhealthstatus'}, 'Type','categorical');
opts = setoptions(opts,'smoker','Type','double');
varOpts = getoptions(opts,{'gender','location','smoker', 'selfassessedhealthstatus'});
data = sqlread(conn,tablename,opts);
tail(data)
summary(data)
sqlquery = strcat("DROP TABLE ",tablename);
execute(conn,sqlquery);
close(conn)

示例3：限制导入数据的行数

datasource = "PostgreSQLDataSource";
username = "dbdev";
password = "matlab";
conn = postgresql(datasource,username,password);
tablename = "productTable";
data = sqlread(conn,tablename,'MaxRows',10);
head(data,3)
data.productdescription(1:3)
column = "productdescription";
data = sortrows(data,column);
data.productdescription(1:3)
close(conn)

示例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)"]);
[data,metadata] = sqlread(conn,tablename);
metadata.Properties.RowNames
metadata.VariableType
metadata.FillValue
metadata.MissingRows
index = metadata.MissingRows{5,1};
nullrestoration = data(index,:);
head(nullrestoration)
sqlstr = "DROP TABLE ";
sqlquery = strcat(sqlstr,tablename);
execute(conn,sqlquery);
close(conn)

2. `sqlwrite` 函数：将MATLAB数据插入到PostgreSQL数据库表

sqlwrite 函数用于将MATLAB表中的数据插入到PostgreSQL数据库表中，其语法如下：

sqlwrite(conn,tablename,data)
sqlwrite(conn,tablename,data,Name,Value)

sqlwrite(conn,tablename,data) ：将MATLAB表中的数据插入到数据库表中。如果表已存在，则将MATLAB表中的数据作为行追加到现有数据库表中；如果表不存在，则创建一个具有指定表名的表，然后将数据作为行插入到新表中。
sqlwrite(conn,tablename,data,Name,Value) ：使用一个或多个名称 - 值对参数指定额外的选项。

2.1 输入参数

conn ：PostgreSQL原生接口数据库连接对象。
tablename ：数据库表名，以字符向量或字符串标量表示。
data ：要插入的数据，以表的形式指定。
名称 - 值对参数：
Catalog ：数据库目录名，以字符向量或字符串标量表示。
Schema ：数据库模式名，以字符向量或字符串标量表示。
ColumnType ：数据库列类型，以字符向量、字符串标量、字符向量的单元格数组或字符串数组表示。

2.2 数据类型要求

现有表 ：MATLAB表的变量名必须与数据库表的列名匹配， sqlwrite 函数区分大小写。
| MATLAB表变量的数据类型 | 现有数据库列的数据类型 |
| — | — |
| 数值数组或数值数组的单元格数组 | smallint、integer、bigint、decimal、numeric、real、double precision、smallserial、serial、bigserial |
| 字符向量的单元格数组、字符串数组、datetime数组或duration数组 | date、time、timestamp |
| 日历持续时间数组 | interval |
| 逻辑数组或逻辑数组的单元格数组 | bit |
| 字符向量的单元格数组或字符串数组 | char、varchar |
新表：指定的新数据库表名必须在数据库的所有表中唯一。
| MATLAB表变量的数据类型 | 数据库列的默认数据类型 |
| — | — |
| int8数组 | smallint |
| int16数组 | smallint |
| int32数组 | integer |
| int64数组 | bigint |
| 逻辑数组 | boolean |
| single或double数组 | numeric |
| datetime数组 | timestamp |
| duration数组 | time |
| 日历持续时间数组 | interval |
| 字符向量的单元格数组或字符串数组 | varchar |

2.3 接受的缺失数据

MATLAB表变量的数据类型	数据库列的数据类型	接受的缺失数据
datetime数组	date或timestamp	NaT
duration数组	time	NaN
日历持续时间数组	interval	NaN
double或single数组或double或single数组的单元格数组	numeric	NaN、[]或’‘
字符向量的单元格数组	date或timestamp	‘NaT’或’‘
字符向量的单元格数组	time	‘NaN’或’‘
字符向量的单元格数组	char、varchar或其他文本数据类型	’‘
字符串数组	date或timestamp	”“、”NaT”或missing
字符串数组	time	”“、”NaN”或missing
字符串数组	char、varchar或其他文本数据类型	missing

2.4 示例

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

示例1：将数据追加到现有表中

datasource = "PostgreSQLDataSource";
username = "dbdev";
password = "matlab";
conn = postgresql(datasource,username,password);
tablename = "productTable";
rows = sqlread(conn,tablename);
tail(rows,3)
data = table(30,500000,1000,25,"Rubik's Cube", 'VariableNames',["productnumber" "stocknumber" "suppliernumber" "unitcost" "productdescription"]);
sqlwrite(conn,tablename,data);
rows = sqlread(conn,tablename);
tail(rows,4)
close(conn)

示例2：将数据插入到新表中

datasource = "PostgreSQLDataSource";
username = "dbdev";
password = "matlab";
conn = postgresql(datasource,username,password);
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);
rows
close(conn)

示例3：插入数据到新表时指定列类型

datasource = "PostgreSQLDataSource";
username = "dbdev";
password = "matlab";
conn = postgresql(datasource,username,password);
data = table([30;40],[500000;600000],[1000;2000],[25;30], ["Rubik's Cube";"Doll House"],'VariableNames',["productnumber" "stocknumber" "suppliernumber" "unitcost" "productdescription"]);
tablename = "toytable";
coltypes = ["numeric" "numeric" "numeric" "numeric" "varchar(255)"];
sqlwrite(conn,tablename,data,'ColumnType',coltypes);
rows = sqlread(conn,tablename);
rows
close(conn)

通过以上介绍，你可以使用 sqlread 和 sqlwrite 函数方便地实现MATLAB与PostgreSQL数据库之间的数据交互。在实际应用中，根据具体需求选择合适的函数和参数，确保数据的正确读写。

3. 操作流程总结

3.1 使用 `sqlread` 函数的操作流程

下面是使用 sqlread 函数从PostgreSQL数据库表导入数据的详细操作流程：
1. 建立数据库连接 ：使用 postgresql 函数创建一个PostgreSQL原生接口数据库连接对象。

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

指定数据库表名 ：确定要导入数据的数据库表名。

tablename = "productTable";

选择导入方式 ：
- 基本导入 ：直接使用 sqlread 函数导入数据。

data = sqlread(conn,tablename);

- **使用导入选项**：创建`SQLImportOptions`对象并设置选项后导入数据。

opts = databaseImportOptions(conn,tablename);
% 设置选项
opts = setoptions(opts,{'gender','location','selfassessedhealthstatus'}, 'Type','categorical');
opts = setoptions(opts,'smoker','Type','double');
data = sqlread(conn,tablename,opts);

- **限制行数导入**：使用`MaxRows`参数限制导入的行数。

data = sqlread(conn,tablename,'MaxRows',10);

- **检索元信息**：同时返回导入数据和元信息。

[data,metadata] = sqlread(conn,tablename);

关闭数据库连接 ：操作完成后关闭数据库连接。

close(conn);

下面是该流程的mermaid流程图：

graph TD;
    A[建立数据库连接] --> B[指定数据库表名];
    B --> C{选择导入方式};
    C -->|基本导入| D[使用sqlread(conn,tablename)导入数据];
    C -->|使用导入选项| E[创建并设置SQLImportOptions对象后导入];
    C -->|限制行数导入| F[使用MaxRows参数导入];
    C -->|检索元信息| G[同时返回数据和元信息];
    D --> H[关闭数据库连接];
    E --> H;
    F --> H;
    G --> H;

3.2 使用 `sqlwrite` 函数的操作流程

使用 sqlwrite 函数将MATLAB数据插入到PostgreSQL数据库表的操作流程如下：
1. 建立数据库连接 ：同样使用 postgresql 函数创建连接对象。

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

准备要插入的数据 ：将数据整理成MATLAB表。

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);

- **指定列类型插入**：使用`ColumnType`参数指定列类型后插入数据。

coltypes = ["numeric" "numeric" "numeric" "numeric" "varchar(255)"];
sqlwrite(conn,tablename,data,'ColumnType',coltypes);

关闭数据库连接 ：完成插入操作后关闭连接。

close(conn);

下面是该流程的mermaid流程图：

graph TD;
    A[建立数据库连接] --> B[准备要插入的数据];
    B --> C[指定数据库表名];
    C --> D{选择插入方式};
    D -->|基本插入| E[使用sqlwrite(conn,tablename,data)插入数据];
    D -->|指定列类型插入| F[使用ColumnType参数插入];
    E --> G[关闭数据库连接];
    F --> G;

4. 注意事项和限制

4.1 `sqlread` 函数的限制

VariableNamingRule 参数限制 ：
- 不能将 VariableNamingRule 参数与 SQLImportOptions 对象 opts 一起使用，否则 sqlread 函数会返回错误。
- 当 VariableNamingRule 参数设置为 "modify" 时，变量名 Properties 、 RowNames 和 VariableNames 是表数据类型的保留标识符，且每个变量名的长度必须小于 namelengthmax 返回的值。

4.2 `sqlwrite` 函数的注意事项

数据类型匹配 ：在插入数据时，要确保MATLAB表变量的数据类型与数据库表列的数据类型匹配，特别是对于现有表，变量名必须与列名严格匹配，并且 sqlwrite 函数区分大小写。
缺失数据处理 ：不同的数据类型对应不同的可接受缺失数据，在插入数据时要根据数据类型正确处理缺失数据，否则可能会导致插入失败。

5. 总结

通过 sqlread 和 sqlwrite 函数，我们可以方便地实现MATLAB与PostgreSQL数据库之间的数据交互。 sqlread 函数可以从数据库表中导入数据到MATLAB，支持多种导入方式和元信息检索； sqlwrite 函数可以将MATLAB表中的数据插入到数据库表中，支持插入到现有表或创建新表并插入数据，还可以指定列类型。在使用这些函数时，要注意输入参数的设置、数据类型的匹配以及相关的限制和注意事项，以确保数据的正确读写。希望本文的介绍能帮助你更好地使用这两个函数进行数据交互。