---Purpose--- Read daily changes such as inventory information from the database and pour it into the specified data. Export in the specified format.

Interpreter used: Python3.8

--- Poster's work environment --- 　　　Windows10Pro 64Bit

code

#!/usr/bin/env python
# -*- coding: utf-8 -*-

###Module used###
import sqlite3
import pandas as pd
import datetime

######Get timestamp
ThisDate = datetime.datetime.today().strftime("%Y%m%d")

#CurStocks <<Variable for the data frame that contains the database of the referencing inventory master


######Convert inventory information master to inventory reference data frame
#Here, the data of the inventory reference source is pulled from the file of SQlite3 format,
#Convert your favorite format to dataframe with Pandas.

dbpath = 'C:/Reference source/Inventory SQLite/Zaiko.db' 
conn = sqlite3.connect(dbpath)
cursor = conn.cursor()
cursor=cursor.execute('SELECT * FROM TurnOver_Data')　
CurStocks = pd.DataFrame(cursor.fetchall())
CurStocks.columns=["index", "Product name", "Product CD", "barcode", "stock"]
conn.close()
#(Remarks) ↑ The above method "cursor".fetchall()"
#The title of the column will be omitted, so I will add it later.
#The reference source data is based on the above column title.


######Prepare a Sqlite format file for pouring

print("● Update inventory contact file")
dbpath = 'C:/Pouring destination/Customer_Inventory FMT.db'
conn = sqlite3.connect(dbpath)
df_files=("C:/Pour destination base file/Customer_Inventory FMT.xlsx")
#I make a separate list of pouring destinations in an Excel file, but this is when the number of target items increases, etc.
#Because of the method of creating a base list with an Excel file and rewriting it for easy maintenance

df = pd.read_excel(df_files,encoding="cp932")
Chlist = (df["JAN"].transpose())
#df["JAN"].transpose()<<The data of "JAN" is converted so that it can be read one by one.
#The column of the pouring destination is given the title "JAN" which is the read key.


######The data for inventory reference is turned and referenced sequentially.

for item in Chlist:
    CurStocksult = CurStocks.query('barcode== @item')
    #Here, we will refer to the JAN code that is the key of the data frame of the reference source created earlier and turn it.
    #Reference source column: "Barcode" Pouring destination: "JAN" column title
    #Here, the data frame of the reference source is read one by one.

    if len(CurStocksult) >= 1:
        #Inventory count: If the reference frame has the JAN, refer to the data position.
        CurStocksult = (CurStocksult.iat[0, 3])

        #Get index number (data position of list of secondary array)
        DFindex=int(df.query('JAN == @item').index.astype("int64").values[0])

        if(CurStocksult) < 3 :
            Buf = 0
            df.iat[DFindex, 7] = "Shortage"
        else:
            Buf=CurStocksult
            df.iat[DFindex, 7] = ""
            
        df.iat[DFindex, 6]=Buf
        df.iat[DFindex, 8]=ThisDate
        #The 6th column of the pouring destination is the inventory column, the 7th column is the flag column, and the 8th column is the updated time stamp.

df.to_sql("Stock_Data", conn, if_exists="replace")
df.to_csv('C:/Updated inventory data.csv')
#Updated Sqlite and CSV.

conn.close()
print("● Update completed")

del df
del df_files

Clogged points, etc.

1: Create a list for loop processing Chlist = (df["JAN"].transpose()) In order to process the list of pouring destinations one by one, the key list of pouring destinations I converted it so that it can be turned in a loop

2: Acquisition of index number DFindex=int(df.query('JAN == @item').index.astype("int64").values[0]) Conversion process to get the data position (Y-axis?) Of the list of quadratic arrays

#I tried something like Vlookup with Python # 2

code

Clogged points, etc.