我通過(guò)將所有“插入”事件添加到名為“sqlcommand”表的單獨(dú)表中來(lái)制作自動(dòng)化腳本的日志。我有兩個(gè)單獨(dú)的表，btureading 表和 sqlcommand 表。btureading 將記錄一個(gè)數(shù)字的所有輸出，而 sqlcommand 將記錄插入命令。但是，我遇到了 SQL 語(yǔ)法錯(cuò)誤import mysql.connector as sqlimport random as rdfrom datetime import datetimedef conndev(): conn_dev = sql.connect( host = 'localhost', user = 'root', passwd = '', auth_plugin='mysql_native_password', db = 'test_db' ) return conn_devdef insertsqlcommand(sql_insert): mydb = conndev() mycursor = mydb.cursor() insert_sql_table = "INSERT INTO sqlcommand(sqlStatement) value('" + sql_insert + "')" mycursor = mydb.cursor() mycursor.execute(insert_sql_table) mydb.commit()def insertTodbMeter(myVal): mydb = conndev() mycursor = mydb.cursor() sql_insert = "INSERT INTO btureading(sensorId,dt,flowRate,velocity,netTotalizer)VALUES('"+myVal+"')" mycursor= mydb.cursor() mycursor.execute(sql_insert) mydb.commit() return sql_insertts = str(datetime.now())[0:19]flowRate= str(rd.random()) velocity= str(rd.random())netTotalizer= str(rd.random())for i in range(20): sample_data = str(i+1)+",'"+ts+"',"+flowRate+","+velocity+","+netTotalizer+","+posTotalizer+","+negTotalizer x = insertTodbMeter(sample) insertsqlcommand(x)
2 回答
德瑪西亞99
TA貢獻(xiàn)1770條經(jīng)驗(yàn) 獲得超3個(gè)贊
如果您遇到錯(cuò)誤，請(qǐng)?zhí)峁╁e(cuò)誤（盡可能完整的堆棧跟蹤）。但是，即使沒(méi)有這些，您也可能會(huì)遇到或?qū)⒂龅藉e(cuò)誤處理插入的問(wèn)題。
sample_data = str(i+1)+",'"+ts+"',"+flowRate+","+velocity+","+netTotalizer+","+posTotalizer+","+negTotalizer
此處的粗體顯示您在何處添加單引號(hào)sample_data。然后您使用insertTodbMeter包含單引號(hào)的值調(diào)用，但這些值并非全部引用，這意味著這將中斷：
sql_insert = "INSERT INTO btureading(sensorId,dt,flowRate,velocity,netTotalizer)VALUES('"+myVal+"')"
正確的方法是使用準(zhǔn)備好的語(yǔ)句/參數(shù)化查詢。不要嘗試構(gòu)建 SQL 字符串；傳遞您的值并讓數(shù)據(jù)庫(kù)庫(kù)處理它。這是基于此示例的起點(diǎn)：
def insert_to_db_meter(sensor_id, dt, flow_rate, velocity, net_totalizer):
mydb = conndev()
mycursor= mydb.cursor(prepared=True)
sql_insert = """
INSERT INTO btureading (
sensorId,
dt,
flowRate,
velocity,
netTotalizer
)
VALUES (
%s,
%s,
%s,
%s,
%s
)
"""
mycursor.execute(
sql_insert,
(
sensor_id,
dt,
flow_rate,
velocity,
net_totalizer,
),
)
mydb.commit()
return sql_insert
翻翻過(guò)去那場(chǎng)雪
TA貢獻(xiàn)2065條經(jīng)驗(yàn) 獲得超14個(gè)贊
添加回答
舉報(bào)
0/150
提交
取消