isuk 10-2

import sqlite3
from sqlite3 import Error
import openpyxl
from datetime import datetime
import pprint

current_day = datetime.now().strftime('%d.%m.%Y')

def sql_connection():
  try:
    con = sqlite3.connect('mydatabase.db')
    return con
  except Error:
    print(Error)

def sql_table(con):
  cursorObj = con.cursor()
  ex = 'CREATE TABLE documents(id integer PRIMARY KEY, doc_num text, \
        company_from text, company_to text, \
        reason text, item text, type text, \
        amount int, status text, date text)'
  cursorObj.execute(ex)
  con.commit()

def sql_insert(con, entities):
  cursorObj = con.cursor()
  ex = 'INSERT INTO documents(doc_num, company_from, company_to, reason, item, \
  type, amount, status, date) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)'
  cursorObj.execute(ex, entities)
  con.commit()


def write_data_from_xlsx(con, filename):
  wb = openpyxl.load_workbook(filename)
  ws = wb.active
  companys = {}
  num = 1
  for j in range(2, ws.max_row+1):
    company = ws.cell(row=j, column=2).value
    if company not in companys.keys():
      companys.update({company: f'AB_{num}_{current_day}'})
      num += 1
    data = (companys[company], ws.cell(row=j, column=1).value, ws.cell(row=j, column=2).value,
             ws.cell(row=j, column=3).value,
             ws.cell(row=j, column=4).value, ws.cell(row=j, column=5).value,
             ws.cell(row=j, column=7).value,
             "выдан", current_day)
    sql_insert(con, data)


con = sql_connection()
sql_table(con)
write_data_from_xlsx(con, "data.xlsx")