Tutorial Python, PostgreSQL e Psycopg2

Para desenvolver um aplicativo além de um script simples, é necessário persistir os dados fora da memória em um banco de dados.  Existem muitas opções possíveis para um banco de dados, mas o PostgreSQL é uma plataforma robusta de código aberto que pode ser facilmente escalonada para produção.

Python e PostgreSQL podem ter interface para desenvolver aplicativos poderosos rapidamente.  Psycopg é um adaptador PostgreSQL que pode ser usado para controlar o PostgreSQL por meio da biblioteca baseada em Python.  Este tutorial irá percorrer a instalação do Psycopg2 e alguns códigos Python para demonstrar seu uso.

Você pode instalar o Psycopg2 por meio do comando pip do terminal abaixo.

$ pip install psycopg2

Ao instalar, você deve ver a saída do terminal abaixo.

Coletando psycopg2
Baixando 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 MB)
100% | ████████████████████████████████ | 1.7 MB 397 kB / s
Instalando pacotes coletados: psycopg2
Psycopg2-2 instalado com sucesso.7.3.2
Bradleys-Mini: ~ BradleyPatton $

Para importar o pacote Psycopg2 para o seu aplicativo Python, você usa a linha de código abaixo.

import psycopg2

A fim de obter alguns dados para carregar em nosso banco de dados, peguei emprestado algum código de um tutorial anterior sobre pandas. O código abaixo irá criar um DataFrame do pandas com dados históricos.  Isso será aproveitado para criar uma tabela na tabela PostgreSQL.

def get_data (símbolos, start_date, end_date):
painel = dados.DataReader (símbolos, 'yahoo', start_date, end_date)
df = painel ['Fechar']
df.colunas = mapa (str.inferior, df.colunas)
hd = list (df)
imprimir df.cabeça()
imprimir hd
return df

Agora irei configurar alguns códigos de manutenção usados ​​para executar o tutorial. Esses dois métodos serão usados ​​para chamar os métodos Psycopg2 que criamos.

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

Para conectar ao banco de dados PostgreSQL, precisaremos adicionar o método abaixo. O Try \ Except fornece algum tratamento de erros caso o banco de dados local não esteja em execução ou parâmetros de conexão incorretos sejam passados ​​para o banco de dados. O método de conexão na biblioteca Psycopg2 se conecta ao banco de dados com os parâmetros passados ​​na string de conexão. Seus parâmetros para dbname, usuário e senha podem ser diferentes. Se a conexão falhar por algum motivo, a mensagem de erro será gravada no console. Este método retorna o objeto de conexão de volta ao nosso método de chamada, onde pode ser usado para outras operações de banco de dados.

def conectar ():
cons = "dbname = 'tutorial' user =" postgres "host =" localhost "senha =" senha ""
tentar:
conn = psycopg2.conectar (contras)
imprimir "Conectado"
exceto:
print "Não consigo me conectar ao banco de dados"
retorno conn

Depois de estabelecer a conexão com o banco de dados PostgreSQL, podemos carregar nossos dados do método get_data () em nosso banco de dados. Psycopg2 e pandas tornam esse processo muito simples.

A primeira linha define o método que o pandas deve usar para se conectar ao banco de dados para copiar o DataFrame. Você fornecerá os mesmos parâmetros do seu método de conexão. A segunda linha de código persiste o DataFrame para o banco de dados PostgreSQL com o método to_sql ().

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

Uma rápida olhada em nosso terminal PostgreSQL pgAdmin mostra que o código carregou com sucesso o DataFrame na tabela “fechar”. Agora que temos alguns dados carregados em nosso banco de dados. Podemos usar psycopg para executar algumas consultas nos dados. O método abaixo é construído para pegar a conexão estabelecida em nosso primeiro método e executar uma consulta em nosso banco de dados PostgreSQL. Para criar os 4 objetos SQL, precisamos adicionar outra instrução de importação.

de psycopg2 import sql

Para criar comandos SQL dinâmicos, psycopg usa formatação de string para preencher variáveis ​​na string usando os operadores% se .

PostrgreSQL diferencia maiúsculas de minúsculas. No método get_data (), forçamos nossos cabeçalhos de coluna para letras minúsculas. O índice não foi incluído nesta instrução. Para passar o cabeçalho da coluna "Dados" maiúscula na consulta, precisamos passá-lo para o PostgreSQL entre aspas duplas. Para fazer isso em uma string em Python, você precisa enviar o caractere de escape “\” antes das aspas duplas.

Podemos substituir o “% s” na string usando a sintaxe de formatação de string python abaixo. Isso substitui o% s pelo nosso parâmetro de data dt.

Para executar a consulta SQL que foi criada. Você então precisa passá-lo para o cursor .método execute (). Ligando para o .método fetchall (), você retorna os resultados da consulta. Quando impresso no console, você pode exibir os resultados.

