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

# # Acceso a AzureSQL

# In[1]:


import pyodbc
import pandas as pd


# In[2]:


# Test funcionamiento conexión.  Código extraído de la documentación de Azure

server = 'sqlc2bcurso.database.windows.net'
database = 'CusoC2B'
username = 'administrador'
password = 'Campus.5678@'   
driver= '{ODBC Driver 17 for SQL Server}'

with pyodbc.connect('DRIVER='+driver+';SERVER=tcp:'+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password) as conn:
    with conn.cursor() as cursor:
        cursor.execute("SELECT TOP 3 name, collation_name FROM sys.databases")
        row = cursor.fetchone()
        while row:
            print (str(row[0]) + " " + str(row[1]))
            row = cursor.fetchone()


# In[3]:


cursor.close()


# ### Prueba de descarga de datos

# In[19]:


# Descarga del set de datos Calendario Festivos 2022

server = 'sqlc2bcurso.database.windows.net'
database = 'CusoC2B'
username = 'administrador'
password = 'Campus.5678@'   
driver= '{ODBC Driver 17 for SQL Server}'

with pyodbc.connect('DRIVER='+driver+';SERVER=tcp:'+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password) as conn:
    with conn.cursor() as cursor:
        cursor.execute("SELECT * FROM calendario_festivos_2022")
        row = cursor.fetchone()
        while row:
            print (str(row[0]) + " " + str(row[1]) + " " + str(row[2]) + 
                   " " + str(row[3]) + " " + str(row[4]) + " " + str(row[5]) + 
                   " " + str(row[6]) + " " + str(row[7]) + " " + str(row[8]))
            row = cursor.fetchone()

            
        


# ### Prueba descarga tablas

# #### **Descarga tabla completa**

# In[20]:


server = 'sqlc2bcurso.database.windows.net'
database = 'CusoC2B'
username = 'administrador'
password = 'Campus.5678@'   
driver= '{ODBC Driver 17 for SQL Server}'

