Tutoriel Python, PostgreSQL et Psycopg2

Pour développer une application au-delà d'un simple script, il est nécessaire de persister des données hors mémoire dans une base de données.  Il existe de nombreux choix possibles pour une base de données, mais PostgreSQL est une plate-forme open source robuste qui peut facilement évoluer vers la production.

Python et PostgreSQL peuvent être interfacés pour développer rapidement des applications puissantes.  Psycopg est un adaptateur PostgreSQL qui peut être utilisé pour exploiter PostgreSQL via la bibliothèque basée sur Python.  Ce tutoriel vous guidera à travers l'installation de Psycopg2 et du code Python pour démontrer son utilisation.

Vous pouvez installer Psycopg2 via la commande pip du terminal ci-dessous.

$ pip installer psycopg2

Lors de l'installation, vous devriez voir la sortie du terminal ci-dessous.

Collecte de psycopg2
Téléchargement de psycopg2-2.7.3.2-cp27-cp27m-
macosx_10_6_intel.macosx_10_9_intel.macosx_10_9_x86_64.macosx_10_10_intel.macosx_10
_10_x86_64.whl (1.7 Mo)
100% |████████████████████████████████| 1.7 Mo 397 Ko/s
Installation des packages collectés : psycopg2
psycopg2-2 installé avec succès.7.3.2
Bradleys-Mini :~ BradleyPatton$

Pour importer le package Psycopg2 dans votre application Python, vous utilisez la ligne de code ci-dessous.

importer psycopg2

Afin d'obtenir des données à charger dans notre base de données, j'ai emprunté du code d'un précédent tutoriel sur les pandas. Le code ci-dessous créera un DataFrame pandas avec des données historiques.  Cela sera ensuite exploité pour créer une table dans la table PostgreSQL.

def get_data(symboles, start_date, end_date):
panneau = données.DataReader(symboles, 'yahoo', start_date, end_date)
df = panneau['Fermer']
df.colonnes = map(str.inférieur, df.Colonnes)
hd = liste(df)
imprimer df.diriger()
imprimer en haute définition
retour df

Je vais maintenant configurer un code d'entretien utilisé pour exécuter le didacticiel. Ces deux méthodes seront utilisées pour appeler les méthodes Psycopg2 que nous créons.

def tutorial_run() :
symboles = ['SPY', 'AAPL','GOOG']
df = get_data(symboles, '2006-01-03', '2017-12-31')
if __name__ == "__main__":
tutorial_run()

Afin de se connecter à la base de données PostgreSQL, nous devrons ajouter la méthode ci-dessous. Le Try\Except fournit une gestion des erreurs dans le cas où la base de données locale n'est pas en cours d'exécution ou que des paramètres de connexion incorrects sont transmis à la base de données. La méthode connect de la bibliothèque Psycopg2 se connecte à la base de données avec les paramètres passés dans la chaîne de connexion. Vos paramètres pour le nom de base de données, l'utilisateur et le mot de passe peuvent différer. Si la connexion échoue pour une raison quelconque, le message d'erreur sera écrit sur la console. Cette méthode renvoie l'objet de connexion à notre méthode d'appel où il peut être utilisé pour d'autres opérations de base de données.

def connecter() :
cons = "dbname='tutorial' user="postgres" host="localhost" password="password""
essayer:
conn = psycopg2.connecter (contre)
imprimer "Connecté"
sauf:
print "Je n'arrive pas à me connecter à la base de données"
retour conn

Une fois que nous avons établi la connexion à la base de données PostgreSQL, nous pouvons charger nos données de la méthode get_data() dans notre base de données. Psycopg2 et les pandas en font un processus très simple.

La première ligne définit la méthode que les pandas doivent utiliser pour se connecter à la base de données afin de copier le DataFrame. Vous fournirez les mêmes paramètres que votre méthode de connexion. La deuxième ligne de code persiste le DataFrame dans la base de données PostgreSQL avec la méthode to_sql().

def create_table(table, df):
engine = create_engine('postgresql+psycopg2://postgres:[email protected]:5432/tutorial')
df.to_sql(table, moteur, if_exists='replace')

Un rapide coup d'œil dans notre terminal PostgreSQL pgAdmin montre que le code a chargé avec succès le DataFrame dans la table "fermer". Maintenant que nous avons des données chargées dans notre base de données. Nous pouvons utiliser psycopg pour exécuter des requêtes sur les données. La méthode ci-dessous est conçue pour prendre la connexion établie dans notre première méthode et exécuter une requête sur notre base de données PostgreSQL. Afin de créer les 4 objets SQL, nous devons ajouter une autre instruction d'importation.

à partir de psycopg2 importer sql

Afin de créer des commandes SQL dynamiques, psycopg utilise le formatage de chaîne pour remplir les variables dans la chaîne à l'aide des opérateurs %s et .

PostrgreSQL est sensible à la casse. Dans la méthode get_data(), nous avons forcé nos en-têtes de colonnes en minuscules. L'index n'a pas été inclus dans cette instruction. Afin de passer l'en-tête de colonne "Data" majuscule dans la requête, nous devons le transmettre à PostgreSQL entre guillemets doubles. Pour ce faire dans une chaîne en Python, vous devez envoyer le caractère d'échappement "\" avant les guillemets doubles.

Nous pouvons remplacer le ""%s"" dans la chaîne en utilisant la syntaxe de formatage de chaîne python ci-dessous. Cela remplace le %s par notre paramètre de date dt.

Pour exécuter la requête SQL qui a été créée. Vous devez ensuite le passer au curseur .méthode execute(). En appelant le .méthode fetchall(), vous retournez les résultats de la requête. Une fois imprimé sur la console, vous pouvez afficher les résultats.

