Tutorial su Python, PostgreSQL e Psycopg2

Per sviluppare un'applicazione oltre un semplice script, è necessario rendere persistenti i dati al di fuori della memoria in un database.  Ci sono molte scelte possibili per un database, ma PostgreSQL è una solida piattaforma open source che può facilmente scalare fino alla produzione.

Python e PostgreSQL possono essere interfacciati per sviluppare rapidamente potenti applicazioni.  Psycopg è un adattatore PostgreSQL che può essere utilizzato per sfruttare PostgreSQL tramite la libreria basata su Python.  Questo tutorial illustrerà l'installazione di Psycopg2 e del codice Python per dimostrarne l'uso.

Puoi installare Psycopg2 tramite il comando pip del terminale sottostante.

$ pip installa psycopg2

Durante l'installazione dovresti vedere l'output del terminale di seguito.

Collezionare psycopg2
Download di 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.7MB)
100% |████████████████████████████████| 1.7 MB 397 kB/s
Installazione dei pacchetti raccolti: psycopg2
psycopg2-2 installed installato con successo.7.3.2
Bradleys-Mini:~BradleyPatton$

Per importare il pacchetto Psycopg2 nella tua applicazione Python usa la riga di codice sottostante below.

importa psycopg2

Per caricare alcuni dati nel nostro database, ho preso in prestito del codice da un precedente tutorial sui panda. Il codice seguente creerà un DataFrame panda con dati storici.  Questo verrà quindi sfruttato per creare una tabella nella tabella PostgreSQL.

def get_data(symbols, start_date, end_date):
pannello = dati.DataReader (simboli, 'yahoo', data_inizio, data_fine)
df = pannello['Chiudi']
df.colonne = map(str.inferiore, df.colonne)
hd = lista (df)
stampa df.testa()
stampa hd
ritorno df

Ora imposterò del codice di pulizia utilizzato per eseguire il tutorial. Questi due metodi verranno utilizzati per chiamare i metodi Psycopg2 che creiamo.

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

Per connetterci al database PostgreSQL, dovremo aggiungere il metodo seguente. Try\Except fornisce una gestione degli errori nel caso in cui il database locale non sia in esecuzione o vengano passati al database parametri di connessione errati. Il metodo connect nella libreria Psycopg2 si connette al database con i parametri passati nella stringa di connessione. I tuoi parametri per dbname, utente e password potrebbero differire. Se la connessione fallisce per qualche motivo, il messaggio di errore verrà scritto sulla console. Questo metodo restituisce l'oggetto connessione al nostro metodo di chiamata dove può essere utilizzato per ulteriori operazioni sul database.

def connect():
cons = "dbname='tutorial' user="postgres" host="localhost" password="password""
provare:
conn = psycopg2.collegare (contro)
stampa "Connesso"
tranne:
print "Non riesco a connettermi al database"
ritorno conn

Una volta stabilita la connessione al database PostgreSQL, possiamo caricare i nostri dati dal metodo get_data() nel nostro database. Psycopg2 e panda rendono questo processo molto semplice.

La prima riga definisce il metodo che i panda dovrebbero utilizzare per connettersi al database per copiare il DataFrame. Fornirai gli stessi parametri del tuo metodo di connessione. La seconda riga di codice mantiene il DataFrame nel database PostgreSQL con il metodo to_sql().

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

Una rapida occhiata nel nostro terminale pgAdmin PostgreSQL mostra che il codice ha caricato con successo il DataFrame nella tabella "chiudi". Ora che abbiamo caricato alcuni dati nel nostro database. Possiamo usare psycopg per eseguire alcune query sui dati. Il metodo seguente è costruito per prendere la connessione stabilita nel nostro primo metodo ed eseguire una query sul nostro database PostgreSQL. Per creare i 4 oggetti SQL dobbiamo aggiungere un'altra istruzione di importazione.

da psycopg2 import sql

Per creare comandi SQL dinamici, psycopg utilizza la formattazione delle stringhe per popolare le variabili nella stringa utilizzando gli operatori %s e .

PostrgreSQL fa distinzione tra maiuscole e minuscole. Nel metodo get_data() abbiamo forzato le nostre intestazioni di colonna in minuscolo. L'indice non è stato incluso in questa istruzione. Per passare l'intestazione della colonna "Dati" maiuscola nella query, dobbiamo passarla a PostgreSQL tra virgolette. Per farlo in una stringa in Python, devi inviare il carattere di escape "\" prima delle virgolette doppie double.

Possiamo sostituire ""%s"" nella stringa usando la sintassi di formattazione della stringa python di seguito. Questo sostituisce %s con il nostro parametro data dt.

Per eseguire la query SQL che è stata creata. Devi quindi passarlo al cursore .eseguire() metodo. Chiamando il .fetchall(), restituisci i risultati della query. Una volta stampato sulla console è possibile visualizzare i risultati.

def get_row(dt, conn):
cr = conn.cursore()
query = sql.SQL(""SELECT aapl from close WHERE ""Date"" = '%s'"" % dt)
cr.eseguire (interrogare)
stampa cre.recupera()

