I'm not sure if this is something small that I just cannot see. I'm new to all this SQL in Python and can't see why the transaction is not running in my database. Hope you can help!
#requires mysql.connector
import mysql.connector
#make the sql connection
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="",
db="networks"
)
#create our cursor element
cursor=mydb.cursor()
#lets loop through the table
#run our query to move data in the db to the archive
cursor.execute("""START TRANSACTION;
INSERT INTO archive select * from data where cdate < (NOW() - INTERVAL 1 MONTH);
DELETE FROM archive WHERE cdate < (NOW() - INTERVAL 6 MONTH);
DELETE FROM data;
COMMIT;
""", multi=True)
#select all from devices
cursor.execute("SELECT * FROM devices")
#do something with this query
for row in cursor.fetchall():
#Line for testing output from SQL table
print("Connecting to "+row[1])
#here is where we run our SSH Code
#code from here will re-populate the data table
#make sure we close everything
mydb.commit()
cursor.close()
mydb.close()
