Lire et écrire des fichiers SQL dans Pandas

Lorsque j'ai commencé à apprendre l'analyse de données il y a quelques années, la première chose que j'ai apprise était SQL et Pandas. En tant qu'analyste de données, il est crucial d'avoir une base solide pour travailler avec SQL et Pandas. Les deux sont des outils puissants qui aident les analystes de données à analyser et à manipuler efficacement les données stockées dans les bases de données.

Vue d'ensemble de SQL et de Pandas

SQL (Structured Query Language) est un langage de programmation utilisé pour gérer et manipuler des bases de données relationnelles. D'autre part, Pandas est une bibliothèque Python utilisée pour la manipulation et l'analyse de données.

L'analyse des données implique de travailler avec de grandes quantités de données, et les bases de données sont souvent utilisées pour stocker ces données. SQL et Pandas fournissent des outils puissants pour travailler avec des bases de données, permettant aux analystes de données d'extraire, de manipuler et d'analyser efficacement les données. En tirant parti de ces outils, les analystes de données peuvent obtenir des informations précieuses à partir de données qui seraient autrement difficiles à obtenir.

Dans cet article, nous allons explorer comment utiliser SQL et Pandas pour lire et écrire dans une base de données.

Connexion à la BD

Installation des bibliothèques

Nous devons d'abord installer les bibliothèques nécessaires avant de pouvoir nous connecter à la base de données SQL avec Pandas. Les deux principales bibliothèques requises sont Pandas et SQLAlchemy. Pandas est une bibliothèque de manipulation de données populaire qui permet le stockage de grandes structures de données, comme mentionné dans l'introduction. En revanche, SQLAlchemy fournit une API pour se connecter et interagir avec la base de données SQL.

Nous pouvons installer les deux bibliothèques à l'aide du gestionnaire de packages Python, pip, en exécutant les commandes suivantes à l'invite de commande.

$ pip install pandas
$ pip install sqlalchemy

Établir la connexion

Avec les bibliothèques installées, nous pouvons maintenant utiliser Pandas pour nous connecter à la base de données SQL.

Pour commencer, nous allons créer un objet moteur SQLAlchemy avec create_engine()L’ create_engine() La fonction connecte le code Python à la base de données. Il prend comme argument une chaîne de connexion qui spécifie le type de base de données et les détails de connexion. Dans cet exemple, nous utiliserons le type de base de données SQLite et le chemin du fichier de base de données.

Créez un objet moteur pour une base de données SQLite en utilisant l'exemple ci-dessous :

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

Si le fichier de base de données SQLite, student.db dans notre cas, se trouve dans le même répertoire que le script Python, nous pouvons utiliser le nom du fichier directement, comme indiqué ci-dessous.

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

Lire des fichiers SQL avec Pandas

Lisons les données maintenant que nous avons établi une connexion. Dans cette section, nous examinerons les read_sql, read_sql_tableet la read_sql_query fonctions et comment les utiliser pour travailler avec une base de données.

Exécuter des requêtes SQL à l'aide de Panda read_sql() Fonction

Les read_sql() est une fonction de la bibliothèque Pandas qui nous permet d'exécuter une requête SQL et de récupérer les résultats dans une dataframe Pandas. Le read_sql() connecte SQL et Python, nous permettant de tirer parti de la puissance des deux langages. La fonction enveloppe read_sql_table() ainsi que les read_sql_query()L’ read_sql() la fonction est acheminée en interne en fonction de l'entrée fournie, ce qui signifie que si l'entrée doit exécuter une requête SQL, elle sera acheminée vers read_sql_query(), et s'il s'agit d'une table de base de données, elle sera acheminée vers read_sql_table().

Les read_sql() la syntaxe est la suivante :

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

Les paramètres SQL et con sont requis ; le reste est facultatif. Cependant, nous pouvons manipuler le résultat en utilisant ces paramètres optionnels. Examinons de plus près chaque paramètre.

