py_import_excel_v2.ipynb

{
 "nbformat": 4,
 "nbformat_minor": 2,
 "metadata": {
  "language_info": {
   "name": "python",
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "version": "3.7.6-final"
  },
  "orig_nbformat": 2,
  "file_extension": ".py",
  "mimetype": "text/x-python",
  "name": "python",
  "npconvert_exporter": "python",
  "pygments_lexer": "ipython3",
  "version": 3,
  "kernelspec": {
   "name": "python3",
   "display_name": "Python 3"
  }
 },
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": "\" sys.path.append('lib/')\\nimport Py4Sqlite3 as db \""
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import sys\n",
    "import sqlite3\n",
    "import pandas\n",
    "import json\n",
    "import requests\n",
    "import datetime\n",
    "\n",
    "\"\"\" sys.path.append('lib/')\n",
    "import Py4Sqlite3 as db \"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": "<module 'funcs' from 'lib\\\\funcs.py'>"
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import importlib\n",
    "\n",
    "sys.path.append('lib/')\n",
    "import Py4Sqlite3 as db\n",
    "#import funcs\n",
    "importlib.reload(funcs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn=sqlite3.connect('mucnuoc.kt')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Config\n",
    "colsname={\n",
    "  'Trạm đo': 'maso_tramdo',\n",
    "  'Mực nước (cm)': 'giatri',\n",
    "  'Thời gian đo': 'thoigian'\n",
    "}\n",
    "maso_tramdo={}\n",
    "maso_tramdo=funcs.makeDict('https://github.com/soiqualang/py_import_excel/raw/master/data/tramdo.json')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "excel_data_df = pandas.read_excel('data/mucnuoc_20rec.xlsx', sheet_name='Sheet1', usecols=['Trạm đo', 'Mực nước (cm)', 'Thời gian đo'])\n",
    "#print(excel_data_df)\n",
    "\n",
    "cctl_giatri_mucnuoc=excel_data_df.to_dict(orient='records')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "errs=[]\n",
    "# Prepare data\n",
    "for index,record in enumerate(cctl_giatri_mucnuoc):\n",
    "    #Do file excel mở đầu bằng tiêu đề cột và đếm từ 1\n",
    "    recid=index+2\n",
    "    rec=funcs.prepare_cctl_giatri_mucnuoc(record,colsname,maso_tramdo,errs,recid)\n",
    "    #db.insert_table('cctl_giatri_mucnuoc',rec['fields'],rec['values'],conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": "[{'type': 'err', 'info': 'Lỗi dòng 8, giá trị \"2.9.3\" không phải là số.'}, {'type': 'err', 'info': 'Lỗi dòng 11, giá trị \"2,3\" không phải là số.'}, {'type': 'warn', 'info': 'Cảnh báo! Dòng 20 không có giá trị'}]\n"
    }
   ],
   "source": [
    "print(errs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "rows=db.table_to_array1('cctl_giatri_mucnuoc',conn)\n",
    "for row in rows:\n",
    "    print(row['id'],row['giatri'],row['thoigian'],row['ghichu'],row['maso_tramdo'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "db.truncate_tbl('cctl_giatri_mucnuoc',conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": "20-02-2020_09h38m55\n2020-02-20 09:38:55.149386\n2020-02-20_09:38:55.149386\n"
    }
   ],
   "source": [
    "x = datetime.datetime.now()\n",
    "print(x.strftime(\"%d-%m-%Y_%Hh%Mm%S\"))\n",
    "x=str(x)\n",
    "print(x)\n",
    "print(x.replace(' ','_'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ]
}