Logotipo de Zephyrnet

Lectura y escritura de archivos SQL en Pandas

Fecha:

Cuando comencé a aprender Análisis de datos hace unos años, lo primero que aprendí fue SQL y Pandas. Como analista de datos, es crucial tener una base sólida para trabajar con SQL y Pandas. Ambas son herramientas poderosas que ayudan a los analistas de datos a analizar y manipular de manera eficiente los datos almacenados en las bases de datos.

Descripción general de SQL y Pandas

SQL (Structured Query Language) es un lenguaje de programación utilizado para administrar y manipular bases de datos relacionales. Por otro lado, Pandas es una biblioteca de Python utilizada para la manipulación y el análisis de datos.

El análisis de datos implica trabajar con grandes cantidades de datos y, a menudo, se utilizan bases de datos para almacenar estos datos. SQL y Pandas brindan herramientas poderosas para trabajar con bases de datos, lo que permite a los analistas de datos extraer, manipular y analizar datos de manera eficiente. Al aprovechar estas herramientas, los analistas de datos pueden obtener información valiosa a partir de datos que, de otro modo, serían difíciles de obtener.

En este artículo, exploraremos cómo usar SQL y Pandas para leer y escribir en una base de datos.

Conexión a la base de datos

Instalación de las bibliotecas

Primero debemos instalar las bibliotecas necesarias antes de poder conectarnos a la base de datos SQL con Pandas. Las dos bibliotecas principales requeridas son Pandas y SQLAlchemy. Pandas es una biblioteca de manipulación de datos popular que permite el almacenamiento de grandes estructuras de datos, como se mencionó en la introducción. Por el contrario, SQLAlchemy proporciona una API para conectarse e interactuar con la base de datos SQL.

Podemos instalar ambas bibliotecas utilizando el administrador de paquetes de Python, pip, ejecutando los siguientes comandos en el símbolo del sistema.

$ pip install pandas
$ pip install sqlalchemy

Haciendo la conexión

Con las bibliotecas instaladas, ahora podemos usar Pandas para conectarnos a la base de datos SQL.

Para comenzar, crearemos un objeto de motor SQLAlchemy con create_engine(). create_engine() La función conecta el código de Python a la base de datos. Toma como argumento una cadena de conexión que especifica el tipo de base de datos y los detalles de la conexión. En este ejemplo, usaremos el tipo de base de datos SQLite y la ruta del archivo de la base de datos.

Cree un objeto de motor para una base de datos SQLite usando el siguiente ejemplo:

import pandas as pd
from sqlalchemy import create_engine engine = create_engine('sqlite:///C/SQLite/student.db')

Si el archivo de la base de datos SQLite, student.db en nuestro caso, está en el mismo directorio que el script de Python, podemos usar el nombre del archivo directamente, como se muestra a continuación.

engine = create_engine('sqlite:///student.db')

Lectura de archivos SQL con Pandas

Leamos los datos ahora que hemos establecido una conexión. En esta sección, veremos la read_sql, read_sql_tabley read_sql_query funciones y cómo usarlas para trabajar con una base de datos.

Ejecutar consultas SQL usando Panda's leer_sql() Función

La read_sql() es una función de la biblioteca de Pandas que nos permite ejecutar una consulta SQL y recuperar los resultados en un marco de datos de Pandas. El read_sql() La función conecta SQL y Python, lo que nos permite aprovechar el poder de ambos lenguajes. La función envuelve read_sql_table() y read_sql_query(). read_sql() La función se enruta internamente en función de la entrada proporcionada, lo que significa que si la entrada es para ejecutar una consulta SQL, se enrutará a read_sql_query(), y si es una tabla de base de datos, se enrutará a read_sql_table().

La read_sql() la sintaxis es la siguiente:

pandas.read_sql(sql, con, index_col=None, coerce_float=True, params=None, parse_dates=None, columns=None, chunksize=None)

Se requieren parámetros SQL y con; el resto son opcionales. Sin embargo, podemos manipular el resultado usando estos parámetros opcionales. Echemos un vistazo más de cerca a cada parámetro.

  • sql: consulta SQL o nombre de la tabla de la base de datos
  • con: Objeto de conexión o URL de conexión
  • index_col: Este parámetro nos permite usar una o más columnas del resultado de la consulta SQL como índice del marco de datos. Puede tomar una sola columna o una lista de columnas.
  • coerce_float: este parámetro especifica si los valores no numéricos deben convertirse en números flotantes o dejarse como cadenas. Se establece en verdadero de forma predeterminada. Si es posible, convierte valores no numéricos en tipos flotantes.
  • params: Los parámetros proporcionan un método seguro para pasar valores dinámicos a la consulta SQL. Podemos usar el parámetro params para pasar un diccionario, una tupla o una lista. Dependiendo de la base de datos, la sintaxis de params varía.
  • parse_dates: Esto nos permite especificar qué columna en el marco de datos resultante se interpretará como una fecha. Acepta una sola columna, una lista de columnas o un diccionario con la clave como nombre de columna y el valor como formato de columna.
  • columns: Esto nos permite obtener solo las columnas seleccionadas de la lista.
  • chunksize: cuando se trabaja con un gran conjunto de datos, el tamaño de fragmento es importante. Recupera el resultado de la consulta en fragmentos más pequeños, lo que mejora el rendimiento.

