Texte intégral 5520 Mots, durée estimée de l'étude 16 Procès-verbaux
Source: Pexels
J'utilise souvent SQL dans mon travail. Il y a beaucoup de nuances et de limitations gênantes, mais en dernière analyse, c'est la pierre angulaire de l'industrie des données. Par conséquent, pour chaque travailleur dans le domaine des données, SQL est indispensable. La maîtrise de SQL est d'une grande importance.
SQL est très bon, mais comment pouvons-nous nous contenter de «bon»? Pourquoi ne pas manipuler davantage SQL?
Les instructions déclaratives peuvent induire des restrictions SQL, c'est-à-dire que pour rechercher des données à partir de SQL, SQL trouvera et fera des commentaires dans une base de données spécifique. Pour de nombreuses tâches d'extraction de données ou de manipulation de données simples, cela suffit.
Mais que se passe-t-il s'il y a plus de demande?
Cet article vous montrera comment procéder.
Commencez avec les bases
importer pyodbc depuis datetime import datetime classSql: def__init __ (auto, base de données, serveur = "XXVIR00012,55000"): # ici, nous disons à python à quoi se connecter (notre serveur SQL) self.cnxn = pyodbc.connect ("Driver = {SQL Server Native Client 11.0};" "Serveur =" + serveur + ";" "Base de données =" + base de données + ";" "Trusted_Connection = yes;") # initialiser l'attribut de requête self.query = "- {} \ n \ n-- Made in Python" .format (datetime.now () .strftime ("% d /% m /% Y"))Ce code est la base du fonctionnement du serveur MS SQL. Une fois que vous avez écrit ce code, la connexion à SQL via Python ne nécessite que:
sql = Sql ('database123')C'est simple? Plusieurs choses se sont produites en même temps, le code sera analysé ci-dessous. classe Sql:
La première chose à noter est que ce code est contenu dans une classe. J'ai trouvé cela logique, car dans ce format, le processus d'ajout ou de suppression de cette base de données particulière a déjà été effectué. Si vous voyez son processus de travail, la réflexion sera plus claire.
Classe d'initialisation:
def __init __ (auto, base de données, serveur = "XXVIR00012,55000"):Étant donné que l'auteur et ses collègues se connectent presque toujours au même serveur, j'ai défini le nom de ce navigateur universel comme serveur de paramètres par défaut.
Le nom du serveur se trouve dans la boîte de dialogue "Se connecter au serveur" ou en haut de la fenêtre MS SQL Server Management Studio:
Ensuite, connectez-vous à SQL:
self.cnxn = pyodbc.connect ("Driver = {SQL Server Native Client 11.0};" "Server =" + self.server + ";" "Database =" + self.database + ";" "Trusted_Connection = yes;")Le module pyodbc rend cette étape extrêmement simple. Transformez simplement la chaîne de connexion en fonction pyodbc.connect (...), cliquez ici pour plus de détails.
Enfin, j'écris généralement une chaîne de requête dans la classe Sql, et la classe sql est mise à jour avec chaque requête passée à la classe:
self.query = "- {} \ n \ n - Made in Python" .format (datetime.now () .strftime ("% d /% m /% Y"))Cela facilite l'enregistrement du code, tout en rendant la sortie plus lisible et plus confortable à lire pour les autres.
Veuillez noter que dans l'extrait de code suivant, l'auteur ne mettra plus à jour la partie self.query du code.
Tronçon
Certaines fonctions importantes sont très utiles et je les utilise presque tous les jours. Ces fonctions sont axées sur le transfert de données dans et hors de la base de données.
Le répertoire de fichiers image suivant démarre:
Pour ce projet en cours, vous avez besoin de:
· Importez des fichiers dans SQL
· Combinez-les en une seule table
· Création flexible de plusieurs tableaux en fonction des catégories dans les colonnes
Une fois la classe SQL constamment enrichie, le suivi sera beaucoup plus simple:
importer sys sys.path.insert (0, r'C: \ User \ medium \ pysqlplus \ lib ') importer os depuis data importSql sql = Sql ('database123') # initialise l'objet Sql directory = r'C: \ User \ medium \ data \ '# c'est là que nos données génériques sont stockées file_list = os.listdir (répertoire) # obtenir une liste de tous les fichiers pour le fichier dans file_list: # boucle pour importer des fichiers dans sql df = pd.read_csv (répertoire + fichier) # lire le fichier dans la trame de données sql.push_dataframe (df, fichier) # maintenant, nous convertissons nos noms file_list en noms de table que nous avons importés en SQL table_names = sql.union (table_names, 'generic_jan') # union nos fichiers dans une nouvelle table appelée 'generic_jan' sql.drop (table_names) # supprimez nos tables d'origine car nous avons maintenant une table complète # obtenir la liste des catégories dans colX, par exemple sets = list (sql.manual ("SELECT colX AS 'category' FROM generic_jan GROUP BY colX", response = True)) pour la catégorie en sets: sql.manual ("SELECT * INTO generic_jan _" + catégorie + "FROM generic_jan OERE colX = '" + catégorie + "'")Commencer à partir de zéro.
Structure de données push
defpush_dataframe (self, data, table = "raw_data", batchsize = 500): # créer un curseur d'exécution curseur = self.cnxn.cursor () # activer exécution rapide cursor.fast_executemany = True # create create table instruction query = "CREATE TABLE (\ n" # parcourir chaque colonne à inclure dans l'instruction create table pour i inrange (len (list (data))): requête + = "\ t varchar (255)". format (liste (données) ) # ajouter une colonne (tout est varchar pour l'instant) # ajouter un code de connexion / instruction de fin correct si i! = len (liste (données)) - 1: requête + = ", \ n" autre: requête + = "\ n);" cursor.execute (query) # exécute l'instruction create table self.cnxn.commit () # modifications de validation # ajouter une requête à notre enregistreur de code SQL self.query + = ("\ n \ n-- créer une table \ n" + requête) # insérer les données par lots query = ("INSERT INTO ({}) \ n" .format (table, ',') + "VALEURS \ n (? {})". Format (",?" * (Len (liste (données)) - 1))) # insérer des données dans la table cible par lots de 'batchsize' pour i inrange (0, len (data), batchsize): si i + batchsize > len (données): batch = data.values.tolist () autre: batch = data.values.tolist () # exécuter l'insertion par lots cursor.executemany (requête, lot) # commit insert dans SQL Server self.cnxn.commit ()Cette fonction est incluse dans la classe SQL et peut facilement insérer la trame de données Pandas dans la base de données SQL.
Il est très utile lorsque vous devez télécharger un grand nombre de fichiers. Cependant, la vraie raison pour laquelle Python peut insérer des données dans SQL est sa flexibilité.
Il est vraiment horrible d'insérer une étiquette spécifique dans SQL pour couvrir une douzaine de classeurs Excel. Mais avec Python, c'est du gâteau. Nous avons maintenant construit une fonction qui peut lire des balises à l'aide de Python et peut également insérer des balises dans SQL.
Manuel (fonction)
defmanual (self, query, response = False): cursor = self.cnxn.cursor () # créer un curseur d'exécution si réponse: returnread_sql (query, self.cnxn) # récupère la sortie de la requête sql vers le dataframe essayer: cursor.execute (query) # execute sauf erreur pyodbc.ProgrammingErroras: print ("Avertissement: \ n {}". format (erreur)) # erreur d'impression comme avertissement self.cnxn.commit () # requête de validation sur SQL Server retourner "Requête terminée".Cette fonction est en fait utilisée dans les fonctions union et drop. Cela ne peut que rendre le traitement du code SQL aussi simple que possible.
Le paramètre de réponse peut décompresser la sortie de la requête vers le DataFrame. Le colX de la table generic_jan peut être utilisé pour extraire toutes les valeurs uniques. L'opération est la suivante:
sets = list (sql.manual ("SELECT colX AS 'category' FROM generic_jan GROUP BYcolX", response = True))Union (fonction)
Après avoir construit la fonction manuelle, la création de la fonction union est simple:
défunion (self, table_list, name = "union", join = "UNION"): # initialiser la requête query = "SELECT * INTO FROM (\ n" # construire la requête SQL query + = f '\ n {join} \ n'.join ( ) query + = ") x" # add end of query self.manual (requête, fast = True) # exécution rapideLa création d'une fonction d'union fait simplement référence au nom de table proposé par table_list pour construire une requête de fonction UNION pour le nom de table donné. Utilisez ensuite le traitement self.manual (requête).
Drop (fonction)
Il est possible de télécharger un grand nombre de tables sur le serveur SQL. Bien que réalisable, il surchargera rapidement la base de données. Pour résoudre ce problème, vous devez créer une fonction de dépôt:
defdrop (auto, tableaux): # vérifier si unique ou liste ifisinstance (tableaux, str): # si chaîne unique, convertir en élément unique dans la liste pour for-loop tables = pour table en tables: # vérifier la table préexistante et supprimer si présente query = ("IF OBJECT_ID ('', 'U') IS NOT NULL" "DROP TABLE") self.manual (query) # executeafficher rawpysqlplus_drop_short.py hébergé avec par GitHub
Cliquez sur
https://gist.github.com/jamescalam/b316c1714c30986fff58c22b00395cc0
Obtenez une image complète
Encore une fois, cette fonction est extrêmement simple en raison de la fonction manuelle. L'opérateur peut choisir d'entrer des caractères dans le tableau, supprimer un seul tableau ou fournir un nom de liste au tableau, supprimer plusieurs tableaux.
Source: Pexels
Lorsque ces fonctions très simples sont combinées, vous pouvez profiter de Python pour enrichir considérablement les fonctionnalités de SQL.
J'utilise cette méthode presque tous les jours, elle est simple et très efficace.
J'espère aider d'autres utilisateurs à trouver des moyens d'incorporer Python dans leur chemin SQL, merci pour la lecture!
laissez un commentaire
Partageons ensemble les produits de base de l'apprentissage et du développement de l'IA
Si réimprimé, veuillez laisser un message en arrière-plan et suivre les spécifications de réimpression