esp8266_arduino直连postgresql

qingkong · 发表于 2020-3-30 14:43:45

爱科技、爱创意、爱折腾、爱极致，我们都是技术控

您需要登录才可以下载或查看，没有账号？立即注册

x

传感器直接连接数据库，不是物联网的正确打开方式。物联网还是mqtt之类的比较正常。
但是，很多时候都想简单了再简单，于是直连数据库就成了必须的功能。。。。 :lol:

postgresql这个arduino默认库里没有，去这里下来之后放到 libraries 里

https://github.com/ethanak/SimplePgSQL

复制代码

以下是代码，只保证可用

/*
* SimplePgSQL.c - Lightweight PostgreSQL connector for Arduino
* Copyright (C) Bohdan R. Rau 2016 <ethanak@polip.com>
*
* SimplePgSQL is free software; you can redistribute it and/or
* modify it under the terms of the GNU Lesser General Public
* License as published by the Free Software Foundation; either
* version 2.1 of the License, or (at your option) any later version.
*
* SimplePgSQL is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
* Lesser General Public License for more details.
*
* You should have received a copy of the GNU Lesser General Public
* License along with SimplePgSQL. If not, write to:
* The Free Software Foundation, Inc.,
* 51 Franklin Street, Fifth Floor
* Boston, MA 02110-1301, USA.
*/
/*
* Demo program for SimplePgSQL library
* Simple PostgreSQL console
* Accepts:
* - PostgreSQL simple queries
* - \d - displays table list
* - \d tablename - list table columns
* - exit - closes connection
*/
#include <stdlib.h>
#ifdef ESP8266
#include <ESP8266WiFi.h>
#include <DNSServer.h>
#include <ESP8266WebServer.h>
#include <WiFiManager.h>
#else
// Uncomment line below if you use WiFi shield instead of Ethernet
// #define USE_ARDUINO WIFI 1
#ifdef USE_ARDUINO_WIFI
#include <WIFI.h>
#else
#include <Ethernet.h>
#endif
#endif
#include <SimplePgSQL.h>
IPAddress PGIP(192, 168, 1, 5); // your PostgreSQL server IP
//const char ssid[] = "network_ssid"; // your network SSID (name)
//const char pass[] = "network_pass"; // your network password
const char user[] = "esp"; // your database user
const char password[] = "Espsdsdsdsd"; // your database password
const char dbname[] = "esp"; // your database name
#if defined(ESP8266) || defined(USE_ARDUINO_WIFI)
int WiFiStatus;
WiFiClient client;
#else
#define USE_ARDUINO_ETHERNET 1
EthernetClient client;
byte mac[] = {0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED}; // your mac address
byte ip[] = {192, 168, 1, 177}; // your IP address
#endif
char buffer[1024];
PGconnection conn(&client, 0, 1024, buffer);
void setup(void) {
Serial.begin(
#ifdef ESP8266
115200
#else
9600
#endif
);
#ifdef USE_ARDUINO_ETHERNET
Ethernet.begin(mac, ip);
#else
//WiFi.begin((char*) ssid, pass);
WiFiManager wifiManager;
#endif
// WiFiManager wifiManager;
//reset saved settings
//wifiManager.resetSettings();
//set custom ip for portal
//wifiManager.setAPStaticIPConfig(IPAddress(10,0,1,1), IPAddress(10,0,1,1), IPAddress(255,255,255,0));
//fetches ssid and pass from eeprom and tries to connect
//if it does not connect it starts an access point with the specified name
//here "AutoConnectAP"
//and goes into a blocking loop awaiting configuration
// wifiManager.autoConnect("AutoConnectAP");
//or use this for auto generated name ESP + ChipID
//wifiManager.autoConnect();
//将到期超时设置为240秒：
wifiManager.setConfigPortalTimeout(240);
if (!wifiManager.autoConnect("ESP_AP", "changeit")) {
Serial.println(F("Failed to connect. Reset and try again. . ."));
delay(3000);
//重置并重试
ESP.reset();
delay(5000);
}
//在我们从网络浏览器中选择Wi-Fi网络和密码后，我们现在连接到Wi-Fi网络：
//如果你到这里，你已经连接到WiFi
Serial.println(F("Connected to Wifi."));
Serial.print(F("My IP:"));
Serial.println(WiFi.localIP());
String hostname = "esp8266_";
hostname += ESP.getChipId();
WiFi.hostname(hostname);
// ADD_STARTUP_OPTION_P(PSTR("application_name"), PSTR("arduino"));
}
#ifndef USE_ARDUINO_ETHERNET
void checkConnection() {
int status = WiFi.status();
if (status != WL_CONNECTED) {
if (WiFiStatus == WL_CONNECTED) {
Serial.println("Connection lost");
WiFiStatus = status;
}
} else {
if (WiFiStatus != WL_CONNECTED) {
Serial.println("Connected");
WiFiStatus = status;
}
}
}
#endif
/*
static PROGMEM const char query_rel[] = "\
SELECT a.attname "Column",\
pg_catalog.format_type(a.atttypid, a.atttypmod) "Type",\
case when a.attnotnull then 'not null ' else 'null' end as "null",\
(SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)\
FROM pg_catalog.pg_attrdef d\
WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) "Extras"\
FROM pg_catalog.pg_attribute a, pg_catalog.pg_class c\
WHERE a.attrelid = c.oid AND c.relkind = 'r' AND\
c.relname = %s AND\
pg_catalog.pg_table_is_visible(c.oid)\
AND a.attnum > 0 AND NOT a.attisdropped\
ORDER BY a.attnum";
static PROGMEM const char query_tables[] = "\
select * from esp_insert limit 10;";
*/
int pg_status = 0;
void doPg(void) {
/*
*
setDbLogin
int setDbLogin(IPAddress server,
const char *user,
const char *passwd = NULL,
const char *db = NULL,
const char *charset = NULL,
int port = 5432);
Initialize connection.
*/
char *msg;
int rc;
if (!pg_status) {
conn.setDbLogin(PGIP, user, password, dbname, "utf8", 9988);
pg_status = 1;
Serial.println("conn.setDbLogin");
return;
}
if (pg_status == 1) {
rc = conn.status();
if (rc == CONNECTION_BAD || rc == CONNECTION_NEEDED) {
char *c = conn.getMessage();
if (c)
Serial.println(c);
pg_status = -1;
} else if (rc == CONNECTION_OK) {
pg_status = 2;
Serial.println("Enter query");
}
return;
}
/*
if (pg_status == 2) {
if (!Serial.available())
return;
char inbuf[64];
int n = Serial.readBytesUntil('\n', inbuf, 63);
while (n > 0) {
if (isspace(inbuf[n - 1]))
n--;
else
break;
}
inbuf[n] = 0;
if (!strcmp(inbuf, "\\d")) {
if (conn.execute(query_tables, true))
goto error;
Serial.println("Working...");
pg_status = 3;
return;
}
if (!strncmp(inbuf, "\\d", 2) && isspace(inbuf[2])) {
char *c = inbuf + 3;
while (*c && isspace(*c))
c++;
if (!*c) {
if (conn.execute(query_tables, true))
goto error;
Serial.println("Working...");
pg_status = 3;
return;
}
if (conn.executeFormat(true, query_rel, c))
goto error;
Serial.println("Working...");
pg_status = 3;
return;
}
*/if (pg_status == 2) {
// int random(int num);
// random函数返回一个0~num-1之间的随机数. random(num)是在stdlib.h中的一个宏定义. num和函数返回值都是整型数.
String sql_query =
" insert \
into esp_insert ( datenow, vaules, s_id, k_id ,up_addr,client_addr )\
select \
now() dateNow, ";
sql_query += random(1000);
sql_query += " vaules, '001' s_iD, '";
sql_query += ESP.getChipId();
sql_query += "',inet_client_addr(),'";
sql_query += WiFi.localIP().toString();
sql_query += "';";
/*
String str1 = String(along);
str1 += "mimi";
char cArr[str1.length() + 1];
char cArr2[str1.length() + 3];
str1.toCharArray(cArr,str1.length() + 1);
str1.toCharArray(cArr2,str1.length() + 3);
*/
char query_rel[sql_query.length() + 1];
sql_query.toCharArray(query_rel, sql_query.length() + 1);
/*
static PROGMEM const char query_rel[] =
" insert \
into esp_insert ( datenow, vaules, s_id, k_id )\
select \
now() dateNow, 12 vaules, '001' s_iD, '\ ESP.getChipId();";
*/
//if (conn.execute(query_tables, true))
if (conn.execute(query_rel, true))
goto error;
Serial.println("Working...");
pg_status = 3;
/*
if (!strncmp(inbuf, "exit", 4)) {
conn.close();
Serial.println("Thank you");
pg_status = -1;
return;
}
if (conn.execute(inbuf))
goto error;
Serial.println("Working...");
pg_status = 3;
*/
}
if (pg_status == 3) {
rc = conn.getData();
int i;
if (rc < 0)
goto error;
if (!rc)
return;
if (rc & PG_RSTAT_HAVE_COLUMNS) {
for (i = 0; i < conn.nfields(); i++) {
if (i)
Serial.print(" | ");
Serial.print(conn.getColumn(i));
}
Serial.println("\n==========");
} else if (rc & PG_RSTAT_HAVE_ROW) {
for (i = 0; i < conn.nfields(); i++) {
if (i)
Serial.print(" | ");
msg = conn.getValue(i);
if (!msg)
msg = (char*) "NULL";
Serial.print(msg);
}
Serial.println();
} else if (rc & PG_RSTAT_HAVE_SUMMARY) {
Serial.print("Rows affected: ");
Serial.println(conn.ntuples());
} else if (rc & PG_RSTAT_HAVE_MESSAGE) {
msg = conn.getMessage();
if (msg)
Serial.println(msg);
}
if (rc & PG_RSTAT_READY) {
pg_status = 2;
Serial.println("Enter query");
}
}
return;
error: msg = conn.getMessage();
if (msg)
Serial.println(msg);
else
Serial.println("UNKNOWN ERROR");
if (conn.status() == CONNECTION_BAD) {
Serial.println("Connection is bad");
pg_status = -1;
}
conn.close();
}
void loop() {
#ifndef USE_ARDUINO_ETHERNET
checkConnection();
if (WiFiStatus == WL_CONNECTED) {
#endif
doPg();
#ifndef USE_ARDUINO_ETHERNET
}
#endif
delay(1000);
}

