I have a question and hope someone can direct me in the right direction; Basically every week I have to run an SQL code (SSMS) to get a table containing some information (date, clientnumber, clientID, orderid etc) and then I copy all the information and that table and past it in a folder as a CSV file. it takes me about 15 min to do all this but I am just thinking can I automate this, if yes how can I do that and also can I schedule it so it can run by itself every week. I believe we live in a technological era and this should be done without human input; so I hope I can find someone here willing to show me how to do it using python.

Many thanks for considering my request.

CodePudding user response：

This should be pretty simple to automate:

1. Use some database adapter which can work with your database, for MSSQL the one delivered by pyodbc will be fine,
2. Within the script, connect to the database, perform the query, parse an output,
3. Save parsed output to a .csv file (you can use csv Python module),
4. Run the script as the periodic task using cron/schtask if you work on Linux/Windows respectively.

CodePudding user response：

Please note that your question is too broad, and shows no research effort.

You will find that Python can do the tasks you desire.

There are many different ways to interact with SQL servers, depending on your implementation. I suggest you learn Python SQL using the built-in sqlite3 library. You will want to save your query as a string, and pass it into an SQL connection manager of your choice; this depends on your server setup, there are many different SQL packages for Python.

You can use pandas for parsing the data, and saving it to a ~.csv file (literally called to_csv).

Python does have many libraries for scheduling tasks, but I suggest you hold off for a while. Develop your code in a way that it can be run manually, which will still be much faster/easier than without Python. Once you know your code works, you can easily implement a scheduler. The downside is that your program will always need to be running, and you will need to keep checking to see if it is running. Personally, I would keep it restricted to manually running the script; you could compile to an ~.exe and bind to a hotkey if you need the accessibility.