def drop_email():
query = """
DROP TABLE IF EXISTS emails
"""
with get_connection() as connection:
try:
cursor = connection.cursor()
cursor.execute(query)
except ProgrammingError as e:
print(f'Erro ao executar script DROP - DDL. Error:{e.msg}')
def update_contatos(data):
with get_connection() as connection:
sql = 'UPDATE contatos SET grupo_id= %s WHERE id = %s'
try:
cursor = connection.cursor()
cursor.execute(sql, data)
connection.commit()
except ProgrammingError as e:
print(f'Erro ao fazer updade do contato {e.msg}')
else:
print(f'{cursor.rowcount} Registro(s) atualizado(s)')
def update_contato_by_id(contato):
with get_connection() as connection:
try:
sql = 'UPDATE contatos SET nome = %s, tel = %s WHERE id = %s'
cursor = connection.cursor()
cursor.execute(sql, contato)
connection.commit()
except ProgrammingError as e:
print(f'Erro ao atualizar contato por id {e.msg}')
else:
print(f'{cursor.rowcount} registro(s) atualidado(s).')
def delete_contato_by_id(id):
with get_connection() as connection:
try:
sql = 'DELETE FROM contatos WHERE id = %s'
cursor = connection.cursor()
cursor.execute(sql, (id, ))
connection.commit()
except ProgrammingError as e:
print(f'Erro ao deletar contato por id {e.msg}')
else:
print(f'{cursor.rowcount} registro(s) deletad(s).')
def include_grupos():
sql = 'INSERT INTO grupos (descricao) VALUES (%s)'
params = (('Trabalho', ), ('Inglês', ), ('Python', ))
with get_connection() as connection:
try:
cursor = connection.cursor()
cursor.executemany(sql, params)
connection.commit()
except ProgrammingError as e:
print(f'Erro ao inserir grupos: {e.msg}')
else:
print(f'{cursor.rowcount} Registros incluidos')
def include_contato():
sql = 'INSERT INTO contatos (nome, tel) VALUES (%s, %s)'
params = ('Tiaozinho', '98987-7845')
with get_connection() as connection:
try:
cursor = connection.cursor()
cursor.execute(sql, params)
connection.commit()
except ProgrammingError as e:
print(f'Erro ao inserir contatos: {e.msg}')
else:
print(f'Registro incluido, ID: {cursor.lastrowid}')
def alter_table():
query = """
ALTER TABLE contatos ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY
"""
tables = []
with get_connection() as connection:
try:
cursor = connection.cursor()
cursor.execute(query)
for table in enumerate(cursor):
tables.append(table[1])
except ProgrammingError as e:
print(f'Erro ao executar script ALTER - DDL. Error:{e.msg}')
def select_contatos():
contatos = []
with get_connection() as connection:
try:
sql = 'SELECT id, nome, tel FROM contatos ORDER BY nome'
cursor = connection.cursor()
cursor.execute(sql)
for response in cursor.fetchall():
contatos.append(Contato(*response))
except ProgrammingError as e:
print(f'Erro ao buscar contatos {e.msg}')
for contato in contatos:
print(contato)
def select_contato_by_name(name):
contatos = []
with get_connection() as connection:
try:
sql = "SELECT id, nome, tel FROM contatos WHERE nome like %s "
params = (f'%{name}%', )
cursor = connection.cursor()
cursor.execute(sql, params)
for response in cursor.fetchall():
contatos.append(Contato(*response))
except ProgrammingError as e:
print(f'Erro ao buscar contato por nome {e.msg}')
for contato in contatos:
print(contato)
def show_tables():
query = """
SHOW TABLES
"""
tables = []
with get_connection() as connection:
try:
cursor = connection.cursor()
cursor.execute(query)
for table in enumerate(cursor):
tables.append(table[1])
except ProgrammingError as e:
print(f'Erro ao executar script DROP - DDL. Error:{e.msg}')
print(tables)
def select_contatos_limit_offset():
contatos = []
with get_connection() as connection:
try:
sql = 'SELECT id, nome, tel FROM contatos LIMIT %s OFFSET %s'
params = (3, 5)
cursor = connection.cursor()
cursor.execute(sql, params)
for response in cursor.fetchall():
contatos.append(Contato(*response))
except ProgrammingError as e:
print(f'Erro ao buscar contatos {e.msg}')
for contato in contatos:
print(contato)
def select_contato_by_id(id):
contato = None
with get_connection() as connection:
try:
sql = 'SELECT id, nome, tel FROM contatos WHERE id = %s'
# sql = 'SELECT id, nome, tel FROM contatos WHERE id = {}'.format(id) # pode causar um sql injection
# cursor.execute(sql)
cursor = connection.cursor()
cursor.execute(sql, (id, ))
response = cursor.fetchone()
contato = Contato(*response)
except ProgrammingError as e:
print(f'Erro ao buscar contato por id {e.msg}')
print(contato)
def create_table():
table_contatos = """
CREATE TABLE IF NOT EXISTS contatos(
nome VARCHAR(50), tel VARCHAR(40)
)
"""
table_emails = """
CREATE TABLE IF NOT EXISTS emails(
id INT AUTO_INCREMENT PRIMARY KEY,
dono VARCHAR(50)
)
"""
with get_connection() as connection:
try:
cursor = connection.cursor()
cursor.execute(table_contatos)
cursor.execute(table_emails)
except ProgrammingError as e:
print(f'Erro ao executar script de DDL. Error:{e.msg}')
def include_contato():
sql = 'INSERT INTO contatos (nome, tel) VALUES (%s, %s)'
params = (
('Jose', '9877-7845'),
('Ana', '9877-9045'),
('Castilho', '9877-7775'),
('Mario', '9877-7215'),
('Ze', '9877-3545'),
('João', '9877-7455'),
('Bia', '9877-78574'),
('Carlos', '9877-45415'),
)
with get_connection() as connection:
try:
cursor = connection.cursor()
cursor.executemany(sql, params)
connection.commit()
except ProgrammingError as e:
print(f'Erro ao inserir contatos: {e.msg}')
else:
print(f'{cursor.rowcount} Registros incluidos')
def create_table():
table_grupos = """
CREATE TABLE IF NOT EXISTS grupos(
id INT AUTO_INCREMENT PRIMARY KEY,
descricao VARCHAR(50)
)
"""
alter_contato_add_column = """
ALTER TABLE contatos ADD COLUMN IF NOT EXISTS grupo_id INT
"""
alter_contato = """
ALTER TABLE contatos ADD FOREIGN KEY IF NOT EXISTS (grupo_id) REFERENCES grupos(id)
"""
with get_connection() as connection:
try:
cursor = connection.cursor()
cursor.execute(table_grupos)
cursor.execute(alter_contato_add_column)
cursor.execute(alter_contato)
except ProgrammingError as e:
print(f'Erro ao executar script de DDL. Error:{e.msg}')
#!/usr/bin/python3
# coding: utf-8
from config_db import get_connection
with get_connection() as connection:
if connection.is_connected():
print('Conectado')