def get_row (dt, conn):
cr = conn.cursor()
consulta = sql.SQL ("SELECT aapl do fechamento WHERE" Data "= '% s'"% dt)
cr.executar (consulta)
imprimir cr.fetchall ()

Para executar esta função, adicionamos a linha de código abaixo ao método tutorial_run (). Você deve obter resultados semelhantes aos abaixo.

get_row ("2017-12-29", conexão)

No próximo método, vamos utilizar os métodos de formato de string para passar vários parâmetros para nossa consulta. Esta consulta terá uma data e três colunas. Além de usar o operador% s, usaremos o operador para juntar variáveis ​​de string em uma string e injetá-las em nossa string de consulta. Nossa string de consulta agora usa a junção abaixo com um separador “,” para passar vários nomes de coluna para nossa consulta.

def get_cols (dt, col1, col2, col3, conn):
cr = conn.cursor()
consulta = sql.SQL ("SELECT de fechar WHERE" Data "= '% s'"% dt).formato(
sql.SQL (',').junte-se ([sql.Identificador (col1), sql.Identificador (col2), sql.Identificador (col3)]))
cr.executar (consulta)
imprimir cr.fetchall ()

Para usar nosso novo método, adicionarei a linha abaixo ao nosso método tutorial_run (). Você deve ver os resultados abaixo.

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

O próximo método que escreveremos usará duas substituições de string para extrair todos os dados em nossa tabela, com exceção de nosso índice. Este método se baseia em nosso método anterior, adicionando uma segunda notação de substituição de colchetes “1”. Desta vez, os colchetes são numerados de modo que sejam substituídos no código de noção de formato de pedido. Nosso novo método une os três parâmetros de coluna com separador de vírgula. Além disso, o segundo parâmetro no método de formato é a variável da tabela. A string de consulta é então construída substituindo os colchetes pelos parâmetros no método de formatação em ordem. Isso é 0 = colunas e 1 = nome da tabela.

def get_tab (tabela, col1, col2, col3, conn):
cr = conn.cursor()
consulta = sql.SQL ("SELECIONE 0 de 1").formato(
sql.SQL (',').junte-se ([sql.Identificador (col1), sql.Identificador (col2),
sql.Identificador (col3)]), sql.Identificador (tabela))
cr.executar (consulta)
imprimir cr.fetchall ()

Para usar nosso novo método, adicionarei a linha abaixo ao nosso método tutorial_run (). Você deve ver os resultados abaixo.

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

Existem muitos outros métodos para explorar na biblioteca psycopg. Isso deve ajudá-lo a começar a compreender as funções do psycopg. Eu forneci mais alguns recursos abaixo nas páginas de documentação que permitirão que você explore mais amplamente a biblioteca.

Código Completo

import psycopg2
de psycopg2 import sql
importar pandas_datareader como dados
def get_data (símbolos, start_date, end_date):
painel = dados.DataReader (símbolos, 'yahoo', start_date, end_date)
df = painel ['Fechar']
df.colunas = mapa (str.inferior, df.colunas)
hd = list (df)
imprimir df.cabeça()
imprimir hd
return df
def conectar ():
cons = "dbname = 'tutorial' user =" postgres "host =" localhost "senha =" senha ""
tentar:
conn = psycopg2.conectar (contras)
imprimir "Conectado"
exceto:
print "Não consigo me conectar ao banco de dados"
retorno conn
def create_table (tabela, df):
engine = create_engine ('postgresql + psycopg2: // postgres: [email protected]: 5432 / tutorial')
df.to_sql (tabela, mecanismo, if_exists = "substituir")
def get_row (dt, conn):
cr = conn.cursor()
consulta = sql.SQL ("SELECT aapl do fechamento WHERE" Data "= '% s'"% dt)
cr.executar (consulta)
imprimir cr.fetchall ()
def get_cols (dt, col1, col2, col3, conn):
cr = conn.cursor()
consulta = sql.SQL ("SELECT de fechar WHERE" Data "= '% s'"% dt).formato(
sql.SQL (',').junte-se ([sql.Identificador (col1),
sql.Identificador (col2), sql.Identificador (col3)]))
cr.executar (consulta)
imprimir cr.fetchall ()
def get_tab (tabela, col1, col2, col3, conn):
cr = conn.cursor()
consulta = sql.SQL ("SELECIONE 0 de 1").formato(
sql.SQL (',').junte-se ([sql.Identificador (col1), sql.Identificador (col2),
sql.Identificador (col3)]), sql.Identificador (tabela))
cr.executar (consulta)
imprimir cr.fetchall ()
def tutorial_run ():
conn = conectar ()
símbolos = ['SPY', 'AAPL', 'GOOG']
df = get_data (símbolos, '2006-01-03', '2017-12-31')
create_table ("fechar", df)
get_row ("2017-12-29", conexão)
get_cols ("2017-12-29", "aapl", "spy", "goog", conn)
get_tab ("close", "aapl", "spy", "goog", conn)
if __name__ == "__main__":
tutorial_run ()

Referências

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