def get_row(dt, conn):
cr = conn.le curseur()
requête = sql.SQL(""SELECT aapl from close WHERE ""Date"" = '%s'"" % dt)
cr.exécuter l'ordre)
imprimer cr.chercher ()

Pour exécuter cette fonction, nous ajoutons la ligne de code ci-dessous à la méthode tutorial_run(). Vous devriez obtenir des résultats similaires à ceux ci-dessous.

get_row(""2017-12-29"",conn)

Dans la méthode suivante, nous utiliserons les méthodes de format de chaîne pour transmettre plusieurs paramètres dans notre requête. Cette requête prendra une date et trois colonnes. En plus d'utiliser l'opérateur %s, nous utiliserons l'opérateur pour joindre les variables de chaîne dans une chaîne et les injecter dans notre chaîne de requête. Notre chaîne de requête utilise maintenant la jointure ci-dessous avec un séparateur "","" pour passer plusieurs noms de colonnes dans notre requête.

def get_cols(dt, col1, col2, col3, conn):
cr = conn.le curseur()
requête = sql.SQL(""SELECT from close WHERE ""Date"" = '%s'"" % dt).format(
sql.SQL(', ').join([sql.Identifiant (col1), sql.Identifiant (col2), sql.Identifiant(col3)]))
cr.exécuter l'ordre)
imprimer cr.chercher ()

Afin d'utiliser notre nouvelle méthode, j'ajouterai la ligne ci-dessous à notre méthode tutorial_run(). Vous devriez voir les résultats ci-dessous.

get_cols(""2017-12-29"",""aapl"",""spy"", ""goog"", conn)

La prochaine méthode que nous écrivons utilisera deux remplacements de chaîne pour extraire toutes les données de notre table à l'exception de notre index. Cette méthode s'appuie sur notre méthode précédente en ajoutant une deuxième notation de crochet de remplacement ""1"". Cette fois les parenthèses sont numérotées pour qu'elles soient remplacées dans le code notion de format de commande. Notre nouvelle méthode joint les trois paramètres de colonne avec un séparateur de virgule. De plus, le deuxième paramètre de la méthode de format est la variable de table. La chaîne de requête est ensuite construite en remplaçant les crochets par les paramètres dans la méthode de format afin. C'est 0 = colonnes et 1 = nom de la table.

def get_tab(table, col1, col2, col3, conn):
cr = conn.le curseur()
requête = sql.SQL(""SELECT 0 à partir de 1 "").format(
sql.SQL(', ').join([sql.Identifiant (col1), sql.Identifiant(col2),
sql.Identifiant (col3)]), sql.Identifiant (tableau))
cr.exécuter l'ordre)
imprimer cr.chercher ()

Afin d'utiliser notre nouvelle méthode, j'ajouterai la ligne ci-dessous à notre méthode tutorial_run(). Vous devriez voir les résultats ci-dessous.

get_tab(""close"", ""aapl"", ""spy"", ""goog"", conn)

Il y a beaucoup plus de méthodes à explorer dans la bibliothèque psycopg. Cela devrait vous permettre de démarrer avec une bonne compréhension des fonctions psycopg. J'ai fourni quelques ressources supplémentaires ci-dessous dans les pages de documentation qui vous permettront d'explorer plus en profondeur la bibliothèque.

Code complet

importer psycopg2
à partir de psycopg2 importer sql
importer pandas_datareader en tant que données
def get_data(symboles, start_date, end_date):
panneau = données.DataReader(symboles, 'yahoo', start_date, end_date)
df = panneau['Fermer']
df.colonnes = map(str.inférieur, df.Colonnes)
hd = liste(df)
imprimer df.diriger()
imprimer en haute définition
retour df
def connecter() :
cons = ""dbname='tutorial' user=""postgres"" host=""localhost"" password=""password""""
essayer:
conn = psycopg2.connecter (contre)
imprimer ""Connecté""
sauf:
print ""Je n'arrive pas à me connecter à la base de données""
retour conn
def create_table(table, df):
engine = create_engine('postgresql+psycopg2://postgres:[email protected]:5432/tutorial')
df.to_sql(table, moteur, if_exists=""replace"")
def get_row(dt, conn):
cr = conn.le curseur()
requête = sql.SQL(""SELECT aapl from close WHERE ""Date"" = '%s'"" % dt)
cr.exécuter l'ordre)
imprimer cr.chercher ()
def get_cols(dt, col1, col2, col3, conn):
cr = conn.le curseur()
requête = sql.SQL(""SELECT de la fermeture WHERE ""Date"" = '%s'"" % dt).format(
sql.SQL(', ').join([sql.Identifiant(col1),
sql.Identifiant (col2), sql.Identifiant(col3)]))
cr.exécuter l'ordre)
imprimer cr.chercher ()
def get_tab(table,col1, col2, col3, conn):
cr = conn.le curseur()
requête = sql.SQL(""SELECT 0 à partir de 1 "").format(
sql.SQL(', ').join([sql.Identifiant (col1), sql.Identifiant(col2),
sql.Identifiant (col3)]), sql.Identifiant (tableau))
cr.exécuter l'ordre)
imprimer cr.chercher ()
def tutorial_run() :
conn = connecter()
symboles = ['SPY', 'AAPL','GOOG']
df = get_data(symboles, '2006-01-03', '2017-12-31')
create_table(""fermer"", df)
get_row(""2017-12-29"",conn)
get_cols(""2017-12-29"",""aapl"",""spy"", ""goog"", conn)
get_tab(""close"", ""aapl"", ""spy"", ""goog"", conn)
if __name__ == ""__main__"":
tutorial_run()

Les références

initd.org/psycopg
initd.org/psycopg/docs/install.html
http://initd.org/psycopg/docs/sql.html
wiki.postgresql.org/wiki/Psycopg2_Tutorial"