python ดึงข้อมูล .csv แล้ว insert เข้า mongoDB

import os
import csv
from pymongo import MongoClient


connection = MongoClient("mongodb://myuser:mypassword@localhost:27017")
# insert your data for database_name and collection_name
db = connection.database_name.collection_name


path="E:/TESTING/mongo_test"            
list_of_files = {}
for filename in os.listdir(path):
     # if the element is a csv file then..
     if filename[-4:] == ".csv":
         list_of_files[filename] = path + "\\" + filename
         print   (list_of_files[filename])
         with open(list_of_files[filename], encoding="utf8" ) as f:
            csv_f = csv.reader(f)
            for i, row in enumerate(csv_f):
               #if i > 5 and len(row) > 1 :
                 print(row)
                 db.insert({'F1': row[0], 'F2': row[1]})
                
# find all documents
results = db.find()

print()
print('==============================')

# display documents from collection
for record in results:
   # print out the document
   print(record['F1'] + ',',record['F2'])

print()

# close the connection to MongoDB
connection.close()

Python กราฟ ดึงข้อมูล mySQL mariaDB โดยเงี่อนไขวันที่

# -*- coding: utf-8 -*-
import csv
from datetime import datetime
import pandas as pd
import matplotlib.pyplot as plt
import matplotlib as mpl
import numpy as np

#### Import Code Files #####
import test_connect_mysql
cursor = test_connect_mysql.cursor
mydb = test_connect_mysql.mydb

cursor.execute('select date_in, SUM(no_tag) as t_no_tag, SUM(barcode)  as t_barcode  from error_bar_tag GROUP BY date_in');
rows = cursor.fetchall()
#str(rows)[0:300]
cursor.close
mydb.close

df = pd.DataFrame( [[ij for ij in i] for i in rows] )
df.rename(columns={0: 'date_in', 1:'t_no_tag', 2:'t_barcode'}, inplace=True);
df = df.sort_values(['date_in'], ascending=[1]);

#print(df['no_tag'])
#tag_error = len(df['no_tag'])
#print(tag_error)

date_show = []
date_in = df['date_in']
for i in range(len(date_in)):
    try:
        date_show.append(str(datetime.date(date_in[i])))
    except:
        date_in[i] = 'date_in[i] decode error'

print("count date_show = " + str(len(date_show)))
for x in date_show:
  print(x)
############## Polt Grash ###############
fp = mpl.font_manager.FontProperties(family='Tahoma',size=10)
x = date_show
no_tag = df['t_no_tag']
barcode = df['t_barcode']
ax = plt.gca()
ax.set_title(u'ทดสอบแสดงข้อมูลไม่อ่าน Tag',fontname='Tahoma',fontsize='13')
ax.set_xlabel(u'ป/ด/ว',fontname='Tahoma',fontsize='10')
ax.set_ylabel(u'จำนวนครั้ง',labelpad=-1,fontname='Tahoma',fontsize='10')
ax.plot(x,barcode,'-og' ,alpha=1)
ax.plot(x,no_tag,'-or' ,alpha=1)
ax.legend([u'บาร์โค๊ต',u'ไม่อ่าน Tag',],prop=fp,loc=0,fancybox=1,shadow=1)
plt.grid()
ax.xaxis.set_label_coords(1.05, -0.05)
ax.yaxis.set_label_coords(1.05, 0.5)
ax.tick_params(axis='x',rotation=35 ,labelsize=7, length=5)
plt.show()

Python วันที่ย้อนหลัง และแปลงเป็นวันที่ mySQL, mariaDB

# -*- coding: utf-8 -*-
from datetime import datetime, timedelta

N = 1
date_N_days_ago = datetime.now() - timedelta(days=N)
print(datetime.now())
print(date_N_days_ago)
print(datetime.date(date_N_days_ago))

#Output แสดงผลลัพท์

คำสั่ง C# connect oracle เก็บไว้ดู

using System;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Data.OracleClient;

/// <summary>
/// Summary description for DBClass
/// Class สำหรับ connect database สามารถ ใช้งานได้กับ ACCESS,SQL Server,Oracle
/// เขียนขึ้นโดย Pheak Email manop.muangpia@hotmail.com
/// มีข้อสงสัย,bug แจ้งได้ทาง Email ครับ
/// </summary>
/// 