复制代码

人艰不拆了 · 发表于 2020-3-30 17:07:50

哈哈，好长的代码，看了二行，看不懂了:titter:

qingkong · 发表于 2020-3-30 17:15:50

人艰不拆了发表于 2020-3-30 17:07
哈哈，好长的代码，看了二行，看不懂了

代码挺简单了。。。毕竟资源有限，实现不了太复杂的操作。

ESP8266还是要上网啊，上网就要存数据啊
物联网么，就是在网上存数取数存数取数 :biggrin:

这东西已经和传统的控制用单片机的应用不太一样了。多数应用追求啥功耗，效率都不是第一位的了。毕竟网络的速度就够慢的，而发射功率都不小

fryefryefrye · 发表于 2020-3-31 22:19:41

本帖最后由 fryefryefrye 于 2020-3-31 22:22 编辑

我都懒得用MQTT，我家里几十个ESP8266和我的树莓派主机，直接互发UDP包。

定义一个数据结构，用一个UDP包整体发送。接收方用同一个数据结构，指针指以下，所有的变量就解析好了。

服务端也懒得搞数据库，所有的日志文件直接存文本文件，每天新建一个文件。平时也不会查看这些文件的，主要是在网页端画曲线图，我所记录的文本文件的格式可以直接喂给网页端把曲线图显示出来。

