-2

I have a question and hope someone can direct me in the right direction; Basically every week I have to run a query (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.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Sami
  • 1
  • 3
  • Does this answer your question? [Automatically scheduling SQL query results to be exported to a csv file](https://stackoverflow.com/questions/55264116/automatically-scheduling-sql-query-results-to-be-exported-to-a-csv-file) – SMor Jun 09 '22 at 20:57
  • Yes but I cannot find the code – Sami Jun 09 '22 at 21:19

2 Answers2

0

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.

Reid Johnson
  • 1
  • 3
  • Hi, Thanks for your comment can you show me one way from the many diff ways, please? thank you – Sami Jun 09 '22 at 21:19
  • Please reference the hyperlinks that I have added to my answer. Since your question is vague, it is hard to provide information that is applicable to your exact situation. YouTube is also a great reference for the kinds of tasks you are trying to do. I suggest that you break up your problem into smaller problems, and research how to do each one. If you are only interested in getting a program that works, and not developing it yourself, you can contract me (or any other programmer) to develop for you. – Reid Johnson Jun 09 '22 at 22:38
0

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.
Pagerous
  • 16
  • Thank you very much for your help. If you don't mind me asking how can I automate this file and send it to my colleague using Gmail – Sami Jun 09 '22 at 21:45
  • To send an email with an attachment using Python you can use `smtplib` https://docs.python.org/3/library/smtplib.html – Pagerous Jun 10 '22 at 11:42