Aquí tienes un ejemplo de cómo usar read_sql():

Código:

import pandas as pd
from sqlalchemy import create_engine engine = create_engine('sqlite:///C/SQLite/student.db') df = pd.read_sql("SELECT * FROM Student", engine, index_col='Roll Number', parse_dates='dateOfBirth')
print(df)
print("The Data type of dateOfBirth: ", df.dateOfBirth.dtype) engine.dispose()

Salida:

 firstName lastName email dateOfBirth
rollNumber
1 Mark Simson [email protected] 2000-02-23
2 Peter Griffen [email protected] 2001-04-15
3 Meg Aniston [email protected] 2001-09-20
Date type of dateOfBirth: datetime64[ns]

Después de conectarnos a la base de datos, ejecutamos una consulta que devuelve todos los registros de la Student table y los almacena en el DataFrame df. La columna "Número de rollo" se convierte en un índice utilizando el index_col parámetro, y el tipo de datos "dateOfBirth" es "datetime64[ns]" debido a parse_dates. Nosotros podemos usar read_sql() no solo para recuperar datos, sino también para realizar otras operaciones, como insertar, eliminar y actualizar. read_sql() es una función genérica.

Carga de tablas o vistas específicas desde la base de datos

Cargando una tabla o vista específica con Pandas read_sql_table() es otra técnica para leer datos de la base de datos en un marco de datos de Pandas.

Que es leer_tabla_sql?

La biblioteca Pandas proporciona la read_sql_table función, que está específicamente diseñada para leer una tabla SQL completa sin ejecutar ninguna consulta y devolver el resultado como un marco de datos de Pandas.

La sintaxis de read_sql_table() es como a continuación:

pandas.read_sql_table(table_name, con, schema=None, index_col=None, coerce_float=True, parse_dates=None, columns=None, chunksize=None)

Excepto por table_name y el esquema, los parámetros se explican de la misma manera que read_sql().

  • table_name: El parámetro table_name es el nombre de la tabla SQL en la base de datos.
  • schema: este parámetro opcional es el nombre del esquema que contiene el nombre de la tabla.

Después de crear una conexión a la base de datos, usaremos el read_sql_table Función para cargar el Student tabla en un Pandas DataFrame.

import pandas as pd
from sqlalchemy import create_engine engine = create_engine('sqlite:///C/SQLite/student.db') df = pd.read_sql_table('Student', engine)
print(df.head()) engine.dispose()

Salida:

 rollNumber firstName lastName email dateOfBirth
0 1 Mark Simson [email protected] 2000-02-23
1 2 Peter Griffen [email protected] 2001-04-15
2 3 Meg Aniston [email protected] 2001-09-20

Asumiremos que es una tabla grande que puede consumir mucha memoria. Exploremos cómo podemos usar el chunksize parámetro para abordar este problema.

Consulte nuestra guía práctica y práctica para aprender Git, con las mejores prácticas, los estándares aceptados por la industria y la hoja de trucos incluida. Deja de buscar en Google los comandos de Git y, de hecho, aprenden ella!

Código:

import pandas as pd
from sqlalchemy import create_engine engine = create_engine('sqlite:///C/SQLite/student.db') df_iterator = pd.read_sql_table('Student', engine, chunksize = 1) for df in df_iterator: print(df.head()) engine.dispose()

Salida:

 rollNumber firstName lastName email dateOfBirth
0 1 Mark Simson [email protected] 2000-02-23
0 2 Peter Griffen [email protected] 2001-04-15
0 3 Meg Aniston [email protected] 2001-09-20

Tenga en cuenta que el chunksize Estoy usando aquí es 1 porque solo tengo 3 registros en mi tabla.

Consultar la base de datos directamente con la sintaxis SQL de Pandas

Extraer información de la base de datos es una parte importante para los analistas de datos y los científicos. Para ello, aprovecharemos la read_sql_query() función.

¿Qué es read_sql_query()?

usando pandas read_sql_query() función, podemos ejecutar consultas SQL y obtener los resultados directamente en un DataFrame. El read_sql_query() La función se crea específicamente para SELECT declaraciones. No se puede utilizar para ninguna otra operación, como DELETE or UPDATE.

Sintaxis:

pandas.read_sql_query(sql, con, index_col=None, coerce_float=True, params=None, parse_dates=None, chunksize=None, dtype=None, dtype_backend=_NoDefault.no_default)

Todas las descripciones de los parámetros son las mismas que las read_sql() función. Aquí hay un ejemplo de read_sql_query():

Código:

import pandas as pd
from sqlalchemy import create_engine engine = create_engine('sqlite:///C/SQLite/student.db') df = pd.read_sql_query('Select firstName, lastName From Student Where rollNumber = 1', engine)
print(df) engine.dispose()

Salida:

 firstName lastName
0 Mark Simson

Escribir archivos SQL con Pandas

