gpt4 book ai didi

python - MySQL 连接器/Python 存储过程插入未提交

转载 作者:可可西里 更新时间:2023-11-01 08:06:16 29 4
gpt4 key购买 nike

我正在编写一个 python 脚本来监控 Raspberry Pi 的几个 1wire 传感器并将结果存储在 MySQL 数据库中。使用 MySQL 连接器/Python 库,我可以成功连接到数据库,并运行查询,但是事务似乎没有完全提交。我知道查询运行成功,因为输出参数设置为新的自动递增 ID。

CREATE TABLE `lamp`.`sensors` (
`SensorID` int(11) unsigned NOT NULL AUTO_INCREMENT,
`SensorSerial` char(15) NOT NULL,
`SensorFamily` tinyint(4) NOT NULL,
PRIMARY KEY (`SensorID`),
UNIQUE KEY `SensorID_UNIQUE` (`SensorID`),
UNIQUE KEY `SensorSerial_UNIQUE` (`SensorSerial`)
)

CREATE PROCEDURE `lamp`.`AddSensor` (sensorSerial char(15),
sensorFamily tinyint, out returnValue int)
BEGIN
INSERT INTO sensors (SensorSerial,SensorFamily) VALUES (sensorSerial,sensorFamily);
SET returnValue=LAST_INSERT_ID();
END

但是,当我尝试查询表时(从传感器中选择 *),我得到 0 个结果。如果我从 MySQL Workbench 或 .Net 应用程序运行该过程,一切都会按预期进行。这意味着我在连接器/Python 方面遗漏了一些东西,但我不知道是什么。我非常困惑,因为自动增量值确实增加了,但没有添加任何记录。也没有报错

 def Test(self):
#this line works fine
#self.RunProcedure("INSERT INTO sensors (SensorSerial,SensorFamily) VALUES ('{0}',{1})".format(self.ID,self.Family),False,())
#this line does not?
args=self.RunProcedure('AddSensor',True,(self.ID,self.Family,-1))
if args[2]>=1:
logging.debug("Successfully added sensor data '{1}' for sensor '{0}' to the database".format(self.ID,value))
return True
else:
logging.critical("Failed to add Data to Database for unknown reason. SensorID: {0} Type: {1} Data:{2}".format(self.ID,self.Family,value))

def RunProcedure(self,proc,isStored,args):
try:
logging.debug("Attempting to connect to database.")
connection=mysql.connector.connect(user='root',password='1q2w3e4r',host='localhost',database='LAMP')
except mysql.connector.Error as e:
logging.exception("Failed to connect to mysql database.\r\n {0}".format(e))
else:
logging.debug("Successfully connected to database.")
try:
cursor=connection.cursor()
if isStored:
args = cursor.callproc(proc,args)
return args
else:
cursor.execute(proc,args)
#these do not seem to solve the issue.
#cursor.execute("Commit;")
#connection.commit()
except mysql.connector.Error as e:
logging.exception("Exception while running the command '{0}' with the args of '{1}' exception is {2}".format(proc,args,e))
finally:
logging.debug("Closing connection to database")
cursor.close()
connection.close()

从到日志的输出如下所示;

2013-06-09 13:21:25,662 Attempting to connect to database.
2013-06-09 13:21:25,704 Successfully connected to database.
2013-06-09 13:21:25,720 Closing connection to database
2013-06-09 13:21:25,723 Successfully added sensor data '22.25' for sensor '10.85FDA8020800' to the database

**编辑

不知道为什么,但是将 autocommit=True 添加到 connection.open 参数似乎已经解决了这个问题。既然提交有问题,为什么 connection.commit() 或 cursor.execute('commit;') 没有更正问题?

最佳答案

问题其实出在你的代码中:

    ..
try:
cursor=connection.cursor()
if isStored:
args = cursor.callproc(proc,args)
return args
else:
cursor.execute(proc,args)
connection.commit()
except mysql.connector.Error as e:
..

如果您正在使用存储例程,您将立即返回,因此永远不会调用 commit()。

关于python - MySQL 连接器/Python 存储过程插入未提交,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/17012274/

29 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com