En este post vamos a conectar Python 3 a una base de datos de SQL Server a través del driver ODBC y el paquete PyODBC.

Haremos un CRUD para demostrar cómo se hacen las 4 operaciones básicas a una base de datos.

Para comenzar, recuerda:

• Descargar e instalar Python y PIP
• Instalar SQL server
• Crear usuario (opcional) y asegurarse de que se puede iniciar sesión con el mismo
• Descargar e instalar driver, recomiendo la última versión.
• Probar conexión a SQL Server desde SSMS

Nota: todos los archivos están en un repositorio de GitHub.

Instalación de PyODBC

Cuando ya hayas instalado pip, instala el paquete PyODBC con el comando:

pip install pyodbc

Como se ve en la imagen:

Base de datos

La base de datos que vamos a consumir se llama “pruebas_parzibyte” y la tabla será una tabla de películas; tu base de datos puede ser llamada como quieras.

/*
 Conexión a SQLServer con Python
 Ejemplo de CRUD evitando inyecciones SQL
 
 @author parzibyte

 Más tutoriales en:
      parzibyte.me/blog
*/CREATE TABLE IF NOT EXISTS peliculas(
 id bigint identity(1,1) primary key, 
 titulo VARCHAR(255) NOT NULL,
 anio SMALLINT NOT NULL
);

Dentro de la tabla se declara una columna autoincrementable.

Recuerda que es un ejemplo, ya que en tu caso serán otras tablas y datos, pero recomiendo crear la tabla que se muestra aquí para que sigas el tutorial.

Ahora por favor comprueba tus credenciales; tanto el usuario, contraseña, nombre de base de datos y nombre de las tablas; todo esto usando SQL Server Management Studio para descartar posibles errores al trabajar con Python.

Probar conexión a SQL Server desde Python

Comencemos a programar. Vamos a crear un archivo de conexión en Python y luego lo vamos a importar en los lugares en donde lo vayamos a usar.

En este archivo pondremos las credenciales y rutas del servidor, así como el nombre de la base de datos:

"""
    Conexión a SQLServer con Python
    Ejemplo de CRUD evitando inyecciones SQL
    
    @author parzibyte

    Más tutoriales en:
                        parzibyte.me/blog
"""
import pyodbc
direccion_servidor = '127.0.0.1'
nombre_bd = 'pruebas_parzibyte'
nombre_usuario = 'usuario'
password = 'hunter2'
try:
    conexion = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=' +
                              direccion_servidor+';DATABASE='+nombre_bd+';UID='+nombre_usuario+';PWD=' + password)
    # OK! conexión exitosa
except Exception as e:
    # Atrapar error
    print("Ocurrió un error al conectar a SQL Server: ", e)

Estamos importando a pyodbc y declarando la variable conexión. Si la misma es exitosa, no se generará un error, y en caso de que sí, lo vamos a atrapar.

Cambia las credenciales y prueba la conexión ejecutando el archivo (python bd.py) y si no se genera ningún error, todo va bien.

Insertar datos

Para insertar datos debemos obtener el cursor de la conexión, eso lo hacemos con conexion.cursor().

Utilizamos with para que el cursor se cierre automáticamente.

Para ejecutar la consulta llamamos a cursor.execute(consulta, datos).

"""
    Conexión a SQLServer con Python
    Ejemplo de CRUD evitando inyecciones SQL
    
    @author parzibyte

    Más tutoriales en:
                        parzibyte.me/blog
"""
from bd import conexion
try:
    with conexion.cursor() as cursor:
        consulta = "INSERT INTO peliculas(titulo, anio) VALUES (?, ?);"
        # Podemos llamar muchas veces a .execute con datos distintos
        cursor.execute(consulta, ("Volver al futuro 1", 1985))
        cursor.execute(consulta, ("Pulp Fiction", 1994))
        cursor.execute(consulta, ("It", 2017))
        cursor.execute(consulta, ("Ready Player One", 2018))
        cursor.execute(consulta, ("Spider-Man: un nuevo universo", 2018))
        cursor.execute(consulta, ("Avengers: Endgame", 2019))
        cursor.execute(consulta, ("John Wick 3: Parabellum", 2019))
        cursor.execute(consulta, ("Toy Story 4", 2019))
        cursor.execute(consulta, ("It 2", 2019))
        cursor.execute(consulta, ("Spider-Man: lejos de casa", 2019))

