Category Archives: sql

crontab and shell script on hosted server

Making things work on a shared hosting server

I recently had to move my hosted server from one provider to another. As part of the move, I had to adapt to differences in the hosting server setup. I chose linux for the operating system of the server, but I wasn’t able to completely control everything as I didn’t have a complete virtual server at my disposal.

First of all, I had to install python. Python 2.7.9 was installed on the server by default, but it didn’t have pip and I wasn’t able to use some libraries that my scripts depended on. In addition, I couldn’t find out how to get it to work correctly. Since my daily update scripts were based on an Anaconda2 installation, I decided to try to install anaconda again and see if that fixed the problem. I downloaded and setup anaconda, and added pymysql using the pip utility provided by anaconda (and not provided by my web hosting provider, necessitating much of the hassle detailed in this post).The anaconda installation did work, however, each time I would ssh into the server I found that the version of python used by default was still 2.7.9, not the anaconda version. Ti get this to work I needed to modify the .bashrc script.  I changed it to


# added by Anaconda2 4.3.0 installer
export PATH="/the/real/path/goeshere/htdocs/anaconda2/bin:$PATH"

After modifying this script, each time I ssh’d into the server I had to execute the following command:

source ~/.bashrc

If I didn’t do this, the server was still using python 2.7.9, the old version which I wasn’t able to get to work correctly.

After this was fixed, I had to copy my files over and fix some sql that referenced the database name in them. Since I was using a hosted server, I didn’t get the choose the name of the server or database. This was causing a mysql 1142 error, stating that I didn’t have permissions to perform a SELECT query. While initially confusing, I eventually figured out that I had referenced the db name in a query and once I fixed this reference, I was back working again.

Once my wayward mysql was corrected, I began trouble shooting my python scripts.  After some work, I was able to get them working but I still had a few things to work out.  Obviously, any references to the user, password, server and database need to be changed to the new server settings.  Once this is done, I was able to use the phpmyadmin provided by my new provider to determine that my python scripts were working.

I then tried to modify the crontab to automate the script.  However, I needed to run the anaconda version of python to use the libraries that I needed for my script, and it appeared that the crontab was using the older version of python.

It is possible to execute multiple commands in one line of the crontab, separating them by either a “;” or “&&” with slightly different results, but I had several problems and couldn’t get this approach to work.  I needed to run the following command first,

source ~/.bashrc

and then change the directory to where my python scripts were located.  i played around with this for a while but wasn’t able to find a solution.  A major problem is that I wasn’t able to access the system log ,where the cron errors are usually stored, so wasn’t able to see why my script was failing and then correct it.  After many different attempts I decided to try something different.

Some searching on the internet led me to try to use a shell script, and then call the script once using the crontab.  I was finally able to get this to work after some experimentation.  Here’s the an example of the script, which I titled daily_update.sh:


#!/bin/bash
source ~/.bashrc
cd /the/real/path/goeshere/htdocs/python/
python some_python_script.py

The I used the crontab editor and made an entry:


5 18 * * * bash /the/real/path/goes/here/daily_update.sh

This script will execute at 1805 every day.

One other note, which may have saved me some hassle–it is wise to make sure that your server is using the time that you think it is for crontab execution.  I found that my server was using the eastern time zone, while I’m located in the central time zone.  This is easy to check in linux using the

date command:

The output will be the server time, something like
Tue Feb 7 22:13:22 EST 2017

How to calculate change and percent change over a time interval in mysql

I have a mysql application that I use to simulate various types of trading and trading strategies.  One of the tables generated is a table with the value of the simulated portfolio and the date with an entry for each day.  I wanted to use a mysql query to generate monthly, quarterly, and annual returns based on the account history table.  While this sounds fairly straightforward, it did become more involved that I thought, as I had to use variables in mysql queries, as well as date aggregration.  In addition, I needed to have the data pulled from the last day of the period instead of the first.

Here’s that I was able to come up with for annual data.  The account history table has two fields that are used:  Date, and Total_Equity with an entry for each day.

SELECT b.`yyyy`, CONCAT('$', FORMAT(b.Open_Equity,2)) as Open_Equity, 
	concat('$',FORMAT(delta, 2)) as delta,
    concat(round(delta_p*100,2),'%') as delta_p 
FROM   (SELECT *,
          (Open_Equity - @pequity) as delta,
          (Open_Equity - @pequity)/@pequity as delta_p,
          (@pequity:= Open_Equity)
        FROM (SELECT (EXTRACT(YEAR FROM `Date`)) as `yyyy`, 
			(SUBSTRING_INDEX(GROUP_CONCAT(CAST(`Open_Equity` AS CHAR) ORDER BY `Date` DESC), ',', 1 )) AS `Open_Equity`
			FROM account_history GROUP BY `yyyy` ORDER BY `yyyy` DESC) p
			CROSS JOIN
			(SELECT @pequity:= NULL) as a
       ORDER BY `yyyy` ) as b
ORDER by `yyyy` ASC

I’m not certain how ofter this sort of thing might come up, but I hope it will save someone some time and help with coding in mysql.

 

Here is some sample output:

yyyy   	Open_Equity	delta	        delta_p
2000	$79,794.00	NULL	        NULL
2001	$92,361.50	$12,567.50	15.75%
2002	$127,137.12	$34,775.62	37.65%
2003	$168,950.25	$41,813.13	32.89%
2004	$177,490.75	$8,540.50	5.06%
2005	$184,199.50	$6,708.75	3.78%
2006	$223,008.17	$38,808.67	21.07%
2007	$289,139.60	$66,131.43	29.65%
2008	$296,956.64	$7,817.04	2.70%
2009	$387,388.70	$90,432.06	30.45%
2010	$552,576.95	$165,188.25	42.64%
2011	$671,374.49	$118,797.54	21.50%
2012	$614,951.48	$-56,423.01	-8.40%
2013	$671,146.02	$56,194.54	9.14%