0

I am a novice at python and I am trying to create my first automated code in jupyter notebooks that will export my data pull from SQL server to a specific path and this code needs to run daily. My questions: 1- It needs to export the CSV file to a specific folder, don't know how to do that 2- I need the code to run by itself on a daily basis

I am stuck, Any help is appreciated.

I have connected to the sql server and successfully pull the report and write a CSV file.

import smtplib
import pyodbc
import pandas as pd
import pandas.io.sql

server = 'example server'
db = 'ExternalUser'

conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=example server;'
                      'Database=ExternalUser;'
                      'Trusted_Connection=yes;')

cursor = conn.cursor()

cursor.execute("my SQL query")

col_headers = [ i[0] for i in cursor.description ]
rows = [ list(i) for i in cursor.fetchall()]
df = pd.DataFrame(rows, columns=col_headers)
df.to_csv("Test v2.csv", header = True, index=False)
Kam S
  • 15
  • 6
  • passing in the location as the filename will create the csv file at a particular location.. Just make sure the directory exists.. – Yatish Kadam Oct 22 '19 at 18:51
  • Running it everyday will be determined by your os. This is not something the python will handle. In linux you would use crontab, in Windows it's the task scheduler. – Rashid 'Lee' Ibrahim Oct 22 '19 at 18:52
  • to run it daily just search up a scheduler program for python .. Something like this https://stackoverflow.com/questions/373335/how-do-i-get-a-cron-like-scheduler-in-python – Yatish Kadam Oct 22 '19 at 18:53
  • Please restrict your questions to one task first. Also, be specific with the problems you have. When asking about code, also extract a [mcve] first. As a new user here, also take the [tour] and read [ask]. – Ulrich Eckhardt Oct 22 '19 at 18:55
  • if you SO is linux ..just set your scheduler in /etc/crontab – GiovaniSalazar Oct 22 '19 at 20:56

1 Answers1

1

For needing to export the csv too a certain folder: It depends where/how you run the script. If you run the script in the folder you want the csv file saved then your current df.to_csv('filename.csv') would work great, or add a path 'Test_dir/filename.csv'. Otherwise you could use a library like shutil (https://docs.python.org/3/library/shutil.html) that will then move the .csv file to a given folder.

For running the code on a daily basis, you could do this locally on your machine (https://medium.com/@thabo_65610/three-ways-to-automate-python-via-jupyter-notebook-d14aaa78de9). Or you could look into configuring a cronjob.

William Knighting
  • 113
  • 1
  • 9