• sql: nom de la requête SQL ou de la table de la base de données
• con: Objet de connexion ou URL de connexion
• index_col: Ce paramètre nous permet d'utiliser une ou plusieurs colonnes du résultat de la requête SQL comme index de bloc de données. Il peut prendre une seule colonne ou une liste de colonnes.
• coerce_float: Ce paramètre spécifie si les valeurs non numériques doivent être converties en nombres flottants ou laissées sous forme de chaînes. Il est défini sur vrai par défaut. Si possible, il convertit les valeurs non numériques en types flottants.
• params: Les paramètres fournissent une méthode sécurisée pour transmettre des valeurs dynamiques à la requête SQL. Nous pouvons utiliser le paramètre params pour passer un dictionnaire, un tuple ou une liste. Selon la base de données, la syntaxe des paramètres varie.
• parse_dates: Cela nous permet de spécifier quelle colonne de la trame de données résultante sera interprétée comme une date. Il accepte une seule colonne, une liste de colonnes ou un dictionnaire avec la clé comme nom de colonne et la valeur comme format de colonne.
• columns: Cela nous permet de récupérer uniquement les colonnes sélectionnées dans la liste.
• chunksize: Lorsque vous travaillez avec un grand ensemble de données, la taille de bloc est importante. Il récupère le résultat de la requête en petits morceaux, ce qui améliore les performances.

Voici un exemple d'utilisation read_sql():

Code:

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()

Sortie :

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

Après la connexion à la base de données, nous exécutons une requête qui renvoie tous les enregistrements de la Student table et les stocke dans le DataFrame df. La colonne « Roll Number » est convertie en index à l'aide de la index_col paramètre, et le type de données "dateOfBirth" est "datetime64[ns]" en raison de parse_dates. On peut utiliser read_sql() non seulement pour récupérer des données, mais également pour effectuer d'autres opérations telles que l'insertion, la suppression et la mise à jour. read_sql() est une fonction générique.

Chargement de tables ou de vues spécifiques à partir de la base de données

Charger une table ou une vue spécifique avec Pandas read_sql_table() est une autre technique pour lire les données de la base de données dans une base de données Pandas.

Qu’est ce qu' read_sql_table?

La bibliothèque Pandas fournit les read_sql_table fonction, qui est spécifiquement conçue pour lire une table SQL entière sans exécuter de requêtes et renvoyer le résultat sous forme de dataframe Pandas.

La syntaxe de read_sql_table() est comme ci-dessous:

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

À l'exception de table_name et schéma, les paramètres sont expliqués de la même manière que read_sql().

• table_name: Le paramètre table_name est le nom de la table SQL dans la base de données.
• schema: Ce paramètre facultatif est le nom du schéma contenant le nom de la table.

Après avoir créé une connexion à la base de données, nous utiliserons le read_sql_table fonction pour charger le Student table dans 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()

Sortie :

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

Nous supposerons qu'il s'agit d'une grande table qui peut être gourmande en mémoire. Voyons comment nous pouvons utiliser le chunksize paramètre pour résoudre ce problème.

Code:

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()

Sortie :

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

Veuillez garder à l'esprit que le chunksize J'utilise ici est 1 car je n'ai que 3 enregistrements dans ma table.

Interroger directement la base de données avec la syntaxe SQL de Pandas

L'extraction d'informations de la base de données est une partie importante pour les analystes de données et les scientifiques. Pour ce faire, nous nous appuierons sur read_sql_query() la fonction.

Qu'est-ce que read_sql_query() ?

Utiliser les pandas read_sql_query() fonction, nous pouvons exécuter des requêtes SQL et obtenir les résultats directement dans un DataFrame. Le read_sql_query() fonction est créée spécifiquement pour SELECT déclarations. Il ne peut pas être utilisé pour d'autres opérations, telles que DELETE or UPDATE.

syntaxe:

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)

Toutes les descriptions de paramètres sont les mêmes que read_sql() fonction. Voici un exemple de read_sql_query():

Code:

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()

Sortie :

 firstName lastName
0 Mark Simson

Écrire des fichiers SQL avec Pandas

Lors de l'analyse des données, supposons que nous ayons découvert que quelques entrées doivent être modifiées ou qu'une nouvelle table ou vue avec les données est requise. Pour mettre à jour ou insérer un nouvel enregistrement, une méthode consiste à utiliser read_sql() et écrire une requête. Cependant, cette méthode peut être longue. Les pandas fournissent une excellente méthode appelée to_sql() pour des situations comme celle-ci.

Dans cette section, nous allons d'abord créer une nouvelle table dans la base de données, puis en modifier une existante.

Création d'une nouvelle table dans la base de données SQL

Avant de créer une nouvelle table, discutons d'abord to_sql() en détail.

Qu’est ce qu' vers_sql()?

