Creating a custom python data pipeline

Having pushed the data in MySQL using a Google App script I wanted to see whether I could then push the data to Snowflake without using one of the automated tools. I decided, initially at least, to use python. Python is a very easy language to use and to achieve the basics, for this sort of project at least, you can plug and play with packages that you need. This is not going to be the most performant and probably won't fly in a proper Enterprise environment though I have previously used complex scripts to generate BI reports for the majority of a companies (start up) reporting. 

Below is my script minus the creation of the connection engines, because for the purposes of this I did not want to go to the trouble of masking them and it is very well documented how to create them. Currently I am running this manually but for £5 a month I can get this scheduled on pythoanywhere but hoping I will pluck up the courage to run this on free tier Azure. 

	from snowflake.connector.pandas_tools import pd_writer
	import pandas as pd
	from sqlalchemy import create_engine
	import pandasql as ps
	#Create MySQL Connection
	mySQL_conn = create_engine("");
	#Create SnowFlake Connection
	Snowengine = create_engine("")
	#Fetech Data from MySQL Weather table into dataframe
	df = pd.read_sql_query('select * from weather where locationName is not null and LocationRegion is not null',mySQL_conn);
	#Transform the data using pandas SQL, because SQL is simple and easy to follow
	get_cols = ps.sqldf("select LocationName , LocationRegion , LocationCountry , LocationLat , LocationLon , CurrentLastUpdated , CurrentTempC , CurrentConditionText, CurrentWindMph, CurrentPrecipMm , CurrentHumidity , CurrentCloud, CurrentFeelslikeC FROM df")
	#Load the processed dataframe into Snowflake
	get_cols.to_sql('stg_weather', con=Snowengine, if_exists='replace',index=False, method=pd_writer);

view raw MySQLtoSnowflake.py hosted with ❤ by GitHub