python - Python 错误 1064:您的 SQL 语法有错误 | Arduino 串行
问题描述
每次运行它都会给我这个错误。
Exception has occurred: 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 ' CURRENT_DATE(), CURRENT_TIME())' at line 2
Query 通过 USB 从 arduino 获取数据。几天前,它可以完美地使用键盘测试它是否有效,直到我尝试使用 RFID 阅读器获取 ID。我只是将列 ID 更改为 SN 以匹配数据库。如果我将 ID 直接放入查询中,它会神奇地起作用。我不知道它是否与我的arduino上的代码有关。
Python代码:
while True:
data = arduino.readline().decode('ascii')
if data != '':
print(data)
cursor.execute("INSERT INTO `Anwesenheit` (SN, Datum, Uhrzeit) VALUES ({}, CURRENT_DATE(), CURRENT_TIME());".format(data))
db.commit()
以前的 Arduino 代码:
#include <Keypad.h>
const byte ROWS = 4;
const byte COLS = 4;
char hexaKeys[ROWS][COLS] = {
{'1', '2', '3', 'A'},
{'4', '5', '6', 'B'},
{'7', '8', '9', 'C'},
{'*', '0', '#', 'D'}
};
String nummer;
byte rowPins[ROWS] = {9, 8, 7, 6};
byte colPins[COLS] = {5, 4, 3, 2};
Keypad customKeypad = Keypad(makeKeymap(hexaKeys), rowPins, colPins, ROWS, COLS);
void setup(){
Serial.begin(9600);
}
void loop(){
char key = customKeypad.getKey();
if (key){
// Serial.println(key);
if(key == '*') {
nummer = "";
} else if(key == '#') {
Serial.println(nummer);
nummer = "";
} else {
nummer += key;
}
}
}
现在的Arduino代码:
#include <Wiegand.h>
#define PIN_D1 3
Wiegand wiegand;
void setup() {
Serial.begin(9600);
wiegand.onReceive(receivedData, "");
wiegand.onReceiveError(receivedDataError, "Card read error: ");
wiegand.onStateChange(stateChanged, "");
wiegand.begin(Wiegand::LENGTH_ANY, true);
pinMode(PIN_D0, INPUT);
pinMode(PIN_D1, INPUT);
attachInterrupt(digitalPinToInterrupt(PIN_D0), pinStateChanged, CHANGE);
attachInterrupt(digitalPinToInterrupt(PIN_D1), pinStateChanged, CHANGE);
pinStateChanged();
}
void loop() {
noInterrupts();
wiegand.flush();
interrupts();
delay(100);
}
// When any of the pins have changed, update the state of the wiegand library
void pinStateChanged() {
wiegand.setPin0State(digitalRead(PIN_D0));
wiegand.setPin1State(digitalRead(PIN_D1));
}
// Notifies when a reader has been connected or disconnected.
// Instead of a message, the seconds parameter can be anything you want -- Whatever you specify on `wiegand.onStateChange()`
void stateChanged(bool plugged, const char* message) {
Serial.print(message);
Serial.println(plugged ? "" : "DISCONNECTED");
}
// Notifies when a card was read.
// Instead of a message, the seconds parameter can be anything you want -- Whatever you specify on `wiegand.onReceive()`
void receivedData(uint8_t* data, uint8_t bits, const char* message) {
//Serial.print(bits);
//Serial.print("bits / ");
//Print value in HEX
uint8_t bytes = (bits+7)/8;
for (int i=0; i<bytes; i++) {
Serial.print(data[i] >> 4, 16);
Serial.print(data[i] & 0xF, 16);
}
Serial.println();
}
// Notifies when an invalid transmission is detected
void receivedDataError(Wiegand::DataError error, uint8_t* rawData, uint8_t rawBits, const char* message) {
Serial.print(message);
Serial.print(Wiegand::DataErrorStr(error));
Serial.print(" - Raw data: ");
Serial.print(rawBits);
Serial.print("bits / ");
//Print value in HEX
uint8_t bytes = (rawBits+7)/8;
for (int i=0; i<bytes; i++) {
Serial.print(rawData[i] >> 4, 16);
Serial.print(rawData[i] & 0xF, 16);
}
Serial.println();
}
解决方案
该data
值被格式化为不带引号的字符串,从而破坏了 SQL 语法。使用绑定变量可以解决这个问题(如果数据不可信,还可以保护您的代码免受 SQL 注入攻击):
cursor.execute("INSERT INTO `Anwesenheit` (SN, Datum, Uhrzeit) VALUES (%s, CURRENT_DATE(), CURRENT_TIME(), (data,))
推荐阅读
- sql - QlikView/SQL - 带有 IsNull 的 If 语句
- javascript - 试图在JS画布中获取点与鼠标之间的距离
- sql - How to read data from multiple XML files in SQL Server?
- ios - 基于数组比较更新 UICollectionView
- python - Facebook sdk python在没有登录的情况下输出访问令牌?
- vba - 是否为空返回“”
- python - 可视化 CNN 过滤器时,元组索引超出范围
- vue.js - 如何从 getPlaceDetails 获取照片位置?在 VUE 中
- python - 无法在 QTreeWidget 中添加 QTextEdit
- clojure - 你在 lisp 中看到的最优雅的一段代码是什么