Les to_sql() fonction de la bibliothèque Pandas nous permet d'écrire ou de mettre à jour la base de données. Le to_sql() La fonction peut enregistrer des données DataFrame dans une base de données SQL.

Syntaxe pour to_sql():

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

Seulement name ainsi que les con les paramètres sont obligatoires pour s'exécuter to_sql(); cependant, d'autres paramètres offrent une flexibilité supplémentaire et des options de personnalisation. Discutons de chaque paramètre en détail :

• name: nom de la table SQL à créer ou à modifier.
• con: L'objet de connexion de la base de données.
• schema: Le schéma de la table (facultatif).
• if_exists: La valeur par défaut de ce paramètre est « fail ». Ce paramètre nous permet de décider de l'action à entreprendre si la table existe déjà. Les options incluent « échouer », « remplacer » et « ajouter ».
• index: Le paramètre index accepte une valeur booléenne. Par défaut, il est défini sur True, ce qui signifie que l'index du DataFrame sera écrit dans la table SQL.
• index_label: Ce paramètre facultatif nous permet de spécifier une étiquette de colonne pour les colonnes d'index. Par défaut, l'index est écrit dans la table, mais un nom spécifique peut être donné à l'aide de ce paramètre.
• chunksize: Le nombre de lignes à écrire à la fois dans la base de données SQL.
• dtype: Ce paramètre accepte un dictionnaire avec des clés comme noms de colonnes et des valeurs comme types de données.
• method: Le paramètre méthode permet de spécifier la méthode utilisée pour insérer les données dans le SQL. Par défaut, il est défini sur Aucun, ce qui signifie que les pandas trouveront le moyen le plus efficace en fonction de la base de données. Il existe deux options principales pour les paramètres de méthode :
  • multi: Il permet d'insérer plusieurs lignes dans une seule requête SQL. Cependant, toutes les bases de données ne prennent pas en charge l'insertion sur plusieurs lignes.
  • Fonction appelable: Ici, nous pouvons écrire une fonction personnalisée pour insert et l'appeler à l'aide des paramètres de méthode.

Voici un exemple utilisant 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()

Une nouvelle table appelée Client est créée dans la base de données, avec deux champs appelés « Nom » et « Âge ».

Instantané de la base de données :

Mise à jour des tables existantes avec les dataframes Pandas

La mise à jour des données dans une base de données est une tâche complexe, en particulier lorsqu'il s'agit de données volumineuses. Cependant, en utilisant le to_sql() fonction dans Pandas peut rendre cette tâche beaucoup plus facile. Pour mettre à jour la table existante dans la base de données, le to_sql() fonction peut être utilisée avec le if_exists paramètre réglé sur "remplacer". Cela écrasera la table existante avec les nouvelles données.

Voici un exemple de to_sql() qui met à jour le précédemment créé Customer tableau. Supposons, dans le Customer table, nous voulons mettre à jour l'âge d'un client nommé Paul de 9 à 10. Pour ce faire, nous pouvons d'abord modifier la ligne correspondante dans le DataFrame, puis utiliser le to_sql() fonction de mise à jour de la base de données.

Code:

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()

Dans la base de données, l'âge de Paul est mis à jour :

Conclusion

En conclusion, Pandas et SQL sont tous deux des outils puissants pour les tâches d'analyse de données telles que la lecture et l'écriture de données dans la base de données SQL. Pandas fournit un moyen simple de se connecter à la base de données SQL, de lire les données de la base de données dans une trame de données Pandas et de réécrire les données de la trame de données dans la base de données.

La bibliothèque Pandas facilite la manipulation des données dans une base de données, tandis que SQL fournit un langage puissant pour interroger les données dans une base de données. L'utilisation à la fois de Pandas et de SQL pour lire et écrire les données peut économiser du temps et des efforts dans les tâches d'analyse de données, en particulier lorsque les données sont très volumineuses. Dans l'ensemble, l'utilisation conjointe de SQL et de Pandas peut aider les analystes de données et les scientifiques à rationaliser leur flux de travail.

• Contenu propulsé par le référencement et distribution de relations publiques. Soyez amplifié aujourd'hui.
• PlatoAiStream. Intelligence des données Web3. Connaissance Amplifiée. Accéder ici.
• Frapper l'avenir avec Adryenn Ashley. Accéder ici.
• Achetez et vendez des actions de sociétés PRE-IPO avec PREIPO®. Accéder ici.
• La source: https://stackabuse.com/reading-and-writing-sql-files-in-pandas/