except Exception as e:
    print("Ocurrió un error al insertar: ", e)
finally:
    conexion.close()

Fíjate en que, dentro de la consulta, no concatenamos los datos, sino que los indicamos con un signo de interrogación y luego los pasamos en el mismo orden dentro de la tupla en el segundo argumento a cursor.execute.

Al utilizar los placeholders o signos de interrogación, evitamos inyecciones SQL.

En el finally, la conexión debe ser cerrada, sin importar si hubo éxito o error en la inserción.

Consultar datos

Para traer los datos en forma de arreglo necesitamos de nuevo, el cursor.

La consulta se hace a través de cursor.execute(consulta), y se pueden pasar datos como cuando insertamos datos.

La diferencia ahora es que para traer los datos de la consulta como arreglo usamos a cursor.fetchall() que devuelve una lista, la cual podemos iterar, codificar con json, etcétera.

"""
    Conexión a SQLServer con Python
    Ejemplo de CRUD evitando inyecciones SQL
    
    @author parzibyte

    Más tutoriales en:
                        parzibyte.me/blog
"""
from bd import conexion

try:
    with conexion.cursor() as cursor:
        # En este caso no necesitamos limpiar ningún dato
        cursor.execute("SELECT id, titulo, anio FROM peliculas;")

        # Con fetchall traemos todas las filas
        peliculas = cursor.fetchall()

        # Recorrer e imprimir
        for pelicula in peliculas:
            print(pelicula)
except Exception as e:
    print("Ocurrió un error al consultar: ", e)
finally:
    conexion.close()

En este caso estamos imprimiendo el resultado completo; si quisieras acceder a un valor de la fila simplemente accede a ese elemento de la lista. Por ejemplo, print(pelicula[0]) imprimiría el id.

A continuación veremos cómo iterar los resultados en lugar de tenerlos como arreglo, un método útil para cuando queremos optimizar los resultados, o cuando hay varios datos.

Iterar resultado de consulta

Para no tener los datos en un arreglo, pero recorrerlos de igual manera, hacemos un ciclo while.

El ciclo se cumplirá mientras cursor.fetchone() regrese algo.

"""
    Conexión a SQLServer con Python
    Ejemplo de CRUD evitando inyecciones SQL
    
    @author parzibyte

    Más tutoriales en:
                        parzibyte.me/blog
"""
from bd import conexion

try:
    with conexion.cursor() as cursor:
        # En este caso no necesitamos limpiar ningún dato
        cursor.execute("SELECT id, titulo, anio FROM peliculas;")
        # Hacer un while, mientras fetchone no regrese None
        pelicula = cursor.fetchone()
        while pelicula:
            print(pelicula)
            pelicula = cursor.fetchone()
except Exception as e:
    print("Ocurrió un error al consultar: ", e)
finally:
    conexion.close()

De esta manera se pueden procesar miles de datos sin sacrificar el rendimiento.

Consultar usando where

Se pueden pasar argumentos en cursor.execute también a las consultas. Por ejemplo, para pasar un dato que formará una condicional se hace lo siguiente:

"""
    Conexión a SQLServer con Python
    Ejemplo de CRUD evitando inyecciones SQL
    
    @author parzibyte

    Más tutoriales en:
                        parzibyte.me/blog
"""
from bd import conexion
try:
    with conexion.cursor() as cursor:

        consulta = "SELECT id, titulo, anio FROM peliculas WHERE anio > ?;"
        cursor.execute(consulta, (2000))

        # Con fetchall traemos todas las filas
        peliculas = cursor.fetchall()

        # Recorrer e imprimir
        for pelicula in peliculas:
            print(pelicula)