Mientras analizamos los datos, supongamos que descubrimos que se deben modificar algunas entradas o que se requiere una nueva tabla o vista con los datos. Para actualizar o insertar un nuevo registro, un método es utilizar read_sql() y escribir una consulta. Sin embargo, ese método puede ser largo. Pandas proporciona un gran método llamado to_sql() para situaciones como esta.

En esta sección, primero construiremos una nueva tabla en la base de datos y luego editaremos una existente.

Crear una nueva tabla en la base de datos SQL

Antes de crear una nueva tabla, analicemos primero to_sql() en detalle.

Que es to_sql()?

La to_sql() La función de la librería Pandas nos permite escribir o actualizar la base de datos. El to_sql() La función puede guardar datos de DataFrame en una base de datos SQL.

Sintaxis para to_sql():

DataFrame.to_sql(name, con, schema=None, if_exists='fail', index=True, index_label=None, chunksize=None, dtype=None, method=None)

Solo name y con los parámetros son obligatorios para ejecutar to_sql(); sin embargo, otros parámetros brindan flexibilidad adicional y opciones de personalización. Analicemos cada parámetro en detalle:

  • name: El nombre de la tabla SQL que se va a crear o modificar.
  • con: El objeto de conexión de la base de datos.
  • schema: El esquema de la tabla (opcional).
  • if_exists: El valor predeterminado de este parámetro es "fail". Este parámetro nos permite decidir la acción a realizar si la tabla ya existe. Las opciones incluyen "error", "reemplazar" y "agregar".
  • index: el parámetro de índice acepta un valor booleano. De forma predeterminada, se establece en True, lo que significa que el índice del DataFrame se escribirá en la tabla SQL.
  • index_label: este parámetro opcional nos permite especificar una etiqueta de columna para las columnas de índice. De forma predeterminada, el índice se escribe en la tabla, pero se puede dar un nombre específico usando este parámetro.
  • chunksize: El número de filas que se escribirán a la vez en la base de datos SQL.
  • dtype: este parámetro acepta un diccionario con claves como nombres de columna y valores como sus tipos de datos.
  • method: El parámetro de método permite especificar el método utilizado para insertar datos en el SQL. De forma predeterminada, está configurado en Ninguno, lo que significa que los pandas encontrarán la forma más eficiente en función de la base de datos. Hay dos opciones principales para los parámetros del método:
    • multi: Permite insertar múltiples filas en una sola consulta SQL. Sin embargo, no todas las bases de datos admiten la inserción de varias filas.
    • Función invocable: Aquí, podemos escribir una función personalizada para insertar y llamarla usando parámetros de método.

Aquí hay un ejemplo usando to_sql():

import pandas as pd
from sqlalchemy import create_engine engine = create_engine('sqlite:///C/SQLite/student.db') data = {'Name': ['Paul', 'Tom', 'Jerry'], 'Age': [9, 8, 7]}
df = pd.DataFrame(data) df.to_sql('Customer', con=engine, if_exists='fail') engine.dispose()

Se crea una nueva tabla llamada Cliente en la base de datos, con dos campos llamados "Nombre" y "Edad".

Instantánea de la base de datos:

Salida de to_sql()

Actualización de tablas existentes con marcos de datos de Pandas

Actualizar datos en una base de datos es una tarea compleja, especialmente cuando se trata de grandes cantidades de datos. Sin embargo, usando el to_sql() en Pandas puede hacer esta tarea mucho más fácil. Para actualizar la tabla existente en la base de datos, el to_sql() La función se puede utilizar con el if_exists parámetro establecido en "reemplazar". Esto sobrescribirá la tabla existente con los nuevos datos.

Aquí hay un ejemplo de to_sql() que actualiza el creado previamente Customer mesa. Supongamos, en el Customer queremos actualizar la edad de un cliente llamado Paul de 9 a 10 años. Para hacerlo, primero podemos modificar la fila correspondiente en el DataFrame, y luego usar el to_sql() Función para actualizar la base de datos.

Código:

import pandas as pd
from sqlalchemy import create_engine engine = create_engine('sqlite:///C/SQLite/student.db') df = pd.read_sql_table('Customer', engine) df.loc[df['Name'] == 'Paul', 'Age'] = 10 df.to_sql('Customer', con=engine, if_exists='replace') engine.dispose()

En la base de datos, la edad de Paul se actualiza:

Salida de to_sql()

Conclusión

En conclusión, Pandas y SQL son herramientas poderosas para tareas de análisis de datos, como leer y escribir datos en la base de datos SQL. Pandas proporciona una manera fácil de conectarse a la base de datos SQL, leer datos de la base de datos en un marco de datos de Pandas y escribir datos del marco de datos nuevamente en la base de datos.

La biblioteca de Pandas facilita la manipulación de datos en un marco de datos, mientras que SQL proporciona un lenguaje poderoso para consultar datos en una base de datos. El uso de Pandas y SQL para leer y escribir los datos puede ahorrar tiempo y esfuerzo en las tareas de análisis de datos, especialmente cuando los datos son muy grandes. En general, aprovechar SQL y Pandas juntos puede ayudar a los analistas de datos y científicos a optimizar su flujo de trabajo.

punto_img

Información más reciente

punto_img