Per eseguire questa funzione aggiungiamo la riga di codice sottostante al metodo tutorial_run(). Dovresti ottenere risultati simili a quelli di seguito.

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

Nel prossimo metodo, utilizzeremo i metodi del formato stringa per passare più parametri nella nostra query. Questa query richiederà una data e tre colonne. Oltre all'utilizzo dell'operatore %s, utilizzeremo l'operatore per unire variabili stringa in una stringa e inserirle nella nostra stringa di query. La nostra stringa di query ora utilizza il join di seguito con un separatore """", per passare più nomi di colonne nella nostra query.

def get_cols(dt, col1, col2, col3, conn):
cr = conn.cursore()
query = sql.SQL(""SELECT from close WHERE ""Date"" = '%s'"" % dt).formato(
sql.SQL(', ').join([sql.Identificatore (col1), sql.Identificatore (col2), sql.Identificatore (col3)]))
cre.eseguire (interrogare)
stampa cre.recupera()

Per utilizzare il nostro nuovo metodo aggiungerò la riga sottostante al nostro metodo tutorial_run()(). Dovresti vedere i risultati qui sotto.

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

Il prossimo metodo che scriveremo utilizzerà due sostituzioni di stringhe per estrarre tutti i dati nella nostra tabella ad eccezione del nostro indice. Questo metodo si basa sul nostro metodo precedente aggiungendo una seconda notazione di parentesi sostitutiva ""1"". Questa volta le parentesi sono numerate in modo da essere sostituite nel codice della nozione del formato dell'ordine. Il nostro nuovo metodo unisce i tre parametri di colonna con il separatore di virgole. Inoltre, il secondo parametro nel metodo format è la variabile di tabella. La stringa di query viene quindi costruita sostituendo le parentesi con i parametri nel metodo format in ordine. Cioè 0 = colonne e 1 = nome tabella.

def get_tab(tabella, col1, col2, col3, conn):
cr = conn.cursore()
query = sql.SQL(""SELEZIONARE 0 da 1"").formato(
sql.SQL(', ').join([sql.Identificatore (col1), sql.Identificatore (col2),
sql.Identificatore (col3)]), sql.Identificatore (tabella))
cr.eseguire (interrogare)
stampa cre.recupera()

Per utilizzare il nostro nuovo metodo aggiungerò la riga sottostante al nostro metodo tutorial_run()(). Dovresti vedere i risultati qui sotto.

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

Ci sono molti altri metodi da esplorare nella libreria psycopg. Questo dovrebbe farti iniziare con una buona comprensione delle funzioni di psycopg. Di seguito ho fornito alcune risorse aggiuntive nelle pagine di documentazione che ti permetteranno di esplorare più ampiamente la libreria.

Codice completo

importa psycopg2
da psycopg2 import sql
importa pandas_datareader come dati
def get_data(symbols, start_date, end_date):
pannello = dati.DataReader (simboli, 'yahoo', data_inizio, data_fine)
df = pannello['Chiudi']
df.colonne = map(str.inferiore, df.colonne)
hd = lista (df)
stampa df.testa()
stampa hd
ritorno df
def connect():
cons = ""dbname='tutorial' user=""postgres"" host=""localhost"" password=""password""""
provare:
conn = psycopg2.collegare (contro)
stampa ""Connesso""
tranne:
print ""Non riesco a connettermi al database""
ritorno conn
def create_table(tabella, df):
engine = create_engine('postgresql+psycopg2://postgres:[email protected]:5432/tutorial')
df.to_sql(tabella, motore, if_exists=""sostituisci"")
def get_row(dt, conn):
cr = conn.cursore()
query = sql.SQL(""SELECT aapl from close WHERE ""Date"" = '%s'"" % dt)
cre.eseguire (interrogare)
stampa cre.recupera()
def get_cols(dt, col1, col2, col3, conn):
cr = conn.cursore()
query = sql.SQL(""SELECT from close WHERE ""Date"" = '%s'"" % dt).formato(
sql.SQL(', ').join([sql.Identificatore (col1),
sql.Identificatore (col2), sql.Identificatore (col3)]))
cre.eseguire (interrogare)
stampa cre.recupera()
def get_tab(table,col1, col2, col3, conn):
cr = conn.cursore()
query = sql.SQL(""SELEZIONARE 0 da 1"").formato(
sql.SQL(', ').join([sql.Identificatore (col1), sql.Identificatore (col2),
sql.Identificatore (col3)]), sql.Identificatore (tabella))
cr.eseguire (interrogare)
stampa cre.recupera()
def tutorial_run():
conn = connetti()
simboli = ['SPY', 'AAPL','GOOG']
df = get_data(simboli, '2006-01-03', '2017-12-31')
create_table(""chiudi"", df)
get_row(""2017-12-29"",conn)
get_cols(""2017-12-29"",""aapl"",""spia"", ""goog"", conn)
get_tab(""close"", ""aapl"", ""spy"", ""goog"", conn)
if __name__ == ""__main__"":
tutorial_run()

Riferimenti

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