except Exception as e:
    print("Ocurrió un error al consultar con where: ", e)
finally:
    conexion.close()

En este caso vamos a consultar las películas cuyo año de estreno sea mayor al 2000. Esta variable puede venir de cualquier lado, la pongo directamente en el código para ejemplificar.

Estamos usando placeholders para evitar inyecciones SQL.

Consultar usando where y Like

Una pregunta muy común es cómo escapar una consulta con Like y los comodines.

La solución es poner un simple placeholder, y concatenar los comodines a la cadena o búsqueda; así se siguen evitando inyecciones SQL.

"""
 Conexión a SQLServer con Python
 Ejemplo de CRUD evitando inyecciones SQL
 
 @author parzibyte

 Más tutoriales en:
      parzibyte.me/blog
"""
from bd import conexion
try:
    with conexion.cursor() as cursor:
        consulta = "SELECT id, titulo, anio FROM peliculas WHERE titulo like ?;"
    # Para Avengers Endgame
    busqueda = "endg"
    cursor.execute(consulta, ("%" + busqueda + "%"))

    # Con fetchall traemos todas las filas
    peliculas = cursor.fetchall()

    # Recorrer e imprimir
    for pelicula in peliculas:
        print(pelicula)
except Exception as e:
    print("Ocurrió un error al consultar con where: ", e)
finally:
    conexion.close()

Recuerda que se puede usar fetchall o iterar los datos en un while, todo depende de lo que quieras hacer.

Editar datos

Para editar hay que hacer una simple consulta y pasar los parámetros necesarios.

"""
    Conexión a SQLServer con Python
    Ejemplo de CRUD evitando inyecciones SQL
    
    @author parzibyte

    Más tutoriales en:
                        parzibyte.me/blog
"""
from bd import conexion
try:
    with conexion.cursor() as cursor:

        consulta = "UPDATE peliculas SET titulo = ? WHERE id = ?;"
        nuevo_titulo = "Ready Player One: comienza el juego"
        id_editar = 2
        cursor.execute(consulta, (nuevo_titulo, id_editar))

    # No olvidemos hacer commit cuando hacemos un cambio a la BD
    conexion.commit()
except Exception as e:
    print("Ocurrió un error al editar: ", e)
finally:
    conexion.close()

Se pueden actualizar todos los datos o solo uno; y la consulta puede llevar una o más condiciones, al final todo depende de SQL y no tanto de Python; pues Python solo pasa los datos al motor.

Eliminar datos

Para terminar veamos cómo eliminar datos, usando de nuevo a execute y pasando argumentos que remplazan a los placeholders para evitar inyecciones SQL.

"""
    Conexión a SQLServer con Python
    Ejemplo de CRUD evitando inyecciones SQL
    
    @author parzibyte

    Más tutoriales en:
                        parzibyte.me/blog
"""
from bd import conexion
try:
    with conexion.cursor() as cursor:

        consulta = "DELETE FROM peliculas WHERE anio < ?;"
        anio = 2000
        cursor.execute(consulta, (anio))

    # No olvidemos hacer commit cuando hacemos un cambio a la BD
    conexion.commit()
except Exception as e:
    print("Error eliminando: ", e)
finally:
    conexion.close()

Conclusión

Hemos visto cómo hacer las operaciones básicas en una base de datos de SQL Server a través de Python y el paquete PyODBC.

Recuerda que son ejemplos simples para no confundir al lector, ya que los datos podrían venir de cualquier lugar, por ejemplo, un formulario web o el teclado del usuario, etcétera.

En todos los ejemplos hemos prevenido inyecciones SQL.

Si usas PHP posiblemente quieras ver este post.