//ประกาศ Connection ของแต่ละ Database
public class ConnectDB
{
    //SQL Server
    public SqlConnection SqlStrCon()
    {
        return new SqlConnection("Data Source=127.0.0.1;Initial Catalog=xxxx" +
            ";Persist Security Info=True;User ID=xxxx;Password=xxxx");
    }
    //Access
    public OleDbConnection AccessStrCon()
    {
        return new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\xxxx.mdb");
    }
    //Oracle
    public OracleConnection OracleStrCon()
    {
        return new OracleConnection("Data Source=xxxx;Persist Security Info=True;User ID=xxxx;Password=xxxx;Unicode=True");
    }
}
public class DBClass
{
    //SQL Server Class
    #region
    public DataSet SqlGet(string sql, string tblName)
    {
        SqlConnection conn = new ConnectDB().SqlStrCon();
        SqlDataAdapter da = new SqlDataAdapter(sql, conn);
        DataSet ds = new DataSet();
        da.Fill(ds, tblName);
        return ds;
    }
    public DataSet SqlGet(string sql, string tblName, SqlParameterCollection parameters)
    {
        SqlConnection conn = new ConnectDB().SqlStrCon();
        SqlDataAdapter da = new SqlDataAdapter(sql, conn);
        DataSet ds = new DataSet();
        foreach (SqlParameter param in parameters)
        {
            da.SelectCommand.Parameters.AddWithValue(param.ParameterName, param.SqlDbType).Value = param.Value;
        }
        da.Fill(ds, tblName);
        return ds;
    }
    public int SqlExecute(string sql)
    {
        int i;
        SqlConnection conn = new ConnectDB().SqlStrCon();
        SqlCommand cmd = new SqlCommand(sql, conn);
        conn.Open();
        i = cmd.ExecuteNonQuery();
        conn.Close();
        return i;
    }
    public int SqlExecute(string sql, SqlParameterCollection parameters)
    {
        int i;
        SqlConnection conn = new ConnectDB().SqlStrCon();
        SqlCommand cmd = new SqlCommand(sql, conn);
        foreach (SqlParameter param in parameters)
        {
            cmd.Parameters.AddWithValue(param.ParameterName, param.SqlDbType).Value = param.Value;
        }
        conn.Open();
        i = cmd.ExecuteNonQuery();
        conn.Close();
        return i;
    }
    public DataSet SqlExcSto(string stpName, string tblName, SqlParameterCollection parameters)
    {
        SqlConnection conn = new ConnectDB().SqlStrCon();
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = conn;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = stpName;
        foreach (SqlParameter param in parameters)
        {
            cmd.Parameters.AddWithValue(param.ParameterName, param.SqlDbType).Value = param.Value;
        }
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds, tblName);
        return ds;
    }
    #endregion

    //Access Class
    #region
    public DataSet AccGet(string sql, string tblName)
    {
        OleDbConnection conn = new ConnectDB().AccessStrCon();
        OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);
        DataSet ds = new DataSet();
        da.Fill(ds, tblName);
        return ds;
    }
    public DataSet AccGet(string sql, string tblName, OleDbParameterCollection parameters)
    {
        OleDbConnection conn = new ConnectDB().AccessStrCon();
        OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);
        DataSet ds = new DataSet();
        foreach (OleDbParameter param in parameters)
        {
            da.SelectCommand.Parameters.AddWithValue(param.ParameterName, param.OleDbType).Value = param.Value;
        }
        da.Fill(ds, tblName);
        return ds;
    }
    public int AccExecute(string sql)
    {
        int i;
        OleDbConnection conn = new ConnectDB().AccessStrCon();
        OleDbCommand cmd = new OleDbCommand(sql,conn);
        conn.Open();
        i = cmd.ExecuteNonQuery();
        conn.Close();
        return i;
    }
    public int AccExecute(string sql, OleDbParameterCollection parameters)
    {
        int i;
        OleDbConnection conn = new ConnectDB().AccessStrCon();
        OleDbCommand cmd = new OleDbCommand(sql, conn);
        foreach (OleDbParameter param in parameters)
        {
            cmd.Parameters.AddWithValue(param.ParameterName, param.OleDbType).Value = param.Value;
        }
        conn.Open();
        i = cmd.ExecuteNonQuery();
        conn.Close();
        return i;
    }
    #endregion

    //Oracle Class
    #region
    public DataSet OracleGet(string sql, string tblName)
    {
        OracleConnection conn = new ConnectDB().OracleStrCon();
        OracleDataAdapter da = new OracleDataAdapter(sql, conn);
        DataSet ds = new DataSet();
        da.Fill(ds, tblName);
        return ds;
    }
    public DataSet OracleGet(string sql, string tblName, OracleParameterCollection parameters)
    {
        OracleConnection conn = new ConnectDB().OracleStrCon();
        OracleDataAdapter da = new OracleDataAdapter(sql, conn);
        DataSet ds = new DataSet();
        foreach (OracleParameter param in parameters)
        {
            da.SelectCommand.Parameters.AddWithValue(param.ParameterName, param.OracleType).Value = param.Value;
        }
        da.Fill(ds, tblName);
        return ds;        
    }
    public int OracleExecute(string sql)
    {
        int i;
        OracleConnection conn = new ConnectDB().OracleStrCon();
        OracleCommand cmd = new OracleCommand(sql, conn);
        conn.Open();
        i = cmd.ExecuteNonQuery();
        conn.Close();
        return i;
    }
    public int OracleExcute(string sql, OracleParameterCollection parameters)
    {
        int i;
        OracleConnection conn = new ConnectDB().OracleStrCon();
        OracleCommand cmd = new OracleCommand(sql, conn);
        foreach (OracleParameter param in parameters)
        {
            cmd.Parameters.AddWithValue(param.ParameterName, param.OracleType).Value = param.Value;
        }
        conn.Open();
        i = cmd.ExecuteNonQuery();
        conn.Close();
        return i;
    }
    #endregion
} 

