Discussion :

[toufous35]

Salut à tous!

Je voudrais avoir un coup de main de votre part, car je galère pour mettre au point mon script python.

Je précise que je suis novice avec quelques notions relatives issus de mes essais divers et autres bidouillages.

Donc pour mon soucis,

J'ai fait un script en python qui interroge un Arduino via le port Serial, lit la réponse et l'envoi dans une BDD MySQL.

Je vous donne le Pgm Arduino, le script Python et l'imprime écran de mon retour d'erreur:

Code Arduino :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
 
#include "EmonLib.h"             // Include Emon Library
 
EnergyMonitor emon1,emon2,emon3,emon4,emon5,emon6,emon7,emon8,emon9,emon10,emon11,emon12 ;             // Create an instance
 
int message = 0;
int ledPin = 13;
 
void capture()
{
  digitalWrite(ledPin, HIGH);
 
  emon1.calcVI(20,2000);         // Calculate all. No.of half wavelengths (crossings), time-out
  emon2.calcVI(20,2000);         // Calculate all. No.of half wavelengths (crossings), time-out
  emon3.calcVI(20,2000);         // Calculate all. No.of half wavelengths (crossings), time-out
  emon3.calcVI(20,2000);           // Print out all variables (realpower, apparent power, Vrms, Irms, power factor)
  emon4.calcVI(20,2000);           // Print out all variables (realpower, apparent power, Vrms, Irms, power factor)
  emon5.calcVI(20,2000);         // Calculate all. No.of half wavelengths (crossings), time-out
  emon6.calcVI(20,2000);         // Calculate all. No.of half wavelengths (crossings), time-out
  emon7.calcVI(20,2000);         // Calculate all. No.of half wavelengths (crossings), time-out
  emon8.calcVI(20,2000);         // Calculate all. No.of half wavelengths (crossings), time-out
  emon9.calcVI(20,2000);         // Calculate all. No.of half wavelengths (crossings), time-out
  emon10.calcVI(20,2000);         // Calculate all. No.of half wavelengths (crossings), time-out
  emon11.calcVI(20,2000);         // Calculate all. No.of half wavelengths (crossings), time-out
  emon12.calcVI(20,2000);         // Calculate all. No.of half wavelengths (crossings), time-out
 
  float realPower       = emon1.realPower;        //extract Real Power into variable
  float apparentPower   = emon1.apparentPower;    //extract Apparent Power into variable
  float powerFActor     = emon1.powerFactor;      //extract Power Factor into Variable
  float supplyVoltage   = emon1.Vrms;             //extract Vrms into Variable
  float Irms            = emon1.Irms;             //extract Irms into Variable
 
  digitalWrite(ledPin, LOW);
 
}
void envoi()
{ 
 
  // passage du float en int
  int a1, a2, a3, a4, a5, a6, a7, a8, a9, a10, a11, a12;
  a1 = ((int) emon1.realPower / random(100,350));
  a2 = ((int) emon2.realPower / random(100,350));
  a3 = ((int) emon3.realPower / random(100,350));
  a4 = ((int) emon4.realPower / random(100,350));
  a5 = ((int) emon5.realPower / random(100,350));
  a6 = ((int) emon6.realPower / random(100,350));
  a7 = ((int) emon7.realPower / random(100,350));
  a8 = ((int) emon8.realPower / random(100,350));
  a9 = ((int) emon9.realPower / random(100,350));
  a10 = ((int) emon10.realPower / random(100,350));
  a11 = ((int) emon11.realPower / random(100,350));
  a12 = ((int) emon12.realPower / random(100,350));
 
  Serial.print(", "); 
  Serial.print((char) 34);
  Serial.print(a1);
  Serial.print((char) 34);
  Serial.print(", ");
  Serial.print((char) 34);
  Serial.print(a2);
  Serial.print((char) 34);
  Serial.print(", ");
  Serial.print((char) 34);
  Serial.print(a3);
  Serial.print((char) 34);
  Serial.print(", ");
  Serial.print((char) 34);
  Serial.print(a4);
  Serial.print((char) 34);
  Serial.print(", ");
  Serial.print((char) 34);
  Serial.print(a5);
  Serial.print((char) 34);
  Serial.print(", ");
  Serial.print((char) 34);
  Serial.print(a6);
  Serial.print((char) 34);
  Serial.print(", ");
  Serial.print((char) 34);
  Serial.print(a7);
  Serial.print((char) 34);
  Serial.print(", ");
  Serial.print((char) 34);
  Serial.print(a8);
  Serial.print((char) 34);
  Serial.print(", ");
  Serial.print((char) 34);
  Serial.print(a9);
  Serial.print((char) 34);
  Serial.print(", ");
  Serial.print((char) 34);
  Serial.print(a10);
  Serial.print((char) 34);
  Serial.print(", ");
  Serial.print((char) 34);
  Serial.print(a11);
  Serial.print((char) 34);
  Serial.print(", ");
  Serial.print((char) 34);
  Serial.print(a12);
  Serial.print((char) 34);
  Serial.println(")");
 
}
void setup()
{  
  Serial.begin(57600);
  pinMode(ledPin, OUTPUT);
 
  // Ref Voltage
  emon1.voltage(0, 234.26, 1.7);  // Voltage: input pin, calibration, phase_shift
  emon2.voltage(0, 234.26, 1.7);  // Voltage: input pin, calibration, phase_shift
  emon3.voltage(0, 234.26, 1.7);  // Voltage: input pin, calibration, phase_shift
  emon4.voltage(0, 234.26, 1.7);  // Voltage: input pin, calibration, phase_shift
  emon5.voltage(0, 234.26, 1.7);  // Voltage: input pin, calibration, phase_shift
  emon6.voltage(0, 234.26, 1.7);  // Voltage: input pin, calibration, phase_shift
  emon7.voltage(0, 234.26, 1.7);  // Voltage: input pin, calibration, phase_shift
  emon8.voltage(0, 234.26, 1.7);  // Voltage: input pin, calibration, phase_shift
  emon9.voltage(0, 234.26, 1.7);  // Voltage: input pin, calibration, phase_shift
  emon10.voltage(0, 234.26, 1.7);  // Voltage: input pin, calibration, phase_shift
  emon11.voltage(0, 234.26, 1.7);  // Voltage: input pin, calibration, phase_shift
  emon12.voltage(0, 234.26, 1.7);  // Voltage: input pin, calibration, phase_shift
 
  // Pin Current
  emon1.current(1, 111.1);       // Current: input pin, calibration.
  emon2.current(2, 111.1);       // Current: input pin, calibration.
  emon3.current(3, 111.1);       // Current: input pin, calibration.
  emon4.current(4, 111.1);       // Current: input pin, calibration.
  emon5.current(5, 111.1);       // Current: input pin, calibration.
  emon6.current(6, 111.1);       // Current: input pin, calibration.
  emon7.current(7, 111.1);       // Current: input pin, calibration.
  emon8.current(8, 111.1);       // Current: input pin, calibration.
  emon9.current(9, 111.1);       // Current: input pin, calibration.
  emon10.current(10, 111.1);       // Current: input pin, calibration.
  emon11.current(11, 111.1);       // Current: input pin, calibration.
  emon12.current(12, 111.1);       // Current: input pin, calibration.
 
}
 
 
void loop() {
  if (Serial.available())  {
    message = Serial.read()-'0';  // on soustrait le caractère 0, qui vaut 48 en ASCII
 
    switch (message) { 
    case 2: 
       capture();
       break;
    case 5: 
       envoi();
       break;
    }
  }
 
}

