BUG: data written with to_sql legacy mode (sqlite/mysql) not persistent GH6846

Correction for missing commit of data into SQL database in legacy code and add test to avoid future regression.

Correct tests according to comments