/////https://developer.oracle.com/dotnet/cook-vs08.html

 //https://www.oracle.com/database/technologies/odp-dotnet-microsoft.html

/////////https://docs.oracle.com/database/121/NETRF/tnsnames.htm#NETRF260 

python กราฟ bar แสดงผลรวมในปีนี้

# -*- coding: utf-8 -*-
import csv
from datetime import datetime
import pandas as pd
import matplotlib.pyplot as plt
import matplotlib as mpl
import numpy as np
#### Import Code Files #####
import test_connect_mysql
cursor = test_connect_mysql.cursor
mydb = test_connect_mysql.mydb

to_date = datetime.now()
year_check = str(datetime.date(to_date))[0:4]
#print(year_check)

cursor.execute("SELECT plaza, SUM(no_tag) AS t_no_tag, SUM(barcode) AS t_barcode     \
                FROM error_bar_tag WHERE date_format(date_in, '%Y')= '" + year_check + "'    \
                GROUP BY plaza \
                ");
rows = cursor.fetchall()
#str(rows)[0:300]
cursor.close
mydb.close

df = pd.DataFrame( [[ij for ij in i] for i in rows] )
df.rename(columns={0:'plaza', 1:'t_no_tag', 2:'t_barcode'}, inplace=True);
df = df.sort_values(['plaza'], ascending=[1]);
#print(df)
# Make a fake dataset #########################################
height = df['t_no_tag']
bars = df['plaza']
y_pos = np.arange(len(bars))

plt.bar(y_pos, height, color='red', align='center', width=0.4)
plt.xticks(y_pos, bars)
ax = plt.gca()
ax.set_title(u'ผลรวมไม่อ่าน Tag ของ Plaza ปี ' + year_check,fontname='Tahoma',fontsize='13')
ax.set_xlabel(u'Plaza',fontname='Tahoma',fontsize='10')
ax.xaxis.set_label_coords(1.05, -0.05)
plt.tick_params(axis='x',rotation=90 ,labelsize=9, length=10)
plt.plot(y_pos, height,c='b')
plt.show()