Este artigo foi publicado como parte do Blogathon de Ciência de Dados
Introdução
Vejamos um exemplo prático de como fazer consultas SQL a um servidor MySQL a partir do código Python: CREATE, SELECT, UPDATE, JOIN, etc.
A maioria dos aplicativos interage com os dados de alguma forma. Portanto, as linguagens de programação (Python não é exceção) fornecem ferramentas para armazenar fontes de dados e acessá-las. MySQL é um dos mais fantásticos e ricos sistemas de gerenciamento de banco de dados (DBMS). No ano passado, ficou em segundo lugar, depois da Oracle, no ranking de banco de dados.
Usando as técnicas descritas neste tutorial, você pode integrar efetivamente um banco de dados MySQL em seu aplicativo Python. Neste tutorial, desenvolveremos um pequeno banco de dados MySQL para um sistema de classificação de filmes e aprenderemos como obter dados dele usando código Python.
O que você saberá após este tutorial é:
-
Conecte seu aplicativo ao banco de dados MySQL
-
Recuperação de dados por meio de uma consulta aos dados necessários do banco de dados
-
Lidar com exceções lançadas ao acessar o banco de dados
Para obter o máximo deste tutorial, é aconselhável ter um conhecimento prático dos conceitos do Python, como looping, funções e tratamento de exceções. Você também deve ter um conhecimento básico de consultas SQL, como e. para SELECT DROP CREATE JOIN
Comparando o MySQL com outro banco de dados SQL
SQL significa Structured Query Language é uma linguagem de programação amplamente usada para gerenciar bancos de dados relacionais. Você deve ter ouvido falar de vários DBMS baseados em SQL: MySQL, PostgreSQL, SQLite e SQL Server. Todos esses bancos de dados estão em conformidade com os padrões SQL, mas diferem em detalhes.
Por causa de seu código-fonte aberto, o MySQL rapidamente se tornou o líder de mercado em soluções SQL. O MySQL é usado atualmente pela maioria das empresas de tecnologia famosas como Google, LinkedIn, Uber, Netflix, Twitter e muito mais.
Além do suporte da comunidade de código aberto, existem outras razões para o sucesso do MySQL:
-
Fácil de instalar - o MySQL foi projetado para ser amigável. O banco de dados é fácil de criar e personalizar. O MySQL está disponível para os principais sistemas operacionais, incluindo Windows, macOS, Linux e Solaris.
-
O Speed- MySQL tem a reputação de ser uma solução rápida de banco de dados. Este SGBD também tem uma boa escalabilidade.
-
Direitos e segurança do usuário - o MySQL permite definir níveis de segurança de senha, adicionar e remover privilégios para contas de usuário. O gerenciamento dos direitos do usuário parece muito mais simples do que em muitos outros SGBD, como PostgreSQL, onde o gerenciamento de arquivos de configuração requer alguma habilidade.
Instalando o MySQL Server e o MySQL Connector
MySQL Server e MySQL Connector são os dois únicos softwares necessários para iniciar este tutorial. O servidor MySQL fornecerá os recursos necessários para trabalhar com o banco de dados. Depois de iniciar o servidor, você deve ser capaz de conectar seu aplicativo Python a ele usando o MySQL Connector / Python.
Instalando o servidor MySQL
A documentação oficial descreve as maneiras recomendadas para baixar e instalar o MySQL Server. Existem instruções para todos os sistemas operacionais populares, incluindo Windows, macOS, Solaris, Linux e muitos mais.
Para Windows, sua melhor aposta é baixar o instalador do MySQL e deixá-lo cuidar do processo. O Installation Manager também o ajudará a definir as configurações de segurança do servidor MySQL. Na página de contas, você precisará inserir uma senha para a conta root e, se desejar, adicionar outros usuários com privilégios diferentes.
Configurando uma conta MySQL
Outras ferramentas úteis, como o MySQL Workbench, podem ser personalizadas usando os instaladores. Uma alternativa conveniente para instalar em um sistema operacional é implantar o MySQL usando o Docker.
Instalação do MySQL Connector / Python
Driver de banco de dados - software que permite que um aplicativo se conecte e interaja com um DBMS. Esses drivers geralmente são fornecidos como módulos separados. A interface padrão com a qual todos os drivers de banco de dados Python devem estar em conformidade é descrita no PEP 249. Para instalar o driver (conector), usaremos o gerenciador de pacotes: pip
pip instalar mysql-connector-python
pip instalará o conector no ambiente atualmente ativo. Para trabalhar com um projeto isolado, recomendamos configurar um ambiente virtual.
Vamos verificar o resultado da instalação executando o seguinte comando no terminal Python:
importar mysql.connector
Se a instrução de importação for executada sem erros, ela foi instalada com êxito e está pronta para uso. MySQL.connector
Estabelecendo uma conexão com o servidor MySQL
MySQL é um sistema de gerenciamento de banco de dados do lado do servidor. Um servidor pode conter vários bancos de dados. Para interagir com o banco de dados, devemos estabelecer uma conexão com o servidor. A interação passo a passo para um programa Python com um banco de dados baseado em MySQL se parece com isto:
-
Nós nos conectamos ao servidor MySQL.
-
Criamos um novo banco de dados (se necessário).
-
Nós nos conectamos ao banco de dados.
-
Executamos a consulta SQL, coletamos os resultados.
-
Informamos ao banco de dados se foram feitas alterações na tabela.
-
Por último, basta fechar a conexão com o servidor MySQL.
Qualquer que seja o aplicativo, a primeira etapa é vincular o aplicativo e o banco de dados.
Conectando-se ao servidor MySQL a partir de Python
Para estabelecer uma conexão, use o módulo. Esta função recebe parâmetros, e, e retorna um objeto. As credenciais podem ser obtidas como resultado da entrada do usuário: connect () mysql.connector host user password MySQLConnection
from getpass import getpass from mysql.connector import connect, Erro try: with connect (host = "localhost", user = input ("Username:"), password = getpass ("Password:"),) as connection: print (connection ) exceto Erro como e: imprimir (e)
O objeto é armazenado em uma variável que usaremos para acessar o servidor MySQL. Alguns pontos importantes: Conexão MySQLConnection
-
Envolva todas as conexões de banco de dados em blocos. Isso tornará mais fácil capturar e examinar quaisquer exceções. tente ... exceto
-
Lembre-se de fechar a conexão após terminar de acessar o banco de dados. Conexões abertas não utilizadas levam a erros inesperados e problemas de desempenho. O código usa o gerenciador de contexto (com ... como ...) para isso.
-
Você nunca deve inserir credenciais (nome de usuário e senha) na forma de string em um script Python. Essa é uma prática de implantação inadequada e representa um sério risco à segurança. O código acima pede suas credenciais de login. Para isso, um módulo embutido é usado para ocultar a senha digitada.
Portanto, estabelecemos uma conexão entre nosso programa e o servidor MySQL. Agora você precisa criar um novo banco de dados ou conectar-se a um existente.
Crie um novo banco de dados
Para criar um novo banco de dados, por exemplo com um nome, você precisa executar a instrução SQL: online_movie_rating
CRIAR BANCO DE DADOS online_movie_rating;
Note
O MySQL requer que você coloque um ponto-e-vírgula (😉 no final de uma instrução. No entanto, o MySQL Connector / Python adiciona um ponto-e-vírgula automaticamente no final de cada consulta.
Para executar uma consulta SQL, precisamos de um cursor que abstraia o processo de acesso aos registros do banco de dados. MySQL Connector / Python fornece uma classe correspondente, uma instância da qual também é chamada de cursor. MySQLCursor
Vamos passar nossa solicitação para criar um banco de dados: online_movie_rating
try: with connect (host = "localhost", user = input ("Username:"), password = getpass ("Password:"),) as connection: create_db_query = "CREATE DATABASE online_movie_rating" com connection.cursor () como cursor : cursor.execute (create_db_query) exceto Erro como e: print (e)
A solicitação é armazenada como uma string em uma variável e então passada para execução para CREATE DATABASE create_db_query cursor.execute ()
Se já existir uma base de dados com o mesmo nome no servidor, receberemos uma mensagem de erro. Usando o mesmo objeto de antes, vamos executar uma consulta para ver todas as tabelas armazenadas no banco de dados: MySQLConnection SHOW DATABASES
try: with connect (host = "localhost", user = input ("Username:"), password = getpass ("Password:"),) as connection: show_db_query = "SHOW DATABASES" com connection.cursor () como cursor: cursor.execute (show_db_query) para db no cursor: print (db) exceto Erro como e: print (e)
SAÍDA
Digite o nome de usuário: root
Digite a senha: ········
('information_schema',)
('mysql',)
('online_movie_rating',)
('performance_schema',)
('sys',)
O código acima imprimirá os nomes de todos os bancos de dados localizados em nosso servidor MySQL. O comando em nosso exemplo também despejou bancos de dados que são criados automaticamente pelo servidor MySQL e fornecem acesso aos metadados do banco de dados e às configurações do servidor. MOSTRAR BASES DE DADOS
Conectando-se a um banco de dados existente
Então, criamos um banco de dados chamado. Para conectar a ele, simplesmente suplementamos a chamada com um parâmetro: online_movie_rating connect () database
try: with connect (host = "localhost", user = input ("Username:"), password = getpass ("Password:"), database = "online_movie_rating",) as connection: print (connection) exceto Erro como e: imprimir (e)
Criação, modificação e eliminação de tabelas
Nesta seção, discutimos como usar Python para realizar algumas consultas básicas: ',' e '.' CREATE TABLE DROP ALTER
Definindo o esquema do banco de dados
Vamos começar criando um esquema de banco de dados para o sistema de classificação de filmes. Considere o banco de dados composto por três tabelas:
1. filmes - informações gerais sobre filmes:
-
Id
-
+
-
título
-
ano de lançamento
-
genre
-
coleção_em_mi
2. revisores- informações sobre as pessoas que publicaram as avaliações dos filmes:
-
id
-
primeiro nome
-
último nome
3. avaliações - informações sobre as avaliações dos filmes pelos críticos:
-
movie_id (chave estrangeira)
-
reviewer_id (chave estrangeira)
-
classificação
Essas três tabelas são suficientes para os fins deste guia.
Diagrama do sistema de classificação do filme
As tabelas no banco de dados estão relacionadas entre si: filmes e revisores devem ter uma relação muitos para muitos: um filme pode ser visto por vários revisores e um revisor pode revisar vários filmes. As classificações da tabela conectam a tabela de filmes à tabela do revisor.
Criação de tabelas usando a instrução CREATE TABLE
Para criar uma nova tabela no MySQL, precisamos usar o operador. A seguinte consulta MySQL criará nossa tabela de banco de dados: CREATE TABLE movies online_movie_rating
CRIAR filmes TABLE (id INT AUTO_INCREMENT PRIMARY KEY, título VARCHAR (100), release_year YEAR (4), gênero VARCHAR (100), collection_in_mil INT);
Se você já encontrou SQL antes, entenderá o significado da consulta acima. O dialeto do MySQL tem alguns recursos distintos. Por exemplo, o MySQL oferece uma ampla variedade de tipos de dados, incluindo e assim por diante. Além disso, o MySQL usa a palavra-chave quando o valor da coluna deve ser incrementado automaticamente quando novos registros são inseridos. ANO INT BIGINT AUTO_INCREMENT
Para criar uma tabela, você precisa passar a consulta especificada para o cursor.execute ()
create_movies_table_query = "" "CRIAR filmes TABLE (id INT AUTO_INCREMENT PRIMARY KEY, título VARCHAR (100), release_year YEAR (4), gênero VARCHAR (100), collection_in_mil INT)" "" com connection.cursor () como cursor: cursor. execute (create_movies_table_query) connection.commit ()
Preste atenção ao operador. Por padrão, o conector MySQL não confirma transações automaticamente. No MySQL, as modificações mencionadas em uma transação só acontecem quando usamos o comando no final. Para fazer alterações em uma tabela, sempre chame esse método após cada transação. connection.commit () COMMIT
Vamos repetir o procedimento para a tabela: revisores
create_reviewers_table_query = “” ”
CREATE TABLE revisores (id INT AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR (100), last_name VARCHAR (100)) "" "com connection.cursor () como cursor: cursor.execute (create_reviewers_table_query) connection.commit () Finalmente, vamos criar um classificações da tabela: create_ratings_table_query = "" "CREATE TABLE classificações (movie_id INT, reviewer_id INT, classificação DECIMAL (2,1), FOREIGN KEY (movie_id) REFERENCES movies (id), FOREIGN KEY (reviewer_id) REFERENCES revisores (id), PRIMARY KEY (movie_id, reviewer_id)) "" "com connection.cursor () como cursor: cursor.execute (create_ratings_table_query) connection.commit ()
A implementação de relacionamentos de chave estrangeira no MySQL é um pouco diferente e tem limitações em comparação com o SQL padrão. No MySQL, o pai e o filho de uma chave estrangeira devem usar o mesmo mecanismo de armazenamento - o componente de software subjacente que o sistema de gerenciamento de banco de dados usa para executar operações SQL. O MySQL oferece dois tipos de mecanismos:
-
Os mecanismos de armazenamento transacional são seguros para transações e permitem reverter transações usando comandos simples como. Muitos motores MySQL populares se enquadram nesta categoria, incluindo InnoDB e NDB. reversão
-
Os mecanismos de armazenamento não transacional dependem de código manual para desfazer instruções confirmadas no banco de dados. São, por exemplo, MyISAM e MEMORY.
InnoDB é o mecanismo de armazenamento padrão mais popular. Ao impor restrições de chave estrangeira, ajuda a manter a integridade dos dados. Isso significa que qualquer operação CRUD de chave estrangeira é pré-verificada para garantir que não leve à inconsistência entre as diferentes tabelas.
Observe que a tabela usa colunas e duas chaves estrangeiras, atuando juntas como uma chave primária. Esse recurso garante que um revisor não possa avaliar o mesmo filme duas vezes. avaliações movie_id reviewer_id
O mesmo cursor pode ser usado para vários acessos. Nesse caso, todas as chamadas se tornarão uma transação atômica. Por exemplo, você pode executar todas as instruções com um cursor e, em seguida, confirmar a transação de uma vez:
CRIAR TABELA com connection.cursor () como cursor: cursor.execute (create_movies_table_query) cursor.execute (create_reviewers_table_query) cursor.execute (create_ratings_table_query) connection.commit ()
Exibindo o Esquema da Tabela Usando a Instrução DESCRIBE
Criamos três tabelas e podemos visualizar o esquema usando o operador. DESCREVER
Supondo que você já tenha um objeto em uma variável, podemos imprimir os resultados obtidos. Este método recupera todas as linhas da última instrução executada: MySQLConnection connection cursor.fetchall ()
show_table_query = "DESCRIBE movies" com connection.cursor () como cursor: cursor.execute (show_table_query) # Busca linhas da última consulta executada result = cursor.fetchall () para linha no resultado: print (row)
SAÍDA
('id', 'int (11)', 'NÃO', 'PRI', Nenhum, 'auto_incremento')
('título', 'varchar (100)', 'SIM', ”, Nenhum,”)
('release_year', 'year (4)', 'YES', ”, None,”)
('gênero', 'varchar (100)', 'SIM', ”, Nenhum,”)
('coleção_em_mil', 'int (11)', 'SIM', ”, Nenhum,”)
Depois de executar o código acima, devemos obter uma tabela contendo informações sobre as colunas da tabela. Para cada coluna, são exibidas informações sobre o tipo de dados, se a coluna é uma chave primária e assim por diante. filmes
Alterar o esquema de uma tabela usando a instrução ALTER
A coluna do nome na tabela contém a bilheteria do filme em milhões de dólares. Podemos escrever a seguinte instrução MySQL para alterar o tipo de dados de um atributo de para coleção_em_mil filmes coleção_em_mil INT DECIMAL
ALTER TABLE filmes MODIFY COLUMN collection_in_mil DECIMAL (4,1);
DECIMAL (4,1) indica um número decimal, que pode ter no máximo quatro algarismos, dos quais um corresponde à décima descarga, por exemplo, e assim sucessivamente. d. 120.1 3.4 38.0
alter_table_query = "" "ALTER TABLE filmes MODIFY COLUMN collection_in_mil DECIMAL (4,1)" "" show_table_query = "DESCRIBE movies" com connection.cursor () como cursor: cursor.execute (alter_table_query) cursor.execute (show_table_query) # Obter linhas do último resultado da consulta executada = cursor.fetchall () print ("Esquema da tabela de filmes após modificação:") para linha no resultado: print (linha) O esquema da tabela de filmes após fazer alterações: ('id', 'int (11) ',' NO ',' PRI ', None,' auto_increment ') (' title ',' varchar (100) ',' YES ',' ', None,' ') (' release_year ',' year (4) ',' SIM ',' ', Nenhum,' ') (' gênero ',' varchar (100) ',' SIM ',' ', Nenhum,' ') (' coleção_em_mil ',' decimal (4,1) ',' SIM ',' ', Nenhum,' ')
Conforme mostrado na saída, o atributo mudou seu tipo também. Observe que, no código acima, estamos chamando duas vezes, mas apenas buscamos linhas da consulta executada mais recentemente, que é. coleção_em_mil DECIMAL (4,1) cursor.execute () cursor.fetchall () show_table_query
Eliminando tabelas usando a instrução DROP
Para excluir tabelas, use o operador. Deixar cair uma mesa é um processo irreversível. Se você executar o código abaixo, precisará invocar a consulta na tabela novamente: DROP TABLE CREATE TABLE avaliações
drop_table_query = "DROP TABLE avaliações" com connection.cursor () como cursor: cursor.execute (drop_table_query)
Inserindo registros em tabelas
Vamos preencher as tabelas com dados. Nesta seção, veremos duas maneiras de inserir registros usando o conector MySQL no código Python.
O primeiro método funciona bem quando o número de registros é pequeno. O segundo é mais adequado para cenários da vida real. .execute () .executemany ()
Inserindo registros com .execute ()
A primeira abordagem usa o mesmo método que temos usado até agora. Escrevemos uma solicitação e a enviamos para cursor.execute () INSERT INTO cursor.execute ()
insert_movies_query = "" "INSERT INTO movies (title, release_year, genre, collection_in_mil) VALUES (" Forrest Gump ", 1994," Drama ", 330.2), (" 3 Idiots ", 2009," Drama ", 2.4), (" Eternal Sunshine of the Spotless Mind ", 2004," Drama ", 34.5), (" Good Will Hunting ", 1997," Drama ", 138.1), (" Skyfall ", 2012," Action ", 304.6), (" Gladiator ", 2000," Action ", 188.7), (" Black ", 2005," Drama ", 3.0), (" Titanic ", 1997," Romance ", 659.2), (" The Shawshank Redemption ", 1994," Drama ", 28.4), (" Udaan ", 2010," Drama ", 1.5), (" Home Alone ", 1990," Comedy ", 286.9), (" Casablanca ", 1942," Romance ", 1.0), (" Avengers: Endgame ", 2019," Action ", 858.8), (" Night of the Living Dead ", 1968," Horror ", 2.5), (" The Godfather ", 1972," Crime ", 135.6), (" Haider ", 2014," Action ", 4.2), (" Inception ", 2010," Adventure ", 293.7), (" Evil ", 2003," Horror ", 1.3), (" Toy Story 4 ", 2019," Animação ", 434.9), (" Air Force One ", 1997," Drama ", 138.1), (" The Dark Knight ", 2008," Action ", 535.4), (" Bhaag Milkha Bhaag ", 2013," Sport ", 4.1), ("T he Lion King ", 1994," Animation ", 423.6), (" Pulp Fiction ", 1994," Crime ", 108.8), (" Kai Po Che ", 2013," Sport ", 6.0), (" Beasts of No Nation ", 2015," War ", 1.4), (" Andadhun ", 2018," Thriller ", 2.9), (" The Silence of the Lambs ", 1991," Crime ", 68.2), (" Deadpool ", 2016 , "Action", 363.6), ("Drishyam", 2015, "Mystery", 3.0) "" "com connection.cursor () como cursor: cursor.execute (insert_movies_query) connection.commit ()
A tabela agora está preenchida com trinta entradas. No final, o código chama. Lembre-se de ligar depois de fazer qualquer alteração na mesa. filmes connection.commit () .commit ()
Inserindo registros com .executemany ()
A abordagem anterior é adequada para o registro menor que pode ser inserido facilmente por meio de código. Mas, normalmente, os dados são armazenados em um arquivo ou gerados por outro script. É aqui que é útil. O método usa dois parâmetros: .executemany ()
-
Uma consulta contendo marcadores de posição para os registros a serem inseridos.
-
Lista de registros a serem inseridos.
Vamos fazer uma abordagem para preencher a tabela: revisores
insert_reviewers_query = "" "INSERT INTO revisores (first_name, last_name) VALORES (% s,% s)" "" reviews_records = [("Chaitanya", "Baweja"), ("Mary", "Cooper"), ("John "," Wayne "), (" Thomas "," Stoneman "), (" Penny "," Hofstadter "), (" Mitchell "," Marsh "), (" Wyatt "," Skaggs "), (" Andre "," Veiga "), (" Sheldon "," Cooper "), (" Kimbra "," Masters "), (" Kat "," Dennings "), (" Bruce "," Wayne "), (" Domingo "," Cortes "), (" Rajesh "," Koothrappali "), (" Ben "," Glocker "), (" Mahinder "," Dhoni "), (" Akbar "," Khan "), (" Howard "," Wolowitz "), (" Pinkie "," Petit "), (" Gurkaran "," Singh "), (" Amy "," Farah Fowler "), (" Marlon "," Crafford "),] com connection.cursor () como cursor: cursor.executemany (insert_reviewers_query, reviews_records) connection.commit ()
Este código tem marcadores de posição para duas strings que são inseridas em. Os marcadores de posição atuam como especificadores de formato e ajudam a reservar espaço para uma variável dentro de uma string. % s insert_reviewers_query
Vamos preencher a tabela da mesma forma: avaliações
insert_ratings_query = "" "INSERT INTO avaliações (classificação, movie_id, reviewer_id) VALORES (% s,% s,% s)" "" ratings_records = [(6.4, 17, 5), (5.6, 19, 1), (6.3 , 22, 14), (5.1, 21, 17), (5.0, 5, 5), (6.5, 21, 5), (8.5, 30, 13), (9.7, 6, 4), (8.5, 24 , 12), (9.9, 14, 9), (8.7, 26, 14), (9.9, 6, 10), (5.1, 30, 6), (5.4, 18, 16), (6.2, 6, 20 ), (7.3, 21, 19), (8.1, 17, 18), (5.0, 7, 2), (9.8, 23, 3), (8.0, 22, 9), (8.5, 11, 13), (5.0, 5, 11), (5.7, 8, 2), (7.6, 25, 19), (5.2, 18, 15), (9.7, 13, 3), (5.8, 18, 8), (5.8 , 30, 15), (8.4, 21, 18), (6.2, 23, 16), (7.0, 10, 18), (9.5, 30, 20), (8.9, 3, 19), (6.4, 12 , 2), (7.8, 12, 22), (9.9, 15, 13), (7.5, 20, 17), (9.0, 25, 6), (8.5, 23, 2), (5.3, 30, 17 ), (6.4, 5, 10), (8.1, 5, 21), (5.7, 22, 1), (6.3, 28, 4), (9.8, 13, 1)] com connection.cursor () como cursor : cursor.executemany (insert_ratings_query, ratings_records) connection.commit ()
Todas as três tabelas agora estão preenchidas com dados. A próxima etapa é descobrir como interagir com esse banco de dados.
Lendo registros do banco de dados
Até agora, criamos apenas itens de banco de dados. É hora de executar algumas consultas e encontrar as propriedades nas quais estamos interessados. Nesta seção, aprenderemos como ler registros de tabelas de banco de dados usando o operador. SELECIONE
Lendo registros com uma instrução SELECT
Para obter os registros, você precisa enviar para a solicitação e retornar o resultado usando: cursor.execute () SELECT cursor.fetchall ()
select_movies_query = "SELECT * FROM movies LIMIT 5" com connection.cursor () como cursor: cursor.execute (select_movies_query) result = cursor.fetchall () para linha no resultado: imprimir (linha)
SAÍDA
(1, 'Forrest Gump', 1994, 'Drama', Decimal ('330.2'))
(2, '3 Idiots', 2009, 'Drama', Decimal ('2.4'))
(3, 'Eternal Sunshine of the Spotless Mind', 2004, 'Drama', Decimal ('34.5 .XNUMX '))
(4, 'Gênio Indomável', 1997, 'Drama', Decimal ('138.1'))
(5, 'Skyfall', 2012, 'Ação', Decimal ('304.6'))
A variável contém os registros retornados. É uma lista de tuplas que representam registros individuais em uma tabela. resultado .fetchall ()
Na consulta acima, usamos uma palavra-chave para limitar o número de linhas recebidas do operador. Os desenvolvedores costumam ser usados para paginar a saída ao processar grandes quantidades de dados. LIMITE SELECCIONE LIMITE
No MySQL, dois argumentos numéricos não negativos podem ser passados para um operador: LIMIT
SELECIONE * DOS filmes LIMIT 2,5;
Ao usar dois argumentos numéricos, o primeiro especifica um deslocamento, que neste exemplo é 2, e o segundo limita o número de linhas retornadas a 5. Ou seja, a consulta do exemplo retornará as linhas 3 a 7.
select_movies_query = "SELECT title, release_year FROM movies LIMIT 2, 5" com connection.cursor () como cursor: cursor.execute (select_movies_query) para linha em cursor.fetchall (): imprimir (linha)
SAÍDA
('Eternal Sunshine of the Spotless Mind', 2004)
('Good Will Hunting', 1997)
('Skyfall', 2012)
('Gladiador', 2000)
('Preto', 2005)
Filtrando resultados com WHERE
As entradas da tabela também podem ser filtradas usando. Para obter todos os filmes com bilheteria acima de $ 300 milhões, execute a seguinte consulta: ONDE
select_movies_query = "" "SELECIONE título, coleção_em_mil DE filmes WHERE coleção_em_mil> 300 ORDER BY coleção_em_mil DESC" "" com connection.cursor () como cursor: cursor.execute (select_movies_query) para filme em cursor.f cursor.fetchall (): imprimir (filme) ('Avengers: Endgame', Decimal ('858.8')) ('Titanic', Decimal ('659.2')) ('The Dark Knight', Decimal ('535.4')) ('Toy Story 4', Decimal (' 434.9 ')) (' O Rei Leão ', Decimal (' 423.6 ')) (' Deadpool ', Decimal (' 363.6 ')) (' Forrest Gump ', Decimal (' 330.2 ')) (' Skyfall ', Decimal ( '304.6'))
A frase na consulta permite que você classifique as taxas da mais alta para a mais baixa. ORDENAR POR
O MySQL oferece muitas operações de formatação de strings, como concatenação de strings. Por exemplo, os títulos dos filmes geralmente são exibidos junto com o ano de lançamento para evitar confusão. Vamos pegar os nomes dos cinco filmes mais lucrativos e suas datas de lançamento: CONCAT
select_movies_query = "" "SELECT CONCAT (title," (", release_year,") "), collection_in_mil FROM movies ORDER BY collection_in_mil DESC LIMIT 5" "" com connection.cursor () como cursor: cursor.execute (select_movies_query) para filme em cursor.fetchall (): imprimir (filme)
SAÍDA
('Avengers: Endgame (2019)', Decimal ('858.8'))
('Titanic (1997)', Decimal ('659.2'))
('The Dark Knight (2008)', Decimal ('535.4'))
('Toy Story 4 (2019)', Decimal ('434.9'))
('O Rei Leão (1994)', Decimal ('423.6'))
Se você não deseja usar e não precisa obter todos os registros, pode usar os métodos do cursor e: LIMIT .fetchone () .fetchmany ()
-
.fetchone () Recupera a próxima linha do resultado como uma tupla, ou se não houver mais linhas disponíveis. Nenhum
-
.fetchmany () Recupera uma lista do próximo conjunto de linhas como uma tupla. Para fazer isso, um argumento é passado a ele, que por padrão é 1. Se não houver mais linhas disponíveis, o método retorna uma lista vazia.
Novamente, extraia os títulos dos cinco filmes de maior bilheteria por ano, mas desta vez usando: .fetchmany ()
select_movies_query = "" "SELECT CONCAT (title," (", release_year,") "), collection_in_mil FROM movies ORDER BY collection_in_mil DESC" "" com connection.cursor () como cursor: cursor.execute (select_movies_query) para filme no cursor .fetchmany (size = 5): print (filme) cursor.fetchall ()
SAÍDA
('Avengers: Endgame (2019)', Decimal ('858.8'))
('Titanic (1997)', Decimal ('659.2'))
('The Dark Knight (2008)', Decimal ('535.4'))
('Toy Story 4 (2019)', Decimal ('434.9'))
('O Rei Leão (1994)', Decimal ('423.6'))
Você deve ter notado um desafio adicional. Fazemos isso para limpar todos os resultados não lidos restantes. cursor.fetchall () .fetchmany ()
Antes de executar qualquer outra instrução na mesma conexão, você deve limpar todos os resultados não lidos. Caso contrário, uma exceção é lançada. Erro interno
JUNTE-SE A Múltiplas Mesas
Para descobrir os nomes dos cinco filmes de maior classificação, execute a seguinte consulta:
select_movies_query = "" "SELECT título, AVG (classificação) como média_rating FROM classificações INNER JOIN movies ON movies.id = ratings.movie_id GROUP BY movie_id ORDER BY average_rating DESC LIMIT 5" "" com connection.cursor () como cursor: cursor. executar (select_movies_query) para o filme em cursor.fetchall (): imprimir (filme)
SAÍDA
('Noite dos Mortos-Vivos', Decimal ('9.90000'))
('O Poderoso Chefão', Decimal ('9.90000'))
('Avengers: Endgame', Decimal ('9.75000'))
('Eternal Sunshine of the Spotless Mind', decimal ('8.90000'))
('Bestas sem nação', decimal ('8.70000'))
Você pode encontrar o nome do revisor com mais avaliações como esta:
select_movies_query = "" "SELECT CONCAT (first_name," ", last_name), COUNT (*) as num FROM revisores INNER JOIN avaliações ON reviews.id = ratings.reviewer_id GROUP BY reviewer_id ORDER BY num DESC LIMIT 1" "" com conexão. cursor () como cursor: cursor.execute (select_movies_query) para filme em cursor.fetchall (): print (filme) ('Mary Cooper', 4)
Como você pode ver, a maioria das análises foi escrita por Mary Cooper.
O processo de execução de uma consulta sempre permanece o mesmo: passamos a consulta para obter os resultados usando. cursor.execute () .fetchall ()
Atualização e exclusão de registros do banco de dados
Nesta seção, iremos atualizar e remover algumas das entradas. Selecionaremos as linhas necessárias usando uma palavra-chave. ONDE
Comando UPDATE
Imagine que uma crítica Amy Farah Fowler é casada com Sheldon Cooper. Ela mudou seu sobrenome para Cooper e precisamos atualizar o banco de dados. Para atualizar os registros no MySQL, use o operador: UPDATE
update_query = "" "UPDATE revisores SET last_name =" Cooper "WHERE first_name =" Amy "" "" com connection.cursor () como cursor: cursor.execute (update_query) connection.commit ()
O código passa a solicitação de atualização e faz as alterações necessárias na tabela. cursor.execute () .commit () revisores
Digamos que queremos permitir que os revisores alterem as notas. O programa precisa saber, e o novo. Exemplo de SQL: movie_id reviewer_id rating
UPDATE classificações SET rating = 5.0 WHERE movie_id = 18 AND reviewer_id = 15; SELECT * FROM classificações WHERE movie_id = 18 AND reviewer_id = 15; As consultas especificadas primeiro atualizam a classificação e, em seguida, geram a atualizada. Vamos escrever um script Python que nos permitirá ajustar as notas: modify_ratings.py de getpass import getpass de mysql.connector import connect, Erro movie_id = input ("Insira a id do filme:") reviewer_id = input ("Insira a id do revisor:" ) new_rating = input ("Insira a nova classificação:") update_query = "" "UPDATE classificações SET classificação ="% s "WHERE movie_id ="% s "AND reviewer_id ="% s "; SELECIONE * FROM classificações WHERE movie_id ="% s "AND reviewer_id ="% s "" ""% (new_rating, movie_id, reviewer_id, movie_id, reviewer_id,) try: with connect (host = "localhost", user = input ("Digite o nome de usuário:"), password = getpass ("Digite a senha:"), banco de dados = "online_movie_rating",) como conexão: com connection.cursor () como cursor: para resultado em cursor.execute (update_query, multi = True): if result.with_rows: print (result. fetchall ()) connection.commit () exceto Erro como e: print (e)
SAÍDA
Insira a id do filme: 18
Insira a id do revisor: 15
Insira a nova classificação: 5
Digite o nome de usuário: root
Digite a senha: ········
[(18, 15, Decimal ('5.0'))]
Para passar várias solicitações para o mesmo cursor, atribuímos um valor ao argumento. Nesse caso, ele retorna um iterador. Cada item no iterador corresponde a um objeto cursor que executa a instrução passada na solicitação. O código acima inicia um loop neste iterador, chamando cada objeto cursor. multi True cursor.execute () para .fetchall ()
Se nenhum conjunto de resultados foi obtido para a operação, uma exceção é lançada. Para evitar esse erro, no código acima, usamos uma propriedade que indica se a última operação realizada criou linhas. .fetchall () cursor.with_rows
Embora esse código faça o trabalho, a instrução, como está, é um alvo tentador para os hackers. É vulnerável a um ataque de injeção de SQL que pode permitir que invasores corrompam ou usem indevidamente o banco de dados. ONDE
Por exemplo, se o usuário enviar, e como entrada, o resultado seria assim: movie_id = 18 reviewer_id = 15 ratings = 5.0
$ python modificar_ratings.py
Insira a id do filme: 18
Insira a id do revisor: 15
Insira a nova classificação: 5.0
Insira nome de usuário:
Digite a senha:
[(18, 15, Decimal ('5.0'))]
A pontuação para e também mudou. Mas se você fosse um hacker, poderia enviar um comando oculto para a entrada: movie_id = 18 reviewer_id = 15 5.0
$ python modificar_ratings.py
Insira a id do filme: 18
Digite a id do revisor: 15 ″; ATUALIZAR revisores SET last_name = “A
Insira a nova classificação: 5.0
Insira nome de usuário:
Digite a senha:
[(18, 15, Decimal ('5.0'))]
Novamente, a saída mostra que a classificação relatada foi alterada para 5.0. O que mudou?
O hacker interceptou a solicitação de atualização de dados. Uma solicitação de atualização mudará todos os registros na tabela do revisor: last_name “A”
>>> select_query = “” ”
… SELECT primeiro_nome, sobrenome
… DOS revisores
… “” ”
>>> com connection.cursor () como cursor:
… Cursor.execute (select_query)
… Para revisor em cursor.fetchall ():
… Imprimir (revisor)
...
('Chaitanya', 'A')
('Maria', 'A')
('João', 'A')
('Thomas', 'A')
('Penny', 'A')
('Mitchell', 'A')
('Wyatt', 'A')
('André', 'A')
('Sheldon', 'A')
('Kimbra', 'A')
('Kat', 'A')
('Bruce', 'A')
('Domingo', 'A')
('Rajesh', 'A')
('Ben', 'A')
('Mahinder', 'A')
('Akbar', 'A')
('Howard', 'A')
('Pinkie', 'A')
('Gurkaran', 'A')
('Amy', 'A')
('Marlon', 'A')
O código acima é exibido para todos os registros da tabela de revisores. Um ataque de injeção de SQL corrompeu esta tabela, alterando todos os registros para “A”. first_name last_name last_name
Existe uma solução rápida para evitar esses ataques. Não adicione valores de consulta fornecidos pelo usuário diretamente à string de consulta. Melhor atualizar o script enviando valores de solicitação como argumentos para .execute ()
modify_ratings.py de getpass import getpass de mysql.connector import connect, Erro movie_id = input ("Insira a id do filme:") reviewer_id = input ("Insira a id do revisor:") new_rating = input ("Insira a nova classificação:") update_query = "" "UPDATE avaliações SET rating =% s WHERE movie_id =% s AND reviewer_id =% s; SELECT * FROM avaliações WHERE movie_id =% s AND reviewer_id =% s" "" val_tuple = (new_rating, movie_id, reviewer_id, movie_id, reviewer_id ,) try: with connect (host = "localhost", user = input ("Enter username:"), password = getpass ("Enter password:"), database = "online_movie_rating",) as connection: with connection.cursor ( ) como cursor: para resultado em cursor.execute (update_query, val_tuple, multi = True): if result.with_rows: print (result.fetchall ()) connection.commit () exceto Erro como e: print (e)
Observe que os marcadores de posição não estão mais entre aspas de string. verifica se os valores na tupla fornecidos como um argumento são do tipo de dados necessário. Se o usuário tentar inserir alguns caracteres problemáticos, o código lançará uma exceção:% s cursor.execute ()
SAÍDA
$ python modificar_ratings.py
Insira a id do filme: 18
Digite a id do revisor: 15 ″; ATUALIZAR revisores SET last_name = “A
Insira a nova classificação: 5.0
Insira nome de usuário:
Digite a senha:
1292 (22007): Valor DOUBLE incorreto truncado: '15 ”;
Essa abordagem sempre deve ser usada quando você inclui a entrada do usuário em uma solicitação. Reserve um tempo para aprender sobre outras maneiras de evitar ataques de injeção de SQL.
Excluindo registros: o comando DELETE
O procedimento para excluir registros é muito semelhante ao de atualizá-los. Como esta é uma operação irreversível, recomendamos que você execute primeiro a consulta com o mesmo filtro para garantir que está excluindo os registros desejados. Por exemplo, para remover todas as classificações e dados de filmes, podemos primeiro executar a consulta apropriada: DELETE SELECT reviewer_id = 7 SELECT
select_movies_query = "" "SELECT reviewer_id, movie_id FROM classificação WHERE review_id = 7" "" com connection.cursor () como cursor: cursor.execute (select_movies_query) para filme em cursor.fetchall (): imprimir (filme)
SAÍDA
(2, 7)
(2, 8)
(2, 12)
(2, 23)
O trecho de código acima exibe um par, e para entradas nas estimativas de tabela, para o qual. Depois de nos certificarmos de que esses são os registros a serem excluídos, vamos executar a consulta com o mesmo filtro: reviewer_id movie_id reviewer_id = 2 DELETE
delete_query = "DELETE FROM avaliações WHERE reviewer_id = 2" com connection.cursor () como cursor: cursor.execute (delete_query) connection.commit ()
Outras maneiras de conectar Python e MySQL
Neste tutorial, apresentamos o MySQL Connector / Python, que é o meio oficialmente recomendado de interagir com um banco de dados MySQL a partir de um aplicativo Python. Aqui estão alguns outros conectores populares:
-
mysqlclient é uma biblioteca que compete com o conector oficial e está sendo ativamente complementada com novas funções. Como o núcleo da biblioteca é escrito em C, ele tem melhor desempenho do que o conector Python puro oficial. A grande desvantagem é que o mysqlclient é bastante difícil de configurar e instalar, especialmente no Windows.
-
MySQLdb é um software legado que ainda hoje é usado em aplicativos comerciais. Escrito em C e MySQL Connector / Python mais rápido, mas disponível apenas para Python 2.
Esses drivers atuam como interfaces entre seu programa e o banco de dados MySQL. Na verdade, você apenas envia suas consultas SQL por meio deles. No entanto, muitos desenvolvedores preferem usar o paradigma orientado a objetos para gerenciamento de dados, não consultas SQL.
Mapeamento objeto-relacional (ORM) é um processo que permite não só a consulta, mas também a manipulação de dados de um banco de dados diretamente usandoOOPs. A biblioteca ORM encapsula o código necessário para manipular dados, liberando os desenvolvedores da necessidade de usar consultas SQL. Aqui estão as bibliotecas ORM mais populares para combinar Python e SQL:
-
SQLAlchemy é um ORM que simplifica a comunicação entre Python e outros bancos de dados SQL. Você pode criar diferentes mecanismos para diferentes bancos de dados como MySQL, PostgreSQL, SQLite, etc.
-
peewee é uma biblioteca ORM leve e rápida com uma configuração simples, o que é muito útil quando sua interação com o banco de dados se limita a buscar alguns registros. Se você precisar copiar registros individuais de um banco de dados MySQL para um arquivo CSV, peewee é a melhor escolha.
-
O Django ORM é uma das partes mais poderosas da estrutura da Web do Django, permitindo que você interaja facilmente com uma variedade de bancos de dados SQLite, PostgreSQL e MySQL. Muitos aplicativos baseados em Django usam o Django ORM para modelagem de dados e consultas básicas, entretanto, para tarefas mais complexas, os desenvolvedores geralmente usam SQLAlchemy.
Conclusão
Neste tutorial, demos uma olhada em como integrar um banco de dados MySQL em seu aplicativo Python. Também desenvolvemos um exemplo de teste do banco de dados MySQL e interagimos com ele diretamente do código Python. Python tem conectores para outros DBMSs, como MongoDB e PostgreSQL. Teremos o maior prazer em saber em que outros materiais sobre Python e bancos de dados você estaria interessado.
As mídias mostradas neste artigo não são propriedade da Analytics Vidhya e são usadas a critério do autor.
Relacionado
- '
- "
- 100
- 11
- 2016
- 2019
- 7
- 9
- Acesso
- Conta
- Açao Social
- ativo
- Adicional
- Aventura
- Força Aérea
- Todos os Produtos
- Permitindo
- análise
- analítica
- animação
- Aplicação
- aplicações
- argumentos
- artigo
- Ataques
- MELHOR
- Preto
- Caixa
- bilheteria
- chamada
- Cuidado
- luta
- desafiar
- alterar
- criança
- código
- Coluna
- Comédia
- comercial
- Comunicação
- comunidade
- componente
- confusão
- Coneções
- Casal
- Criar
- Credenciais
- Crime
- dados,
- análise de dados
- gestão de dados
- banco de dados
- bases de dados
- Datas
- morto
- detalhe
- desenvolver
- desenvolvedores
- Django
- Estivador
- dólares
- Drama
- motorista
- Cair
- Meio Ambiente
- estimativas
- etc.
- execução
- RÁPIDO
- Característica
- Funcionalidades
- Taxas
- Ficção
- Figura
- Filme
- filmes
- Finalmente
- Primeiro nome
- formulário
- formato
- Quadro
- função
- Geral
- Bom estado, com sinais de uso
- agarrar
- Grupo
- guia
- cabouqueiro
- hackers
- Manipulação
- acessível
- SUA PARTICIPAÇÃO FAZ A DIFERENÇA
- Esconder
- Início
- Como funciona o dobrador de carta de canal
- Como Negociar
- HTTPS
- Incluindo
- INFORMAÇÕES
- interação
- isolamento
- IT
- Trabalho
- juntar
- Chave
- chaves
- Rei
- Conhecimento
- língua
- Idiomas
- grande
- conduzir
- APRENDER
- Biblioteca
- Limitado
- LINK
- linux
- Lista
- MacOS
- principal
- Fazendo
- de grupos
- Manipulação
- mercado
- Líder de mercado
- materiais
- Mídia
- milhão
- MongoDB
- Mais populares
- filme
- Filmes
- nomes
- Netflix
- Oferece
- oficial
- compensar
- aberto
- código-fonte aberto
- operando
- sistema operativo
- sistemas operacionais
- Operações
- oráculo
- ordem
- Outros
- paradigma
- Senha
- Pessoas
- atuação
- Popular
- Agenda
- Programação
- linguagens de programação
- projeto
- propriedade
- Python
- alcance
- avaliações
- razões
- registros
- Relacionamentos
- Recursos
- Resultados
- Retorna
- rever
- Opinões
- Risco
- Rolo
- Execute
- corrida
- Ciência
- segurança
- conjunto
- contexto
- simples
- pequeno
- So
- Software
- Solaris
- Soluções
- Espaço
- Desporto
- SQL
- Injeção de SQL
- padrões
- começo
- começado
- Declaração
- armazenamento
- sucesso
- luz do sol
- completar
- ajuda
- .
- sistemas
- Target
- tecnologia
- teste
- tempo
- brinquedo
- transação
- Transações
- tutorial
- Atualizar
- us
- usuários
- valor
- Ver
- Virtual
- Vulnerável
- guerra
- web
- QUEM
- Windows
- dentro
- Atividades:
- trabalho
- ano