I'm developing a web-scraping tool by Python for my own use. I will run regularly the Python program to write data to a database. I hope with not too much manipulation/script we can export data from this database into a same worksheet of an Excel file everyday.
Does anyone know which database (e.g., MySQL, MongoDB) should I choose? How can I update easily the Excel worksheet everyday?
The answer is: It doesn't matter which database you choose. Any major database will be able to be accessed via Python. And once you can access the data in Python, it is a simple matter to write data out to an Excel workbook using Python.
The easiest and most efficient way to write to an Excel workbook in Python is to use one of the ready-made packages, of which the leading one for your use case (updating an existing workbook) is openpyxl.
If you can adjust your workflow such that you're always creating a new workbook, instead of updating an existing one, then I strongly prefer XlsxWriter (and I use xlrd for reading data from existing workbooks).
You could also try pandas, which is a very popular higher-level package that uses openpyxl and/or XlsxWriter (as well as many other components) under the covers.
Finally, if you are doing this on Windows or Mac, and you have Excel itself installed, then another possible approach is to use Python to script a running instance of Excel. This is the most reliable way to preserve the fidelity of an existing workbook as you're updating it (particularly if you are going to incorporate fancy formatting, charts, or other advanced Excel features). The leading package for this is xlwings.
Instead of generating a Excel file why not connect excel directly to the database and skip the middle man.
