Rslink, excel and mysql database

[sigmadelta]

And this is a starting point for using MySQL with Python:

https://www.w3schools.com/python/python_mysql_getstarted.asp

 

[drbitboy]

Added MariaDB/MySQL logging to my generic command-line pylogix logger, cf. here.


Two lines compose the guts of what was needed;

### Connect to database and instantiate a cursor in sub-class .__init__(...) method:



self.cursor = MySQLdb.connect(autocommit=True,**mysql_kwargs).cursor()





[...]





### Write data to a new record (row) in .__class__(...) method:



self.cursor.executemany("""INSERT INTO log (tag_name,tag_value,timestamp) VALUES (%s,%s,%s)""",self.changeds)

 

​

The rest is one-time setup stuff and window dressing to handle the command-line options.

Newcomers to MariaDB/MySQL will spend more time learning the DB ecosystem than writing code.

 

[drbitboy]

And this is one way to extract those data from MariaDB/MySQL:

$ mysql test_drbitboy -e "select * from log where tag_name<>'seconds' or mod(tag_value,10)<1" -vvvvvv



--------------

select * from log where tag_name<>'seconds' or mod(tag_value,10)<1

--------------



+-------+---------------------+----------+-----------+

| rowid | timestamp           | tag_name | tag_value |

+-------+---------------------+----------+-----------+

|    52 | 2022-01-31 19:30:33 | minutes  |        31 |

|    53 | 2022-01-31 19:30:33 | hours    |        14 |

|    54 | 2022-01-31 19:30:33 | day      |        31 |

|    55 | 2022-01-31 19:30:33 | month    |         1 |

|    56 | 2022-01-31 19:30:33 | year     |      2022 |

|    59 | 2022-01-31 19:30:36 | seconds  |        20 |

|    69 | 2022-01-31 19:30:46 | seconds  |        30 |

|    79 | 2022-01-31 19:31:04 | minutes  |        31 |

|    80 | 2022-01-31 19:31:04 | hours    |        14 |

|    81 | 2022-01-31 19:31:04 | day      |        31 |

|    82 | 2022-01-31 19:31:04 | month    |         1 |

|    83 | 2022-01-31 19:31:04 | year     |      2022 |

|    85 | 2022-01-31 19:31:06 | seconds  |        50 |

|    95 | 2022-01-31 19:31:16 | seconds  |         0 |

|    96 | 2022-01-31 19:31:16 | minutes  |        32 |

|   106 | 2022-01-31 19:31:26 | seconds  |        10 |

|   116 | 2022-01-31 19:31:36 | seconds  |        20 |

|   126 | 2022-01-31 19:31:46 | seconds  |        30 |

|   136 | 2022-01-31 19:31:56 | seconds  |        40 |

|   146 | 2022-01-31 19:32:06 | seconds  |        50 |

|   156 | 2022-01-31 19:32:16 | seconds  |         0 |

|   157 | 2022-01-31 19:32:16 | minutes  |        33 |

|   167 | 2022-01-31 19:32:26 | seconds  |        10 |

+-------+---------------------+----------+-----------+

23 rows in set (0.026 sec)



Bye

​