{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"code","source":"# !sudo apt-get install python3-dev default-libmysqlclient-dev -y \n!pip install pymysql \n!pip install mysql-connector-python-rf","metadata":{"execution":{"iopub.status.busy":"2022-03-28T07:29:21.043352Z","iopub.execute_input":"2022-03-28T07:29:21.043801Z","iopub.status.idle":"2022-03-28T07:29:38.746688Z","shell.execute_reply.started":"2022-03-28T07:29:21.043767Z","shell.execute_reply":"2022-03-28T07:29:38.745975Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import pandas as pd\nfrom tqdm import tqdm\nimport sqlalchemy\nfrom kaggle_secrets import UserSecretsClient\n\nimport mysql.connector \nfrom mysql.connector import errorcode\nfrom sqlalchemy import create_engine\n\n","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","execution":{"iopub.status.busy":"2022-03-28T07:29:38.748459Z","iopub.execute_input":"2022-03-28T07:29:38.748714Z","iopub.status.idle":"2022-03-28T07:29:38.776548Z","shell.execute_reply.started":"2022-03-28T07:29:38.748684Z","shell.execute_reply":"2022-03-28T07:29:38.775680Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df = pd.read_csv('../input/competitive-data-science-predict-future-sales/sales_train.csv')\ndf","metadata":{"execution":{"iopub.status.busy":"2022-03-28T07:29:38.778295Z","iopub.execute_input":"2022-03-28T07:29:38.778637Z","iopub.status.idle":"2022-03-28T07:29:40.723514Z","shell.execute_reply.started":"2022-03-28T07:29:38.778591Z","shell.execute_reply":"2022-03-28T07:29:40.722798Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"max_id = df.item_price.values.max()\nmax_id","metadata":{"execution":{"iopub.status.busy":"2022-03-28T07:29:40.724694Z","iopub.execute_input":"2022-03-28T07:29:40.725030Z","iopub.status.idle":"2022-03-28T07:29:40.736254Z","shell.execute_reply.started":"2022-03-28T07:29:40.724986Z","shell.execute_reply":"2022-03-28T07:29:40.735082Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# m = 0\n# for i in tqdm(range(max_id)):\n#     e = df[df.item_id == i].shape[0]\n#     if e>m:\n#         m = e\n#         k = i\nk = 20949","metadata":{"execution":{"iopub.status.busy":"2022-03-28T07:29:40.740191Z","iopub.execute_input":"2022-03-28T07:29:40.740599Z","iopub.status.idle":"2022-03-28T07:29:40.747021Z","shell.execute_reply.started":"2022-03-28T07:29:40.740549Z","shell.execute_reply":"2022-03-28T07:29:40.745547Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df = df[df.item_id == k]\ndf","metadata":{"execution":{"iopub.status.busy":"2022-03-28T07:29:40.749252Z","iopub.execute_input":"2022-03-28T07:29:40.749527Z","iopub.status.idle":"2022-03-28T07:29:40.787945Z","shell.execute_reply.started":"2022-03-28T07:29:40.749497Z","shell.execute_reply":"2022-03-28T07:29:40.787046Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df = df.sort_values('date')\ndf","metadata":{"execution":{"iopub.status.busy":"2022-03-28T07:29:40.789257Z","iopub.execute_input":"2022-03-28T07:29:40.789510Z","iopub.status.idle":"2022-03-28T07:29:40.849683Z","shell.execute_reply.started":"2022-03-28T07:29:40.789481Z","shell.execute_reply":"2022-03-28T07:29:40.848850Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df = df.groupby(['date']).sum()\ndf","metadata":{"execution":{"iopub.status.busy":"2022-03-28T07:29:40.851272Z","iopub.execute_input":"2022-03-28T07:29:40.851523Z","iopub.status.idle":"2022-03-28T07:29:40.878907Z","shell.execute_reply.started":"2022-03-28T07:29:40.851493Z","shell.execute_reply":"2022-03-28T07:29:40.877950Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df['Date'] = pd.to_datetime(df.index)","metadata":{"execution":{"iopub.status.busy":"2022-03-28T07:29:40.880185Z","iopub.execute_input":"2022-03-28T07:29:40.880429Z","iopub.status.idle":"2022-03-28T07:29:40.890672Z","shell.execute_reply.started":"2022-03-28T07:29:40.880399Z","shell.execute_reply":"2022-03-28T07:29:40.890012Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df = df.sort_values(by = 'Date')[['Date','item_cnt_day']]\ndf","metadata":{"execution":{"iopub.status.busy":"2022-03-28T07:29:40.891841Z","iopub.execute_input":"2022-03-28T07:29:40.892119Z","iopub.status.idle":"2022-03-28T07:29:40.914139Z","shell.execute_reply.started":"2022-03-28T07:29:40.892085Z","shell.execute_reply":"2022-03-28T07:29:40.913255Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df = df[['Date', 'item_cnt_day']]\n\ndf.reset_index(drop = True, inplace = True)\ndf","metadata":{"execution":{"iopub.status.busy":"2022-03-28T07:29:40.916442Z","iopub.execute_input":"2022-03-28T07:29:40.917660Z","iopub.status.idle":"2022-03-28T07:29:40.938450Z","shell.execute_reply.started":"2022-03-28T07:29:40.917515Z","shell.execute_reply":"2022-03-28T07:29:40.937542Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"user_secrets = UserSecretsClient()\n\n\nhost = user_secrets.get_secret(\"hostdb\")\npassword = user_secrets.get_secret(\"password_db\")\npost = 3306\ndbname = 'sales'\nuser = 'admin'","metadata":{"execution":{"iopub.status.busy":"2022-03-28T07:29:40.939774Z","iopub.execute_input":"2022-03-28T07:29:40.940106Z","iopub.status.idle":"2022-03-28T07:29:41.523410Z","shell.execute_reply.started":"2022-03-28T07:29:40.940074Z","shell.execute_reply":"2022-03-28T07:29:41.522476Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"database_connection = sqlalchemy.create_engine('mysql+pymysql://{0}:{1}@{2}/{3}'\n            .format(user, password,host, dbname)).connect()","metadata":{"execution":{"iopub.status.busy":"2022-03-28T07:29:51.448422Z","iopub.execute_input":"2022-03-28T07:29:51.448977Z","iopub.status.idle":"2022-03-28T07:29:52.230050Z","shell.execute_reply.started":"2022-03-28T07:29:51.448941Z","shell.execute_reply":"2022-03-28T07:29:52.229381Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# df.to_sql('sales', database_connection)","metadata":{"execution":{"iopub.status.busy":"2022-03-28T07:31:28.554077Z","iopub.execute_input":"2022-03-28T07:31:28.554392Z","iopub.status.idle":"2022-03-28T07:31:28.557741Z","shell.execute_reply.started":"2022-03-28T07:31:28.554361Z","shell.execute_reply":"2022-03-28T07:31:28.557096Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import pandas as pd\n\nquery = \"\"\"SELECT * FROM sales\"\"\"\n\nevents_df = pd.read_sql(query, con= database_connection)","metadata":{"execution":{"iopub.status.busy":"2022-03-28T07:30:40.488242Z","iopub.execute_input":"2022-03-28T07:30:40.488562Z","iopub.status.idle":"2022-03-28T07:30:40.904427Z","shell.execute_reply.started":"2022-03-28T07:30:40.488521Z","shell.execute_reply":"2022-03-28T07:30:40.903175Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"events_df","metadata":{"execution":{"iopub.status.busy":"2022-03-28T07:30:42.503719Z","iopub.execute_input":"2022-03-28T07:30:42.504133Z","iopub.status.idle":"2022-03-28T07:30:42.523768Z","shell.execute_reply.started":"2022-03-28T07:30:42.504093Z","shell.execute_reply":"2022-03-28T07:30:42.522326Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"data = events_df['item_cnt_day'].values\ndata[:10]","metadata":{"execution":{"iopub.status.busy":"2022-03-28T07:32:24.673981Z","iopub.execute_input":"2022-03-28T07:32:24.674305Z","iopub.status.idle":"2022-03-28T07:32:24.680759Z","shell.execute_reply.started":"2022-03-28T07:32:24.674264Z","shell.execute_reply":"2022-03-28T07:32:24.679955Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Reference https://towardsdatascience.com/build-solid-foundations-for-your-data-science-projects-with-aws-rds-and-sqlalchemy-da8388e1e209","metadata":{}}]}