坏处就是每页最多显示一天的数据。

qingkong · 发表于 2020-3-31 23:46:13

fryefryefrye 发表于 2020-3-31 22:19
我都懒得用MQTT，我家里几十个ESP8266和我的树莓派主机，直接互发UDP包。

定义一个数据结构，用一个UDP包 ...

你家里那么多esp8266 都干啥用啊……

数据还是入库各种都方便，如果有数据生成第一考虑还是入库

而postgresql 一样有arm版本
我是跑香橙派的……树莓派太贵买不起………

mqtt不仅仅是上传下达第三方接收也能干点别的

qingkong · 发表于 2020-4-1 00:00:02

postgresql 可以直接接收或者返回json
对于表还可以连http也不写直接上 PostgREST 直接提供http的json结果

要偷懒这玩意是个好选择:lol:

fryefryefrye · 发表于 2020-4-1 22:04:07

qingkong 发表于 2020-3-31 23:46
你家里那么多esp8266 都干啥用啊……

数据还是入库各种都方便，如果有数据生成第一考虑还是入库

比如4台空调，每台一个。每个房间的灯光控制。家里各个自动门的控制。315mhz设备的控制。门禁系统等等，都是8266.

数据库当年学的很熟的，最近工作也偶尔用Access数据库，但是自己DIY，我是用树莓派直接把Log文件写到了NAS服务器上了，直接代码printf就行。清理老数据，删删旧文件就行了。

Linux这种安装上的轻型数据库，存储出来是一个文件吗？文件放哪儿？

qingkong · 发表于 2020-4-2 08:19:14

fryefryefrye 发表于 2020-4-1 22:04
比如4台空调，每台一个。每个房间的灯光控制。家里各个自动门的控制。315mhz设备的控制。门禁系统等等， ...

postgresql可不是access那种简单数据库。。。与它比 pg就是核弹级别的

pg 与 sql server 是同类型的但是跨平台和扩展性更好

单文件数据库的话，我现在更倾向于选择sqlit

不能跨平台的 win 专用的软件我现在基本上不考虑

[Arduino] esp8266_arduino直连postgresql

爱科技、爱创意、爱折腾、爱极致，我们都是技术控

打赏

相关帖子

打赏

账号		自动登录	找回密码
密码			立即注册