db = pyodbc.connect('DRIVER='+driver+';SERVER=tcp:'+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = db.cursor()

df = pd.read_sql_query('SELECT * FROM calendario_festivos_2022', db)

#cursor.execute("DROP TABLE table_name")


# In[21]:


df


# In[22]:


df.info()


# In[70]:


cursor.close()


# #### **Descarga de datos filtrados**

# La sintaxis para extracción de datos vía sql es la siguiente:
# 
# SELECT campos (separados por comas) FROM tabla WHERE condicion

# In[187]:


server = 'sqlc2bcurso.database.windows.net'
database = 'CusoC2B'
username = 'administrador'
password = 'Campus.5678@'   
driver= '{ODBC Driver 17 for SQL Server}'

db = pyodbc.connect('DRIVER='+driver+';SERVER=tcp:'+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = db.cursor()


# In[74]:


# Extraer de la tabla transacciones, aquellas realizadas en el CP 48004
pd.read_sql_query('SELECT * FROM Transacciones WHERE Codigo_Postal = 48004', db)


# In[75]:


# Extraer de la tabla transacciones, aquellas realizadas en el CP>48004 y CP< 48010
pd.read_sql_query('SELECT * FROM Transacciones WHERE Codigo_Postal >= 48004 AND Codigo_Postal <= 48010', db)


# In[76]:


# Extraer de la tabla transacciones la columna Tipo de Comercio y Transacciones, para transacciones aquellas realizadas en el CP>48004 y CP< 48010
pd.read_sql_query('SELECT Tipo_de_Comercio, Transacciones FROM Transacciones WHERE Codigo_Postal >= 48004 AND Codigo_Postal <= 48010', db)


# In[77]:


# Otra manera
pd.read_sql_query('SELECT Tipo_de_Comercio, Transacciones FROM Transacciones WHERE Codigo_Postal BETWEEN 48004 AND 48010 ', db)


# **Lógica booleana y operadores en SQL**
# 
# Algunos de los operadores que nos permiten construir expresiones booleanas son:
# 
#     > : "A > B" devuelve cierto si A es estrictamente mayor que B, de lo contrario devuelve falso.
#     < : "A < B" devuelve cierto si A es estrictamente menor que B, de lo contrario devuelve falso.
#     = : "A = B" devuelve cierto si A es igual a B, de lo contrario devuelve falso.
#     >= : "A >= B" devuelve cierto si A es mayor o igual a B, de lo contrario devuelve falso.
#     <= : "A <= B" devuelve cierto si A es menor o igual a B, de lo contrario devuelve falso.
#     != : "A != B" devuelve cierto si A es distinto a B, de lo contrario devuelve falso.
#     
#     AND : "A and B" devuelve cierto si A y B valen cierto, y falso en cualquier otro caso.
#     OR : "A or B" devuelve cierto si A o B valen cierto, y falso únicamente cuando tanto A como B valen falso.
#     NOT : "not A" devuelve falso si A vale cierto, y cierto si A vale falso.

# In[78]:


pd.read_sql_query('SELECT * FROM Transacciones WHERE (Transacciones = 8)', db)


# #### **Agregados**

# In[79]:


# Total transacciones
pd.read_sql_query('SELECT SUM(Transacciones) FROM Transacciones', db)


# In[80]:


# Total registros en tabla
pd.read_sql_query('SELECT count(*) FROM Transacciones', db)


# In[81]:


# Podemos agrupar ambas consultas en una
pd.read_sql_query('SELECT SUM(Transacciones), count(*) FROM Transacciones', db)


# In[82]:


# Incluso ejecutar operaciones
pd.read_sql_query('SELECT SUM(Transacciones) / count(*) FROM Transacciones', db)


# In[83]:


# Medias
pd.read_sql_query('SELECT avg(Transacciones) FROM Transacciones', db)


# In[84]:


# Podemos obtener el resultado desde sql con nombres de columna
pd.read_sql_query('SELECT SUM(Transacciones) AS Sum_Transacciones, count(*) AS Transacciones  FROM Transacciones', db)


# #### **Agrupaciones**

# In[85]:


# Transacciones totales por tipo de comercio
pd.read_sql_query('SELECT Tipo_de_Comercio, count(*) AS Total FROM Transacciones GROUP BY Tipo_de_Comercio', db)


# In[86]:


# Elementos únicos en una columna
pd.read_sql_query('SELECT Tipo_de_Comercio FROM Transacciones GROUP BY Tipo_de_Comercio', db)


# In[87]:


# Otra manera
pd.read_sql_query('SELECT DISTINCT Tipo_de_Comercio FROM Transacciones', db)


# #### **Ordenar los datos**

# In[88]:


pd.read_sql_query('SELECT * FROM Transacciones ORDER BY Transacciones DESC', db)


# In[89]:


# Ordenar por varios campos
pd.read_sql_query('SELECT * FROM Transacciones ORDER BY Codigo_Postal, Transacciones DESC', db)


# #### **Operador LIKE**

# In[97]:


# A través de este operador podemos hacer búsquedas que cumplan un determinado criterio
pd.read_sql_query("SELECT Fecha, Codigo_Postal, Tipo_de_Comercio FROM Transacciones WHERE Tipo_de_Comercio LIKE 'es_hospital' ", db)


# In[98]:


# Y la consulta opuesta
pd.read_sql_query("SELECT Fecha, Codigo_Postal, Tipo_de_Comercio FROM Transacciones WHERE Tipo_de_Comercio NOT LIKE 'es_hospital' ", db)


# #### **Extracción de datos NO nulos**

# In[100]:


pd.read_sql_query("SELECT * FROM Transacciones WHERE Tipo_de_Comercio IS NOT NULL ", db)


# #### **Producto cartesiano entre tablas**

# In[116]:


pd.read_sql_query("""SELECT * FROM TablaA, TablaB""", db)

# Básicamente combina todos los registros de A con todos los de B


# In[118]:


#Extracción de datos de ambas tablas en base a un criterio
pd.read_sql_query("""SELECT * FROM TablaA, TablaB WHERE TablaA.Nombre = TablaB.Nombre""", db)


# #### **Unión de tablas**

# In[121]:


# Inner
pd.read_sql_query("""SELECT * FROM TablaA C INNER JOIN TablaB P ON C.Id = P.Id""", db)


# In[122]:


# Left
pd.read_sql_query("""SELECT * FROM TablaA C LEFT JOIN TablaB P ON C.Id = P.Id""", db)


# In[124]:


#Rigth
pd.read_sql_query("""SELECT * FROM TablaA C RIGHT JOIN TablaB P ON C.Nombre = P.Nombre""", db)


# In[127]:


#Outer
pd.read_sql_query("""SELECT * FROM TablaA C FULL OUTER JOIN TablaB P ON C.Nombre = P.Nombre""", db)


# ### Operaciones CRUD

# In[182]:


#Cerramos el cursor previo
db.close()


# In[183]:


server = 'sqlc2bcurso.database.windows.net'
database = 'CusoC2B'
username = 'administrador'
password = 'Campus.5678@'   
driver= '{ODBC Driver 17 for SQL Server}'

db = pyodbc.connect('DRIVER='+driver+';SERVER=tcp:'+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = db.cursor()


# In[184]:


datos = pd.read_sql_query("""SELECT * FROM Personas""", db)


# In[163]:


datos


# La implementación del SQL en Azure no sigue el estandar 100, por ello vamos a crear una BBDD SQL virtual (en memoria) contra la que haremos las operaciones CRUD con procedimientos estándar.

# In[170]:


from sqlalchemy import create_engine

engine = create_engine('sqlite://', echo=False)


# In[178]:


# Creamos un dataset
df = pd.DataFrame({'name' : ['User 1', 'User 2', 'User 3']})
df


# In[173]:


# Insertamos la tabla recién creada en la BBDD.  Hay que tener en cuenta que la BBDD está vacía.
df.to_sql('users', con=engine)

engine.execute("SELECT * FROM users").fetchall()


# In[179]:


df1 = pd.DataFrame({'name' : ['User 4', 'User 5']})
df1.to_sql('users', con=engine, if_exists='append') # Las opciones disponibles son fail, append, replace
engine.execute("SELECT * FROM users").fetchall()

#fail: Si la tabla no existe, devuelve ValueError
#replace: Borra la tabla existente y crea una nueva con los nuevos datos
#append: Añade nuevos elementos a la tabla existente


# In[176]:


df2 = pd.DataFrame({'name' : ['User 6', 'User 7']})
df2.to_sql('users', con=engine, if_exists='append')
engine.execute("SELECT * FROM users").fetchall()


# In[177]:





# #### **Inserción de datos en Azure a través de to_sql**

# In[239]:


# El proceso varía ligeramente de lo visto en el paso previo
from urllib.parse import quote_plus
import numpy as np
import pandas as pd
from sqlalchemy import create_engine, event
import pyodbc

# cadena de conexión a azure sql
server = 'sqlc2bcurso.database.windows.net'
database = 'CusoC2B'
username = 'administrador'
password = 'Campus.5678@'   
driver= '{ODBC Driver 17 for SQL Server}'

# crear la conexión
conn ='Driver={ODBC Driver 17 for SQL Server};Server=tcp:sqlc2bcurso.database.windows.net,1433;Database=CusoC2B;Uid=administrador;Pwd=Campus.5678@;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;'
quoted = quote_plus(conn)
engine=create_engine('mssql+pyodbc:///?odbc_connect={}'.format(quoted))

# carga de datos en la bbdd
datos.to_sql('prueba2', engine, index=False, if_exists='append')


# In[237]:


datos


# #### **Inserción de datos en azure.  ¡Ojo! Es necesario que la tabla ya exista en la BBDD.**
# 

# In[212]:


server = 'sqlc2bcurso.database.windows.net'
database = 'CusoC2B'
username = 'administrador'
password = 'Campus.5678@'   
driver= '{ODBC Driver 17 for SQL Server}'

db = pyodbc.connect('DRIVER='+driver+';SERVER=tcp:'+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = db.cursor()


# In[217]:


# Este proceso hace un append sobre la tabla Prueba

for index, row in datos.iterrows():
     cursor.execute("INSERT INTO Prueba (Id,Nombre,Ciudad) values(?,?,?)", row.Id, row.Nombre, row.Ciudad)
db.commit()


# In[218]:


pd.read_sql_query("""SELECT * FROM Prueba""", db)


# #### **Crear tablas**

# In[228]:


#Cerramos el cursor previo
db.close()


# In[240]:


# Supongamos que queremos crear una tabla, llamada Productos, con los siguientes campos:

# Variable      Tipo variable
# ----------------------------------
# idProducto    int (clave primaria)
# producto      nvarchar(50)
# precio        int

# Para generar la nueva tabla...


# In[229]:


server = 'sqlc2bcurso.database.windows.net'
database = 'CusoC2B'
username = 'administrador'
password = 'Campus.5678@'   
driver= '{ODBC Driver 17 for SQL Server}'

db = pyodbc.connect('DRIVER='+driver+';SERVER=tcp:'+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = db.cursor()


# In[ ]:


# Generación de la tabla
cursor.execute('''CREATE TABLE Productos (idProducto int primary key, producto nvarchar(50),precio int)''')
db.commit()


# In[233]:


db.close()