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

I added a call to commit method for pushing data into the SQL database and not only in the memory.
Tests are updated too in order to avoid future regression with a closed connection between writing and reading in the database. Since all the data in a memory based database in sqlite3 should be removed when closed, I create a temporary file used as database. The NamedTemporaryFile documentation states that it should be cross platforms but I don't have any Windows to test it. Normally doesn't break something. I never have done a pull request before, so let me know if something is bad. It's my first time and I'm happy to do it with pandas...

Closes #6846