0

I created a batch file to upload CVSs to a MySQL database using python pandas_tosql. When I manually run the batch file it works fine. When I try to run it from scheduler it appears to work correctly(I input a winsound.beep tone and it makes a sound) however, the data doesn't appear in my database. I'm thinking there is a setting in MySQL to reject it, but cannot figure it out. The database is homebrewed for analytics only so I have free range to change anything I want. It's mainly just to store data for exploratory reasoning.

c:
call C:\Users\hknighton\AppData\Local\Continuum\anaconda3\Scripts\activate base
pythonw C:\Users\hknighton\dump\sql.py
conda deactivate

The python script

from sqlalchemy import create_engine
import numpy as np
import pandas as pd
import os
import winsound
frequency = 2500  # Set Frequency To 2500 Hertz
duration = 1500  # Set Duration To 1000 ms == 1 second
directory = os.getcwd()






from IPython.display import clear_output
def update_progress(progress):
    bar_length = 20
    if isinstance(progress, int):
        progress = float(progress)
    if not isinstance(progress, float):
        progress = 0
    if progress < 0:
        progress = 0
    if progress >= 1:
        progress = 1
    block = int(round(bar_length * progress))
    clear_output(wait = True)
    text = "Progress: [{0}] {1:.1f}%".format( "#" * block + "-" * (bar_length - block), progress * 100)
    print(text)




engine = create_engine('mysql://root:password!@localhost:3306/ceesmart')










num_files = 0
for filename in os.listdir(directory):
    if filename.endswith(".csv"): 
        num_files +=1
num_files





i = 1
for filename in os.listdir(directory):
    if filename.endswith(".csv"): 
        with open(filename, 'r') as file:
            data = pd.read_csv(file)
            file2 = str(file)
            data = data[pd.notnull(data['Time'])]
            data = data.drop_duplicates()
        data.to_sql("hourlydata", con=engine, index=True, index_label='id', if_exists='append')
        print(i)
        i = i + 1
        update_progress(i/num_files)

winsound.Beep(frequency, duration)    
6
  • Curiously the sound doesn't occur when I run the batch file directly. Jun 18, 2019 at 15:42
  • WHO is the batch file being run as? When you click on it, it's you. When you run it from the scheduler, I bet it isn't. Jun 18, 2019 at 15:52
  • Are you referring to the Run the task from the following account? It lists my account. Jun 18, 2019 at 16:36
  • Just to make sure, I would temporarily add a line to the batch file to pipe the current domain/user to a text file when it runs. It still sounds like permissions to me. What exactly does the batch file look like? Is it possible that pyton needs something that isn't in the environment when the scheduler runs it? Are you using this method? Jun 18, 2019 at 16:41
  • Posting the batch file (sanitized of sensitive info) could help us help you.
    – uSlackr
    Jun 18, 2019 at 17:01

2 Answers 2

2

This should be a comment. But I haven't yet got the "cred" to create one.

May I suggest that you look in your MySQL (error) log shortly after the timer fires for your task/cron/... That will hold any information as to what happened, eg; login failed for someuser@someplace PASSWORD SOMEPASSWORD.

HTH

2

Usually when I run into this it's because the scheduler doesn't open the files in the same directory as the script does when you run it manually.

I suspect that os.listdir(directory) is defaulting to C:\Users\hknighton\ and not finding any csv files?

1
  • You were right! I added os.chdir("C:/Users/hknighton/dump") to the python script and it worked. Thanks Jun 19, 2019 at 1:43

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .