Repository navigation
Expand file tree
/
Copy pathConsultas_sql.py
More file actions
126 lines (95 loc) · 3.86 KB
/
Copy pathConsultas_sql.py
File metadata and controls
126 lines (95 loc) · 3.86 KB
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
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Created on Wed Mar 3 11:51:05 2021
@author: sergio
"""
import psycopg2
import pandas as pd
"""
*******************************
CONEXIÓN DATABASE
*******************************
"""
database = 'acp'
user = 'sergio'
password = 'sergio'
host = '192.168.1.66'
port = '5432'
connection = psycopg2.connect(database=database, user=user, password=password, host=host, port=port)
cursor = connection.cursor()
# CSP TEST
host_test = '192.168.1.166'
connection_test = psycopg2.connect(database=database, user=user, password=password, host=host_test, port=port)
cursor_test = connection_test.cursor()
"""
*******************************
CONSULTAS SQL
*******************************
"""
# Inspecciones
def psql_inspecciones(contrato = 'CDADSVERPI', fecha_creacion = '2021-01-01'):
query = '''SELECT *
FROM "public"."inspecciones"
WHERE contrato = %s AND fecha_inspeccion >= %s
ORDER BY fecha_inspeccion ASC''' #cod_prov = %s AND
#cod_prov, orden_produccion, referencia, estado, contrato
cursor.execute(query, [contrato, fecha_creacion]) #'CBC' SOPINDOFT,
colnames = [desc[0] for desc in cursor.description]
data_psql= cursor.fetchall()
df = pd.DataFrame(data_psql, columns=colnames)
return (df)
# Defectos
def psql_defectos(contrato = 'CDADSVERPI', fecha_creacion = '2021-01-01'):
query = '''SELECT *
FROM "public"."defectos"
WHERE contrato = %s AND created_at >= %s
ORDER BY created_at ASC'''
cursor.execute(query, [contrato, fecha_creacion]) #'CBC',
colnames = [desc[0] for desc in cursor.description]
data_psql= cursor.fetchall()
df = pd.DataFrame(data_psql, columns=colnames)
return(df)
# Pilatus QTY-NC
def psql_pilatusqtync(contrato = 'GQPILQIIN'):
query = '''SELECT a.id, a.codigo, caa.campo_adicional, caa.id as ca_id, rcaa.contenido as contenido
FROM auditorias a
LEFT JOIN registro_campos_adicionales_auditoria rcaa ON a.id = rcaa.idauditoria::bigint
LEFT JOIN campos_adicionales_auditoria caa ON caa.id = rcaa.idcampo::bigint
WHERE a.contrato = %s AND a.estado != 'CANCELADO'
ORDER BY a.codigo'''
cursor.execute(query, [contrato]) #'CBC',
colnames = [desc[0] for desc in cursor.description]
data_psql= cursor.fetchall()
df = pd.DataFrame(data_psql, columns=colnames)
return(df)
# Pilatus NO CONFORMIDADES
def psql_pilatusnc(contrato = 'GQPILQIIN'):
query = '''SELECT a.id, a.codigo, caa.campo_adicional, caa.id as ca_id, rcaa.contenido as contenido
FROM auditorias a
LEFT JOIN registro_campos_adicionales_auditoria rcaa ON a.id = rcaa.idauditoria::bigint
LEFT JOIN campos_adicionales_auditoria caa ON caa.id = rcaa.idcampo::bigint
WHERE a.contrato = %s AND a.estado != 'CANCELADO'
ORDER BY a.codigo'''
cursor.execute(query, [contrato]) #'CBC',
colnames = [desc[0] for desc in cursor.description]
data_psql= cursor.fetchall()
df = pd.DataFrame(data_psql, columns=colnames)
return(df)
"""
*******************************
CONSULTAS SQL - TEST
*******************************
"""
# Inspecciones
def psql_inspecciones_test(contrato = 'SOPINDOFT', fecha_creacion = '2021-01-01'):
query = '''SELECT *
FROM "public"."inspecciones"
WHERE contrato = %s AND fecha_inspeccion >= %s
ORDER BY fecha_inspeccion ASC''' #cod_prov = %s AND
#cod_prov, orden_produccion, referencia, estado, contrato
cursor.execute(query, [contrato, fecha_creacion]) #'CBC' SOPINDOFT,
colnames = [desc[0] for desc in cursor.description]
data_psql= cursor.fetchall()
df = pd.DataFrame(data_psql, columns=colnames)
return (df)