使用dart将数据插入Postgres数据库

Question

我有一个postgres数据库，其中包含一个名为"用户"；。该表有四个字段：

• id二进制主键
• display_name TEXT NOT NULL
• 电子邮件文本不为空
• 电话号码文本

我想从dart代码中添加数据。我的尝试看起来是这样的：

import "dart:io";
import "package:postgres/postgres.dart";
void main() async{
var connection = PostgreSQLConnection(
"localhost", 5432, "test",
username: "something", password: "notTellingYou");
try {
await connection.open();
} catch (e) {
print(e);
}
Map<String, dynamic> data = {
"display_name": "foo",
"email": "[e-mail]"
};
await setData(connection, "users", data);

}
Future<void> setData(PostgreSQLConnection connection, String table, Map<String, dynamic> data) async {
await connection.query("""
INSERT INTO $table(${data.keys})
VALUES ${data.values};
""");
}

问题是我得到了一个异常

(PostgreSQL Severity.error 42601：在"display_name"处或附近出现语法错误(

Answer 1

尝试使用变量替换：

import "dart:io";
import "package:postgres/postgres.dart";
Future<void> main() async {
final connection = PostgreSQLConnection("localhost", 5432, "test",
username: "something", password: "notTellingYou");
await connection.open();
final data = <String, dynamic>{
"display_name": "foo",
"email": "[e-mail]"
};
await setData(connection, "users", data);
await connection.close();
}
Future<void> setData(PostgreSQLConnection connection, String table,
Map<String, dynamic> data) async {
await connection.execute(
'INSERT INTO $table (${data.keys.join(', ')}) VALUES (${data.keys.map((k) => '@$k').join(', ')})',
substitutionValues: data);
}

生成的文本为："INSERT INTO users (display_name, email) VALUES (@display_name, @email)"，每个变量(以@开头(将自动替换为映射中每个键的值。

这也将确保值中特殊字符的正确转义。