Script Python :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
import mysql.connector
import serial
import sys
import time
from datetime import datetime
ser = serial.Serial('/dev/ttyACM0', 57600)
time.sleep(1)
ser.write('2')
print("Calcul en cours !")
time.sleep(60)
ser.write('5')
print("Reception !")
i=1
while i < 2 :
      reference = ("(\""+datetime.now().strftime("%Y/%m/%d")+"\", \""+datetime.now().strftime("%H:%M")+"\""+ser.readline())
      i = i +1
print(reference)
time.sleep(2)
conn = mysql.connector.connect(host="localhost",
                               user="*******",
			       password="******",
                               database="Monitor")
 
cursor = conn.cursor()
 
print("Database !")
 
cursor.execute("""INSERT INTO DATA (Jour, Heure, Capteur1, Capteur2, Capteur3, Capteur4, Capteur5, Capteur6, Capteur7, Capteur8, Capteur9, Capteur10, Capteur11, Capteur12) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);""", reference)
 
conn.commit()
 
conn.close()

et le retour d'erreur:

pi@orangepizero:~# python BDD.py
Calcul en cours !
Reception !
("2019/03/08", "12:49", "-201", "86", "-159", "114", "116", "245", "200", "119", "107", "87", "90", "106")
Database !

Traceback (most recent call last):
File "BDD.py", line 28, in <module>
cursor.execute("""INSERT INTO DATA (Jour, Heure, Capteur1, Capteur2, Capteur3, Capteur4, Capteur5, Capteur6, Capteur7, Capteur8, Capteur9, Capteur10, Capteur11, Capteur12) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);""", reference)
File "/usr/lib/python2.7/dist-packages/mysql/connector/cursor.py", line 559, in execute
self._handle_result(self._connection.cmd_query(stmt))
File "/usr/lib/python2.7/dist-packages/mysql/connector/connection.py", line 494, in cmd_query
result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
File "/usr/lib/python2.7/dist-packages/mysql/connector/connection.py", line 396, in _handle_result
raise errors.get_exception(packet)
mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)' at line 1

pi@orangepizero:~#

Je patauge et après avoir cherché sur le web, je me tourne vers vous!

D'avance je vous remercie!

PS: si vous avez également de proposition pour alléger, améliorer les scripts/programmes, je vous remercie aussi!