1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84
| # coding: utf-8
import cx_Oracle
import os.path
import os
import datetime
import os.path, time
import shutil
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.base import MIMEBase
from email import encoders
import xlsxwriter
def requete_to_stock_BVT(taille):
#Nous créeons notre requête SQL ici
sql= """
SELECT *
FROM prod_stok
where QTE_"""+ taille +""" < 100
and code_art_com IN ('5046','5101','86545','5408','87115','84320','87795','86440','86118','5346','5021','0046','90007',
'7-110046','184','317','884401','884400','802149','802147','2603','2504','2202','10745','10746',
'10779','11383','11938','11341','13317','14521','14940')
and code_lieu= 'BVT'
and typ_enrg = '0'
and sais IN('00P','21E')
"""
try:
#Connexion à la base de donnée
with cx_Oracle.connect("user", "pass", "base de donnnées") as connection:
with connection.cursor() as cursor:
cursor.execute(sql)
wb = xlsxwriter.Workbook('alert_BVT.xlsx')
ws = wb.add_worksheet()
compteur_ligne = 0
ws.write("A1","Lieu")
ws.write("B1","Magasin")
ws.write("C1","Saison")
ws.write("D1","Article")
ws.write("E1","Couleur")
ws.write("F1","60/36")
ws.write("G1","65/38")
ws.write("H1","70/40")
ws.write("I1","75/42")
ws.write("J1","80/44")
ws.write("K1","85/46")
ws.write("L1","90/48")
ws.write("N1","95/50")
ws.write("M1","100/52")
ws.write("O1","105/54")
ws.write("P1","110/56")
ws.write("Q1","115/58")
for row in cursor:
compteur_ligne += 1
ws.write(compteur_ligne,0,str(row[0]))
ws.write(compteur_ligne,1,str(row[1]))
ws.write(compteur_ligne,2,str(row[2]))
ws.write(compteur_ligne,3,str(row[3]))
ws.write(compteur_ligne,4,str(row[4]))
ws.write(compteur_ligne,5,str(row[8]))
ws.write(compteur_ligne,6,str(row[9]))
ws.write(compteur_ligne,7,str(row[10]))
ws.write(compteur_ligne,8,str(row[11]))
ws.write(compteur_ligne,9,str(row[12]))
ws.write(compteur_ligne,10,str(row[13]))
ws.write(compteur_ligne,11,str(row[14]))
ws.write(compteur_ligne,12,str(row[15]))
ws.write(compteur_ligne,12,str(row[16]))
ws.write(compteur_ligne,13,str(row[17]))
ws.write(compteur_ligne,14,str(row[18]))
ws.write(compteur_ligne,15,str(row[19]))
ws.write(compteur_ligne,16,str(row[20]))
wb.close()
#Cette partie de code nous renvois les erreurs de la requête s'il y en a.
except cx_Oracle.Error as error:
print(error)
def alert_to_stock_BVT():
for y in range(12):
if y >= 1:
requete_to_stock_BVT(str(y))
alert_to_stock_BVT(); |
Partager